getCalculatedValue when loading multiple files

Topics: Developer Forum, User Forum
Jul 13, 2010 at 7:01 PM
Edited Jul 13, 2010 at 7:14 PM
I am having a problem with the function getCalculatedValue() on multiple files. Here is a small example: I have 2 tables and 2 different excel files, they look like this:

A - B
Total Tests - 79
Succeded - 38
Test Failed - 3
Incomplete - 38

A - B
Total Tests - 23
Tests Succeded - 15
Test Failed - 2
Incomplete - 6

The value for "Total Tests" is actually a formula, [=SUM(B2:B4)].
What I want my program to do, is find the combined "Total Tests" value, which should be 79+23=102. Here is the code i have:
/** Error reporting */

/** Include path **/
ini_set('include_path', ini_get('include_path').';library/phpexcel/Classes/');

/** PHPExcel */
include 'PHPExcel.php';

/** PHPExcel_Writer_Excel2007 */
include 'PHPExcel/Writer/Excel2007.php';
include 'PHPExcel/Reader/Excel2007.php';

$total = 0;

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load("library/phpexcel/Data/1.xlsx");
echo "Formula: " . $objPHPExcel->getSheetByName("Sheet1")->getCell("B1")->getValue() . "
\n"; echo "Calculated Value: " . $objPHPExcel->getSheetByName("Sheet1")->getCell("B1")->getCalculatedValue() . "
\n"; echo "B2: " . $objPHPExcel->getSheetByName("Sheet1")->getCell("B2")->getValue() . "
\n"; echo "B3: " . $objPHPExcel->getSheetByName("Sheet1")->getCell("B3")->getValue() . "
\n"; echo "B4: " . $objPHPExcel->getSheetByName("Sheet1")->getCell("B4")->getValue() . "
\n"; $total += $objPHPExcel->getSheetByName("Sheet1")->getCell("B1")->getCalculatedValue() . "
\n"; echo "Total: " . $total . "

\n"; $objPHPExcel = $objReader->load("library/phpexcel/Data/2.xlsx"); echo "Formula: " . $objPHPExcel->getSheetByName("Sheet1")->getCell("B1")->getValue() . "
\n"; echo "Calculated Value: " . $objPHPExcel->getSheetByName("Sheet1")->getCell("B1")->getCalculatedValue() . "
\n"; echo "B2: " . $objPHPExcel->getSheetByName("Sheet1")->getCell("B2")->getValue() . "
\n"; echo "B3: " . $objPHPExcel->getSheetByName("Sheet1")->getCell("B3")->getValue() . "
\n"; echo "B4: " . $objPHPExcel->getSheetByName("Sheet1")->getCell("B4")->getValue() . "
\n"; $total += $objPHPExcel->getSheetByName("Sheet1")->getCell("B1")->getCalculatedValue(); echo "Total: " . $total . "

\n"; ?>
Here is the debug output:

Formula: =SUM(B2:B4)
Calculated Value: 79
B2: 38
B3: 3
B4: 38
Total: 79

Formula: =SUM(B2:B4)
Calculated Value: 79
B2: 15
B3: 2
B4: 6
Total: 158
As you can see, the result is NOT correct. For some reason, the 2nd getCalculatedValue loads the numbers B2:B4 from the first excel file that was loaded, thus I get 79 again. Even if I rename all the variables, to $objReader2 and $objPHPExcel2, the same output occurs. Any help would be appreciated, thanks! P.S. Sorry, I'm not really sure how to format the code, if a mod could help that would be great too! PHP: 5.3 PHPExcel: 1.73c Excel: 2007
Jul 13, 2010 at 8:16 PM
Edited Jul 13, 2010 at 8:37 PM
EDIT: Actually, this didn't fix the problem at all... The other getCalculatedValues still return a 79 as the value.. ------Old Post Sorry to have wasted everyone's time. Finally found the destructor method which was the cause of the problem.
Thanks for awesome library.
Jul 13, 2010 at 8:32 PM
Edited Jul 13, 2010 at 8:34 PM

To improve performance when Formulae reference other calculated cells, the PHPExcel calculation engine caches the results of all calculations (by default).


A10 Formula =SUM(A1:A9) 
A11 Formula =A10 / COUNT(A1:A9) 

So if I request the calculated values of A10 and then of A11, the calculation engine will not recalculate the value of A10 a second time, because it has already performed that calculation once and stored the result.

Most of the time, this is the preferred behaviour because it improves performance when repeatedly referencing calculated cells in different formulae.


This cache is indexed by Worksheet name and cell reference.


In your case, you have two workbooks, each with the same worksheet names and cells containing formulae, but which result in different values; but the second workbook will reference the same entry in the cache as the first workbook.

What you'll need to do is clear the cache between completing your calculations for the first workbook, and starting the calculations for the second workbook. You can do this using:


You would also need to call this if you were getting the calculated value for B1; then modifying the values in B2, B3 or B4; then recalculating B1 to get the new sum.


Note that the calculation cache also has an expiry, so it is sometimes possible that a previously calculated value wouldn't remain in cache, and might be recalculated subsequently.

Jul 13, 2010 at 9:28 PM
Thank you Mark, that fixed the problem. Would it be bad if I disabled the caching? PHPExcel_Calculation::getInstance()->setCalculationCacheEnabled(false)
Jul 13, 2010 at 10:13 PM

If you disable calculation cacheing, then it will slow down any calculations that repeatedly reference other calculated cells, but have no other adverse effects.