Apache POI:在计算公式时例外。

I have gone through many questions posted here and referred POI documentation too, but i am not able to resolve this issue.

我已经在这里发布了很多问题，也提到了POI文档，但是我无法解决这个问题。

Issue: When trying to re-calculate the formula i am getting an exception.

问题:当试图重新计算公式时，我得到一个例外。

Formula:

公式:

=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"))

Exception:

例外:

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)

I made following attempts to re-calculate formula:

我尝试重新计算公式:

// ExcelFile.workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();

.evaluateAll .createFormulaEvaluator / / ExcelFile.workbook.getCreationHelper()()();
// HSSFFormulaEvaluator.evaluateAllFormulaCells(ExcelFile.workbook);

/ / HSSFFormulaEvaluator.evaluateAllFormulaCells(ExcelFile.workbook);
for (Row r : sheet) { for (Cell c : r) { if (c.getCellType() == Cell.CELL_TYPE_FORMULA) { System.out.println("Evaluating formula for cell = " + c.getCellFormula()); evaluator.setDebugEvaluationOutputForNextEval(true); evaluator.evaluate(c);

for(行r: sheet) {for (Cell c: r) {if (c. getcelltype () = Cell.CELL_TYPE_FORMULA) {System.out。println(“计算cell =”+ c.getCellFormula()的公式);evaluator.setDebugEvaluationOutputForNextEval(真正的);evaluator.evaluate(c);

2 个解决方案

#1

After enabling the poi logs i found that the cell on which

在启用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"))

was dependent, were also excel formula.

依赖，也是excel公式。

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

This formula had an extra comma(,), Which was causing an MissingArgument while parsing the formula, and while evaluating this Missing Argument results in IllegalArgumentException.

这个公式有一个额外的逗号(，)，它在解析公式时引起了一个错误的争论，并且在对这个丢失的参数进行评估时，会导致IllegalArgumentException。

POI logs showed:

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

#2

POI can now evaluate formulas which apply the range operator to area refs.

POI现在可以计算公式，该公式适用范围算子对区域refs。

However, POI still cannot parse these formulas. For example, these calls will fail:

然而，POI仍然不能解析这些公式。例如，这些调用将失败:

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

Refer this link to fix for the same

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

#1