is setPreCalculateFormulas(false) overridden by setIncludeCharts(true)?

Topics: Developer Forum
May 3, 2013 at 4:23 PM
Is setPreCalculateFormulas(false) overridden by setIncludeCharts(true)?

I am trying to use an excel file with charts as a template.

Using setPreCalculateFormulas(false) works just fine and no calculation is done, except when setIncludeCharts(true) is set, at which point calculation is happening and phpExcel is once again able to end if a bad formula calls for a Fatal Error.

Is it possible to override this behavior? the code i'm using is posted below. Its pretty basic.

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

thanks a million!
May 6, 2013 at 5:47 PM
Yes! Me too. Why are formulas being calculated when i include a chart and how do i disable it? It would be a big help if this gets fixed.

May 6, 2013 at 6:00 PM
Edited May 6, 2013 at 6:00 PM
In part it is overridden, though only for those cells that are part of the chart data.

I don't know how to fix it. The chart needs to store the result of the calculation, which means the calculation engine needs to do the calculation.

It does raise a question: what formulae are you using that are required by your charts that cause the PHPExcel calculation engine to throw a fatal error? Perhaps that should be the question... setting PreCalculateFormulas to FALSE isn't a solution, it's a workround.
May 6, 2013 at 8:46 PM
Edited May 6, 2013 at 8:56 PM
Thanks so much for responding I hope this information helps.

I'm almost more familiar with phpExcel at this point than Excel itself. I've copied both the php error and what i could find in the "Select Data Source" property of Excel. One thing I noticed, is that if i uncheck all of the horizontal categories but one (it doesn't matter which one) the error does not occur.

PHP Error:
Fatal error: Uncaught exception 'Exception' with message 'Formula Error: Unexpected ,' in /home/humbleuser/reports/3rdParty/PHPExcel2/Classes/PHPExcel/Calculation.php:3598
Stack trace:
#0 /home/humbleuser/reports/3rdParty/PHPExcel2/Classes/PHPExcel/Calculation.php(2808): PHPExcel_Calculation->_raiseFormulaError('Formula Error: ...')
#1 /home/humbleuser/reports/3rdParty/PHPExcel2/Classes/PHPExcel/Calculation.php(2327): PHPExcel_Calculation->_parseFormula('(Rollup_detail!...', Object(PHPExcel_Cell))
#2 /home/humbleuser/reports/3rdParty/PHPExcel2/Classes/PHPExcel/Chart/DataSeriesValues.php(288): PHPExcel_Calculation->_calculateFormulaValue('=(Rollup_detail...', NULL, Object(PHPExcel_Cell))
#3 /home/humbleuser/reports/3rdParty/PHPExcel2/Classes/PHPExcel/Chart/DataSeries.php(342): PHPExcel_Chart_DataSeriesValues->refresh(Object(PHPExcel_Worksheet), true)
#4 /home/humbleuser/reports/3rdParty/PHPExcel2/Classes/PHPExcel/Chart/PlotArea.php(121): PHPExcel_Chart_DataSeries->refresh(Object(PHPExcel_Worksheet))
#5 /home/humbleuser/reports/3rdParty/PHPExcel2/Clas in /home/humbleuser/reports/3rdParty/PHPExcel2/Classes/PHPExcel/Calculation.php on line 3598
Excel Info:
Chart Data Range is set to: =Rollup_detail!$A$126:$B$127,Rollup_detail!$A$129:$B$130,Rollup_detail!$A$137:$B$137

Legend Entries (Series):
Series 1:
    Series Name: [ left blank ]
    Series values: =(Rollup_detail!$B$126:$B$127,Rollup_detail!$B$129:$B$130,Rollup_detail!$B$137)

Horizontal Categories:
    DIRECT TRAFFIC: =(Rollup_detail!$A$126:$A$127,Rollup_detail!$A$129:$A$130,Rollup_detail!$A$137)
    REFERRING SITES: =(Rollup_detail!$A$126:$A$127,Rollup_detail!$A$129:$A$130,Rollup_detail!$A$137)
    ORGANIC: =(Rollup_detail!$A$126:$A$127,Rollup_detail!$A$129:$A$130,Rollup_detail!$A$137)
    CPC: =(Rollup_detail!$A$126:$A$127,Rollup_detail!$A$129:$A$130,Rollup_detail!$A$137)
    OTHER SOURCES: =(Rollup_detail!$A$126:$A$127,Rollup_detail!$A$129:$A$130,Rollup_detail!$A$137)
Thanks again!