PHPExcel excel read is not working for some cells with calculation

Question

I use PHPExcel lib for read the excel file in Codeigniter project. It is not read some cells with calculation. It show as #VALUE! But some values with calculation is reading in same excel sheet. Whats wrong with those cells?

Cells with have following calculation is not reading

=+D109*1000     
=+B16/B13
=+D23/$D$109

Cells with have following calculation is reading

=+B10-B11
=+C10-C11

But all cells are reading for some excel sheet. This issue come with xlsx format

This is my code

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);
$this->excel = $objReader->load($path);
$this->excel->setActiveSheetIndex($sheet);
$data = $this->excel->getActiveSheet()->toArray(null, true, true, true);

print_r($data);

I check with google. getCalculatedValue() should use for read calculated values. But i can't use it one by one. Is it has a method to read all sheet as array?

How ever i checked some cells with following way

$this->excel->getActiveSheet()->getCell('B18')->getCalculatedValue() // return #VALUE!
$this->excel->getActiveSheet()->getCell('B18')->getOldCalculatedValue() //return 0.4211

How i use old calculated value using toArray?

It should work exactly as you've coded it (the second argument to toArray() tells PHPExcel to return calculated values), and your cells aren't complicated calculations using formulae that aren't supported.... so if that doesn't work then there is no other way.... but I'm more intrigued by why it isn't working.... what is there about those cells that makes them different, and means they aren't being calculated when they should be — Mark Baker
You can't use getOldCalculatedValue() in toArray().... If PHPExcel cannot calculate a basic formula such as =+B16/B13 then there is something very seriously wrong in PHPExcel.... something that needs fixing, so I need to be able to recreate the problem.... could you send me a copy of the spreadsheet that PHPExcel is failing on — Mark Baker
I have the same thing with product formula on other sheet. If you are still working on the problem, I'd be glad to send you what I've got. — acriel
I am not working on any problem until somebody produces some evidence of a problem with a test case that will allow me to work on it without simply trying to pluck solutions from the air.... nobody has provided me with any workbook that demonstrates a problem, and I have spent a full day so far trying unsuccessfully to find evidence that any problem exists. — Mark Baker

Dinuka Thilanga Dinuka Thilanga · Accepted Answer · 2014-07-10T11:18:15

Finally I get old getOldCalculatedValue using loop.

$data = $this->excel->getActiveSheet()->toArray(null, true, true, true);

//This code add for some calculated cells were not reading in xlsx format
foreach ($data as $no => $row) {
    foreach ($row as $key => $value) {
        if (isset($value) && $value == '#VALUE!') {
            $data[$no][$key] = $this->excel->getActiveSheet()->getCell($key.$no)->getOldCalculatedValue();
        }
    }
}

PHPExcel excel read is not working for some cells with calculation

1 Answers