Bug 56420

Summary: Sumif throws NullPointerException
Product: POI Reporter: Yoshihiro Kameda <kameda.sbng>
Component: SS CommonAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: normal CC: czamarth
Priority: P2    
Version: 3.10-FINAL   
Target Milestone: ---   
Hardware: All   
OS: All   
Bug Depends on:    
Bug Blocks: 60858    
Attachments: for bug 56420

Description Yoshihiro Kameda 2014-04-17 02:58:33 UTC
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
Comment 1 Nick Burch 2014-04-17 10:50:14 UTC
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?
Comment 2 Marc S. 2014-07-03 13:05:07 UTC
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();
        }
    }
Comment 3 Marc S. 2014-07-03 13:06:23 UTC
Created attachment 31783 [details]
for bug 56420
Comment 4 Nick Burch 2014-07-03 14:32:02 UTC
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...
Comment 5 Dominik Stadler 2015-06-20 11:30:56 UTC
Fixed via r1686564, the formula contains a reference to an empty cell and Sumif did not take this possible null-result into account.