POI 5.0.0 XSSF formula resolution error due to Excel flagging some formulae as array automatically

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

POI 5.0.0 XSSF formula resolution error due to Excel flagging some formulae as array automatically

Edd Cawley

On the attached the first cell resolves to FALSE in Excel 365 Version 2002 Build 12527.21504. When resolving in POI it fails, the logging is

 

[POI.FormulaEval]W   - evaluateFormula('Sheet1'/A1): [{   /* NamePtg */                  "index": 0 }, {   /* RefPtg */          "row": 0               , "rowRelative": true       , "column": 2      , "colRelative": true         , "formatReference": "C1" }, {   /* IntPtg */                 "value": 1 }, {   /* FuncVarPtg */                 "functionIndex": 29 /* 0x001d */            , "functionName": "INDEX"                , "numberOfOperands": 3             , "externalFunction": false            , "defaultOperandClass": 0           , "cetab": false }, {   /* IntPtg */       "value": 9 }, {   /* FuncVarPtg */                 "functionIndex": 115 /* 0x0073 */          , "functionName": "LEFT"                , "numberOfOperands": 2             , "externalFunction": false            , "defaultOperandClass": 32 /* 0x20 */     , "cetab": false }, {   /* RefPtg */   "row": 1             , "rowRelative": true       , "column": 1        , "colRelative": true         , "formatReference": "B2" }, {   /* EqualPtg */ }, {   /* RefPtg */      "row": 11 /* 0x0000000b */ , "rowRelative": true       , "column": 0      , "colRelative": true         , "formatReference": "A12" }, {   /* RefPtg */               "row": 10 /* 0x0000000a */      , "rowRelative": true       , "column": 0      , "colRelative": true         , "formatReference": "A11" }, {   /* NotEqualPtg */ }, {   /* FuncVarPtg */     "functionIndex": 36 /* 0x0024 */            , "functionName": "AND"                , "numberOfOperands": 2             , "externalFunction": false            , "defaultOperandClass": 32 /* 0x20 */     , "cetab": false }]

[POI.FormulaEval]I     * ptg 0: {   /* NamePtg */     "index": 0 }, stack: []

[POI.FormulaEval]I       = org.apache.poi.ss.formula.LazyAreaEval[Sheet1!A4:B6]

[POI.FormulaEval]I     * ptg 1: {   /* RefPtg */          "row": 0             , "rowRelative": true       , "column": 2      , "colRelative": true           , "formatReference": "C1" }, stack: [org.apache.poi.ss.formula.LazyAreaEval[Sheet1!A4:B6]]

[POI.FormulaEval]I       = org.apache.poi.ss.formula.LazyRefEval[Sheet1!C1]

[POI.FormulaEval]I     * ptg 2: {   /* IntPtg */            "value": 1 }, stack: [org.apache.poi.ss.formula.LazyAreaEval[Sheet1!A4:B6], org.apache.poi.ss.formula.LazyRefEval[Sheet1!C1]]

[POI.FormulaEval]I       = org.apache.poi.ss.formula.eval.NumberEval [1]

[POI.FormulaEval]I     * ptg 3: {   /* FuncVarPtg */                 "functionIndex": 29 /* 0x001d */            , "functionName": "INDEX"                , "numberOfOperands": 3             , "externalFunction": false            , "defaultOperandClass": 0           , "cetab": false }, stack: [org.apache.poi.ss.formula.LazyAreaEval[Sheet1!A4:B6], org.apache.poi.ss.formula.LazyRefEval[Sheet1!C1], org.apache.poi.ss.formula.eval.NumberEval [1]]

[POI.FormulaEval]I       = org.apache.poi.ss.formula.LazyAreaEval[Sheet1!A5:A5]

[POI.FormulaEval]I     * ptg 4: {   /* IntPtg */            "value": 9 }, stack: [org.apache.poi.ss.formula.LazyAreaEval[Sheet1!A5:A5]]

[POI.FormulaEval]I       = org.apache.poi.ss.formula.eval.NumberEval [9]

[POI.FormulaEval]I     * ptg 5: {   /* FuncVarPtg */                 "functionIndex": 115 /* 0x0073 */          , "functionName": "LEFT"   , "numberOfOperands": 2             , "externalFunction": false            , "defaultOperandClass": 32 /* 0x20 */   , "cetab": false }, stack: [org.apache.poi.ss.formula.LazyAreaEval[Sheet1!A5:A5], org.apache.poi.ss.formula.eval.NumberEval [9]]

[POI.FormulaEval]I       = org.apache.poi.ss.formula.eval.StringEval [b]

[POI.FormulaEval]I     * ptg 6: {   /* RefPtg */          "row": 1             , "rowRelative": true       , "column": 1      , "colRelative": true           , "formatReference": "B2" }, stack: [org.apache.poi.ss.formula.eval.StringEval [b]]

[POI.FormulaEval]I       = org.apache.poi.ss.formula.LazyRefEval[Sheet1!B2]

[POI.FormulaEval]I     * ptg 7: {   /* EqualPtg */ }, stack: [org.apache.poi.ss.formula.eval.StringEval [b], org.apache.poi.ss.formula.LazyRefEval[Sheet1!B2]]

[POI.FormulaEval]I       = org.apache.poi.ss.formula.eval.BoolEval [FALSE]

[POI.FormulaEval]I     * ptg 8: {   /* RefPtg */          "row": 11 /* 0x0000000b */      , "rowRelative": true       , "column": 0                 , "colRelative": true         , "formatReference": "A12" }, stack: [org.apache.poi.ss.formula.eval.BoolEval [FALSE]]

[POI.FormulaEval]I       = org.apache.poi.ss.formula.LazyRefEval[Sheet1!A12]

[POI.FormulaEval]I     * ptg 9: {   /* RefPtg */          "row": 10 /* 0x0000000a */      , "rowRelative": true       , "column": 0                 , "colRelative": true         , "formatReference": "A11" }, stack: [org.apache.poi.ss.formula.eval.BoolEval [FALSE], org.apache.poi.ss.formula.LazyRefEval[Sheet1!A12]]

[POI.FormulaEval]I       = org.apache.poi.ss.formula.LazyRefEval[Sheet1!A11]

[POI.FormulaEval]I     * ptg 10: {   /* NotEqualPtg */ }, stack: [org.apache.poi.ss.formula.eval.BoolEval [FALSE], org.apache.poi.ss.formula.LazyRefEval[Sheet1!A12], org.apache.poi.ss.formula.LazyRefEval[Sheet1!A11]]

[POI.FormulaEval]I       = org.apache.poi.ss.formula.eval.BoolEval [TRUE]

[POI.FormulaEval]I     * ptg 11: {   /* FuncVarPtg */              "functionIndex": 36 /* 0x0024 */            , "functionName": "AND"   , "numberOfOperands": 2             , "externalFunction": false            , "defaultOperandClass": 32 /* 0x20 */   , "cetab": false }, stack: [org.apache.poi.ss.formula.eval.BoolEval [FALSE], org.apache.poi.ss.formula.eval.BoolEval [TRUE]]

[POI.FormulaEval]I       = org.apache.poi.ss.formula.eval.ErrorEval [#VALUE!]

[POI.FormulaEval]I   finished eval of A1: org.apache.poi.ss.formula.eval.ErrorEval [#VALUE!]

 

So the fail is right at the end. The problem occurs as in sheet1.xml some formulae are flagged as array

 

<row r="1" x14ac:dyDescent="0.25" spans="1:4">

    <c r="A1" cm="1" t="b">

        <f ref="A1" t="array">AND(LEFT(INDEX(matchtest,C1,1),9)=B2,A12<>A11)

        </f>

        <v>0</v>

    </c>

    <c r="B1" cm="1" t="str">

        <f ref="B1" t="array">INDEX(matchtest,C1,1)</f>

        <v>b</v>

    </c>

    <c r="C1">

        <v>2</v>

    </c>

    <c r="D1" t="str">

        <f>INDEX(matchtest,1,1)</f>

        <v>a</v>

    </c>

</row>

 

I think index combined with a cell reference means the result could be an array (if index is 0) so Excel will say t=”array” but it’s not an array in the “Control+Shift+Enter in the formula box” way.

 

This array means XSSFSheet.java (I am using the 5.0.0 src download not from SVN) line 3561 is triggered

 

        if (f != null && f.getT() == STCellFormulaType.ARRAY && f.getRef() != null) {

            arrayFormulas.add(CellRangeAddress.valueOf(f.getRef()));

        }

 

The cell is in arrayFormulas. Then in OperationEvaluationFactory.java 131

 

                                                                if(evalCell.isPartOfArrayFormulaGroup()){

                                                                                // array arguments must be evaluated relative to the function defining range

                                                                                CellRangeAddress ca = evalCell.getArrayFormulaRange();

                                                                                return func.evaluateArray(args, ca.getFirstRow(), ca.getFirstColumn());

 

This pushes, in this case, a two argument array into BooleanFunction.java which then errors (line 174)

 

                public ValueEval evaluateArray(ValueEval[] args, int srcRowIndex, int srcColumnIndex) {

                                if (args.length != 1) {

                                                return ErrorEval.VALUE_INVALID;

                                }

 

My temporary fix is not a proper solution, I hacked  OperationEvaluationFactory to simply not call evaluateArray if all arguments are Booleans.

 

                                                                // start azquo hack

                                                                boolean allBooleans = args.length > 0;

                                                                for (ValueEval arg : args) {

                                                                                if (!(arg instanceof BoolEval)) {

                                                                                                allBooleans = false;

                                                                                                break;

                                                                                }

                                                                }

                                                                if (!allBooleans){

                                                                                if(evalCell.isPartOfArrayFormulaGroup()){

                                                                                                // array arguments must be evaluated relative to the function defining range

                                                                                                CellRangeAddress ca = evalCell.getArrayFormulaRange();

                                                                                                return func.evaluateArray(args, ca.getFirstRow(), ca.getFirstColumn());

                                                                                } else if (ec.isArraymode()){

                                                                                                return func.evaluateArray(args, ec.getRowIndex(), ec.getColumnIndex());

                                                                                }

                                                                }

                                                                // end azquo hack

 

I would guess that in some cases t=”array” should be ignored but I am not clear on how it is used. Perhaps related to bugs 65058 or 65059?

 

Edd Cawley



---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

poitest.xlsx (13K) Download Attachment