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

Answer 2

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
        }
    }
}

Answer 3

loop columns as range

foreach ( range('A', $Excel->getActiveSheet()->getHighestColumn()) as $column_key) {

}

Answer 4

Try this! Work!

$limit = 10000;
$value='sua mensagem'
for($i=0,$j='A';$i<$limit;$i++,$j++) {
    $objPHPExcel->setActiveSheetIndex(0)
  ->setCellValue($j.$i, $value);
}

set in $value what you want print.

Answer 5

This is the fix for the getColumnLetter method of the last message, it allows you to get the "letters" of the columns, whatever the number of columns you have

function getColumnLetter( $number ){
    $prefix = '';
    $suffix = '';
    $prefNum = intval( $number/26 );
    if( $number > 25 ){
        $prefix = getColumnLetter( $prefNum - 1 );
    }
    $suffix = chr( fmod( $number, 26 )+65 );
    return $prefix.$suffix;
}

Answer 6

This recursive method was designed allows you to get the "letters" of the columns, whatever the number of columns you have, from "A" to "ZZZZZZZZZ..." :

function getColumnLetter( $number )
{
    $prefix = '';
    $suffix = '';
    $prefNum = intval( $number/26 );
    if( $prefNum > 25 )
    {
        $prefix = getColumnLetter( $prefNum );
    }
    $suffix = chr( fmod( $number, 26 )+65 );
    return $prefix.$suffix;
}

So you can loop the columns of an PHP array on index number and convert it to a string of letters and use it in PHPExcel methods, like "setCellValue" ... But solutions above are surely faster if you can use them !

Answer 7

In PhpSpreadsheet ( which replaces PHPExcel ) you can do:

        foreach( $sheet->getColumnIterator() as $col )
        {
            $sheet->getColumnDimension( $col->getColumnIndex() )->setAutosize( true );
        }