javaapache-poi

IllegalStateException when invoking evaluate on WORKDAY function

Problem: Using v5.3.0, the minimal example below throws IllegalStateException when invoking evaluate() on a formula containing the WORKDAY function. The text of the formula includes a cell reference for the Days parameter.

Mitigation: The issue does not arise when the Days parameter is either a constant or a function call, as seen in the commented lines; either produces the expected result. When traversing a spreadsheet and catching the IllegalStateException, invoking getNumericCellValue() on the relevant Cell returns "the pre-calculated value," as expected.

Question: Is this a bug, or have I misunderstood the API?

Addendum: @PJ Fanning has submitted this as Bug 69418.

Code:

package org.example.poi;

import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/** @see <a href="https://stackoverflow.com/q/79135438/230513">Q&A</a> */
public class FormulaISE {

    private static final String LOG_KEY = "log4j2.loggerContextFactory";
    private static final String LOG_VALUE
        = "org.apache.logging.log4j.simple.SimpleLoggerContextFactory";

    public static void main(String[] args) {
        System.setProperty(LOG_KEY, LOG_VALUE);
        var book = new XSSFWorkbook();
        var sheet = book.createSheet();
        var row = sheet.createRow(0);
        var cellA1 = row.createCell(0);
        cellA1.setCellValue(3);
        var cellB1 = row.createCell(1);
        
        //cellB1.setCellFormula("WORKDAY(TODAY(), 3)");
        cellB1.setCellFormula("WORKDAY(TODAY(), A1)");
        //cellB1.setCellFormula("WORKDAY(TODAY(), INT(A1))");

        var eval = book.getCreationHelper().createFormulaEvaluator();
        var cellValue = eval.evaluate(cellB1);
        if (cellValue.getCellType().equals(CellType.NUMERIC)) {
            System.out.println(DateUtil.getLocalDateTime(cellValue.getNumberValue()));
        }
    }
}

Stack trace:

Exception in thread "main" java.lang.IllegalStateException: Failed to evaluate cell: Sheet0!B1, value: WORKDAY(TODAY(), A1)
    at org.apache.poi.xssf.usermodel.BaseXSSFFormulaEvaluator.evaluateFormulaCellValue(BaseXSSFFormulaEvaluator.java:76)
    at org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluate(BaseFormulaEvaluator.java:109)
    at org.example.poi.FormulaISE.main(FormulaISE.java:27)
Caused by: java.lang.IllegalStateException: Unexpected arg eval type (org.apache.poi.ss.formula.LazyRefEval)
    at org.apache.poi.ss.formula.eval.OperandResolver.coerceValueToDouble(OperandResolver.java:270)
    at org.apache.poi.ss.formula.atp.ArgumentsEvaluator.evaluateNumberArg(ArgumentsEvaluator.java:116)
    at org.apache.poi.ss.formula.atp.WorkdayFunction.evaluate(WorkdayFunction.java:67)
    at org.apache.poi.ss.formula.UserDefinedFunction.evaluate(UserDefinedFunction.java:77)
    at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:135)
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:537)
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:264)
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:205)
    at org.apache.poi.xssf.usermodel.BaseXSSFFormulaEvaluator.evaluateFormulaCellValue(BaseXSSFFormulaEvaluator.java:63)
    ... 2 more
Solution

  • For reference, @PJ Fanning has submitted this as Bug 69418. The corrected code was incorporated in Apache POI Version 5.4.0, as shown in the changelog. The informative revision, r1921651, appears to be inaccessible; alternatively, it may be seen in the project's mirror as Commit 7ea9561.