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

Answer 2

This is a somewhat simplified version of dqhendricks answer. I have added to copies, one function assuming you enter the full excel cell reference (ie. "AB12") and the other assuming you enter just the column reference (ie. "AB"). They both return a zero based index.

Input Full Cell Reference

function getIndex ($cell) {
    // Strip cell reference down to just letters
    $let = preg_replace('/[^A-Z]/', '', $cell);

    // Iterate through each letter, starting at the back to increment the value
    for ($num = 0, $i = 0; $let != ''; $let = substr($let, 0, -1), $i++)
        $num += (ord(substr($let, -1)) - 65) * pow(26, $i);

    return $num;
}

Input Column Reference Only

function getIndex ($let) {
    // Iterate through each letter, starting at the back to increment the value
    for ($num = 0, $i = 0; $let != ''; $let = substr($let, 0, -1), $i++)
        $num += (ord(substr($let, -1)) - 65) * pow(26, $i);

    return $num;
}

The function goes from the back of the string to the front to increase the value of the column. It uses the ord() function to get the numeric value of a character and then has the letter value subtracted to give the local column value. Finally it is multiplied by the current power of 26.