PHPEXCEL getCalculatedValue or getFormattedValue returns fatal error

Question

I have a file with mime-type "application/octet-stream; charset=binary" and extension ".xlsx". PHPExcel_IOFactory::identify($this->file) returns 'Excel2007'. I create my reader like this:

$this->objectReader = PHPExcel_IOFactory::createReaderForFile($this->file);

When I try to get a cell value with a formula I can only do it in two ways:

1)$sheet->getCell($columns[$i].$row->getRowIndex())->getValue() 
//return the actual formula as a string
2)$sheet->getCell($columns[$i].$row->getRowIndex())->getOldCalculatedValue() 
//returns the oldCalculated value which is not always corret (for example the value in the cell is 17.4% and it returns 0.17)

When I dump the $sheet->getCell($columns[$i].$row->getRowIndex()) object the output starts with the fallowing :

object(PHPExcel_Cell)[142370]
  private '_value' => string '=IFERROR(IF(C$6="yes",IF(ISBLANK(INDIRECT(ADDRESS($AN15,MATCH($B$6,INDIRECT("'"&C$7&"'!"&$AN$9-1&":"&$AN$9-1),0),,,C$7),TRUE)),"",INDIRECT(ADDRESS($AN15,MATCH($B$6,INDIRECT("'"&C$7&"'!"&$AN$9-1&":"&$AN$9-1),0),,,C$7),TRUE)),""),"")' (length=231)
  private '_calculatedValue' => float 0.1744360902255639
  private '_dataType' => string 'f' (length=1)
  private '_parent' =>...

The correct value is there but I can't access it... When I try to get the cell value with getFormattedValue() or with getCalculatedValue() the output is the fallowing fatal error:

Catchable fatal error: Argument 2 passed to PHPExcel_Calculation_LookupRef::INDIRECT() must be an instance of PHPExcel_Cell, boolean given

Any idea of how to solve this problem ? I am really stuck and have no idea. Thank you.

Mark Baker Mark Baker · Accepted Answer · 2015-04-30T16:33:43

A call to

$sheet->getCell($columns[$i].$row->getRowIndex())->getValue()

will return the actual formula for any cell that contains a formula

The value _calculatedValue that you can see when you dump the cell object is the one that you can retrieve using the call to

$sheet->getCell($columns[$i].$row->getRowIndex())->getOldCalculatedValue()

In this case, you say it's incorrect..... you're wrong, it is correct: 17.4% is 0.174... the difference is that applying a % format mask displays the actual cell value in MS Excel multiplied by 100

When you call

$sheet->getCell($columns[$i].$row->getRowIndex())->getCalculatedValue()

or

$sheet->getCell($columns[$i].$row->getRowIndex())->getFormattedValue()

PHPExcel tries to execute the formula for the cell and recalculate the value. If this is throwing an exception, you can debug the calculation engine to see where it is going wrong by enabling the calculation engine's debug mode, as described in this gist

PHPEXCEL getCalculatedValue or getFormattedValue returns fatal error

1 Answers