I'm creating a spreadsheet with a block of SUMIFS formula cells. It creates fine, POI doesn't complain on cell.setCellFormula() and doesn't complain on evaluator.evaluateFormulaCell() either.
我正在创建一个包含SUMIFS公式单元格的电子表格。它创建良好，POI不会在cell.setCellFormula()上抱怨，也不会在evaluator.evaluateformula()上抱怨。
The SUMIFS cells are like this:
SUMIFS细胞是这样的:
SUMIFS(raw!$C$2:$C$2625, raw!$D$2:$D$2625, C$1, raw!$B$2:$B$2625, $A2)
Where "raw" is the name of a second sheet where the raw data comes from.
其中“raw”是原始数据来自的第二个表的名称。
When I open the spreadsheet in LibreOffice Calc, the cells have the correct formulas in them, but all show #ADDIN? instead of the value. F9 does nothing. Ctrl+Shift+F9 does nothing.
当我在LibreOffice Calc中打开电子表格时，单元格中有正确的公式，但是都显示#ADDIN吗?而不是价值。F9什么也不做。Ctrl + Shift + F9没有。
However, if I touch the formula (ie add a space and delete it) then press enter, the cell magically has the correct value in it. So LibreOffice clearly accepts the formula as-is.
然而，如果我触摸公式(即添加空格并删除它)，然后按enter，单元格就神奇地拥有正确的值。所以LibreOffice很清楚地接受了这个公式。
How do I get around this error? Or do I have to use some other XLS-writing library?
如何避免这个错误?或者我必须使用其他的xls编写库吗?
2 个解决方案
#1
1
Found the answer -- SUMIFS() is apparently not supported by HSSFWorkbook, but it is supported by XSSFWorkbook. Changed one line in my code to create an XSSF workbook instead, and the emitted XLSX file works just fine with LibreOffice.
找到答案——SUMIFS()显然不受HSSFWorkbook的支持，但它受到XSSFWorkbook的支持。更改了代码中的一行，以创建XSSF工作簿，而发出的XLSX文件在LibreOffice中运行良好。
#2
0
Try to use _xlfn.SUMIFS instead of SUMIFS. It worked fine for me! Or maybe you are running into an error due to lack of parenthesis around the formula, check this out: Apache POI 3.x - Logical formula resulting in #VALUE! (error) only in MS Excel 2010
尝试使用_xlfn。条件求和代替条件求和。对我来说很正常!或者您可能由于公式附近没有括号而遇到错误，请检查一下:Apache POI 3。x -逻辑公式导致#值!(错误)仅在MS Excel 2010中。
#1
1
Found the answer -- SUMIFS() is apparently not supported by HSSFWorkbook, but it is supported by XSSFWorkbook. Changed one line in my code to create an XSSF workbook instead, and the emitted XLSX file works just fine with LibreOffice.
找到答案——SUMIFS()显然不受HSSFWorkbook的支持，但它受到XSSFWorkbook的支持。更改了代码中的一行，以创建XSSF工作簿，而发出的XLSX文件在LibreOffice中运行良好。
#2
0
Try to use _xlfn.SUMIFS instead of SUMIFS. It worked fine for me! Or maybe you are running into an error due to lack of parenthesis around the formula, check this out: Apache POI 3.x - Logical formula resulting in #VALUE! (error) only in MS Excel 2010
尝试使用_xlfn。条件求和代替条件求和。对我来说很正常!或者您可能由于公式附近没有括号而遇到错误，请检查一下:Apache POI 3。x -逻辑公式导致#值!(错误)仅在MS Excel 2010中。