Apache POI and SUMPRODUCT formula evaluation

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"))

a开发者_开发知识库lso 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.

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.

Is SumProduct an array based formula function?

If so, that would explain the issue. One option is to contribute a patch to POI to add the missing support. There's been some discussion on the dev list and bugzilla on what's needed, and if you were to post to the dev list then we'd be happy to help you get started.

Otherwise, you could just set the formula recalculation flag and get Excel to recalculate the value on load