1

I am on a project and I am reading values from an excel document. When using the getCalculatedValue function it does always return the same value. I tried the getOldCalculatedValue function without success. I read somewhere that it will be helpful if you clear the cache before recalculating the value from that cell. Can someone explain how can I do that with the below code? 

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');  
$objPHPExcel = $objReader->load("test.xlsx");
$value1 = $objPHPExcel->getActiveSheet()->getCell('B1')->getCalculatedValue();
$value2 = $objPHPExcel->getActiveSheet()->getCell('D1')->getCalculatedValue();
Demetris
  • 11
  • 2

1 Answers1

1

When you first retrieve a calculated value with a call to getCalculatedValue(), PHPExcel (by default) caches the result, so subsequent calls won't recalculate, but simply return the cached value.

This reduces the calculation overhead typically found when the same calculated cell is referenced in other cell formulae

It can be a problem when you're reading calculated data, then changing cell values and then re-calculating, so PHPExcel provides methods that allow you to control the cache

You can change the default behaviour so that calculated results are never cached, by disabling the calculation cache using either:

PHPExcel_Calculation::getInstance($objPHPExcel)->disableCalculationCache();

or:

PHPExcel_Calculation::getInstance($objPHPExcel)->setCalculationCacheEnabled(false);

Or you can flush the cache at any point by making a call to:

PHPExcel_Calculation::getInstance($objPHPExcel)->clearCalculationCache();

Note that the getOldCalculatedValue() method is used to retrieve the last value calculated for a cell by MS Excel itself, not from within PHPExcel. It isn't guaranteed to reflect the current data in any way, because it's possible to disable MS Excel from executing formula calculations; but can sometimes be useful for formulae that reference external files or data sources.

Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • Thank you Mark for your response. I tried the above suggestions but it didn't work. The value in the excel cell changes based on my input values and when I open the excel the value is correct. When I read it from my script it is the same all the time (and the value is wrong). $objPHPExcel = $objReader->load("test.xlsx"); PHPExcel_Calculation::getInstance($objPHPExcel)->setCalculationCacheEnabled(false); PHPExcel_Calculation::getInstance($objPHPExcel)->clearCalculationCache(); $value1 = $objPHPExcel->getActiveSheet()->getCell('B1')->getCalculatedValue(); – Demetris Jul 10 '15 at 07:45
  • Can you please provide an example of a formula that exhibits this behaviour then? – Mark Baker Jul 10 '15 at 08:10
  • Perhaps you're using a function that isn't supported by PHPExcel. I've explained how calculation caching can be suppressed or flushed, the programmed behaviour; but without knowing the specific details of your calculation, or the changes you're making that should affect that calculation, then I can't explain why you're getting the same result every time..... normally I'd expect that unless the formula or underlying data has been changed, but you don't say what changes you've made – Mark Baker Jul 10 '15 at 08:15
  • In my site, I have input boxes and user can enter different values. Those values are inserted into some cells in the excel document. After the input, I have a row of cells with calculations in each cell using the IF function as follows (example): =IF(VALUE(F49)<>1, E33-F49-E57-E58-E60-E61+E59, 1). My goal is to get the maximum value contained into all of those cells in the row. So, in the cell that I am reading from my script (B1) it is formatted as: =MAX(D63:AF63). – Demetris Jul 10 '15 at 08:48
  • And this is my code: $objReader = PHPExcel_IOFactory::createReader('Excel2007'); $objPHPExcel = $objReader->load("test.xlsx"); $objPHPExcel->getActiveSheet() ->setCellValue('B3',$input1) ->setCellValue('B4',$ input2); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('test.xlsx'); $objPHPExcel = $objReader->load("test.xlsx"); $value1= $objPHPExcel->getActiveSheet()->getCell('B1')->getCalculatedValue(); – Demetris Jul 10 '15 at 08:49
  • Based on the above, why should any change to cells B3 or B4 affect the result of =MAX(D63:AF63) in B1? – Mark Baker Jul 10 '15 at 08:54
  • Can you provide a working example with just a few cells? – Mark Baker Jul 10 '15 at 08:54
  • I would like to attach here some documents so it will be really what I did but I can't since I am a new user. Is there any other way? – Demetris Jul 10 '15 at 09:29
  • You can't attach documents on SO, you'd need to upload them somewhere like dropbox and provide a link – Mark Baker Jul 10 '15 at 09:29
  • Please use this link: http://www.filedropper.com/webtool . I uploaded the total project (2 pages and 1 excel document). – Demetris Jul 10 '15 at 09:49