I use PHPExcel to read and write Excel files. It is a very useful class. However, I ran into an issue with cells containing an IF formula.
PHP
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | $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); foreach ($cellIterator as $cell) { echo '<td>' . $cell->getCalculatedValue() . '</td>' . "\n"; } echo '</tr>' . "\n"; } echo '</table>' . "\n"; |
Excel
The Issue
I use the formula =IF($B$4<>"",($B$4*$B$1),($A$4*$B$1)) for cell C4. The expected result for this cell is 0, as seen in the image above. After using getCalculatedValue(), I get a result of 1000. The work around is to use getOldCalculatedValue().
From Mark Baker at PHPExcel:
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.
However, there is still no explanation as to why the formula is not calculating correctly.
=IF($B$4″”,($B$4*$B$1),($A$4*$B$1))
the problem is with your formula
you try to check if b4 is distinct of empty space when you have a 0 there -> that means the that the affirmation fails( are used for comparation) cause it should get != to get in 1st case…
or:
=IF(NOT($B$4=””),($B$4*$B$1),($A$4*$B$1))
as Mark said PHP Excel !== MS Excel
If you are unsure about the content of a cell (value or formula included), I recommend you to primarily do a check if the cell has a formula and then copy – paste accordingly. getOldCalculatedValue() is very helpful in this case. Here is an example of that:
$code = $sheet->getCell(‘A’.$y)->getValue();
if(strstr($code,’=’)==true)
{
$code = $sheet->getCell(‘A’.$y)->getOldCalculatedValue();
}
$objPHPExcel4->setActiveSheetIndex(0)
->setCellValue(‘A’.$l, $code);
For large data sets, getCalculatedValue() function is really cumbersome and lots of memory will be required to perform correctly.