Apache POI和SUMPRODUCT公式评估(Apache POI and SUMPRODUCT formula evaluation)

 我有一个模板XLS文件，我使用Apache POI加载并在其中写入大量数据，然后将其另存为另一个文件。 我的XLS文件中有这样的公式： 
 
=SUMPRODUCT((DS!B:B="IN_THIS_ONLY")*(DS!D:D="New trade"))
 
 也试过了 
 
=SUMPRODUCT(0+(DS!B:B="IN_THIS_ONLY"),0+(DS!D:D="New trade"))
 
 如果我按下Excel中的单元格上的Enter，这些评估会正确。 但是，只是打电话 
 
HSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);
 
 似乎没有评估它们，也没有按下Excel中的“现在计算”按钮 - 所以我想这是一个特殊的公式或函数。 
 
 另一个更传统的COUNTIF和SUMIF工作正常，但是这些不允许指定多个条件。 
 
 POI不支持数组公式。 
 
 有什么办法可以使这些工作。 我正在使用POI 3.7版。 

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.

原文：https://stackoverflow.com/questions/5832695