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)
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."
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.
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.
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).