How can I loop through G1 to AJ5

Mar 7, 2014 at 12:28 AM
Hello,

I am trying to loop from G1 to AJ5. If I write a foreach loop:
foreach (range('G', 'AJ') as $column) {
        for ($row = 1; $row <= 5; $row++) {
            $cellIndex = $column.$row;
   }
}

AJ will be recognized as A instead...
Mar 7, 2014 at 1:03 AM
Edited Mar 7, 2014 at 2:31 AM
PHPs range() function doesn't work like that. It can do single letters since they can be indexed as ascii codes.

You could do range(getColumnIndex('G'), getColumnIndex('AJ')) and then reverse the index reference inside the loop if you needed it.

Note: getColumnIndex() is just a generic function call as an example. You'll have to find the proper one from PHPExcel or make your own.

-- Christopher Mullins
Coordinator
Mar 7, 2014 at 8:02 AM
Edited Mar 7, 2014 at 8:08 AM
    $firstColumn = 'G';
    $lastColumn = 'AJ';

    $lastColumn++;
    for($column = $firstColumn; $column !== $lastColumn; $column++) {
        ... do stuff
    }
or if you're using PHP 5.5, you can use Generators:
    function excelColumnRange($lower, $upper) {
        ++$upper;
        for ($i = $lower; $i != $upper; ++$i) {
            yield $i;
        }
    }

    foreach (excelColumnRange('G', 'AJ') as $column) {
        ... do stuff
    }
Mar 7, 2014 at 5:28 PM
Thank you guys for the reply. Here is the solution:
    for ($column = 'G'; $column != 'AJ'; $column++) {

        for ($row = 1; $row <= $endRow; $row++) {
            $cellIndex = $column.$row;
                    }
            }
Mar 14, 2014 at 10:29 AM
Hello! you might have already solved this, however i have a function that lets you handle any excel column letter as numbers. You might find it useful, i use it to loop though files that need a diferent amount of labels created every time...

Here it is:
//This function is for converting Numbers to Letters that are compatible with Excel, and Excel writter for cell selection
function num_to_letter($num, $uppercase = TRUE)
{
    $num -= 1;
    $letter =   chr(($num % 26) + 97);
    if ($num >= 26) {
        $letter = num_to_letter(floor($num/26),$uppercase).$letter;
    }
    return      ($uppercase ? strtoupper($letter) : $letter); 
}
Coordinator
Mar 14, 2014 at 11:48 AM
Why not simply use the built in PHPExcel_Cell::columnIndexFromString() and PHPExcel_Cell::stringFromColumnIndex() methods?
Mar 20, 2014 at 9:12 PM
Hi, I use thisd code for columns:
$lastColumn = 90;
for ($i=0;$i<$lastColumn;$i++) {
$column = chr(65+$i);
}
Now you can refer to columns with the $column variable
Coordinator
Mar 20, 2014 at 11:42 PM
As I said, why not simply use the built in PHPExcel_Cell::columnIndexFromString() and PHPExcel_Cell::stringFromColumnIndex() methods? These aren't limited to the range A-Z, or to the range A-ZZ, but support the full MS Excel range of columns.