[BugReport] Converting excel-style cell coordinates to absolute coordinates does not work correctly

Topics: Developer Forum
Oct 1, 2009 at 12:29 PM
Edited Oct 1, 2009 at 12:47 PM

Environment: Ubuntu Linux, PHP 5.2.6-3ubuntu4.2, PHPEXcel 1.7.0

Test script 1:

<?php
require_once('Classes/PHPExcel.php');
require_once('Classes/PHPExcel/Writer/Excel5.php');

$xls = new PHPExcel();
$sheet = $xls->getActiveSheet();
for( $i = 1; $i <= 10; $i++ )
{
$sheet->setCellValue( 'A'+ $i, $i * 2 );
}
$sheet->setCellValue( 'A11', '=SUM(A1:A10)' );

for( $i = 1; $i <= 10; $i++ )
{
$val = $sheet->getCell( 'A'+$i )->getValue();
echo 'i='.$i.' val='.$val."<br>\n";
}

echo $sheet->getCell( 'A11' )->getValue().' ';
echo $sheet->getCell( 'A11' )->getCalculatedValue()."<br>\n";

$xlsWriter = new PHPExcel_Writer_Excel5($xls);
$xlsWriter->save("mytable.xls");

echo 'done';
?>

Expected results: 1) 10 lines with text like "i=5 val=10", then line with text '=SUM(A1:A10) 110'; 2) the same 11 rows in file "mytable.xls"

Practical results: 10 lines with text like "i=5 val=10" - as expected. Then line with text "=SUM(A1:A10) 0" (than means formula was not calculated at all). And then an error message:

Fatal error: Uncaught exception 'Exception' with message 'Column string index can not be empty.' in /......./Classes/PHPExcel/Cell.php:630"

related to string with call to $xlsWriter->save() method

Test script 2:

<?php
require_once('Classes/PHPExcel.php');
require_once('Classes/PHPExcel/Writer/Excel5.php');

$xls = new PHPExcel();
$sheet = $xls->getActiveSheet();
for( $i = 1; $i <= 10; $i++ )
{
  $sheet->getCellByColumnAndRow( 0, $i )->setValue( $i * 2 );
}
$sheet->getCellByColumnAndRow( 0, 11 )->setValue( '=SUM(A1:A10)' );

for( $i = 1; $i <= 10; $i++ )
{
  $val1 = $sheet->getCellByColumnAndRow( 0, $i )->getValue();
  echo 'i='.$i.' val1='.$val1."<br>\n";
}

echo $sheet->getCellByColumnAndRow( 0, 11 )->getValue().'   ';
echo $sheet->getCellByColumnAndRow( 0, 11 )->getCalculatedValue()."<br>\n";

$xlsWriter = new PHPExcel_Writer_Excel5($xls);
$xlsWriter->save("mytable1.xls");

echo 'done';
?>

Note: calls to getCell() with excel-style cell coordinates (like 'A7') in script 1 were changed into calls to getCellByColumnAndRow with abcolute coordinates in script 2. All the rest is the same in both scripts.

Expected results: the same as in test script 1

Practical results: as expected.

 

Coordinator
Oct 1, 2009 at 12:53 PM

'A'+ $i

or

'A' . $i

??

Oct 1, 2009 at 1:09 PM

Original "test script 1" contained " 'A'+$i " expression. After I replaced them to " 'A' . $i " - script work as expected.

So it seems to be not a bug but my brain failure. :-(

 

Oct 1, 2009 at 1:32 PM

My brain has failed, but situation is really a bug.

Let's think (for example) $i=3. In this case result of expression 'A'+$i is 3. So when script executes function call '$sheet->getCell( 'A'+$i)' it will execute function call '$cell->coordinateFromString(3)' and (as I think ) must throw an exception because parameter does not match coordinate string format.

Developer
Oct 3, 2009 at 1:03 AM

>> (as I think ) must throw an exception because parameter does not match coordinate string format.

Should now be fixed.

http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=10721