PHPExcel loop through rows and columns

Question

Need help identifying weird problem that i'm facing. I did tried searching in stack overflow but didn't find any possible answer.

Here is sample program that works displaying all rows and columns on UI

<?php

date_default_timezone_set('America/Los_Angeles');
require_once 'PHPExcel-1.8/Classes/PHPExcel.php';
include 'PHPExcel-1.8/Classes/PHPExcel/IOFactory.php';

$path = 'demo.xlsx';

$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
 $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
for ($row = 2; $row <= $highestRow; ++ $row) {
    $val=array();
for ($col = 0; $col < $highestColumnIndex; ++ $col) {
   $cell = $worksheet->getCellByColumnAndRow($col, $row);
   $val[] = $cell->getValue();
 //End of For loop   
}

$Col1 = $val[0] ;
$Col2 = $val[1] ;
$Col3 = $val[2];

echo $Col1;
echo $Col2;
echo $Col3;
echo "<br>";

//End of for loop
}
?>

This program works perfectly fine printing all columns and rows for n-lenght

Problem - Now our requirement is to get values of Col1, Col2, Col3 and using mysql_query compare into database and do further action.

Minute we add anything above //End of for loop. It only iterates once and stops without throwing any php errors.

e.g.

.....

echo $Col1;
echo $Col2;
echo $Col3;
echo "<br>";

**$sql = mysql_query("select COALESCE(MAX(SrNo), 0)  AS Max_No from TABLEA where ColumnA = 1 and ColumnB = '$Col3'");
    $row = mysql_fetch_array($sql);


    echo $row["Max_No"];**

//End of for loop
}
?>

If we add above SQL the same program only iterates once and stops? It doesn't show any errors in logs or on screen.

Thanks in advance for your help!.

@arkascha - First program print all columns and rows correctly. You can give try by adding simple demo.xls After I add second part of program it only iterates once and stops running. — Amod Gokhale, Jul 11 '15 at 06:28

score 4 · Answer 1 · answered Oct 04 '16 at 22:36

If you try to iterate with for ($col = 2; $col <= $highestColumn; ++ $col){...} it will work for columns from A to Z, but it fails pass the Z (Ex. iterate between 'A' to 'AB').

In order to iterate pass 'Z', you need to convert the column to integer, increment, compare, and get it as string again:

$MAX_COL = $sheet->getHighestDataColumn();
$MAX_COL_INDEX = PHPExcel_Cell::columnIndexFromString($MAX_COL);
    for($index=0 ; $index <= $MAX_COL_INDEX ; $index++){
    $col = PHPExcel_Cell::stringFromColumnIndex($index);

    // do something, like set the column width...
    $sheet->getColumnDimension($col)->setAutoSize(TRUE);
}

With this, you easy iterate pass the 'Z' column.

score 3 · Accepted Answer · answered Jul 11 '15 at 10:39

3

As you're using the same variable $row for the row number in the Excel iteration and for the result of your select query, it's not surprising that you're running into problems.....

The integer value that holds the Excel row number is being overwritten by the array that you get from your SQL query, and then you're trying to use that result array as the next Excel row number

Solution: Use a different variable for these two elements.

$rowData = mysql_fetch_array($sql);
echo $rowData["Max_No"];**

answered Jul 11 '15 at 10:39

Mark Baker

209,507
32
346
385

Thanks so much!!!! I feel stupid now. It worked. I looked at code 10 times.. not sure how it missed! – Amod Gokhale Jul 11 '15 at 11:05
1

Feeling stupid is nature's way of teaching us not to make the same mistake again in the future :) – Mark Baker Jul 11 '15 at 11:06
Totally agree.. Not sure how much time it took you to look at that mistake.. I certainly spend 2 hours eating my brain out :( – Amod Gokhale Jul 11 '15 at 11:09
1

Nature has taught me that particular lesson in the past, so it's probably something that I pay more attention to myself now – Mark Baker Jul 11 '15 at 11:22

PHPExcel loop through rows and columns

2 Answers2