php - PHPExcel Column Loop

Question

How can I do a loop which based on Excel worksheet columns? I found (and used) WorksheetIterator, RowIterator and CellIterator but nothing about columns.

Answer 1

There is no ColumnIterator, so you'll have to do this by hand.

For any given worksheet:

To loop rows for a column:

$column = 'A';
$lastRow = $worksheet->getHighestRow();
for ($row = 1; $row <= $lastRow; $row++) {
    $cell = $worksheet->getCell($column.$row);
    //  Do what you want with the cell
}

To loop columns in a row, you can take advantage of PHP's Perls-style ability to increment characters:

$row = 1;
$lastColumn = $worksheet->getHighestColumn();
$lastColumn++;
for ($column = 'A'; $column != $lastColumn; $column++) {
    $cell = $worksheet->getCell($column.$row);
    //  Do what you want with the cell
}

Note that when comparing column letters to test for the last column in the loop, we can't simply use < or <= because we're comparing strings, and "B" > "AZ" in standard string comparison, so we use a != comparison, having incremented the highest column value to give the first column ID past the end point.

You can also use

$worksheet->cellExists($column.$row);

in the loop to test for the existence of a cell before accessing it using getCell() (or not) to emulate the iterator getIterateOnlyExistingCells() behaviour

The iterators are actually fairly slow, so you may well find these simple loops are faster than using the iterators.

UPDATE (2015-05-06)

PHPExcel version 1.8.1 has introduced a new Column Iterator. The Row and Column iterators also allows you to specify a range of rows or columns to iterate, and allow you to use prev() and well as next() when looping through