I've got a template XLS file which i load with Apache POI and write lots of data inside it, then save it as being another file. I've formulas during my XLS file such as this:

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

also attempted

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

these evaluate properly basically press Enter around the cell in Stand out. However, simply calling

HSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);

doesn't appear to judge them, neither does pressing around the "Calculate now" button in Stand out - and so i guess this can be a special formula or function.

Another, more conventional COUNTIFs and SUMIFs work fine, however, these do not let multiple conditions to become specified.

POI doesn't support array formulas.

Can there be in whatever way to create these work. I am using POI version 3.7.

It's possible to press Control-ALT-F9 to by hand re-evaluate all formulas vigorously in Stand out.

Here is the secret to really make it work instantly on workbook open.

Add the next for your formula:

+(NOW()*0)

so for instance, my SUMPRODUCT above becomes

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

Which works! Stand out now recalculates my special formula cells on open.

The reason behind this really is that NOW() is really a volatile function. Here's where I discovered this: http://msdn.microsoft.com/en-us/library/bb687891.aspx

Application.CalculateFull also works, only in Stand out 2007 and then (not to mention, you have to enable macros to operate). Regrettably, during my situation despite the fact that I personally use Stand out 2007 my workbook is going to be opened up by Stand out 2003 customers too, which means this wasn't a choice.

Is SumProduct an assortment based formula function?

If that's the case, that will explain the problem. One choice is to lead an area to POI to include the missing support. There is some discussion around the dev list and bugzilla on certain requirements, and when you had been to publish towards the dev list then we'd be at liberty to obtain began.

Otherwise, you can just set the formula recalculation flag and obtain Stand out to recalculate the worthiness on load