Looping through worksheets with PHPExcel
Solution 1
You're using iterators. Did you look at the code example for iterators in the /Tests directory? If so, you might have seen reference to the WorksheetIterator
Alternatively, the getAllSheets() method of the PHPExcel object returns an array of worksheets, which allows you to use a foreach loop
Solution 2
I think you can do this. Increment the active sheet until there aren't any left, then do what you want with each one:
<?php
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load("test.xlsx");
$i = 0;
while ($objPHPExcel->setActiveSheetIndex($i)){
$objWorksheet = $objPHPExcel->getActiveSheet();
//now do whatever you want with the active sheet
...
$i++;
}
...
?>
Solution 3
Here's a useful function I use for iterating over sheets and returning an array of cell values for each with the sheet title as array key:
function getSheets($fileName) {
try {
$fileType = PHPExcel_IOFactory::identify($fileName);
$objReader = PHPExcel_IOFactory::createReader($fileType);
$objPHPExcel = $objReader->load($fileName);
$sheets = [];
foreach ($objPHPExcel->getAllSheets() as $sheet) {
$sheets[$sheet->getTitle()] = $sheet->toArray();
}
return $sheets;
} catch (Exception $e) {
die($e->getMessage());
}
}
user717236
Updated on July 05, 2022Comments
-
user717236 almost 2 years
I'm using the PHPExcel library to read an Excel file and perform processing on it. I want to loop through each worksheet. I checked the documentation and all I could find was changing the active worksheet index or only loading specified worksheets. How can I loop through all worksheets?
Thank you for any help.
Here is the documentation's looping example, for reference:
<?php $objReader = PHPExcel_IOFactory::createReader('Excel2007'); $objReader->setReadDataOnly(true); $objPHPExcel = $objReader->load("test.xlsx"); $objWorksheet = $objPHPExcel->getActiveSheet(); echo '<table>' . "\n"; foreach ($objWorksheet->getRowIterator() as $row) { echo '<tr>' . "\n"; $cellIterator = $row->getCellIterator(); $cellIterator->setIterateOnlyExistingCells(false); // This loops all cells, // even if it is not set. // By default, only cells // that are set will be // iterated. foreach ($cellIterator as $cell) { echo '<td>' . $cell->getValue() . '</td>' . "\n"; } echo '</tr>' . "\n"; } echo '</table>' . "\n"; ?>
-
user717236 almost 12 yearsThank you very much! I did find the iterator in the Test directory -- 28iterator.php. getAllSheets() looks good, too. Thank you.
-
Yitzhak almost 9 yearsInteresting approach. Thanks for that answer.
-
Andrey Vorobyev almost 9 yearsyou needs try ... catch construction, because last page throws exception
-
Ardit Meti about 8 yearsif($i <$objPHPExcel->getSheetCount()-1 ) $i++; else break; add this in the end of the loop because will give you error finding non-existing last sheet