From 741e290267929ab03ff333b9f04912e61bae8b0d Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Sat, 7 Aug 2021 23:23:39 +0000 Subject: [PATCH] init support for XLOOKUP git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1892096 13f79535-47bb-0310-9956-ffa450edef68 --- .../poi/ss/formula/atp/AnalysisToolPak.java | 1 + .../poi/ss/formula/atp/XLookupFunction.java | 85 +++++++++++++++++++ .../ss/formula/atp/TestTextJoinFunction.java | 4 +- .../ss/formula/atp/TestXLookupFunction.java | 71 ++++++++++++++++ .../poi/ss/formula/functions/TestAddress.java | 2 +- .../poi/ss/formula/functions/TestAreas.java | 2 +- .../poi/ss/formula/functions/TestClean.java | 2 +- .../poi/ss/formula/functions/TestConcat.java | 2 +- .../poi/ss/formula/functions/TestFind.java | 24 ++---- .../poi/ss/formula/functions/TestSum.java | 13 +-- .../ss/formula/functions/TestTimeValue.java | 13 +-- .../java/org/apache/poi/ss/util/Utils.java | 4 +- 12 files changed, 177 insertions(+), 46 deletions(-) create mode 100644 poi/src/main/java/org/apache/poi/ss/formula/atp/XLookupFunction.java create mode 100644 poi/src/test/java/org/apache/poi/ss/formula/atp/TestXLookupFunction.java diff --git a/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java b/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java index ca0a91df00..05e3a7a678 100644 --- a/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java +++ b/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java @@ -175,6 +175,7 @@ public final class AnalysisToolPak implements UDFFinder { r(m, "WEEKNUM", WeekNum.instance); r(m, "WORKDAY", WorkdayFunction.instance); r(m, "XIRR", null); + r(m, "XLOOKUP", XLookupFunction.instance); r(m, "XNPV", null); r(m, "YEARFRAC", YearFrac.instance); r(m, "YIELD", null); diff --git a/poi/src/main/java/org/apache/poi/ss/formula/atp/XLookupFunction.java b/poi/src/main/java/org/apache/poi/ss/formula/atp/XLookupFunction.java new file mode 100644 index 0000000000..164af5681e --- /dev/null +++ b/poi/src/main/java/org/apache/poi/ss/formula/atp/XLookupFunction.java @@ -0,0 +1,85 @@ +/* ==================================================================== + 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.atp; + +import org.apache.poi.ss.formula.OperationEvaluationContext; +import org.apache.poi.ss.formula.eval.*; +import org.apache.poi.ss.formula.functions.FreeRefFunction; + +/** + * Implementation of Excel function XLOOKUP() + * + * Syntax
+ * XLOOKUP

+ * + * @since POI 5.0.1 + */ +final class XLookupFunction implements FreeRefFunction { + + public static final FreeRefFunction instance = new XLookupFunction(ArgumentsEvaluator.instance); + + private ArgumentsEvaluator evaluator; + + private XLookupFunction(ArgumentsEvaluator anEvaluator) { + // enforces singleton + this.evaluator = anEvaluator; + } + + public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) { + int srcRowIndex = ec.getRowIndex(); + int srcColumnIndex = ec.getColumnIndex(); + if (args.length < 3) { + return ErrorEval.VALUE_INVALID; + } + return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1], args[2]); + } + + private ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval lookupEval, ValueEval indexEval, + ValueEval valueEval) { + try { + ValueEval lookupValue = OperandResolver.getSingleValue(lookupEval, srcRowIndex, srcColumnIndex); + String lookup = OperandResolver.coerceValueToString(lookupValue); + int matchedRow = matchedIndex(indexEval, lookup); + if (matchedRow != -1) { + if (valueEval instanceof AreaEval) { + AreaEval area = (AreaEval)valueEval; + return area.getRelativeValue(matchedRow, 0); + } + } + return ErrorEval.NUM_ERROR; + } catch (EvaluationException e) { + return e.getErrorEval(); + } + } + + private int matchedIndex(ValueEval areaEval, String lookup) { + if (areaEval instanceof AreaEval) { + AreaEval area = (AreaEval)areaEval; + for (int r = 0; r <= area.getHeight(); r++) { + for (int c = 0; c <= area.getWidth(); c++) { + ValueEval cellEval = area.getRelativeValue(r, c); + String cellValue = OperandResolver.coerceValueToString(cellEval); + if (lookup.equals(cellValue)) { + return r; + } + } + } + } + return -1; + } +} diff --git a/poi/src/test/java/org/apache/poi/ss/formula/atp/TestTextJoinFunction.java b/poi/src/test/java/org/apache/poi/ss/formula/atp/TestTextJoinFunction.java index 8e8c15a69f..f8eb12a6c8 100644 --- a/poi/src/test/java/org/apache/poi/ss/formula/atp/TestTextJoinFunction.java +++ b/poi/src/test/java/org/apache/poi/ss/formula/atp/TestTextJoinFunction.java @@ -41,11 +41,9 @@ public class TestTextJoinFunction { private FormulaEvaluator evaluator; private Cell textCell1; private Cell textCell2; - private Cell textCell3; private Cell numericCell1; private Cell numericCell2; private Cell blankCell; - private Cell emptyCell; private Cell formulaCell; @BeforeEach @@ -250,7 +248,7 @@ public class TestTextJoinFunction { cell.setCellFormula(formulaText); fe.notifyUpdateCell(cell); CellValue result = fe.evaluate(cell); - assertEquals(result.getCellType(), CellType.STRING); + assertEquals(CellType.STRING, result.getCellType()); assertEquals(expectedResult, result.getStringValue()); } } diff --git a/poi/src/test/java/org/apache/poi/ss/formula/atp/TestXLookupFunction.java b/poi/src/test/java/org/apache/poi/ss/formula/atp/TestXLookupFunction.java new file mode 100644 index 0000000000..88e238cd66 --- /dev/null +++ b/poi/src/test/java/org/apache/poi/ss/formula/atp/TestXLookupFunction.java @@ -0,0 +1,71 @@ + +/* ==================================================================== + 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.atp; + +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.usermodel.*; +import org.junit.jupiter.api.Test; + +import java.io.IOException; + +import static org.apache.poi.ss.util.Utils.addRow; +import static org.junit.jupiter.api.Assertions.assertEquals; + +/** + * Testcase for function XLOOKUP() + */ +public class TestXLookupFunction { + + //https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929 + @Test + void testMicrosoftExample1() throws IOException { + try (HSSFWorkbook wb = initWorkbook1()) { + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); + HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100); + confirmResult(fe, cell, "XLOOKUP(F2,B2:B11,D2:D11)", "+55"); + } + } + + private HSSFWorkbook initWorkbook1() { + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet = wb.createSheet(); + addRow(sheet, 0, null, "Country", "Abr", "Prefix"); + addRow(sheet, 1, null, "China", "CN", "+86", null, "Brazil"); + addRow(sheet, 2, null, "India", "IN", "+91"); + addRow(sheet, 3, null, "United States", "US", "+1"); + addRow(sheet, 4, null, "Indonesia", "ID", "+62"); + addRow(sheet, 5, null, "Brazil", "BR", "+55"); + addRow(sheet, 6, null, "Pakistan", "PK", "+92"); + addRow(sheet, 7, null, "Nigeria", "NG", "+234"); + addRow(sheet, 8, null, "Bangladesh", "BD", "+880"); + addRow(sheet, 9, null, "Russia", "RU", "+7"); + addRow(sheet, 10, null, "Mexico", "MX", "+52"); + return wb; + } + + private static void confirmResult(HSSFFormulaEvaluator fe, HSSFCell cell, String formulaText, String expectedResult) { + cell.setCellFormula(formulaText); + fe.notifyUpdateCell(cell); + CellValue result = fe.evaluate(cell); + assertEquals(CellType.STRING, result.getCellType()); + assertEquals(expectedResult, result.getStringValue()); + } +} diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAddress.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAddress.java index d706f60a07..9bba10be01 100644 --- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAddress.java +++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAddress.java @@ -75,7 +75,7 @@ final class TestAddress { cell.setCellFormula(formulaText); fe.notifyUpdateCell(cell); CellValue result = fe.evaluate(cell); - assertEquals(result.getCellType(), CellType.STRING); + assertEquals(CellType.STRING, result.getCellType()); assertEquals(expectedResult, result.getStringValue()); } } diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAreas.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAreas.java index 3137614d3a..7f0ed66737 100644 --- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAreas.java +++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAreas.java @@ -54,7 +54,7 @@ final class TestAreas { cell.setCellFormula(formulaText); fe.notifyUpdateCell(cell); CellValue result = fe.evaluate(cell); - assertEquals(result.getCellType(), CellType.NUMERIC); + assertEquals(CellType.NUMERIC, result.getCellType()); assertEquals(expectedResult, result.getNumberValue(), 0); } } diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestClean.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestClean.java index 1ab4f90ea0..a93443fc4b 100644 --- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestClean.java +++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestClean.java @@ -62,7 +62,7 @@ final class TestClean { cell.setCellFormula(formulaText); fe.notifyUpdateCell(cell); CellValue result = fe.evaluate(cell); - assertEquals(result.getCellType(), CellType.STRING); + assertEquals(CellType.STRING, result.getCellType()); assertEquals(expectedResult, result.getStringValue()); } } diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestConcat.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestConcat.java index ccc2b8b772..84f19207fa 100644 --- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestConcat.java +++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestConcat.java @@ -100,7 +100,7 @@ final class TestConcat { cell.setCellFormula(formulaText); fe.notifyUpdateCell(cell); CellValue result = fe.evaluate(cell); - assertEquals(result.getCellType(), CellType.STRING); + assertEquals(CellType.STRING, result.getCellType()); assertEquals(expectedResult, result.getStringValue()); } } diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestFind.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestFind.java index f8fbcc4cba..73268790d8 100644 --- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestFind.java +++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestFind.java @@ -17,6 +17,7 @@ package org.apache.poi.ss.formula.functions; +import static org.apache.poi.ss.util.Utils.assertError; import static org.junit.jupiter.api.Assertions.assertEquals; import java.io.IOException; @@ -51,12 +52,12 @@ final class TestFind { confirmResult(fe, cell, "find(5, 87654)", 4); // Errors - confirmError(fe, cell, "find(\"n\", \"haystack\")", FormulaError.VALUE); - confirmError(fe, cell, "find(\"k\", \"haystack\",9)", FormulaError.VALUE); - confirmError(fe, cell, "find(\"k\", \"haystack\",#REF!)", FormulaError.REF); - confirmError(fe, cell, "find(\"k\", \"haystack\",0)", FormulaError.VALUE); - confirmError(fe, cell, "find(#DIV/0!, #N/A, #REF!)", FormulaError.DIV0); - confirmError(fe, cell, "find(2, #N/A, #REF!)", FormulaError.NA); + assertError(fe, cell, "find(\"n\", \"haystack\")", FormulaError.VALUE); + assertError(fe, cell, "find(\"k\", \"haystack\",9)", FormulaError.VALUE); + assertError(fe, cell, "find(\"k\", \"haystack\",#REF!)", FormulaError.REF); + assertError(fe, cell, "find(\"k\", \"haystack\",0)", FormulaError.VALUE); + assertError(fe, cell, "find(#DIV/0!, #N/A, #REF!)", FormulaError.DIV0); + assertError(fe, cell, "find(2, #N/A, #REF!)", FormulaError.NA); wb.close(); } @@ -66,16 +67,7 @@ final class TestFind { cell.setCellFormula(formulaText); fe.notifyUpdateCell(cell); CellValue result = fe.evaluate(cell); - assertEquals(result.getCellType(), CellType.NUMERIC); + assertEquals(CellType.NUMERIC, result.getCellType()); assertEquals(expectedResult, result.getNumberValue(), 0.0); } - - private static void confirmError(HSSFFormulaEvaluator fe, HSSFCell cell, String formulaText, - FormulaError expectedErrorCode) { - cell.setCellFormula(formulaText); - fe.notifyUpdateCell(cell); - CellValue result = fe.evaluate(cell); - assertEquals(result.getCellType(), CellType.ERROR); - assertEquals(expectedErrorCode.getCode(), result.getErrorValue()); - } } diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSum.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSum.java index 9be0c81485..1ffd8c91c6 100644 --- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSum.java +++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSum.java @@ -29,6 +29,7 @@ import org.junit.jupiter.api.Test; import java.io.IOException; import static org.apache.poi.ss.util.Utils.addRow; +import static org.apache.poi.ss.util.Utils.assertError; import static org.junit.jupiter.api.Assertions.*; /** @@ -59,7 +60,7 @@ final class TestSum { try (HSSFWorkbook wb = initWorkbookWithNA()) { HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100); - confirmError(fe, cell, "SUM(B2:B6)", FormulaError.NA); + assertError(fe, cell, "SUM(B2:B6)", FormulaError.NA); } } @@ -92,15 +93,7 @@ final class TestSum { cell.setCellFormula(formulaText); fe.notifyUpdateCell(cell); CellValue result = fe.evaluate(cell); - assertEquals(result.getCellType(), CellType.NUMERIC); + assertEquals(CellType.NUMERIC, result.getCellType()); assertEquals(expectedResult, result.getNumberValue()); } - - private static void confirmError(HSSFFormulaEvaluator fe, HSSFCell cell, String formulaText, FormulaError expectedError) { - cell.setCellFormula(formulaText); - fe.notifyUpdateCell(cell); - CellValue result = fe.evaluate(cell); - assertEquals(result.getCellType(), CellType.ERROR); - assertEquals(expectedError.getCode(), result.getErrorValue()); - } } diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestTimeValue.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestTimeValue.java index b7f4d4a6ab..7a5ab614c0 100644 --- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestTimeValue.java +++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestTimeValue.java @@ -19,8 +19,6 @@ package org.apache.poi.ss.formula.functions; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.formula.eval.*; -import org.apache.poi.ss.usermodel.CellType; -import org.apache.poi.ss.usermodel.CellValue; import org.apache.poi.util.LocaleUtil; import org.junit.jupiter.api.AfterAll; import org.junit.jupiter.api.BeforeAll; @@ -29,6 +27,7 @@ import org.junit.jupiter.api.Test; import java.io.IOException; import java.util.Locale; +import static org.apache.poi.ss.util.Utils.assertDouble; import static org.junit.jupiter.api.Assertions.assertEquals; /** @@ -89,7 +88,7 @@ final class TestTimeValue { HSSFRow row = sheet.createRow(0); row.createCell(0).setCellValue("8/22/2011 12:00"); HSSFCell cell = row.createCell(1); - confirmNumericResult(fe, cell, "TIMEVALUE(A1)", 0.5); + assertDouble(fe, cell, "TIMEVALUE(A1)", 0.5); } } @@ -113,12 +112,4 @@ final class TestTimeValue { assertEquals(ErrorEval.class, result.getClass()); assertEquals(ErrorEval.VALUE_INVALID.getErrorCode(), ((ErrorEval) result).getErrorCode()); } - - private static void confirmNumericResult(HSSFFormulaEvaluator fe, HSSFCell cell, String formulaText, double expectedResult) { - cell.setCellFormula(formulaText); - fe.notifyUpdateCell(cell); - CellValue result = fe.evaluate(cell); - assertEquals(result.getCellType(), CellType.NUMERIC); - assertEquals(expectedResult, result.getNumberValue(), 0.0001); - } } diff --git a/poi/src/test/java/org/apache/poi/ss/util/Utils.java b/poi/src/test/java/org/apache/poi/ss/util/Utils.java index c2c8d67cb1..b498ffb5fb 100644 --- a/poi/src/test/java/org/apache/poi/ss/util/Utils.java +++ b/poi/src/test/java/org/apache/poi/ss/util/Utils.java @@ -59,7 +59,7 @@ public class Utils { cell.setCellFormula(formulaText); fe.notifyUpdateCell(cell); CellValue result = fe.evaluate(cell); - assertEquals(result.getCellType(), CellType.NUMERIC); + assertEquals(CellType.NUMERIC, result.getCellType()); assertEquals(expectedResult, result.getNumberValue()); } @@ -67,7 +67,7 @@ public class Utils { cell.setCellFormula(formulaText); fe.notifyUpdateCell(cell); CellValue result = fe.evaluate(cell); - assertEquals(result.getCellType(), CellType.ERROR); + assertEquals(CellType.ERROR, result.getCellType()); assertEquals(expectedError.getCode(), result.getErrorValue()); } } -- 2.39.5