setPreCalculateFormulas removes formulas from sheet?

Topics: Developer Forum, User Forum
Apr 25, 2013 at 5:52 PM
Same issue on two different systems:
Mac OS 10.6, PHP 5.3.10, PHPExcel 1.7.8
CentOS 5.5, PHP 5.3.3, PHPExcel 1.7.8

I have a spreadsheet that contains "COUNTIFS" ... I know that PHPExcel does not "know" (for lack of a better word) about this function. That is OK since I do not need to read the calculated value from it. I do want to keep it in the spreadsheet, though, so I am proceeding by setting setPreCalculateFormulas to false.

If I do nothing more than below, I get completely empty cells where the COUNTIFS functions should be. Of course, if I do not setPreCalculateFormulas(FALSE), then it complaints about the function and does not save anything out... Can anyone think of a reason why setPreCalculateFormulas(FALSE) would be removing all formulas from the Excel spreadsheet?

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$xlsx = $objReader->load($template);

$objWriter = PHPExcel_IOFactory::createWriter($xlsx,'Excel2007');

Thanks for any help provided.
Apr 26, 2013 at 1:00 PM
I've never experienced this at all. setPreCalculateFormulas to False simply stops the save from executing the calculation engine to retrieve the calculated value for every cell. The actual formula should be written regardless; and in fact will be written even if it's an invalid formula; though MS EXcel would almost certainly complain when a spreadsheet containing invalid formulae was loaded.
Apr 29, 2013 at 3:02 PM
i can confirm that it is possible to write "unknown" formulas in excel setting setPreCalculateFormulas to False
I did it with some special bloomberg functions with no problem at all..
May 1, 2013 at 9:13 PM
The spreadsheet in question has multiple sheets with formulas referencing other sheets (and these sheet names have spaces). I really don't know what is going on, but I do know this client likes to get really "creative" when designing Excel spreadsheets. Maybe the sheet names/hidden rows/etc has something to do with it on top of setPreCalculateFormulas? Possible its elsewhere.

I can create my own spreadsheet with "COUNTIFS" formulas and setPreCalculateFormulas does not remove them, but for whatever reason this spreadsheet is giving me problems. I can't save the spreadsheet w/out "setPreCalculateFormulas" because of the COUNIFS formulas, and I can't setPreCalculateFormulas to false because it completely removes the formula altogether.

I wish I could provide the spreadsheet to prove my case, but can't since it is from a large (financial industry, lots of regulations) client who would not appreciate it being shared. I can guarantee you formulas are being removed from the spreadsheet, though. I'll post a response here if I ever do figure it out. For now, I think I'll try to come up with an alternate solution to getting data into this sheet...
May 1, 2013 at 11:37 PM
Are they array formulas? At present PHPExcel doesn't support these, which is one of the reasons I'm restructuring the cell definition and rewriting the calculation engine.
May 9, 2013 at 8:28 PM
They are array formulas, indeed. Interesting that they're completely removed from the cells. At least now I know why.