When I use SUMIF function in xls file. If predicate value is cell and cell value is null, org.apache.poi.ss.formula.functions.Sumif.accumulate method throws NullPointerException. This is stack trace: java.lang.NullPointerException at org.apache.poi.ss.formula.functions.Sumif.accumulate(Sumif.java:92) at org.apache.poi.ss.formula.functions.Sumif.sumMatchingCells(Sumif.java:83) at org.apache.poi.ss.formula.functions.Sumif.eval(Sumif.java:72) at org.apache.poi.ss.formula.functions.Sumif.evaluate(Sumif.java:65) at org.apache.poi.ss.formula.functions.Var2or3ArgFunction.evaluate(Var2or3ArgFunction.java:36) at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:132) at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:525) at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:288) at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:230) at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCellValue(HSSFFormulaEvaluator.java:351) at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCell(HSSFFormulaEvaluator.java:213) at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAllFormulaCells(HSSFFormulaEvaluator.java:324) at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAll(HSSFFormulaEvaluator.java:343) And this is the code that throws NullPointerException: (Sumif.java) 89 private static double accumulate(AreaEval aeRange, I_MatchPredicate mp, AreaEval aeSum, int relRowIndex, 90 int relColIndex) { 91 92 if (!mp.matches(aeRange.getRelativeValue(relRowIndex, relColIndex))) { // <------ This line 93 return 0.0; 94 } 95 ValueEval addend = aeSum.getRelativeValue(relRowIndex, relColIndex); 96 if (addend instanceof NumberEval) { 97 return ((NumberEval)addend).getNumberValue(); 98 } 99 // everything else (including string and boolean values) counts as zero 100 return 0.0; 101 } I guess that mp is null when predicate value is null. But I have not verified it. Anyone can fix this bug? ---- Yoshihiro Kameda
Can you please share either a file that triggers the problem, or the steps (ideally as a junit test case) to create one that does?
I've got the same issu. I add the attachment "test.xlsx", where you can find : __|__A__|__B__|__C__|__D__|__...__| _1|a | 1| | | | _2|b | 2| | | | _3| | | 0| | | _4|d | 3| | | | _5| | | | | | The formula is in C3 : =SUMIF($A$1:$A$4;A3;$B$1:$B$4) He here is the code to get the NullPointerException : private static void POI_sumif() throws IOException { Workbook wb = new XSSFWorkbook(new FileInputStream(new File("C:/Temp/test.xlsx"))); FormulaEvaluator evaluator = wb.getCreationHelper() .createFormulaEvaluator(); try { Cell c = wb.getSheetAt(0).getRow(2).getCell(2); System.out.println(c.getCellFormula()); evaluator.evaluateInCell(c); } catch (Exception e) { e.printStackTrace(); } }
Created attachment 31783 [details] for bug 56420
Thanks for the file. In r1607661 I've added a unit test for this problem The test is currently disabled though, as the bug exists... Would be great if someone could now use the unit test to dig into the formula code, and see why the problem is happening...
Fixed via r1686564, the formula contains a reference to an empty cell and Sumif did not take this possible null-result into account.