excel - Apache POI and SUMPRODUCT formula evaluation -

i have template xls file load apache poi , write loads of data in it, save file. have formulas in xls file 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 press enter on cell in excel. however, calling

hssfformulaevaluator.evaluateallformulacells(workbook); 

does not seem evaluate them, neither pressing on "calculate now" button in excel - guess special formula or function.

the other, more conventional countifs , sumifs work fine, these not allow multiple conditions specified.

poi not support array formulas.

is there way make these work. i'm using poi version 3.7.

one can press ctrl-alt-f9 manually re-evaluate formulas forcefully in excel.

and here trick make work automatically on workbook open.

add following formula:

+(now()*0) 

so example, sumproduct above becomes

=sumproduct((ds!b:b="in_this_only")*(ds!d:d="new trade"))+(now()*0) 

and works! excel recalculates special formula cells on open.

the reason now() volatile function. here learned this: http://msdn.microsoft.com/en-us/library/bb687891.aspx

application.calculatefull works, in excel 2007 , later (and of course, 1 must enable macros run). unfortunately, in case though use excel 2007 workbook opened excel 2003 users well, not option.