Bug 56420 - Sumif throws NullPointerException
Summary: Sumif throws NullPointerException
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 3.10-FINAL
Hardware: All All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks: 60858
  Show dependency tree
 
Reported: 2014-04-17 02:58 UTC by Yoshihiro Kameda
Modified: 2017-03-13 20:09 UTC (History)
1 user (show)



Attachments
for bug 56420 (8.99 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-07-03 13:06 UTC, Marc S.
Details

Note You need to log in before you can comment on or make changes to this bug.
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.