You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ye...@apache.org on 2017/12/18 15:54:50 UTC
svn commit: r1818587 - in /poi/trunk: src/java/org/apache/poi/ss/formula/
src/java/org/apache/poi/ss/formula/eval/
src/testcases/org/apache/poi/ss/formula/functions/ test-data/spreadsheet/
Author: yegor
Date: Mon Dec 18 15:54:50 2017
New Revision: 1818587
URL: http://svn.apache.org/viewvc?rev=1818587&view=rev
Log:
Bug 61859: support for evaluating comparison operators in array mode, detect array mode from formula ptgs
Added:
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestRelationalOperations.java (with props)
poi/trunk/test-data/spreadsheet/maxindextest.xls (with props)
Modified:
poi/trunk/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java
poi/trunk/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java
poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java
poi/trunk/src/java/org/apache/poi/ss/formula/eval/OperandResolver.java
poi/trunk/src/java/org/apache/poi/ss/formula/eval/RelationalOperationEval.java
poi/trunk/src/java/org/apache/poi/ss/formula/eval/TwoOperandNumericOperation.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestIndex.java
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java?rev=1818587&r1=1818586&r2=1818587&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java Mon Dec 18 15:54:50 2017
@@ -33,13 +33,8 @@ import org.apache.poi.ss.formula.eval.Re
import org.apache.poi.ss.formula.eval.StringEval;
import org.apache.poi.ss.formula.eval.ValueEval;
import org.apache.poi.ss.formula.functions.FreeRefFunction;
-import org.apache.poi.ss.formula.ptg.Area3DPtg;
-import org.apache.poi.ss.formula.ptg.Area3DPxg;
-import org.apache.poi.ss.formula.ptg.NameXPtg;
-import org.apache.poi.ss.formula.ptg.NameXPxg;
-import org.apache.poi.ss.formula.ptg.Ptg;
-import org.apache.poi.ss.formula.ptg.Ref3DPtg;
-import org.apache.poi.ss.formula.ptg.Ref3DPxg;
+import org.apache.poi.ss.formula.functions.Function;
+import org.apache.poi.ss.formula.ptg.*;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.ss.util.CellReference.NameType;
@@ -58,7 +53,8 @@ public final class OperationEvaluationCo
private final EvaluationTracker _tracker;
private final WorkbookEvaluator _bookEvaluator;
private final boolean _isSingleValue;
-
+ private final boolean _isInArrayContext;
+
public OperationEvaluationContext(WorkbookEvaluator bookEvaluator, EvaluationWorkbook workbook, int sheetIndex, int srcRowNum,
int srcColNum, EvaluationTracker tracker) {
this(bookEvaluator, workbook, sheetIndex, srcRowNum, srcColNum, tracker, true);
@@ -66,6 +62,11 @@ public final class OperationEvaluationCo
public OperationEvaluationContext(WorkbookEvaluator bookEvaluator, EvaluationWorkbook workbook, int sheetIndex, int srcRowNum,
int srcColNum, EvaluationTracker tracker, boolean isSingleValue) {
+ this(bookEvaluator, workbook, sheetIndex, srcRowNum, srcColNum, tracker, isSingleValue, null);
+ }
+
+ public OperationEvaluationContext(WorkbookEvaluator bookEvaluator, EvaluationWorkbook workbook, int sheetIndex, int srcRowNum,
+ int srcColNum, EvaluationTracker tracker, boolean isSingleValue, Ptg[] ptgs) {
_bookEvaluator = bookEvaluator;
_workbook = workbook;
_sheetIndex = sheetIndex;
@@ -73,6 +74,48 @@ public final class OperationEvaluationCo
_columnIndex = srcColNum;
_tracker = tracker;
_isSingleValue = isSingleValue;
+
+ _isInArrayContext = isInArrayContext(ptgs);
+ }
+
+ /**
+ * Check if the given formula should be evaluated in array mode.
+ *
+ * <p>
+ * Normally, array formulas are recognized from their definition:
+ * pressing Ctrl+Shift+Enter in Excel marks the input as an array entered formula.
+ *</p>
+ * <p>
+ * However, in some cases Excel evaluates tokens in array mode depending on the context.
+ * The <code>INDEX( area, row_num, [column_num])</code> function is an example:
+ *
+ * If the array argument includes more than one row and row_num is omitted or set to 0,
+ * the Excel INDEX function returns an array of the entire column. Similarly, if array
+ * includes more than one column and the column_num argument is omitted or set to 0,
+ * the INDEX formula returns the entire row
+ * </p>
+ *
+ * @param ptgs parsed formula to analyze
+ * @return whether the formula should be evaluated in array mode
+ */
+ private boolean isInArrayContext(Ptg[] ptgs){
+ boolean arrayMode = false;
+ if(ptgs != null) for(int j = ptgs.length - 1; j >= 0; j--){
+ if(ptgs[j] instanceof FuncVarPtg){
+ FuncVarPtg f = (FuncVarPtg)ptgs[j];
+ if(f.getName().equals("INDEX") && f.getNumberOfOperands() <= 3){
+ // check 2nd and 3rd arguments.
+ arrayMode = (ptgs[j - 1] instanceof IntPtg && ((IntPtg)ptgs[j - 1]).getValue() == 0)
+ || (ptgs[j - 2] instanceof IntPtg && ((IntPtg)ptgs[j - 2]).getValue() == 0);
+ if(arrayMode) break;
+ }
+ }
+ }
+ return arrayMode;
+ }
+
+ public boolean isInArrayContext(){
+ return _isInArrayContext;
}
public EvaluationWorkbook getWorkbook() {
@@ -478,7 +521,8 @@ public final class OperationEvaluationCo
// Need to evaluate the reference in the context of the other book
OperationEvaluationContext refWorkbookContext = new OperationEvaluationContext(
- refWorkbookEvaluator, refWorkbookEvaluator.getWorkbook(), -1, -1, -1, _tracker);
+ refWorkbookEvaluator, refWorkbookEvaluator.getWorkbook(), -1, -1, -1, _tracker,
+ true, evaluationName.getNameDefinition());
Ptg ptg = evaluationName.getNameDefinition()[0];
if (ptg instanceof Ref3DPtg){
@@ -500,4 +544,5 @@ public final class OperationEvaluationCo
return ErrorEval.REF_INVALID;
}
}
+
}
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java?rev=1818587&r1=1818586&r2=1818587&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java Mon Dec 18 15:54:50 2017
@@ -119,8 +119,8 @@ final class OperationEvaluatorFactory {
if (result != null) {
EvaluationSheet evalSheet = ec.getWorkbook().getSheet(ec.getSheetIndex());
EvaluationCell evalCell = evalSheet.getCell(ec.getRowIndex(), ec.getColumnIndex());
-
- if (evalCell.isPartOfArrayFormulaGroup() && result instanceof ArrayFunction)
+
+ if ((evalCell.isPartOfArrayFormulaGroup() || ec.isInArrayContext()) && result instanceof ArrayFunction)
return ((ArrayFunction) result).evaluateArray(args, ec.getRowIndex(), ec.getColumnIndex());
return result.evaluate(args, ec.getRowIndex(), (short) ec.getColumnIndex());
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=1818587&r1=1818586&r2=1818587&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 Mon Dec 18 15:54:50 2017
@@ -268,11 +268,12 @@ public final class WorkbookEvaluator {
if (!tracker.startEvaluate(cce)) {
return ErrorEval.CIRCULAR_REF_ERROR;
}
- OperationEvaluationContext ec = new OperationEvaluationContext(this, _workbook, sheetIndex, rowIndex, columnIndex, tracker);
try {
Ptg[] ptgs = _workbook.getFormulaTokens(srcCell);
+ OperationEvaluationContext ec = new OperationEvaluationContext
+ (this, _workbook, sheetIndex, rowIndex, columnIndex, tracker, true, ptgs);
if (evalListener == null) {
result = evaluateFormula(ec, ptgs);
} else {
@@ -779,15 +780,17 @@ public final class WorkbookEvaluator {
}
int rowIndex = ref == null ? -1 : ref.getRow();
short colIndex = ref == null ? -1 : ref.getCol();
+ Ptg[] ptgs = FormulaParser.parse(formula, (FormulaParsingWorkbook) getWorkbook(), FormulaType.CELL, sheetIndex, rowIndex);
final OperationEvaluationContext ec = new OperationEvaluationContext(
this,
getWorkbook(),
sheetIndex,
rowIndex,
colIndex,
- new EvaluationTracker(_cache)
+ new EvaluationTracker(_cache),
+ true,
+ ptgs
);
- Ptg[] ptgs = FormulaParser.parse(formula, (FormulaParsingWorkbook) getWorkbook(), FormulaType.CELL, sheetIndex, rowIndex);
return evaluateNameFormula(ptgs, ec);
}
@@ -836,7 +839,7 @@ public final class WorkbookEvaluator {
adjustRegionRelativeReference(ptgs, target, region);
- final OperationEvaluationContext ec = new OperationEvaluationContext(this, getWorkbook(), sheetIndex, target.getRow(), target.getCol(), new EvaluationTracker(_cache), formulaType.isSingleValue());
+ final OperationEvaluationContext ec = new OperationEvaluationContext(this, getWorkbook(), sheetIndex, target.getRow(), target.getCol(), new EvaluationTracker(_cache), formulaType.isSingleValue(), ptgs);
return evaluateNameFormula(ptgs, ec);
}
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/eval/OperandResolver.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/eval/OperandResolver.java?rev=1818587&r1=1818586&r2=1818587&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/eval/OperandResolver.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/eval/OperandResolver.java Mon Dec 18 15:54:50 2017
@@ -202,7 +202,7 @@ public final class OperandResolver {
if(!ae.isRow()) {
// multi-column, multi-row area
if(ae.containsRow(srcCellRow) && ae.containsColumn(srcCellCol)) {
- return ae.getAbsoluteValue(ae.getFirstRow(), ae.getFirstColumn());
+ return ae.getAbsoluteValue(srcCellRow, srcCellCol);
}
throw EvaluationException.invalidValue();
}
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/eval/RelationalOperationEval.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/eval/RelationalOperationEval.java?rev=1818587&r1=1818586&r2=1818587&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/eval/RelationalOperationEval.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/eval/RelationalOperationEval.java Mon Dec 18 15:54:50 2017
@@ -17,6 +17,8 @@
package org.apache.poi.ss.formula.eval;
+import org.apache.poi.ss.formula.CacheAreaEval;
+import org.apache.poi.ss.formula.functions.ArrayFunction;
import org.apache.poi.ss.formula.functions.Fixed2ArgFunction;
import org.apache.poi.ss.formula.functions.Function;
import org.apache.poi.ss.util.NumberComparer;
@@ -26,7 +28,7 @@ import org.apache.poi.ss.util.NumberComp
*
* @author Amol S. Deshmukh < amolweb at ya hoo dot com >
*/
-public abstract class RelationalOperationEval extends Fixed2ArgFunction {
+public abstract class RelationalOperationEval extends Fixed2ArgFunction implements ArrayFunction {
/**
* Converts a standard compare result (-1, 0, 1) to <code>true</code> or <code>false</code>
@@ -56,6 +58,7 @@ public abstract class RelationalOperatio
* Blank < Positive numbers
* </pre>
*/
+
public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {
ValueEval vA;
@@ -71,6 +74,86 @@ public abstract class RelationalOperatio
return BoolEval.valueOf(result);
}
+ public ValueEval evaluateArray(ValueEval[] args, int srcRowIndex, int srcColumnIndex) {
+ ValueEval arg0 = args[0];
+ ValueEval arg1 = args[1];
+
+ int w1, w2, h1, h2;
+ int a1FirstCol = 0, a1FirstRow = 0;
+ if (arg0 instanceof AreaEval) {
+ AreaEval ae = (AreaEval)arg0;
+ w1 = ae.getWidth();
+ h1 = ae.getHeight();
+ a1FirstCol = ae.getFirstColumn();
+ a1FirstRow = ae.getFirstRow();
+ } else if (arg0 instanceof RefEval){
+ RefEval ref = (RefEval)arg0;
+ w1 = 1;
+ h1 = 1;
+ a1FirstCol = ref.getColumn();
+ a1FirstRow = ref.getRow();
+ } else {
+ w1 = 1;
+ h1 = 1;
+ }
+ int a2FirstCol = 0, a2FirstRow = 0;
+ if (arg1 instanceof AreaEval) {
+ AreaEval ae = (AreaEval)arg1;
+ w2 = ae.getWidth();
+ h2 = ae.getHeight();
+ a2FirstCol = ae.getFirstColumn();
+ a2FirstRow = ae.getFirstRow();
+ } else if (arg1 instanceof RefEval){
+ RefEval ref = (RefEval)arg1;
+ w2 = 1;
+ h2 = 1;
+ a2FirstCol = ref.getColumn();
+ a2FirstRow = ref.getRow();
+ } else {
+ w2 = 1;
+ h2 = 1;
+ }
+
+ int width = Math.max(w1, w2);
+ int height = Math.max(h1, h2);
+
+ ValueEval[] vals = new ValueEval[height * width];
+
+ int idx = 0;
+ for(int i = 0; i < height; i++){
+ for(int j = 0; j < width; j++){
+ ValueEval vA;
+ try {
+ vA = OperandResolver.getSingleValue(arg0, a1FirstRow + i, a1FirstCol + j);
+ } catch (EvaluationException e) {
+ vA = e.getErrorEval();
+ }
+ ValueEval vB;
+ try {
+ vB = OperandResolver.getSingleValue(arg1, a2FirstRow + i, a2FirstCol + j);
+ } catch (EvaluationException e) {
+ vB = e.getErrorEval();
+ }
+ if(vA instanceof ErrorEval){
+ vals[idx++] = vA;
+ } else if (vB instanceof ErrorEval) {
+ vals[idx++] = vB;
+ } else {
+ int cmpResult = doCompare(vA, vB);
+ boolean result = convertComparisonResult(cmpResult);
+ vals[idx++] = BoolEval.valueOf(result);
+ }
+
+ }
+ }
+
+ if (vals.length == 1) {
+ return vals[0];
+ }
+
+ return new CacheAreaEval(srcRowIndex, srcColumnIndex, srcRowIndex + height - 1, srcColumnIndex + width - 1, vals);
+ }
+
private static int doCompare(ValueEval va, ValueEval vb) {
// special cases when one operand is blank
if (va == BlankEval.instance) {
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/eval/TwoOperandNumericOperation.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/eval/TwoOperandNumericOperation.java?rev=1818587&r1=1818586&r2=1818587&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/eval/TwoOperandNumericOperation.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/eval/TwoOperandNumericOperation.java Mon Dec 18 15:54:50 2017
@@ -64,7 +64,7 @@ public abstract class TwoOperandNumericO
protected abstract double evaluate(double d0, double d1) throws EvaluationException;
private final class ArrayEval extends TwoArrayArg {
- private final MutableValueCollector instance = new MutableValueCollector(false, true);
+ private final MutableValueCollector instance = new MutableValueCollector(true, true);
protected double[] collectValues(ValueEval arg) throws EvaluationException {
return instance.collectValues(arg);
Modified: poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestIndex.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestIndex.java?rev=1818587&r1=1818586&r2=1818587&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestIndex.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestIndex.java Mon Dec 18 15:54:50 2017
@@ -22,11 +22,17 @@ import java.util.Arrays;
import junit.framework.AssertionFailedError;
import junit.framework.TestCase;
+import org.apache.poi.hssf.HSSFTestDataSamples;
+import org.apache.poi.hssf.usermodel.HSSFCell;
+import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
+import org.apache.poi.hssf.usermodel.HSSFSheet;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.eval.AreaEval;
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.WorkbookEvaluator;
+import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
/**
@@ -154,4 +160,145 @@ public final class TestIndex extends Tes
assertEquals(cra.getLastColumn(), ae.getLastColumn());
return ae;
}
+
+ public void test61859(){
+ Workbook wb = HSSFTestDataSamples.openSampleWorkbook("maxindextest.xls");
+ FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
+
+ Sheet example1 = wb.getSheetAt(0);
+ Cell ex1cell1 = example1.getRow(1).getCell(6);
+ assertEquals("MAX(INDEX(($B$2:$B$11=F2)*$A$2:$A$11,0))", ex1cell1.getCellFormula());
+ fe.evaluate(ex1cell1);
+ assertEquals(4.0, ex1cell1.getNumericCellValue());
+
+ Cell ex1cell2 = example1.getRow(2).getCell(6);
+ assertEquals("MAX(INDEX(($B$2:$B$11=F3)*$A$2:$A$11,0))", ex1cell2.getCellFormula());
+ fe.evaluate(ex1cell2);
+ assertEquals(10.0, ex1cell2.getNumericCellValue());
+
+ Cell ex1cell3 = example1.getRow(3).getCell(6);
+ assertEquals("MAX(INDEX(($B$2:$B$11=F4)*$A$2:$A$11,0))", ex1cell3.getCellFormula());
+ fe.evaluate(ex1cell3);
+ assertEquals(20.0, ex1cell3.getNumericCellValue());
+ }
+
+ /**
+ * If both the Row_num and Column_num arguments are used,
+ * INDEX returns the value in the cell at the intersection of Row_num and Column_num
+ */
+ public void testReference2DArea(){
+ Workbook wb = new HSSFWorkbook();
+ Sheet sheet = wb.createSheet();
+ /**
+ * 1 2 3
+ * 4 5 6
+ * 7 8 9
+ */
+ int val = 0;
+ for(int i = 0; i < 3; i++){
+ Row row = sheet.createRow(i);
+ for(int j = 0; j < 3; j++){
+ row.createCell(j).setCellValue(++val);
+ }
+ }
+ FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
+
+ Cell c1 = sheet.getRow(0).createCell(5);
+ c1.setCellFormula("INDEX(A1:C3,2,2)");
+ Cell c2 = sheet.getRow(0).createCell(6);
+ c2.setCellFormula("INDEX(A1:C3,3,2)");
+
+ assertEquals(5.0, fe.evaluate(c1).getNumberValue());
+ assertEquals(8.0, fe.evaluate(c2).getNumberValue());
+ }
+
+ /**
+ * If Column_num is 0 (zero), INDEX returns the array of values for the entire row.
+ */
+ public void testArrayArgument_RowLookup(){
+ Workbook wb = new HSSFWorkbook();
+ Sheet sheet = wb.createSheet();
+ /**
+ * 1 2 3
+ * 4 5 6
+ * 7 8 9
+ */
+ int val = 0;
+ for(int i = 0; i < 3; i++){
+ Row row = sheet.createRow(i);
+ for(int j = 0; j < 3; j++){
+ row.createCell(j).setCellValue(++val);
+ }
+ }
+ Cell c1 = sheet.getRow(0).createCell(5);
+ c1.setCellFormula("SUM(INDEX(A1:C3,1,0))"); // sum of all values in the 1st row: 1 + 2 + 3 = 6
+
+ Cell c2 = sheet.getRow(0).createCell(6);
+ c2.setCellFormula("SUM(INDEX(A1:C3,2,0))"); // sum of all values in the 2nd row: 4 + 5 + 6 = 15
+
+ FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
+
+ assertEquals(6.0, fe.evaluate(c1).getNumberValue());
+ assertEquals(15.0, fe.evaluate(c2).getNumberValue());
+
+ }
+
+ /**
+ * If Row_num is 0 (zero), INDEX returns the array of values for the entire column.
+ */
+ public void testArrayArgument_ColumnLookup(){
+ Workbook wb = new HSSFWorkbook();
+ Sheet sheet = wb.createSheet();
+ /**
+ * 1 2 3
+ * 4 5 6
+ * 7 8 9
+ */
+ int val = 0;
+ for(int i = 0; i < 3; i++){
+ Row row = sheet.createRow(i);
+ for(int j = 0; j < 3; j++){
+ row.createCell(j).setCellValue(++val);
+ }
+ }
+ Cell c1 = sheet.getRow(0).createCell(5);
+ c1.setCellFormula("SUM(INDEX(A1:C3,0,1))"); // sum of all values in the 1st column: 1 + 4 + 7 = 12
+
+ Cell c2 = sheet.getRow(0).createCell(6);
+ c2.setCellFormula("SUM(INDEX(A1:C3,0,3))"); // sum of all values in the 3rd column: 3 + 6 + 9 = 18
+
+ FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
+
+ assertEquals(12.0, fe.evaluate(c1).getNumberValue());
+ assertEquals(18.0, fe.evaluate(c2).getNumberValue());
+ }
+
+ /**
+ * =SUM(B1:INDEX(B1:B3,2))
+ *
+ * The sum of the range starting at B1, and ending at the intersection of the 2nd row of the range B1:B3,
+ * which is the sum of B1:B2.
+ */
+ public void testDynamicReference(){
+ Workbook wb = new HSSFWorkbook();
+ Sheet sheet = wb.createSheet();
+ /**
+ * 1 2 3
+ * 4 5 6
+ * 7 8 9
+ */
+ int val = 0;
+ for(int i = 0; i < 3; i++){
+ Row row = sheet.createRow(i);
+ for(int j = 0; j < 3; j++){
+ row.createCell(j).setCellValue(++val);
+ }
+ }
+ Cell c1 = sheet.getRow(0).createCell(5);
+ c1.setCellFormula("SUM(B1:INDEX(B1:B3,2))"); // B1:INDEX(B1:B3,2) evaluates to B1:B2
+
+ FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
+
+ assertEquals(7.0, fe.evaluate(c1).getNumberValue());
+ }
}
Added: poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestRelationalOperations.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestRelationalOperations.java?rev=1818587&view=auto
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestRelationalOperations.java (added)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestRelationalOperations.java Mon Dec 18 15:54:50 2017
@@ -0,0 +1,192 @@
+/* ====================================================================
+ 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.functions;
+
+import junit.framework.TestCase;
+import org.apache.poi.hssf.HSSFTestDataSamples;
+import org.apache.poi.hssf.usermodel.HSSFCell;
+import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
+import org.apache.poi.hssf.usermodel.HSSFSheet;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.ss.formula.CacheAreaEval;
+import org.apache.poi.ss.formula.eval.*;
+import org.apache.poi.ss.usermodel.CellValue;
+
+import static org.junit.Assert.assertEquals;
+
+public class TestRelationalOperations extends TestCase {
+
+ /**
+ * (1, 1)(1, 1) = 1
+ *
+ * evaluates to
+ *
+ * (TRUE, TRUE)(TRUE, TRUE)
+ *
+ */
+ public void testEqMatrixByScalar_Numbers() {
+ ValueEval[] values = new ValueEval[4];
+ for (int i = 0; i < values.length; i++) {
+ values[i] = new NumberEval(1);
+ }
+
+ ValueEval arg1 = EvalFactory.createAreaEval("A1:B2", values);
+ ValueEval arg2 = EvalFactory.createRefEval("D1", new NumberEval(1));
+
+ RelationalOperationEval eq = (RelationalOperationEval)RelationalOperationEval.EqualEval;
+ ValueEval result = eq.evaluateArray(new ValueEval[]{ arg1, arg2}, 2, 5);
+
+ assertEquals("expected CacheAreaEval", CacheAreaEval.class, result.getClass());
+ CacheAreaEval ce = (CacheAreaEval)result;
+ assertEquals(2, ce.getWidth());
+ assertEquals(2, ce.getHeight());
+ for(int i =0; i < ce.getHeight(); i++){
+ for(int j = 0; j < ce.getWidth(); j++){
+ assertEquals(BoolEval.TRUE, ce.getRelativeValue(i, j));
+ }
+ }
+ }
+
+ public void testEqMatrixByScalar_String() {
+ ValueEval[] values = new ValueEval[4];
+ for (int i = 0; i < values.length; i++) {
+ values[i] = new StringEval("ABC");
+ }
+
+ ValueEval arg1 = EvalFactory.createAreaEval("A1:B2", values);
+ ValueEval arg2 = EvalFactory.createRefEval("D1", new StringEval("ABC"));
+ RelationalOperationEval eq = (RelationalOperationEval)RelationalOperationEval.EqualEval;
+ ValueEval result = eq.evaluateArray(new ValueEval[]{ arg1, arg2}, 2, 5);
+
+ assertEquals("expected CacheAreaEval", CacheAreaEval.class, result.getClass());
+ CacheAreaEval ce = (CacheAreaEval)result;
+ assertEquals(2, ce.getWidth());
+ assertEquals(2, ce.getHeight());
+ for(int i =0; i < ce.getHeight(); i++){
+ for(int j = 0; j < ce.getWidth(); j++){
+ assertEquals(BoolEval.TRUE, ce.getRelativeValue(i, j));
+ }
+ }
+ }
+
+ public void testEqMatrixBy_Row() {
+ ValueEval[] matrix = {
+ new NumberEval(-1), new NumberEval(1),
+ new NumberEval(-1), new NumberEval(1)
+ };
+
+
+ ValueEval[] row = {
+ new NumberEval(1), new NumberEval(1), new NumberEval(1)
+ };
+
+ ValueEval[] expected = {
+ BoolEval.FALSE, BoolEval.TRUE, ErrorEval.VALUE_INVALID,
+ BoolEval.FALSE, BoolEval.TRUE, ErrorEval.VALUE_INVALID
+ };
+
+ ValueEval arg1 = EvalFactory.createAreaEval("A1:B2", matrix);
+ ValueEval arg2 = EvalFactory.createAreaEval("A4:C4", row);
+ RelationalOperationEval eq = (RelationalOperationEval)RelationalOperationEval.EqualEval;
+ ValueEval result = eq.evaluateArray(new ValueEval[]{ arg1, arg2}, 4, 5);
+
+ assertEquals("expected CacheAreaEval", CacheAreaEval.class, result.getClass());
+ CacheAreaEval ce = (CacheAreaEval)result;
+ assertEquals(3, ce.getWidth());
+ assertEquals(2, ce.getHeight());
+ int idx = 0;
+ for(int i =0; i < ce.getHeight(); i++){
+ for(int j = 0; j < ce.getWidth(); j++){
+ assertEquals("[" + i + "," + j + "]", expected[idx++], ce.getRelativeValue(i, j));
+ }
+ }
+ }
+
+ public void testEqMatrixBy_Column() {
+ ValueEval[] matrix = {
+ new NumberEval(-1), new NumberEval(1),
+ new NumberEval(-1), new NumberEval(1)
+ };
+
+
+ ValueEval[] column = {
+ new NumberEval(1),
+ new NumberEval(1),
+ new NumberEval(1)
+ };
+
+ ValueEval[] expected = {
+ BoolEval.FALSE, BoolEval.TRUE,
+ BoolEval.FALSE, BoolEval.TRUE,
+ ErrorEval.VALUE_INVALID, ErrorEval.VALUE_INVALID
+ };
+
+ ValueEval arg1 = EvalFactory.createAreaEval("A1:B2", matrix);
+ ValueEval arg2 = EvalFactory.createAreaEval("A6:A8", column);
+ RelationalOperationEval eq = (RelationalOperationEval)RelationalOperationEval.EqualEval;
+ ValueEval result = eq.evaluateArray(new ValueEval[]{ arg1, arg2}, 4, 6);
+
+ assertEquals("expected CacheAreaEval", CacheAreaEval.class, result.getClass());
+ CacheAreaEval ce = (CacheAreaEval)result;
+ assertEquals(2, ce.getWidth());
+ assertEquals(3, ce.getHeight());
+ int idx = 0;
+ for(int i =0; i < ce.getHeight(); i++){
+ for(int j = 0; j < ce.getWidth(); j++){
+ assertEquals("[" + i + "," + j + "]", expected[idx++], ce.getRelativeValue(i, j));
+ }
+ }
+ }
+
+ public void testEqMatrixBy_Matrix() {
+ // A1:B2
+ ValueEval[] matrix1 = {
+ new NumberEval(-1), new NumberEval(1),
+ new NumberEval(-1), new NumberEval(1)
+ };
+
+ // A10:C12
+ ValueEval[] matrix2 = {
+ new NumberEval(1), new NumberEval(1), new NumberEval(1),
+ new NumberEval(1), new NumberEval(1), new NumberEval(1),
+ new NumberEval(1), new NumberEval(1), new NumberEval(1)
+ };
+
+ ValueEval[] expected = {
+ BoolEval.FALSE, BoolEval.TRUE, ErrorEval.VALUE_INVALID,
+ BoolEval.FALSE, BoolEval.TRUE, ErrorEval.VALUE_INVALID,
+ ErrorEval.VALUE_INVALID, ErrorEval.VALUE_INVALID, ErrorEval.VALUE_INVALID
+ };
+
+ ValueEval arg1 = EvalFactory.createAreaEval("A1:B2", matrix1);
+ ValueEval arg2 = EvalFactory.createAreaEval("A10:C12", matrix2);
+ RelationalOperationEval eq = (RelationalOperationEval)RelationalOperationEval.EqualEval;
+ ValueEval result = eq.evaluateArray(new ValueEval[]{ arg1, arg2}, 4, 6);
+
+ assertEquals("expected CacheAreaEval", CacheAreaEval.class, result.getClass());
+ CacheAreaEval ce = (CacheAreaEval)result;
+ assertEquals(3, ce.getWidth());
+ assertEquals(3, ce.getHeight());
+ int idx = 0;
+ for(int i =0; i < ce.getHeight(); i++){
+ for(int j = 0; j < ce.getWidth(); j++){
+ assertEquals("[" + i + "," + j + "]", expected[idx++], ce.getRelativeValue(i, j));
+ }
+ }
+ }
+
+}
Propchange: poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestRelationalOperations.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: poi/trunk/test-data/spreadsheet/maxindextest.xls
URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/maxindextest.xls?rev=1818587&view=auto
==============================================================================
Binary file - no diff available.
Propchange: poi/trunk/test-data/spreadsheet/maxindextest.xls
------------------------------------------------------------------------------
svn:mime-type = application/vnd.ms-excel
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org