getCalculatedValue produces incorrect value

Topics: Developer Forum, User Forum
Jan 16, 2012 at 9:11 PM
Edited Jan 16, 2012 at 9:12 PM

I am using PHPExcel to pull data on a certain sheet. The sheet references a value of a logical test cell, and is producing an incorrect value. Perhaps there is something wrong with the cell formula...

=IF($C$22<>"",$C$22*$E$20,$B$22*$E$20)

When I use the code below, it will use the 'false' value of the logical test even if it's true.

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load("file.xlsx");
$objWorksheet = $objPHPExcel->setActiveSheetIndex(4);
PHPExcel_Calculation::getInstance()->disableCalculationCache();
echo '<table border=1>' . "\n";
foreach ($objWorksheet->getRowIterator() as $row) {
  echo '<tr>' . "\n";
  $cellIterator = $row->getCellIterator();
  $cellIterator->setIterateOnlyExistingCells(false); // This loops all cells,
                                                     // even if it is not set.
                                                     // By default, only cells
                                                     // that are set will be
                                                     // iterated.
  foreach ($cellIterator as $cell) {
    echo '<td>' . $cell->getCalculatedValue() . '</td>' . "\n";
  }
  echo '</tr>' . "\n";
}
echo '</table>' . "\n";
Jan 16, 2012 at 9:22 PM

PHPExcel uses the false value even when true using this formula as well.

=IF($C$22="",$B$22*$E$20,$C$22*$E$20)

Jan 16, 2012 at 9:38 PM

I found a work around in discussion:62010 to use getOldCalculatedValue().

Can someone please explain to why getOldCalculatedValue works?

I believe this may relate to:

Bugfix:   (MB) Work item 10362 - getCalculatedValue() sometimes incorrect with IF formula and 0-values
Bugfix: (MBaker) Work item 10563 - IF formula calculation problem in PHPExcel 1.7.0 (string comparisons)
Bugfix: (ET) Work item 10272 - Problem in reading formula : IF( IF ) with PHPExcel_Reader_Excel5

Coordinator
Jan 17, 2012 at 7:40 AM

getOldCalculatedValues() reads the value as it was last calculated by MS Excel (if available) though you can't guarantee it will always be there, or be correct if it is (you can disable autocalculation in MS Excel, or it won't be set if loading a CSV file); while getCalculatedValue() actually recalculates the value within PHPExcel itself.

Quite why your formula isn't calculating correctly is a problem... can you generate a test case spreadsheet or at least let me know the values for the referenced cells.

Jan 17, 2012 at 8:47 PM
Edited Jan 17, 2012 at 8:48 PM

Can I add attachments on here?

Here is my PHP code:

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load("file.xlsx");
$objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
PHPExcel_Calculation::getInstance()->disableCalculationCache();
echo '<table border=1>' . "\n";
foreach ($objWorksheet->getRowIterator() as $row) {
  echo '<tr>' . "\n";
  $cellIterator = $row->getCellIterator();
  $cellIterator->setIterateOnlyExistingCells(false); // This loops all cells,
                                                     // even if it is not set.
                                                     // By default, only cells
                                                     // that are set will be
                                                     // iterated.
  foreach ($cellIterator as $cell) {
    echo '<td>' . $cell->getCalculatedValue() . '</td>' . "\n";
  }
  echo '</tr>' . "\n";
}
echo '</table>' . "\n";

Here is what file.xlsx looks like in Excel:

  A B C
1 Factor 50  
2  
3 Column 1 Column 2 Use Column 2 if not empty
4 20 0 0

Cell C4 has the formula "=IF($B$4<>"",($B$4*$B$1),($A$4*$B$1))

After I run PHP Excel, it ignores the '0' in B4 and calculates 20*50 instead of 20*0.

Make sense?

Coordinator
Jan 17, 2012 at 9:04 PM

Files can't be attached to discussion threads, but we do have a Work Item specially for that purpose. http://phpexcel.codeplex.com/workitem/10749?ProjectName=phpexcel

Jan 17, 2012 at 10:11 PM

http://phpexcel.codeplex.com/Download/AttachmentDownload.ashx?ProjectName=phpexcel&WorkItemId=10749&FileAttachmentId=6128

Jan 26, 2012 at 6:53 PM
MarkBaker wrote:

Files can't be attached to discussion threads, but we do have a Work Item specially for that purpose. http://phpexcel.codeplex.com/workitem/10749?ProjectName=phpexcel

Mark,

Any updates on this?