I am generating a large excel sheet using PHPExcel. It has lots of formulas in it and it takes much time to process all of them, sometimes it times out though php variables are set for timeout properly. After searching for optimization options I found
the option to set pre-calculate formulas to false. I used that and the performance was improved dramatically, from 5/7 minutes to 5-10 seconds. It was working absolutely fine so far, when I opened the generated excel file, the cell values (where there is a
formula to be calculated) were also calculated there automatically. But suddenly its behaviour has changed. It generates the file as before but the cell values are not calcualated automatically anymore. They show as 0. When I click on the cell, the values
then get calculated. I am confused as to why the same thing worked fine before and not working as expected now. When I set pre-calculate formulas to true it takes time but calculates everything fine. After some seraching on this particular thing I found this
dicussion http://phpexcel.codeplex.com/discussions/237881, where the following is mentioned:
"When writing to xls or xlsx, PHPExcel will execute formulae by default so that it can write both the formulae and the result to the file, and that can slow down the process quite considerably (dependent on the complexities of the formula) but setPreCalculateFormulas(false)
disables this calculation, so it only writes the formulae. This can reduce memory usage to just an additional 2-3 MB for the writer itself (no need to include the calculation engine) and double the speed of writing (dependent on the number of formulae in the
worksheet). The cost is forcing MS Excel to do the recalculation (not always done automatically) when the resultant file is opened in MS Excel."
It says the calculation is "not always done automatically". Please help me here to understand in which case particularly this automatic calculation works and where it doesnt. Does it depend on Excel version or any specific formula? or
some other settings in either the PHP Excel Writer or the Excel document opening software?