Author: centic
Date: Tue May 23 21:13:02 2017
New Revision: 1795963
URL: http://svn.apache.org/viewvc?rev=1795963&view=rev
Log:
Bug 61063: Add a RefListEval to handle UnionPtg and implement it for Rank(),
may be missing for other functions where ArrayEval is currently handled
Added:
poi/trunk/src/java/org/apache/poi/ss/formula/eval/RefListEval.java
poi/trunk/test-data/spreadsheet/61063.xlsx
Modified:
poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java
poi/trunk/src/java/org/apache/poi/ss/formula/functions/LogicalFunction.java
poi/trunk/src/java/org/apache/poi/ss/formula/functions/Rank.java
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/TestWorkbookEvaluator.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestTFunc.java
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java?rev=1795963&r1=1795962&r2=1795963&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java
(original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java Tue May
23 21:13:02 2017
@@ -28,19 +28,7 @@ import java.util.TreeSet;
import org.apache.poi.ss.SpreadsheetVersion;
import
org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.WorkbookNotFoundException;
import org.apache.poi.ss.formula.atp.AnalysisToolPak;
-import org.apache.poi.ss.formula.eval.BlankEval;
-import org.apache.poi.ss.formula.eval.BoolEval;
-import org.apache.poi.ss.formula.eval.ErrorEval;
-import org.apache.poi.ss.formula.eval.EvaluationException;
-import org.apache.poi.ss.formula.eval.ExternalNameEval;
-import org.apache.poi.ss.formula.eval.FunctionEval;
-import org.apache.poi.ss.formula.eval.FunctionNameEval;
-import org.apache.poi.ss.formula.eval.MissingArgEval;
-import org.apache.poi.ss.formula.eval.NotImplementedException;
-import org.apache.poi.ss.formula.eval.NumberEval;
-import org.apache.poi.ss.formula.eval.OperandResolver;
-import org.apache.poi.ss.formula.eval.StringEval;
-import org.apache.poi.ss.formula.eval.ValueEval;
+import org.apache.poi.ss.formula.eval.*;
import org.apache.poi.ss.formula.function.FunctionMetadataRegistry;
import org.apache.poi.ss.formula.functions.Choose;
import org.apache.poi.ss.formula.functions.FreeRefFunction;
@@ -84,12 +72,12 @@ public final class WorkbookEvaluator {
private final IStabilityClassifier _stabilityClassifier;
private final AggregatingUDFFinder _udfFinder;
- private boolean _ignoreMissingWorkbooks = false;
+ private boolean _ignoreMissingWorkbooks;
/**
* whether print detailed messages about the next formula evaluation
*/
- private boolean dbgEvaluationOutputForNextEval = false;
+ private boolean dbgEvaluationOutputForNextEval;
// special logger for formula evaluation output (because of possibly very
large output)
private final POILogger EVAL_LOG =
POILogFactory.getLogger("POI.FormulaEval");
@@ -415,11 +403,10 @@ public final class WorkbookEvaluator {
Stack<ValueEval> stack = new Stack<ValueEval>();
for (int i = 0, iSize = ptgs.length; i < iSize; i++) {
-
// since we don't know how to handle these yet :(
Ptg ptg = ptgs[i];
if (dbgEvaluationOutputIndent > 0) {
- EVAL_LOG.log(POILogger.INFO, dbgIndentStr + " * ptg " + i +
": " + ptg);
+ EVAL_LOG.log(POILogger.INFO, dbgIndentStr + " * ptg " + i +
": " + ptg + ", stack: " + stack);
}
if (ptg instanceof AttrPtg) {
AttrPtg attrPtg = (AttrPtg) ptg;
@@ -504,13 +491,17 @@ public final class WorkbookEvaluator {
continue;
}
+ if (ptg instanceof UnionPtg) {
+ ValueEval v2 = stack.pop();
+ ValueEval v1 = stack.pop();
+ stack.push(new RefListEval(v1, v2));
+ continue;
+ }
+
ValueEval opResult;
if (ptg instanceof OperationPtg) {
OperationPtg optg = (OperationPtg) ptg;
- if (optg instanceof UnionPtg) { continue; }
-
-
int numops = optg.getNumberOfOperands();
ValueEval[] ops = new ValueEval[numops];
Added: poi/trunk/src/java/org/apache/poi/ss/formula/eval/RefListEval.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/eval/RefListEval.java?rev=1795963&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/eval/RefListEval.java (added)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/eval/RefListEval.java Tue May
23 21:13:02 2017
@@ -0,0 +1,46 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.formula.eval;
+
+import java.util.ArrayList;
+import java.util.List;
+
+/**
+ * Handling of a list of values, e.g. the 2nd argument in RANK(A1,(B1,B2,B3),1)
+ */
+public class RefListEval implements ValueEval {
+ private final List<ValueEval> list = new ArrayList<ValueEval>();
+
+ public RefListEval(ValueEval v1, ValueEval v2) {
+ add(v1);
+ add(v2);
+ }
+
+ private void add(ValueEval v) {
+ // flatten multiple nested RefListEval
+ if(v instanceof RefListEval) {
+ list.addAll(((RefListEval)v).list);
+ } else {
+ list.add(v);
+ }
+ }
+
+ public List<ValueEval> getList() {
+ return list;
+ }
+}
Modified:
poi/trunk/src/java/org/apache/poi/ss/formula/functions/LogicalFunction.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/LogicalFunction.java?rev=1795963&r1=1795962&r2=1795963&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/functions/LogicalFunction.java
(original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/functions/LogicalFunction.java
Tue May 23 21:13:02 2017
@@ -17,16 +17,7 @@
package org.apache.poi.ss.formula.functions;
-import org.apache.poi.ss.formula.eval.AreaEval;
-import org.apache.poi.ss.formula.eval.BlankEval;
-import org.apache.poi.ss.formula.eval.BoolEval;
-import org.apache.poi.ss.formula.eval.ErrorEval;
-import org.apache.poi.ss.formula.eval.EvaluationException;
-import org.apache.poi.ss.formula.eval.NumberEval;
-import org.apache.poi.ss.formula.eval.OperandResolver;
-import org.apache.poi.ss.formula.eval.RefEval;
-import org.apache.poi.ss.formula.eval.StringEval;
-import org.apache.poi.ss.formula.eval.ValueEval;
+import org.apache.poi.ss.formula.eval.*;
/**
* Implementation of the various ISxxx Logical Functions, which
@@ -131,7 +122,7 @@ public abstract class LogicalFunction ex
public static final Function ISREF = new Fixed1ArgFunction() {
public ValueEval evaluate(int srcRowIndex, int srcColumnIndex,
ValueEval arg0) {
- if (arg0 instanceof RefEval || arg0 instanceof
AreaEval) {
+ if (arg0 instanceof RefEval || arg0 instanceof AreaEval
|| arg0 instanceof RefListEval) {
return BoolEval.TRUE;
}
return BoolEval.FALSE;
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/functions/Rank.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/Rank.java?rev=1795963&r1=1795962&r2=1795963&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/functions/Rank.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/functions/Rank.java Tue May 23
21:13:02 2017
@@ -19,13 +19,7 @@
package org.apache.poi.ss.formula.functions;
-import org.apache.poi.ss.formula.eval.AreaEval;
-import org.apache.poi.ss.formula.eval.ErrorEval;
-import org.apache.poi.ss.formula.eval.EvaluationException;
-import org.apache.poi.ss.formula.eval.NumberEval;
-import org.apache.poi.ss.formula.eval.OperandResolver;
-import org.apache.poi.ss.formula.eval.RefEval;
-import org.apache.poi.ss.formula.eval.ValueEval;
+import org.apache.poi.ss.formula.eval.*;
/**
@@ -45,51 +39,56 @@ import org.apache.poi.ss.formula.eval.Va
public class Rank extends Var2or3ArgFunction {
public ValueEval evaluate(int srcRowIndex, int srcColumnIndex,
ValueEval arg0, ValueEval arg1) {
-
- AreaEval aeRange;
- double result;
try {
ValueEval ve = OperandResolver.getSingleValue(arg0,
srcRowIndex, srcColumnIndex);
- result = OperandResolver.coerceValueToDouble(ve);
+ double result = OperandResolver.coerceValueToDouble(ve);
if (Double.isNaN(result) || Double.isInfinite(result)) {
throw new
EvaluationException(ErrorEval.NUM_ERROR);
}
- aeRange = convertRangeArg(arg1);
+
+ if(arg1 instanceof RefListEval) {
+ return eval(result, ((RefListEval)arg1), true);
+ }
+
+ final AreaEval aeRange = convertRangeArg(arg1);
+
+ return eval(result, aeRange, true);
} catch (EvaluationException e) {
return e.getErrorEval();
}
- return eval(srcRowIndex, srcColumnIndex, result, aeRange, true);
}
public ValueEval evaluate(int srcRowIndex, int srcColumnIndex,
ValueEval arg0, ValueEval arg1, ValueEval arg2) {
-
- AreaEval aeRange;
- double result;
- boolean order=false;
try {
ValueEval ve = OperandResolver.getSingleValue(arg0,
srcRowIndex, srcColumnIndex);
- result = OperandResolver.coerceValueToDouble(ve);
+ final double result =
OperandResolver.coerceValueToDouble(ve);
if (Double.isNaN(result) || Double.isInfinite(result)) {
throw new
EvaluationException(ErrorEval.NUM_ERROR);
}
- aeRange = convertRangeArg(arg1);
-
- ve = OperandResolver.getSingleValue(arg2, srcRowIndex,
srcColumnIndex);
- int order_value = OperandResolver.coerceValueToInt(ve);
- if(order_value==0){
- order=true;
- }else if(order_value==1){
- order=false;
- }else throw new
EvaluationException(ErrorEval.NUM_ERROR);
-
+
+ ve = OperandResolver.getSingleValue(arg2, srcRowIndex,
srcColumnIndex);
+ int order_value = OperandResolver.coerceValueToInt(ve);
+ final boolean order;
+ if(order_value==0) {
+ order = true;
+ } else if(order_value==1) {
+ order = false;
+ } else {
+ throw new EvaluationException(ErrorEval.NUM_ERROR);
+ }
+
+ if(arg1 instanceof RefListEval) {
+ return eval(result, ((RefListEval)arg1), order);
+ }
+
+ final AreaEval aeRange = convertRangeArg(arg1);
+ return eval(result, aeRange, order);
} catch (EvaluationException e) {
return e.getErrorEval();
}
- return eval(srcRowIndex, srcColumnIndex, result, aeRange,
order);
}
- private static ValueEval eval(int srcRowIndex, int srcColumnIndex,
double arg0, AreaEval aeRange, boolean descending_order) {
-
+ private static ValueEval eval(double arg0, AreaEval aeRange, boolean
descending_order) {
int rank = 1;
int height=aeRange.getHeight();
int width= aeRange.getWidth();
@@ -105,9 +104,30 @@ public class Rank extends Var2or3ArgFunc
}
return new NumberEval(rank);
}
-
- private static Double getValue(AreaEval aeRange, int relRowIndex, int
relColIndex) {
+ private static ValueEval eval(double arg0, RefListEval aeRange, boolean
descending_order) {
+ int rank = 1;
+ for(ValueEval ve : aeRange.getList()) {
+ if (ve instanceof RefEval) {
+ ve = ((RefEval) ve).getInnerValueEval(((RefEval)
ve).getFirstSheetIndex());
+ }
+
+ final Double value;
+ if (ve instanceof NumberEval) {
+ value = ((NumberEval)ve).getNumberValue();
+ } else {
+ continue;
+ }
+
+ if(descending_order && value>arg0 || !descending_order &&
value<arg0){
+ rank++;
+ }
+ }
+
+ return new NumberEval(rank);
+ }
+
+ private static Double getValue(AreaEval aeRange, int relRowIndex, int
relColIndex) {
ValueEval addend = aeRange.getRelativeValue(relRowIndex,
relColIndex);
if (addend instanceof NumberEval) {
return ((NumberEval)addend).getNumberValue();
Modified:
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java?rev=1795963&r1=1795962&r2=1795963&view=diff
==============================================================================
---
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java
(original)
+++
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java
Tue May 23 21:13:02 2017
@@ -3188,4 +3188,23 @@ public final class TestXSSFBugs extends
wb.close();
}
+
+ @Test
+ public void bug61063() throws Exception {
+ Workbook wb = XSSFTestDataSamples.openSampleWorkbook("61063.xlsx");
+
+ FormulaEvaluator eval =
wb.getCreationHelper().createFormulaEvaluator();
+ Sheet s = wb.getSheetAt(0);
+
+ Row r = s.getRow(3);
+ Cell c = r.getCell(0);
+ assertEquals(CellType.FORMULA, c.getCellTypeEnum());
+ System.out.println(c.getCellFormula());
+ eval.setDebugEvaluationOutputForNextEval(true);
+ CellValue cv = eval.evaluate(c);
+ assertNotNull(cv);
+ assertEquals("Had: " + cv, 2.0, cv.getNumberValue(), 0.00001);
+
+ wb.close();
+ }
}
\ No newline at end of file
Modified:
poi/trunk/src/testcases/org/apache/poi/ss/formula/TestWorkbookEvaluator.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/TestWorkbookEvaluator.java?rev=1795963&r1=1795962&r2=1795963&view=diff
==============================================================================
---
poi/trunk/src/testcases/org/apache/poi/ss/formula/TestWorkbookEvaluator.java
(original)
+++
poi/trunk/src/testcases/org/apache/poi/ss/formula/TestWorkbookEvaluator.java
Tue May 23 21:13:02 2017
@@ -36,13 +36,7 @@ import org.apache.poi.ss.formula.eval.Er
import org.apache.poi.ss.formula.eval.MissingArgEval;
import org.apache.poi.ss.formula.eval.NumberEval;
import org.apache.poi.ss.formula.eval.ValueEval;
-import org.apache.poi.ss.formula.ptg.AreaErrPtg;
-import org.apache.poi.ss.formula.ptg.AttrPtg;
-import org.apache.poi.ss.formula.ptg.DeletedArea3DPtg;
-import org.apache.poi.ss.formula.ptg.DeletedRef3DPtg;
-import org.apache.poi.ss.formula.ptg.IntPtg;
-import org.apache.poi.ss.formula.ptg.Ptg;
-import org.apache.poi.ss.formula.ptg.RefErrorPtg;
+import org.apache.poi.ss.formula.ptg.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.CellValue;
@@ -112,7 +106,6 @@ public class TestWorkbookEvaluator {
*/
@Test
public void testMemFunc() {
-
Ptg[] ptgs = {
new IntPtg(42),
AttrPtg.SUM,
@@ -122,7 +115,6 @@ public class TestWorkbookEvaluator {
assertEquals(42, ((NumberEval)result).getNumberValue(), 0.0);
}
-
@Test
public void testEvaluateMultipleWorkbooks() {
HSSFWorkbook wbA =
HSSFTestDataSamples.openSampleWorkbook("multibookFormulaA.xls");
@@ -227,7 +219,6 @@ public class TestWorkbookEvaluator {
/**
* Functions like IF, INDIRECT, INDEX, OFFSET etc can return AreaEvals
which
* should be dereferenced by the evaluator
- * @throws IOException
*/
@Test
public void testResultOutsideRange() throws IOException {
@@ -262,7 +253,6 @@ public class TestWorkbookEvaluator {
/**
* formulas with defined names.
- * @throws IOException
*/
@Test
public void testNamesInFormulas() throws IOException {
@@ -406,7 +396,7 @@ public class TestWorkbookEvaluator {
final String formula, final CellType cellType, final String
expectedFormula, final double expectedValue) {
testIFEqualsFormulaEvaluation_evaluate(formula, cellType,
expectedFormula, expectedValue);
testIFEqualsFormulaEvaluation_evaluateFormulaCell(formula, cellType,
expectedFormula, expectedValue);
- testIFEqualsFormulaEvaluation_evaluateInCell(formula, cellType,
expectedFormula, expectedValue);
+ testIFEqualsFormulaEvaluation_evaluateInCell(formula, cellType,
expectedValue);
testIFEqualsFormulaEvaluation_evaluateAll(formula, cellType,
expectedFormula, expectedValue);
testIFEqualsFormulaEvaluation_evaluateAllFormulaCells(formula,
cellType, expectedFormula, expectedValue);
}
@@ -552,7 +542,7 @@ public class TestWorkbookEvaluator {
}
private void testIFEqualsFormulaEvaluation_evaluateInCell(
- String formula, CellType cellType, String expectedFormula, double
expectedResult) {
+ String formula, CellType cellType, double expectedResult) {
Workbook wb = testIFEqualsFormulaEvaluation_setup(formula, cellType);
Cell D1 = wb.getSheet("IFEquals").getRow(0).getCell(3);
@@ -564,7 +554,9 @@ public class TestWorkbookEvaluator {
try {
D1.getCellFormula();
fail("cell formula should be overwritten with formula result");
- } catch (final IllegalStateException expected) { }
+ } catch (final IllegalStateException expected) {
+ // expected here
+ }
assertEquals(CellType.NUMERIC, D1.getCellTypeEnum());
assertEquals(expectedResult, D1.getNumericCellValue(), EPSILON);
Modified:
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestTFunc.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestTFunc.java?rev=1795963&r1=1795962&r2=1795963&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestTFunc.java
(original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestTFunc.java
Tue May 23 21:13:02 2017
@@ -19,13 +19,7 @@ package org.apache.poi.ss.formula.functi
import junit.framework.TestCase;
-import org.apache.poi.ss.formula.eval.AreaEval;
-import org.apache.poi.ss.formula.eval.BlankEval;
-import org.apache.poi.ss.formula.eval.BoolEval;
-import org.apache.poi.ss.formula.eval.ErrorEval;
-import org.apache.poi.ss.formula.eval.NumberEval;
-import org.apache.poi.ss.formula.eval.StringEval;
-import org.apache.poi.ss.formula.eval.ValueEval;
+import org.apache.poi.ss.formula.eval.*;
/**
* Test cases for Excel function T()
@@ -43,6 +37,7 @@ public final class TestTFunc extends Tes
assertNotNull("result may never be null", result);
return result;
}
+
/**
* Simulates call: T(A1)
* where cell A1 has the specified innerValue
@@ -60,7 +55,6 @@ public final class TestTFunc extends Tes
}
public void testTextValues() {
-
confirmText("abc");
confirmText("");
confirmText(" ");
@@ -121,8 +115,7 @@ public final class TestTFunc extends Tes
};
AreaEval ae = EvalFactory.createAreaEval("C10:D11", areaValues);
- ValueEval ve;
- ve = invokeT(ae);
+ ValueEval ve = invokeT(ae);
confirmString(ve, "abc");
areaValues[0] = new NumberEval(5.0);
Added: poi/trunk/test-data/spreadsheet/61063.xlsx
URL:
http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/61063.xlsx?rev=1795963&view=auto
==============================================================================
Binary files poi/trunk/test-data/spreadsheet/61063.xlsx (added) and
poi/trunk/test-data/spreadsheet/61063.xlsx Tue May 23 21:13:02 2017 differ
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
