Apache POI:计算公式时出现异常 [英] Apache POI: Exception while calculating formulae

问题描述

我已经解决了这里发布的许多问题，也参考了 POI 文档，但我无法解决这个问题.

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

问题:尝试重新计算公式时出现异常.

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

公式:

=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();

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

//HSSFFormulaEvaluator.evaluateAllFormulaCells(ExcelFile.workbook);

// HSSFFormulaEvaluator.evaluateAllFormulaCells(ExcelFile.workbook);

for (Row r : sheet) {对于(单元格 c:r){if (c.getCellType() == Cell.CELL_TYPE_FORMULA) {System.out.println("计算单元格公式 = " + c.getCellFormula());evalator.setDebugEvaluationOutputForNextEval(true);evaluator.evaluate(c);

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);

推荐答案

启用 poi 日志后，我发现

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

=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公式.

was dependent, were also excel formula.

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

这个公式有一个额外的逗号(,)，它在解析公式时导致了 MissingArgument，而在评估这个 Missing Argument 时会导致 IllegalArgumentException.

This formula had an extra comma(,), Which was causing an MissingArgument while parsing the formula, and while evaluating this Missing Argument results in 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

这篇关于Apache POI:计算公式时出现异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！