php - How to get the number of columns of worksheet as integer (28) instead of Excel-letters ("AB")?

Question

Given:

$this->objPHPExcelReader = PHPExcel_IOFactory::createReaderForFile($this->config['file']);
$this->objPHPExcelReader->setLoadSheetsOnly(array($this->config['worksheet']));
$this->objPHPExcelReader->setReadDataOnly(true);
$this->objPHPExcel = $this->objPHPExcelReader->load($this->config['file']);

I can iterate through the rows like this but it is very slow, i.e. in a 3MB Excel file with a worksheet that has "EL" columns, it takes about 1 second per row:

foreach ($this->objPHPExcel->setActiveSheetIndex(0)->getRowIterator() as $row)
{
    $dataset = array();
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(false);
    foreach ($cellIterator as $cell)
    {
        if (!is_null($cell))
        {
            $dataset[] = $cell->getCalculatedValue();
        }
    }
    $this->datasets[] = $dataset;
}

When I iterate like this, it it significantly faster (approx. 2000 rows in 30 seconds), but I will have to convert the letters e.g. "EL" to a number:

$highestColumm = $this->objPHPExcel->setActiveSheetIndex(0)->getHighestColumn(); // e.g. "EL"
$highestRow = $this->objPHPExcel->setActiveSheetIndex(0)->getHighestRow();

$number_of_columns = 150; // TODO: figure out how to get the number of cols as int
for ($row = 1; $row < $highestRow + 1; $row++) {
    $dataset = array();
    for ($column = 0; $column < $number_of_columns; $column++) {
        $dataset[] = $this->objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($column, $row)->getValue();
    }
    $this->datasets[] = $dataset;
}

Is there a way to get the highest column as an integer (e.g. "28") instead of in Excel-styled letters (e.g. "AB")?

Answer 1

$colNumber = PHPExcel_Cell::columnIndexFromString($colString);

returns 1 from a $colString of 'A', 26 from 'Z', 27 from 'AA', etc.

and the (almost) reverse

$colString = PHPExcel_Cell::stringFromColumnIndex($colNumber);

returns 'A' from a $colNumber of 0, 'Z' from 25, 'AA' from 26, etc.

EDIT

A couple of useful tricks:

There is a toArray() method for the worksheet class:

$this->datasets = $this->objPHPExcel->setActiveSheetIndex(0)->toArray();

which accepts the following parameters:

* @param  mixed    $nullValue          Value returned in the array entry if a cell doesn't exist
* @param  boolean  $calculateFormulas  Should formulas be calculated?
* @param  boolean  $formatData         Should formatting be applied to cell values?
* @param  boolean  $returnCellRef      False - Return a simple array of rows and columns indexed by number counting from zero
*                                      True - Return rows and columns indexed by their actual row and column IDs

although it does use the iterators, so would be slightly slower

OR

Take advantage of PHP's ability to increment character strings Perl Style

$highestColumm = $this->objPHPExcel->setActiveSheetIndex(0)->getHighestColumn(); // e.g. "EL" 
$highestRow = $this->objPHPExcel->setActiveSheetIndex(0)->getHighestRow();  

$highestColumm++;
for ($row = 1; $row < $highestRow + 1; $row++) {     
    $dataset = array();     
    for ($column = 'A'; $column != $highestColumm; $column++) {
        $dataset[] = $this->objPHPExcel->setActiveSheetIndex(0)->getCell($column . $row)->getValue();
    }
    $this->datasets[] = $dataset;
}

and if you're processing a large number of rows, you might actually notice the performance improvement of ++$row over $row++