Identifying worksheet index while Looping through worksheets, via getWorksheetIterator(), with PHPExcel

user717236 picture user717236 · Jul 24, 2012 · Viewed 10.9k times · Source

I'm using the PHPExcel library to read an Excel file with many worksheets and perform processing on it. Now I have no problem looping through each worksheet. However, I want to extract the index of each worksheet and print it out and I'm not sure how to do this. Sure, I can make my own counter and be done with it. But there must be a built-in method with the getWorksheetIterator() method, no?

That is, how do I extract the current worksheet iterator?

Thank you for any help.

Here is the documentation's looping example, for reference, slightly modified with the inclusion of the worksheetiterator method:

<?php
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);

$objPHPExcel = $objReader->load("test.xlsx");
$objWorksheet = $objPHPExcel->getActiveSheet();

$CurrentWorkSheetIndex = 0; 

foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
    echo 'WorkSheet' . $CurrentWorkSheetIndex++ . "\n";
    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";
}
?>

Answer

Mark Baker picture Mark Baker · Jul 24, 2012

The WorksheetIterator loops through each worksheet in turn, so the first is index 0, the second is index 1, the third is index 2, etc.

So either increment your $CurrentWorkSheetIndex for each loop of the iterator.

$CurrentWorkSheetIndex = 0;   

foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
    $index = $CurrentWorkSheetIndex;
    ....
    $CurrentWorkSheetIndex++;
}

Or use the Iterator's key() method.

$wsIterator = $objPHPExcelR->getWorksheetIterator();
foreach($wsIterator as $worksheet) {
    $index = $wsIterator->key();
    ....
}