22

PHPExcel $cell->getColumn() returns 'A', 'B', 'C', ...

which is the best way to get the integer (0, 1, 2, ...) from the cell.

This function doesn't exist.

$colIndex = $cell->getColumnIndex();

So what is the alternative withoput converting chr to ascii ?

john Griffiths
  • 247
  • 1
  • 2
  • 5

3 Answers3

47
$colIndex = PHPExcel_Cell::columnIndexFromString($cell->getColumn());
Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • 6
    OK works fine, I notice that the value returned is 1 for column A but $cell = "worksheet->getCellByColumnAndRow($col, $row); uses 0 for column A go figure – john Griffiths Aug 11 '10 at 16:17
  • 2
    Yup, quirky... something I inherited when I took over the development of PHPExcel, but changing it would break backward compatibility for a lot of people – Mark Baker Aug 11 '10 at 16:22
  • 5
    There is also PHPExcel_Cell::stringFromColumnIndex for those who need to go the other way. – Reactgular Nov 29 '12 at 03:18
  • A great answer can be found [here](http://stackoverflow.com/questions/4564155/how-to-get-the-number-of-columns-of-worksheet-as-integer-28-instead-of-excel-l) - helped me a lot - – t1gor Jan 09 '14 at 18:45
  • @MarkBaker - This question , answers and comments were made a long time ago, but if there arent any solution to this yet? It's a not a big issue but kind of annyoing. You could consider implementing which index to start the column on and what return value that should be used (e.g. $PHPExcel->setStartColumnIndex(0); $PHPExcel->setReturnValue(string) or $PHPExcel->setReturnValue(int) etc etc). Just a tip! Else this library is AWESOME :-) – bestprogrammerintheworld Apr 30 '17 at 05:55
  • 1
    There is a solution.... if you want zero-indexed, then subtract 1 from the returned value.... but it is never a good idea to change a library behaviour to return a zero in this case, when users already have their code written to work with a 1-indexed return, and who will then find their previously working code suddenly broken – Mark Baker Apr 30 '17 at 10:20
5

You can get column index while iterate.

$xls = PHPExcel_IOFactory::load($fn);
$xls->setActiveSheetIndex(0);
$sheet = $xls->getActiveSheet();

foreach($sheet->getRowIterator() as $row)
{
    foreach($row->getCellIterator() as $key => $cell)
    {
        echo $key; // 0, 1, 2...
        echo $cell->getCalculatedValue(); // Value here
    }
}
Shad
  • 63
  • 1
  • 4
2
If you want to get decrement cell address using this function, you have to use another function with this function as follows.

<?php
echo columnLetter("AB");

function columnLetter($c){


$letter="";
    $c = intval(columnNumber($c));
    if ($c<=0) return '';

    while($c != 0){
       $p = ($c - 1) % 26;
       $c = intval(($c - $p) / 26);
       $letter = chr(65 + $p) . $letter;
    }

    return $letter;

}

function columnNumber($col){

    $col = str_pad($col,2,'0',STR_PAD_LEFT);
    $i = ($col{0} == '0') ? 0 : (ord($col{0}) - 64) * 26;
    $i += ord($col{1}) - 64;

    return $i-1;

}
?>
Sarath Wijeshinghe
  • 21
  • 2