Apache POI and SUMPRODUCT formula evaluation

Question

I have a template XLS file that I load with Apache POI and write loads of data in it, then save it as another file. I have formulas in my XLS file like this:

=SUMPRODUCT((DS!B:B="IN_THIS_ONLY")*(DS!D:D="New trade"))

also tried

=SUMPRODUCT(0+(DS!B:B="IN_THIS_ONLY"),0+(DS!D:D="New trade"))

these evaluate correctly if I press Enter on the cell in Excel. However, simply calling

HSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);

does not seem to evaluate them, neither does pressing on the "Calculate now" button in Excel - so I guess this is a special formula or function.

The other, more conventional COUNTIFs and SUMIFs work fine, however these do not allow multiple conditions to be specified.

POI does not support array formulas.

Is there any way to make these work. I'm using POI version 3.7.

egbokul egbokul · Accepted Answer · 2011-05-02T11:48:36

One can press CTRL-ALT-F9 to manually re-evaluate all formulas forcefully in Excel.

And here is the trick to make it work automatically on workbook open.

Add the following to your formula:

+(NOW()*0)

so for example, my SUMPRODUCT above becomes

=SUMPRODUCT((DS!B:B="IN_THIS_ONLY")*(DS!D:D="New trade"))+(NOW()*0)

And this works! Excel now recalculates my special formula cells on open.

The reason for this is that NOW() is a volatile function. Here is where I learned about this: http://msdn.microsoft.com/en-us/library/bb687891.aspx

Application.CalculateFull also works, but only in Excel 2007 and later (and of course, one must enable macros to run). Unfortunately, in my case even though I use Excel 2007 my workbook will be opened by Excel 2003 users as well, so this was not an option.

Apache POI and SUMPRODUCT formula evaluation

2 Answers