Looping through worksheets with PHPExcel

Question

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";
?>

score 16 · Answer 1 · answered Jul 23 '12 at 17:12

16

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++;

    }

    ...

?>

answered Jul 23 '12 at 17:12

jeffery_the_wind

17,048
34
98
160

Interesting approach. Thanks for that answer. – Yitzhak May 19 '15 at 15:23
1

you needs try ... catch construction, because last page throws exception – Andrey Vorobyev Jun 11 '15 at 13:00
6

if($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 – Ardit Meti Feb 25 '16 at 13:42

score 13 · Accepted Answer · answered Jul 23 '12 at 17:09

13

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

answered Jul 23 '12 at 17:09

Mark Baker

209,507
32
346
385

Thank you very much! I did find the iterator in the Test directory -- 28iterator.php. getAllSheets() looks good, too. Thank you. – user717236 Jul 23 '12 at 18:58

score 11 · Answer 3 · answered Nov 18 '15 at 15:16

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());
    }
}

Looping through worksheets with PHPExcel

3 Answers3

Linked