PHPExcel getOldCalculatedValue returns NULL

Question

I concluded that getOldCalculatedValue() returns already calculated formula if a cell contains a formula, or value if a cell does not contain a formula. My assumption is confirmed by Mark Baker's post (althought this post is from year 2011):

$value = $objPHPExcel->setActiveSheetIndex(0)
                     ->getCell('G'.$i)
                     ->getValue();

Will return the actual formula if the cell contains a formula, or the
value if the cell contains a value.

$value = $objPHPExcel->setActiveSheetIndex(0)
                     ->getCell('G'.$i)
                     ->getCalculatedValue();

Will return the calculated value if the cell contains a formula, or the value
if the cell contains a value.

$value = $objPHPExcel->setActiveSheetIndex(0)
                     ->getCell('G'.$i)
                     ->getOldCalculatedValue();

Will return the previous result of a calculation if the cell contains a formula,
or the value if the cell contains a value.

I created xlsx (Excel 2007) with one cell A1. A1's content is string 'foo'. When I call

$reader = new PHPExcel_Reader_Excel2007();
$excel = $reader->load(Input::file("pricelist")->getRealPath());
$worksheet = $excel->setActiveSheetIndex(0);
var_dump($worksheet->getCell('A1')->getOldCalculatedValue());

it prints NULL. If I change function to getCalculatedValue() or getValue() it works ok. I'm using PHPExcel 1.8.0.

Mark Baker Mark Baker · Accepted Answer · 2014-10-08T12:51:22

It isn't strictly true, there are several reasons why getOldCalculatedValue() may return a NULL, even for a cell that contains a formula.

The main reasons are:

Not all spreadsheet formats support maintaining the last calculated value
Even for formats that do support maintaining the last calculated value, the application that created the file may not have written the value to the file
Automatic calculation may have been disabled in Excel (assuming that Excel was used to create the file

The last may be even more of an issue if automatic calculation was disabled at some point after the formula was entered, but there have been subsequent data changes which affect the formula, because they won't be reflected in the old calculated value

PHPExcel getOldCalculatedValue returns NULL

1 Answers