0

I'm seeing unusual errors when evaluating cells in my workbook. For context, POI is being used to input values into an excel workbook using defined names. Excel then takes the inputs and does a variety of calculations and lookups to calculate a set of output amounts, which is handled differently depending on which US State it corresponds to. The issue I'm seeing is when a specific set of sheets are evaluated (We'll use the state of Florida in this context), their cells evaluate to org.apache.poi.ss.formula.eval.ErrorEval [#REF!], and thus breaks all of the output values. Normally when I get incorrect outputs, I can quickly find them by saving the spreadsheet after inserting all of the inputs, evaluating the workbook (using evaluateAll()), then saving the updated "debugger" version to my local machine. The issue with this however, is that when I check the updated version, my output amounts seem to have been calculated successfully, and I'm not seeing any #RREF! errors anywhere within the workbook.

Using various input value combinations, which showed pointed to only a specific set of sheets cause the error.

Changing how the sheets are evaluated from the FormulaEvaluators evaluateFormulaCell(cell) function, then parsing the response in a switch that returns the cellValue as a string, to simply using evaluateAll().

Removing all external links to workbooks, and removing conditional formatting on the potentially troublesome sheets.

Enabling POI logs to try and find the root cause of the issue.

clearing cached values using FormulaEvaluator clearAllCachedResultValues() before performing an evaluation.

And lastly making sure that none of the spreadsheets contain any unsupported POI formulas with FunctionEval getSupportedFunctionNames() and getNotSupportedFunctionNames().

These are the first POI logs to display the ErrorEval[#REF!] issue.

    ...
    DEBUG - Evaluated Assumed Census!C41 to org.apache.poi.ss.formula.eval.NumberEval [0.0714285714285714]
    DEBUG - Evaluated Lookup Table 1!D41 to org.apache.poi.ss.formula.eval.NumberEval [0.0662525879917184]
    DEBUG - Evaluated Lookup Table 1!E41 to org.apache.poi.ss.formula.eval.NumberEval [0.13768115942029]
    DEBUG - Evaluated Lookup Table 1!E57 to org.apache.poi.ss.formula.eval.NumberEval [0.0196687370600414]
    DEBUG - Evaluated Calc Table 2 FL!O4 to org.apache.poi.ss.formula.eval.ErrorEval [#REF!]
    DEBUG - Evaluated Calc Table 2 FL!O88 to org.apache.poi.ss.formula.eval.ErrorEval [#REF!]
    DEBUG - Evaluated Input Sheet!E165 to org.apache.poi.ss.formula.eval.ErrorEval [#REF!]
    DEBUG - Evaluated Input Sheet!D165 to org.apache.poi.ss.formula.eval.ErrorEval [#REF!]
    DEBUG - Evaluated Input Sheet!D198 to org.apache.poi.ss.formula.eval.ErrorEval [#REF!]
    DEBUG - Evaluated Calc Table 2 FL!Q4 to org.apache.poi.ss.formula.eval.ErrorEval [#REF!]
    DEBUG - Evaluated Calc Table 2 FL!Q88 to org.apache.poi.ss.formula.eval.ErrorEval [#REF!]
    DEBUG - Evaluated Input Sheet!E167 to org.apache.poi.ss.formula.eval.ErrorEval [#REF!]
    DEBUG - Evaluated Input Sheet!D167 to org.apache.poi.ss.formula.eval.ErrorEval [#REF!]
    DEBUG - Evaluated Input Sheet!D200 to org.apache.poi.ss.formula.eval.ErrorEval [#REF!]
    DEBUG - Evaluated Calc Table 2 FL!P4 to org.apache.poi.ss.formula.eval.ErrorEval [#REF!]
    DEBUG - Evaluated Calc Table 2 FL!R4 to org.apache.poi.ss.formula.eval.ErrorEval [#REF!]
    DEBUG - Evaluated Calc Table 2 FL!R88 to org.apache.poi.ss.formula.eval.ErrorEval [#REF!]
    DEBUG - Evaluated Input Sheet!E168 to org.apache.poi.ss.formula.eval.ErrorEval [#REF!]
    ...

Also, this is how the spreadsheet is currently being evaluated.

    when (evaluator.evaluateFormulaCell(cell)) {
        CellType.NUMERIC -> cell.numericCellValue
        CellType.STRING -> cell.stringCellValue.toString().toDoubleOrNull() ?: 0.0
        CellType.BOOLEAN -> cell.booleanCellValue.toString().toDoubleOrNull() ?: 0.0
        CellType.ERROR -> cell.errorCellValue.toString().toDoubleOrNull() ?: 0.0
        else -> cell.toString().toDoubleOrNull() ?: 0.0
    }

Expected results are the values on the output cells from the debugger sheet, but instead the output values are either 0 or 23 in the results. The debugger spreadsheet does not contain any #REF! errors after it's processed with the inputs and evaluated by POI, instead it shows valid values that I'd normally expect.

UPDATE: Also tried changing evaluate to use evaluator.evaluate(cell).cellType in the switch statement, and now the debugger spreadsheet shows #REF! everywhere; however, seeing all the errors in excel doesn't really help indicate the root cause of this. When entering values manually, the spreadsheet calculates correctly as well.

Mike
  • 1
  • 2
  • First `#REF!` had got from cell `'Calc Table 2 FL'!O4`. So what formula is in that cerll? I suspect a formula which refers any of the other cells which evaluate in `#REF!`. So what formulas are in `'Calc Table 2 FL'!O88`, `'Input Sheet'!E165`, `'Input Sheet'!D165` and `'Input Sheet'!D198`? – Axel Richter Jun 08 '19 at 11:36

1 Answers1

0

In the Calc Table 2 FL sheet, the OFFSET formula was being used for a very large query (over 255 columns). After digging through the Apache POI source code for createOffset, we were able to find this:

    if (absRows.isOutOfBounds(0, 65535)) {
        throw new EvaluationException(ErrorEval.REF_INVALID);
    }

So there is an intentional limitation on the number of columns in this function for POI. I have no idea why, but will definitely like to hear why it exists

Mike
  • 1
  • 2