Bug 61652

Summary: Attempting to getConditionalFormattingForCell causes NPE
Product: POI Reporter: Blake <blake.watson>
Component: XSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: normal    
Priority: P2    
Version: 3.17-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   
Bug Depends on: 63264    
Bug Blocks:    
Attachments: The errant spreadsheet...

Description Blake 2017-10-23 17:15:03 UTC
Created attachment 35449 [details]
The errant spreadsheet...

In the attached spreadsheet, an attempt to fetch the conditional formatting for cell IRPPCalc!S12.

This is the code:

public class Main {
    public static void main(String[] args) {
            try {
                Workbook wb = WorkbookFactory.create(new File("condfail.xlsx"));
                Sheet sheet = (wb.getSheet("IRPPCalc"));
                Row row = (sheet.getRow(11));
                XSSFCell cell = (XSSFCell) row.getCell(18);
                WorkbookEvaluatorProvider fe = (WorkbookEvaluatorProvider) wb.getCreationHelper().createFormulaEvaluator();
                ConditionalFormattingEvaluator condfmt = new ConditionalFormattingEvaluator(wb, fe);
                System.out.println(">>>>" + condfmt.getConditionalFormattingForCell(cell));
            } catch(Exception e) {e.printStackTrace();};
    }
}

This is the resultant stack trace:

java.lang.NullPointerException
	at java.util.Calendar.setTime(Calendar.java:1770)
	at org.apache.poi.ss.formula.functions.EDate.evaluate(EDate.java:49)
	at org.apache.poi.ss.formula.UserDefinedFunction.evaluate(UserDefinedFunction.java:61)
	at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:129)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:514)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:278)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:722)
	at org.apache.poi.ss.formula.SheetRefEvaluator.getEvalForCell(SheetRefEvaluator.java:48)
	at org.apache.poi.ss.formula.SheetRangeEvaluator.getEvalForCell(SheetRangeEvaluator.java:74)
	at org.apache.poi.ss.formula.LazyRefEval.getInnerValueEval(LazyRefEval.java:39)
	at org.apache.poi.ss.formula.eval.OperandResolver.chooseSingleElementFromRef(OperandResolver.java:179)
	at org.apache.poi.ss.formula.eval.OperandResolver.getSingleValue(OperandResolver.java:62)
	at org.apache.poi.ss.formula.WorkbookEvaluator.dereferenceResult(WorkbookEvaluator.java:589)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:536)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:278)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:722)
	at org.apache.poi.ss.formula.SheetRefEvaluator.getEvalForCell(SheetRefEvaluator.java:48)
	at org.apache.poi.ss.formula.SheetRangeEvaluator.getEvalForCell(SheetRangeEvaluator.java:74)
	at org.apache.poi.ss.formula.LazyRefEval.getInnerValueEval(LazyRefEval.java:39)
	at org.apache.poi.ss.formula.eval.OperandResolver.chooseSingleElementFromRef(OperandResolver.java:179)
	at org.apache.poi.ss.formula.eval.OperandResolver.getSingleValue(OperandResolver.java:62)
	at org.apache.poi.ss.formula.eval.RelationalOperationEval.evaluate(RelationalOperationEval.java:65)
	at org.apache.poi.ss.formula.functions.Fixed2ArgFunction.evaluate(Fixed2ArgFunction.java:33)
	at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:119)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:514)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:278)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:722)
	at org.apache.poi.ss.formula.SheetRefEvaluator.getEvalForCell(SheetRefEvaluator.java:48)
	at org.apache.poi.ss.formula.SheetRangeEvaluator.getEvalForCell(SheetRangeEvaluator.java:74)
	at org.apache.poi.ss.formula.LazyRefEval.getInnerValueEval(LazyRefEval.java:39)
	at org.apache.poi.ss.formula.eval.OperandResolver.chooseSingleElementFromRef(OperandResolver.java:179)
	at org.apache.poi.ss.formula.eval.OperandResolver.getSingleValue(OperandResolver.java:62)
	at org.apache.poi.ss.formula.WorkbookEvaluator.dereferenceResult(WorkbookEvaluator.java:589)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:536)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:278)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:722)
	at org.apache.poi.ss.formula.SheetRefEvaluator.getEvalForCell(SheetRefEvaluator.java:48)
	at org.apache.poi.ss.formula.SheetRangeEvaluator.getEvalForCell(SheetRangeEvaluator.java:74)
	at org.apache.poi.ss.formula.LazyRefEval.getInnerValueEval(LazyRefEval.java:39)
	at org.apache.poi.ss.formula.eval.OperandResolver.chooseSingleElementFromRef(OperandResolver.java:179)
	at org.apache.poi.ss.formula.eval.OperandResolver.getSingleValue(OperandResolver.java:62)
	at org.apache.poi.ss.formula.eval.RelationalOperationEval.evaluate(RelationalOperationEval.java:64)
	at org.apache.poi.ss.formula.functions.Fixed2ArgFunction.evaluate(Fixed2ArgFunction.java:33)
	at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:119)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:514)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateNameFormula(WorkbookEvaluator.java:711)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:803)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:772)
	at org.apache.poi.ss.formula.EvaluationConditionalFormatRule.checkFormula(EvaluationConditionalFormatRule.java:378)
	at org.apache.poi.ss.formula.EvaluationConditionalFormatRule.matches(EvaluationConditionalFormatRule.java:306)
	at org.apache.poi.ss.formula.ConditionalFormattingEvaluator.getConditionalFormattingForCell(ConditionalFormattingEvaluator.java:176)
	at org.apache.poi.ss.formula.ConditionalFormattingEvaluator.getConditionalFormattingForCell(ConditionalFormattingEvaluator.java:206)
	at com.fidnip.Main.main(Main.java:29)
Comment 1 Blake 2017-10-23 17:16:38 UTC
Sorry, that should've been a little clearer:

"In the attached spreadsheet, an attempt to fetch the conditional formatting for cell IRPPCalc!S12."

should be:

"In the attached spreadsheet, an attempt to fetch the conditional formatting for cell IRPPCalc!S12 results in a Null Pointer Error in the java.util.Calendar.setTime function."
Comment 2 Dominik Stadler 2017-12-28 11:28:18 UTC
I tried to reproduce this, but could not, the evaluation of the rule works fine for me now, probably this was already fixed by some other change now. Please re-run your code with a recent nightly build to verify that it also works now for your.
Comment 3 Greg Woolsey 2019-03-16 06:44:38 UTC
My fix for #63264 now reproduces this bug via the existing unit test.  Apparently previously POI wasn't calculating conditional formatting when it should for this case.  Now off to fix the NPE.
Comment 4 Greg Woolsey 2019-03-16 06:57:20 UTC
Fixed in r1855627.  Turns out now that conditional formats are evaluated properly again, it shows back up.  Function EDATE() needed to handle cases where the input date argument evaluates to null (invalid date double).