1

I've built a reporting solution where the data can be exported as Excel. Using PHPExcel, this work's beautifully. But, we had a need to build dashboard style template sheets and have them inserted into the output at runtime, including formulas. I have all of that working fine, my issue is that sometimes these dashboard templates are fairly complex and PHPExcel chokes on the formula's when it tries to output.

I've spent a considerable amount of time finding workarounds and even went as far as forking my own branch and adding a method of disabling calculation that would try to stop the formulas from being processed, but could not get it to work. I've also tried disabling calculation cache.

@MarkBaker has done a fantastic job with this library, and I have seen multiple times where he has mentioned this is not designed for modifying files, but if done very carefully, it seems to work.

My issue is, how can I tell PHPExcel to stop trying to calculate formulas? I want it to treat them just like any other text value and let Excel itself handle validation. Is this possible?

As an example, here is a formula that PHPExcel appears to not like:

=IFERROR(IF($L$3="UNITS",COUNTIF('K1_Current_Accounts'!$M$2:$M$10000,'Snapshot Summary'!H5)+COUNTIF('K1_Current_Available'!$M$2:$M$10000,'Snapshot Summary'!H5),IF($L$3="Cost Basis",SUMIF('K1_Current_Accounts'!$M$2:$M$10000,'Snapshot Summary'!H5,'K1_Current_Accounts'!$E$2:$E$10000)+SUMIF('K1_Current_Available'!$M$2:$M$10000,'Snapshot Summary'!H5,'K1_Current_Available'!$E$2:$E$10000),IF($L$3="Notes",SUMIFS('K1_Current_Accounts'!$AA$2:$AA$10000,'K1_Current_Accounts'!$M$2:$M$10000,'Snapshot Summary'!H5,'K1_Current_Accounts'!$R$2:$R$10000,'Snapshot Summary'!$A$7)+SUMIFS('K1_Current_Accounts'!$AA$2:$AA$10000,'K1_Current_Accounts'!$M$2:$M$10000,'Snapshot Summary'!H5,'K1_Current_Accounts'!$R$2:$R$10000,'Snapshot Summary'!$A$8)+SUMIFS('K1_Current_Accounts'!$AA$2:$AA$10000,'K1_Current_Accounts'!$M$2:$M$10000,'Snapshot Summary'!H5,'K1_Current_Accounts'!$R$2:$R$10000,'Snapshot Summary'!$A$9),IF($L$3="Cash",SUMIFS('K1_Historical_Accounts'!$AC$2:$AC$10000,'K1_Historical_Accounts'!$M$2:$M$10000,'Snapshot Summary'!H5,'K1_Historical_Accounts'!$R$2:$R$10000,'Snapshot Summary'!$A$10))))),"")

enter image description here

I read that SUMIFS can cause problems, but I have no clue how to refactor those to SUMPRODUCT as suggested (these dashboards are built by business development teams). Also, we have our ranges for columns go to like 10000 because ranges like A:A do not work either. They work fine in Excel, so if I could just get PHPExcel to stop trying to validate everything and just let it through, my life will be glorious!

EDIT

Looking further at the stack trace, it seems to start happening when it tries to write a chart:

enter image description here

EDIT 2

Looking at the chart generation as the source of this problem, there is only one chart and it's data source is ='Sales History'!$Y$4:$CR$5, which I can see it for sure tries to process, but that shouldn't cause an issue as it is a relatively simple reference.

Jeremy Harris
  • 24,318
  • 13
  • 79
  • 133

2 Answers2

0

You can prevent PHPExcel from calculating formulas when writing by setting the Writer's preCalculateFormulas flag to false using

$objWriter->setPreCalculateFormulas(false);

EDIT

I've made some experimental changes to the Excel2007 Writer to disable cell calculations for charts:

In /PHPExcel/Writer/Excel2007/Chart.php I've added a new property

protected $calculateCellValues;

And modified the writeChart() method to accept an additional argument:

public function writeChart(PHPExcel_Chart $pChart = null, $calculateCellValues = true)
{
    $this->calculateCellValues = $calculateCellValues;

and modified the subsequent call to $pChart->refresh()

//    Ensure that data series values are up-to-date before we save
if ($this->calculateCellValues) {
    $pChart->refresh();
}

And in the save() method of /PHPExcel/Writer/Excel2007.php I've modified the call to writeChart() around line 300 to read:

$objZip->addFromString(
    'xl/charts/chart' . ($chartCount + 1) . '.xml', 
    $this->getWriterPart('Chart')
        ->writeChart($chart, $this->preCalculateFormulas)
);

(ie. I've injected $this->preCalculateFormulas into the chart writer.)

A provisional test shows that MS Excel will trigger a recalculation itself when the resultant xlsx files are opened.... but this hasn't been a full test

Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • I've tried that. It defaults to `false` also in `PHPExcel\Writer\Excel2007` line 39. In that specific writer (the one I need), it passes that value in on line 290 for worksheets, yet still tries to validate the formula's anyway. I'm trying to do everything I can to avoid writing my own library to handle this stuff because it is ridiculously complex (as you know), but I'm not making much headway. Let me see if I can find the code path here where it is still running formula calculations. Does that function stop calculation in charts? – Jeremy Harris Nov 05 '15 at 20:42
  • Are you using column autosizing? That should be only trigger for formula calculation that can override preCalculateFormulas false – Mark Baker Nov 05 '15 at 20:48
  • No, but I am using auto filtering. I posted an edit to my question with a screenshot of further down in the stack trace. It appears that chart generation is what fires off the series of events resulting in formulas being calculated. – Jeremy Harris Nov 05 '15 at 20:50
  • AutoFiltering doesn't use calculations IIRC, though it's been a while since I looked at that part of the code; but charts did.... though I thought I'd put a disable in the charting for that.... I'll check the code and see if I missed anything there – Mark Baker Nov 05 '15 at 20:58
  • It looks like `writeChart` fires off a `refresh()` method for the chart, then that fires the same method for the PlotArea, then the DataSeries, then the DataSeriesValues. In `PHPExcel_Chart_DataSeriesValues`, the `refresh()` method runs `$calcEngine->_calculateFormulaValue(...)` with the `$pCell` value starting at A1. That then gets the worksheet as `$pCellParent` and then ends up doing something with the stack and checking some formulas. It's hard for me to follow without spending more time trying to understand it all. – Jeremy Harris Nov 05 '15 at 21:03
  • Ok, it looks like the chart stuff tries to validate any cells that are referenced by the chart. We have some really complex formulas. Is there something I can do in my fork to stop it from trying to validate formulas when generating charts? – Jeremy Harris Nov 05 '15 at 21:10
  • I've figured out what I feel is a reasonable solution to preventing charts from validating formulas using the `preCalculateFormulas` flag. For my use case it appears to be working and I am currently running my project off of my fork. I've submitted a PR back to PHPExcel, thanks for your help! https://github.com/PHPOffice/PHPExcel/pull/721 – Jeremy Harris Nov 06 '15 at 15:41
0

I have never used PHPExcel but I can rephrase that formula from SUMIF/S() to SUMPRODUCTS for you. You can use Alt + Enter to make those massive formula somewhat readable.

To get your head around the sumproduct, Excel treats TRUE/FALSE as a 1/0 when any math operation is applied, so you can {TRUE,TRUE,FALSE} x {FALSE,TRUE,TRUE} to get {0,1,0}. Then SUMPRODUCT takes the {0,1,0} and multiplies it by the row/column totals you're trying to filter and then sums the values. 

=IFERROR(
IF($L$3="UNITS",
COUNTIF('K1_Current_Accounts'!$M$2:$M$10000,'Snapshot Summary'!H5)
+COUNTIF('K1_Current_Available'!$M$2:$M$10000,'Snapshot Summary'!H5),
IF($L$3="Cost Basis",
SUMPRODUCT(--('K1_Current_Accounts'!$M$2:$M$10000='Snapshot Summary'!H5),'K1_Current_Accounts'!$E$2:$E$10000)
+SUMPRODUCT(--('K1_Current_Available'!$M$2:$M$10000='Snapshot Summary'!H5),'K1_Current_Available'!$E$2:$E$10000),
IF($L$3="Notes",
SUMPRODUCT('K1_Current_Accounts'!$AA$2:$AA$10000,--('K1_Current_Accounts'!$M$2:$M$10000='Snapshot Summary'!H5),--('K1_Current_Accounts'!$R$2:$R$10000='Snapshot Summary'!$A$7))
+SUMPRODUCT('K1_Current_Accounts'!$AA$2:$AA$10000,--('K1_Current_Accounts'!$M$2:$M$10000='Snapshot Summary'!H5),--('K1_Current_Accounts'!$R$2:$R$10000='Snapshot Summary'!$A$8))
+SUMPRODUCT('K1_Current_Accounts'!$AA$2:$AA$10000,--('K1_Current_Accounts'!$M$2:$M$10000='Snapshot Summary'!H5),--('K1_Current_Accounts'!$R$2:$R$10000='Snapshot Summary'!$A$9)),
IF($L$3="Cash",
SUMPRODUCT('K1_Historical_Accounts'!$AC$2:$AC$10000,--('K1_Historical_Accounts'!$M$2:$M$10000='Snapshot Summary'!H5),--('K1_Historical_Accounts'!$R$2:$R$10000='Snapshot Summary'!$A$10))))))
,"")
Kieran
  • 89
  • 1
  • 13