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
poitest.xlsx
Description: poitest.xlsx
--------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
