PHPExcel Column Loop
This short snippet provides loop throught rows of columns. It gets the indexes of last non empty column (and its row) and loops to that indexes, so be aware of forgotten values in excel.
Code loops throught rows of column A, then rows of column B ...
$objReader = new PHPExcel_Reader_Excel2007();
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($file);
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet)
{
$worksheetTitle = $worksheet->getTitle();
$highestColumn = $worksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
// expects same number of row records for all columns
$highestRow = $worksheet->getHighestRow();
for($col = 0; $col < $highestColumnIndex; $col++)
{
// if you do not expect same number of row records for all columns
// get highest row index for each column
// $highestRow = $worksheet->getHighestRow();
for ($row = 1; $row <= $highestRow; $row++)
{
$cell = $worksheet->getCellByColumnAndRow($col, $row);
$val = $cell->getValue();
// do what you want with cell value
}
}
}
loop columns as range
foreach ( range('A', $Excel->getActiveSheet()->getHighestColumn()) as $column_key) {
}
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