javaexcelapache-poi

Apache POI FormulaEvaluator returns #VALUE error

I am using Apache POI 5.2.5 to evaluate an XLOOKUP formula in an Excel sheet. A simple XLOOKUP works fine, but when I combine functions and concatenated ranges, the evaluator returns an error.

What Iʼm trying to do

  1. Set a formula on a cell via POI
  2. Evaluate it with FormulaEvaluator

What works

FormulaEvaluator evaluator = workbook.getCreationHelper()
    .createFormulaEvaluator();

// Simple XLOOKUP
cell.setCellFormula("_xlfn.XLOOKUP(C$11,"Sheet2!$A:$A,Sheet2!$Q:$Q)");

CellValue result = evaluator.evaluate(cell);
// result.getCellType() == CellType.NUMERIC or STRING as expected

What fails

When I change the formula to use LEFT, concatenation of three columns and a fallback value

FormulaEvaluator evaluator = workbook.getCreationHelper()
    .createFormulaEvaluator();

cell.setCellFormula("_xlfn.XLOOKUP(LEFT(C$18,11)&C$9&C$11,Sheet2!$A$4:$A$600&Sheet2!$C$4:$C$600&Sheet2!$E$4:$E$600,Sheet2!$N$4:$N$600,\"Not found\")");

CellValue result = evaluator.evaluate(cell);
// result.getCellType() == CellType.ERROR
// result.getErrorValue() == 15 (#VALUE! error)

Environment

  1. Apache POI 5.2.5
  2. Java 21
  3. Excel file created in Office 365 (xlsm)
Solution

  • Apache POI's FormulaEvaluator does not fully support dynamic array operations such as concatenating entire cell ranges on the fly within a formula's argument. So use a helper column for working around this evaluation limitation. It simplifies the formula into a structure FormulaEvaluator can successfully process.

    Instead of performing the complex concatenation inside the XLOOKUP formula pre-calculate it in a new column on Sheet2.

    1. In your Excel template (Sheet2) add a new helper column (column X) to hold the concatenated lookup keys.

    2. Set the helper formula in cell X4 of Sheet2:

    =A4&C4&E4

    3. Apply this formula down to X600. You can do this once in the template file or programmatically using POI if the sheet is dynamic

    4. Simplify your XLOOKUP formula you set in Java code to reference this new helper column

    5. Your Java code would then look like this:

    FormulaEvaluator evaluator = workbook.getCreationHelper()
    .createFormulaEvaluator();

String formula = "_xlfn.XLOOKUP(LEFT(C$18,11)&C$9&C$11, Sheet2!$X$4:$X$600, Sheet2!$N$4:$N$600, \"Not found\")";
cell.setCellFormula(formula);

CellValue result = evaluator.evaluate(cell);

    The lookup value remains the same but the lookup array is now a simple range.

    Alternative option is to perform the entire lookup logic within your code bypassing the FormulaEvaluator for this specific cell. It'll give you complete control but require writing more code to replicate the Excel formula's logic. Use it if you can't modify the structure of the file.