CODEWITHSUNDEEP
phpmysqlphpexcel
Amod Gokhale
Amod Gokhale

Reputation: 2448

PHPExcel loop through rows and columns

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!.

Upvotes: 1

Views: 15312

Answers (2)

Alejandro Silva
Alejandro Silva

Reputation: 9118

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.

Upvotes: 4

Mark Baker
Mark Baker

Reputation: 212522

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"];**

Upvotes: 3

Related Questions