Author: onealj
Date: Tue Jun 20 05:47:56 2017
New Revision: 1799302
URL: http://svn.apache.org/viewvc?rev=1799302&view=rev
Log:
bug 52063: support 2-argument LOOKUP(lookup_value, array) function in Excel
Modified:
poi/trunk/src/java/org/apache/poi/ss/formula/functions/Lookup.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/eval/TestFormulaBugs.java
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/functions/Lookup.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/Lookup.java?rev=1799302&r1=1799301&r2=1799302&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/functions/Lookup.java
(original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/functions/Lookup.java Tue Jun
20 05:47:56 2017
@@ -6,7 +6,7 @@
(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
+ 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,
@@ -39,11 +39,42 @@ import org.apache.poi.ss.formula.TwoDEva
*/
public final class Lookup extends Var2or3ArgFunction {
+ @Override
public ValueEval evaluate(int srcRowIndex, int srcColumnIndex,
ValueEval arg0, ValueEval arg1) {
// complex rules to choose lookupVector and resultVector from
the single area ref
- throw new RuntimeException("Two arg version of LOOKUP not
supported yet");
+
+ try {
+ /*
+ The array form of LOOKUP is very similar to the HLOOKUP
and VLOOKUP functions. The difference is that HLOOKUP searches for the value of
lookup_value in the first row, VLOOKUP searches in the first column, and LOOKUP
searches according to the dimensions of array.
+ If array covers an area that is wider than it is tall
(more columns than rows), LOOKUP searches for the value of lookup_value in the
first row.
+ If an array is square or is taller than it is wide
(more rows than columns), LOOKUP searches in the first column.
+ With the HLOOKUP and VLOOKUP functions, you can index
down or across, but LOOKUP always selects the last value in the row or column.
+ */
+ ValueEval lookupValue =
OperandResolver.getSingleValue(arg0, srcRowIndex, srcColumnIndex);
+ TwoDEval lookupArray =
LookupUtils.resolveTableArrayArg(arg1);
+ ValueVector lookupVector;
+ ValueVector resultVector;
+
+ if (lookupArray.getWidth() > lookupArray.getHeight()) {
+ // If array covers an area that is wider than
it is tall (more columns than rows), LOOKUP searches for the value of
lookup_value in the first row.
+ lookupVector =
createVector(lookupArray.getRow(0));
+ resultVector =
createVector(lookupArray.getRow(lookupArray.getHeight() - 1));
+ } else {
+ // If an array is square or is taller than it
is wide (more rows than columns), LOOKUP searches in the first column.
+ lookupVector =
createVector(lookupArray.getColumn(0));
+ resultVector =
createVector(lookupArray.getColumn(lookupArray.getWidth() - 1));
+ }
+ // if a rectangular area reference was passed in as
arg1, lookupVector and resultVector should be the same size
+ assert (lookupVector.getSize() ==
resultVector.getSize());
+
+ int index = LookupUtils.lookupIndexOfValue(lookupValue,
lookupVector, true);
+ return resultVector.getItem(index);
+ } catch (final EvaluationException e) {
+ return e.getErrorEval();
+ }
}
+ @Override
public ValueEval evaluate(int srcRowIndex, int srcColumnIndex,
ValueEval arg0, ValueEval arg1,
ValueEval arg2) {
try {
Modified:
poi/trunk/src/testcases/org/apache/poi/ss/formula/eval/TestFormulaBugs.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/eval/TestFormulaBugs.java?rev=1799302&r1=1799301&r2=1799302&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/eval/TestFormulaBugs.java
(original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/eval/TestFormulaBugs.java
Tue Jun 20 05:47:56 2017
@@ -189,13 +189,68 @@ public final class TestFormulaBugs {
wb.close();
}
-
- private void checkFormulaValue(Workbook wb, Cell cell, String formula,
double expectedValue) {
- cell.setCellFormula(formula);
+
+ // bug 52063: LOOKUP(2-arg) and LOOKUP(3-arg)
+ // FIXME: This could be moved into LookupFunctionsTestCaseData.xls,
which is tested by TestLookupFunctionsFromSpreadsheet.java
+ @Test
+ public void testLookupFormula() throws Exception {
+ Workbook wb = new HSSFWorkbook();
+ Sheet sheet = wb.createSheet("52063");
+
+ // Note: Values in arrays are in ascending order since LOOKUP
expects that in order to work properly
+ // column
+ // A B C
+ // +-------
+ // row 1 | P Q R
+ // row 2 | X Y Z
+ Row row = sheet.createRow(0);
+ row.createCell(0).setCellValue("P");
+ row.createCell(1).setCellValue("Q");
+ row.createCell(2).setCellValue("R");
+ row = sheet.createRow(1);
+ row.createCell(0).setCellValue("X");
+ row.createCell(1).setCellValue("Y");
+ row.createCell(2).setCellValue("Z");
+
+ Cell evalcell = sheet.createRow(2).createCell(0);
+
+ //// ROW VECTORS
+ // lookup and result row are the same
+ checkFormulaValue(wb, evalcell, "LOOKUP(\"Q\", A1:C1)", "Q");
+ checkFormulaValue(wb, evalcell, "LOOKUP(\"R\", A1:C1)", "R");
+ checkFormulaValue(wb, evalcell, "LOOKUP(\"Q\", A1:C1, A1:C1)", "Q");
+ checkFormulaValue(wb, evalcell, "LOOKUP(\"R\", A1:C1, A1:C1)", "R");
+
+ // lookup and result row are different
+ checkFormulaValue(wb, evalcell, "LOOKUP(\"Q\", A1:C2)", "Y");
+ checkFormulaValue(wb, evalcell, "LOOKUP(\"R\", A1:C2)", "Z");
+ checkFormulaValue(wb, evalcell, "LOOKUP(\"Q\", A1:C1, A2:C2)", "Y");
+ checkFormulaValue(wb, evalcell, "LOOKUP(\"R\", A1:C1, A2:C2)", "Z");
+
+ //// COLUMN VECTORS
+ // lookup and result column are different
+ checkFormulaValue(wb, evalcell, "LOOKUP(\"P\", A1:B2)", "Q");
+ checkFormulaValue(wb, evalcell, "LOOKUP(\"X\", A1:A2, C1:C2)", "Z");
+ wb.close();
+ }
+
+ private CellValue evaluateFormulaInCell(Workbook wb, Cell cell, String
formula) {
+ cell.setCellFormula(formula);
+
FormulaEvaluator evaluator =
wb.getCreationHelper().createFormulaEvaluator();
CellValue value = evaluator.evaluate(cell);
+ return value;
+ }
+
+ private void checkFormulaValue(Workbook wb, Cell cell, String formula,
double expectedValue) {
+ CellValue value = evaluateFormulaInCell(wb, cell, formula);
assertEquals(expectedValue, value.getNumberValue(), 0.0001);
}
+
+ private void checkFormulaValue(Workbook wb, Cell cell, String formula,
String expectedValue) {
+ CellValue value = evaluateFormulaInCell(wb, cell, formula);
+ assertEquals(expectedValue, value.getStringValue());
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
