CODEWITHSUNDEEP
phpexcelexcel-formulaphpexcel
Rockers Niloy
Rockers Niloy

Reputation: 101

PHPExcel returns Zero "0" in parsing for a formula in a xlsx file

I want to get the calculated value of Excel Functions via PHP .simply if the input is 

 '=MAX(2,3,4,6)'

then the output will be 

 6

For this , I made an two php script with PHPExcel Library . One script will take that formula/function and will generate a xlsx file .

The second script will read that generated xlsx file and will give calculated value of given formula.

The process seems ok , but it could not generates value for a few functions . Such as for 

"=INDEX({"zz","yy","xx"},2)"

and

"=INDEX({"zz","yy","xx"},MATCH(MAX({11,22,34}),{11,23,34}))"

My first script can generate xlsx file along with their formula's result .

enter image description here

but second script shows invalid result , it shows only 0.

Here is my first script http://pastebin.com/vCErW5wf

and here is my second script 

require_once 'a/Classes/PHPExcel/IOFactory.php';
 require_once 'a/Classes/PHPExcel.php';

 $inFile = 'index.xlsx';

 $objReader = PHPExcel_IOFactory::createReader('Excel2007');
 $objReader->setReadDataOnly(true);
 $objPHPExcel = $objReader->load($inFile);
 $value = $objPHPExcel->setActiveSheetIndex(0)->getCell('C5')->getOldCalculatedValue();

echo $value;

Thanks in Advance .

Upvotes: 4

Views: 1060

Answers (1)

Mark Baker
Mark Baker

Reputation: 212412

Using getOldCalculatedValue() will return the last value calculated for a formula cell that was generated in MS Excel (and even then, it's possible to disable calculations in MS Excel, and this value won't then be set). A file generated by PHPExcel will not populate this value, so it will simply provide a default value of 0.

Generally speaking, you should always use getCalculatedValue() when trying to evaluate a formula value

Upvotes: 1

Related Questions