Apache POI:计算公式时的异常

Question

我已经浏览了此处发布的许多问题并参考了POI文档，但是我无法解决此问题。

问题：尝试重新计算公式时，我遇到异常。

公式：

=CONCATENATE("#DFLT=",COUNTIF(C5:C390,"=DEFAULTERS"),"; #NP=",COUNTIF(C5:C390,"=NOT PAID"),"; #PCsh=",COUNTIF(C5:C390,"=Paid Cash"),"; #PChk=",COUNTIF(C5:C390,"=Paid Cheque"),"; #PNeft=",COUNTIF(C5:C390,"=Paid Neft"))

例外：

10-22 17:13:15.177: E/AndroidRuntime(26300): FATAL EXCEPTION: main
10-22 17:13:15.177: E/AndroidRuntime(26300): java.lang.IllegalArgumentException: Unexpected eval class (org.apache.poi.ss.formula.eval.MissingArgEval)
10-22 17:13:15.177: E/AndroidRuntime(26300):    at org.apache.poi.ss.formula.eval.OperandResolver.coerceValueToString(OperandResolver.java:275)
10-22 17:13:15.177: E/AndroidRuntime(26300):    at org.apache.poi.ss.formula.functions.TextFunction.evaluateStringArg(TextFunction.java:40)
10-22 17:13:15.177: E/AndroidRuntime(26300):    at org.apache.poi.ss.formula.functions.TextFunction$8.evaluate(TextFunction.java:249)
10-22 17:13:15.177: E/AndroidRuntime(26300):    at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:132)
10-22 17:13:15.177: E/AndroidRuntime(26300):    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:525)
10-22 17:13:15.177: E/AndroidRuntime(26300):    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:288)
10-22 17:13:15.177: E/AndroidRuntime(26300):    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:702)
10-22 17:13:15.177: E/AndroidRuntime(26300):    at org.apache.poi.ss.formula.SheetRefEvaluator.getEvalForCell(SheetRefEvaluator.java:51)
10-22 17:13:15.177: E/AndroidRuntime(26300):    at org.apache.poi.ss.formula.LazyAreaEval.getRelativeValue(LazyAreaEval.java:51)
10-22 17:13:15.177: E/AndroidRuntime(26300):    at org.apache.poi.ss.formula.eval.AreaEvalBase.getValue(AreaEvalBase.java:109)
10-22 17:13:15.177: E/AndroidRuntime(26300):    at org.apache.poi.ss.formula.functions.CountUtils.countMatchingCellsInArea(CountUtils.java:55)
10-22 17:13:15.177: E/AndroidRuntime(26300):    at org.apache.poi.ss.formula.functions.Countif.countMatchingCellsInArea(Countif.java:452)
10-22 17:13:15.177: E/AndroidRuntime(26300):    at org.apache.poi.ss.formula.functions.Countif.evaluate(Countif.java:441)
10-22 17:13:15.177: E/AndroidRuntime(26300):    at org.apache.poi.ss.formula.functions.Fixed2ArgFunction.evaluate(Fixed2ArgFunction.java:33)
10-22 17:13:15.177: E/AndroidRuntime(26300):    at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:132)
10-22 17:13:15.177: E/AndroidRuntime(26300):    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:525)
10-22 17:13:15.177: E/AndroidRuntime(26300):    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:288)
10-22 17:13:15.177: E/AndroidRuntime(26300):    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:230)
10-22 17:13:15.177: E/AndroidRuntime(26300):    at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCellValue(HSSFFormulaEvaluator.java:354)
10-22 17:13:15.177: E/AndroidRuntime(26300):    at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluate(HSSFFormulaEvaluator.java:185)
10-22 17:13:15.177: E/AndroidRuntime(26300):    at com.android.impressico.readupdateexcelfile.ExcelFileWriter.writeCellToFile(ExcelFileWriter.java:121)

我进行了以下尝试来重新计算公式：

1. ExcelFile.workbook.getCreationHelper（）.createFormulaEvaluator（）.evaluateAll（）;

2. HSSFFormulaEvaluator.evaluateAllFormulaCells（ExcelFile.workbook）;

3. 对于（行 r ： 工作表） {对于 （单元格 c ： r） { if （c.getCellType（） == Cell.CELL_TYPE_FORMULA） { System.out.println（"Evaluate formula for cell = " + c.getCellFormula（））; evaluator.setDebugEvaluationOutputForNextEval（true）; 评估员.评估（c）;

Answer 1

启用 poi 日志后，我发现

=CONCATENATE("#DFLT=",COUNTIF(C5:C390,"=DEFAULTERS"),"; #NP=",COUNTIF(C5:C390,"=NOT PAID"),"; #PCsh=",COUNTIF(C5:C390,"=Paid Cash"),"; #PChk=",COUNTIF(C5:C390,"=Paid Cheque"),"; #PNeft=",COUNTIF(C5:C390,"=Paid Neft"))

是依赖的，也是Excel公式。

=CONCATENATE(" {",HZ5," - ",IA5,"}, ","{",IB5,,"}")

这个公式有一个额外的逗号（，），这在解析公式时会导致 MissingArgument，并且在计算这个缺失参数时会导致 IllegalArgumentException。

POI 日志显示：

10-30 16:56:18.377: I/System.out(26511): [POI.FormulaEval]I       * ptg 7: org.apache.poi.ss.formula.ptg.MissingArgPtg [ ]
10-30 16:56:18.377: I/System.out(26511): [POI.FormulaEval]I         = org.apache.poi.ss.formula.eval.MissingArgEval@42be7f58

Answer 2

POI 现在可以计算将范围运算符应用于区域引用的公式。

但是，POI 仍然无法解析这些公式。例如，这些调用将失败：

cell.setCellFormula("AVERAGE(J6:OFFSET(I6,0,DAY(TODAY())-1))");
cell.setCellFormula("SUM((C1:D2):(D2:E3))");

请参阅此链接以修复相同的问题