Advertisement
Guest User

Untitled

a guest
Sep 3rd, 2015
155
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 5.11 KB | None | 0 0
  1. $objReader = PHPExcel_IOFactory::createReaderForFile("data/" . $file_name);
  2. $objReader->setLoadSheetsOnly(array($sheet_name));
  3. $objReader->setReadDataOnly(true);
  4. $objPHPExcel = $objReader->load("data/" . $file_name);
  5.  
  6. echo '<table border="1">';
  7. for ($row = 1; $row < $number_of_rows; $row++) {
  8. echo '<tr>';
  9. for ($column = 0; $column < $number_of_columns; $column++) {
  10. $value = $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($column, $row)->getValue();
  11. echo '<td>';
  12. echo $value . '&nbsp;';
  13. echo '</td>';
  14. }
  15. echo '</tr>';
  16. }
  17. echo '</table>';
  18. die;
  19.  
  20. $objReader = PHPExcel_IOFactory::createReaderForFile("data/" . $file_name);
  21. $objReader->setLoadSheetsOnly(0);
  22. $objReader->setReadDataOnly(true);
  23. $objPHPExcel = $objReader->load("data/" . $file_name);
  24.  
  25. echo $objPHPExcel->getSheetCount(), ' worksheets<hr/>';
  26. $loadedSheetNames = $objPHPExcel->getSheetNames();
  27. foreach ($loadedSheetNames as $sheetIndex => $loadedSheetName) {
  28. echo $sheetIndex, ' -> ', $loadedSheetName, '<br />';
  29. }
  30. die;
  31.  
  32. // check if sheet should be skipped
  33. if (isset($this->_loadSheetsOnly) && !in_array($sheet['name'], $this->_loadSheetsOnly)) {
  34. continue;
  35. }
  36.  
  37. /**
  38. * Reads names of the worksheets from a file, without loading the whole file to a PHPExcel object
  39. *
  40. * @param string $pFilename
  41. * @throws Exception
  42. */
  43. public function listWorksheetNames($pFilename)
  44. {
  45. // Check if file exists
  46. if (!file_exists($pFilename)) {
  47. throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
  48. }
  49.  
  50. $worksheetNames = array();
  51.  
  52. $zip = new ZipArchive;
  53. $zip->open($pFilename);
  54.  
  55. $rels = simplexml_load_string($this->_getFromZipArchive($zip, "_rels/.rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships");
  56. foreach ($rels->Relationship as $rel) {
  57. switch ($rel["Type"]) {
  58. case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument":
  59. $xmlWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
  60.  
  61. if ($xmlWorkbook->sheets) {
  62. foreach ($xmlWorkbook->sheets->sheet as $eleSheet) {
  63. // Check if sheet should be skipped
  64. $worksheetNames[] = (string) $eleSheet["name"];
  65. }
  66. }
  67. }
  68. }
  69.  
  70. $zip->close();
  71.  
  72. return $worksheetNames;
  73. }
  74.  
  75. $inputFileType = 'Excel2007';
  76. $inputFileName = 'biostat-behfisk-2005.xlsx';
  77. $objReader = PHPExcel_IOFactory::createReader($inputFileType);
  78. $worksheetNames = $objReader->listWorksheetNames($inputFileName);
  79.  
  80. foreach ($worksheetNames as $sheetName) {
  81. echo $sheetName, '<br />';
  82. }
  83.  
  84. /**
  85. * Reads names of the worksheets from a file, without loading the whole file to a PHPExcel object
  86. *
  87. * @param string $pFilename
  88. * @throws Exception
  89. */
  90. public function listWorksheetNames($pFilename)
  91. {
  92. // Check if file exists
  93. if (!file_exists($pFilename)) {
  94. throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
  95. }
  96.  
  97. $worksheetNames = array();
  98.  
  99. // Read the OLE file
  100. $this->_loadOLE($pFilename);
  101.  
  102. // total byte size of Excel data (workbook global substream + sheet substreams)
  103. $this->_dataSize = strlen($this->_data);
  104.  
  105. $this->_pos = 0;
  106. $this->_sheets = array();
  107.  
  108. // Parse Workbook Global Substream
  109. while ($this->_pos < $this->_dataSize) {
  110. $code = self::_GetInt2d($this->_data, $this->_pos);
  111.  
  112. switch ($code) {
  113. case self::XLS_Type_BOF: $this->_readBof(); break;
  114. case self::XLS_Type_SHEET: $this->_readSheet(); break;
  115. case self::XLS_Type_EOF: $this->_readDefault(); break 2;
  116. default: $this->_readDefault(); break;
  117. }
  118. }
  119.  
  120. foreach ($this->_sheets as $sheet) {
  121. if ($sheet['sheetType'] != 0x00) {
  122. // 0x00: Worksheet, 0x02: Chart, 0x06: Visual Basic module
  123. continue;
  124. }
  125.  
  126. $worksheetNames[] = $sheet['name'];
  127. }
  128.  
  129. return $worksheetNames;
  130. }
  131.  
  132. public function getWorksheetNames($pFilename) {
  133.  
  134. $worksheetNames = array ();
  135.  
  136. $zip = zip_open ( $pFilename );
  137. while ( $entry = zip_read ( $zip ) ) {
  138.  
  139. $entry_name = zip_entry_name ( $entry );
  140. if ($entry_name == 'xl/workbook.xml') {
  141. if (zip_entry_open ( $zip, $entry, "r" )) {
  142. $buf = zip_entry_read ( $entry, zip_entry_filesize ( $entry ) );
  143. $workbook = simplexml_load_string ( $buf );
  144. foreach ( $workbook->sheets as $sheets ) {
  145. foreach( $sheets as $sheet) {
  146. $attributes=$sheet->attributes();
  147. $worksheetNames[]=$attributes['name'];
  148. }
  149. }
  150. zip_entry_close ( $entry );
  151. }
  152. break;
  153. }
  154.  
  155. }
  156. zip_close ( $zip );
  157. return $worksheetNames;
  158. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement