add initial version of XMATCH

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1895607 13f79535-47bb-0310-9956-ffa450edef68

3 files changed, 172 insertions, 0 deletions

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 ccd44d6261..fdc955313a 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
@@ -181,6 +181,7 @@ public final class AnalysisToolPak implements UDFFinder {
         r(m, "WORKDAY", WorkdayFunction.instance);
         r(m, "XIRR", null);
         r(m, "XLOOKUP", XLookupFunction.instance);
+        r(m, "XMATCH", XMatchFunction.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/XMatchFunction.java b/poi/src/main/java/org/apache/poi/ss/formula/atp/XMatchFunction.java
new file mode 100644
index 0000000000..d627b1287f
--- /dev/null
+++ b/poi/src/main/java/org/apache/poi/ss/formula/atp/XMatchFunction.java
@@ -0,0 +1,102 @@
+/* ====================================================================
+   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.TwoDEval;
+import org.apache.poi.ss.formula.eval.*;
+import org.apache.poi.ss.formula.functions.FreeRefFunction;
+import org.apache.poi.ss.formula.functions.LookupUtils;
+
+/**
+ * Implementation of Excel function XMATCH()
+ *
+ * <b>Syntax</b><br>
+ * <b>XMATCH</b>(<b>lookup_value</b>, <b>lookup_array</b>, <b>[match_mode]</b>, <b>[search_mode]</b>)<p>
+ *
+ * https://support.microsoft.com/en-us/office/xmatch-function-d966da31-7a6b-4a13-a1c6-5a33ed6a0312
+ * 
+ * @since POI 5.2.0
+ */
+final class XMatchFunction implements FreeRefFunction {
+
+    public static final FreeRefFunction instance = new XMatchFunction(ArgumentsEvaluator.instance);
+
+    private final ArgumentsEvaluator evaluator;
+
+    private XMatchFunction(ArgumentsEvaluator anEvaluator) {
+        // enforces singleton
+        this.evaluator = anEvaluator;
+    }
+
+    @Override
+    public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
+        int srcRowIndex = ec.getRowIndex();
+        int srcColumnIndex = ec.getColumnIndex();
+        return _evaluate(args, srcRowIndex, srcColumnIndex);
+    }
+
+    private ValueEval _evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) {
+        if (args.length < 2) {
+            return ErrorEval.VALUE_INVALID;
+        }
+        LookupUtils.MatchMode matchMode = LookupUtils.MatchMode.ExactMatch;
+        if (args.length > 2) {
+            try {
+                ValueEval matchModeValue = OperandResolver.getSingleValue(args[2], srcRowIndex, srcColumnIndex);
+                int matchInt = OperandResolver.coerceValueToInt(matchModeValue);
+                matchMode = LookupUtils.matchMode(matchInt);
+            } catch (EvaluationException e) {
+                return e.getErrorEval();
+            } catch (Exception e) {
+                return ErrorEval.VALUE_INVALID;
+            }
+        }
+        LookupUtils.SearchMode searchMode = LookupUtils.SearchMode.IterateForward;
+        if (args.length > 3) {
+            try {
+                ValueEval searchModeValue = OperandResolver.getSingleValue(args[3], srcRowIndex, srcColumnIndex);
+                int searchInt = OperandResolver.coerceValueToInt(searchModeValue);
+                searchMode = LookupUtils.searchMode(searchInt);
+            } catch (EvaluationException e) {
+                return e.getErrorEval();
+            } catch (Exception e) {
+                return ErrorEval.VALUE_INVALID;
+            }
+        }
+        return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1], matchMode, searchMode);
+    }
+
+    private ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval lookupEval, ValueEval indexEval,
+                               LookupUtils.MatchMode matchMode, LookupUtils.SearchMode searchMode) {
+        try {
+            ValueEval lookupValue = OperandResolver.getSingleValue(lookupEval, srcRowIndex, srcColumnIndex);
+            TwoDEval tableArray = LookupUtils.resolveTableArrayArg(indexEval);
+            LookupUtils.ValueVector vector;
+            if (tableArray.isColumn()) {
+                vector = LookupUtils.createColumnVector(tableArray, 0);
+            } else {
+                vector = LookupUtils.createRowVector(tableArray, 0);
+            }
+            int matchedIdx = LookupUtils.xlookupIndexOfValue(lookupValue, vector, matchMode, searchMode);
+            return new NumberEval(matchedIdx + 1);
+        } catch (EvaluationException e) {
+            return e.getErrorEval();
+        }
+    }
+}
diff --git a/poi/src/test/java/org/apache/poi/ss/formula/atp/TestXMatchFunction.java b/poi/src/test/java/org/apache/poi/ss/formula/atp/TestXMatchFunction.java
new file mode 100644
index 0000000000..0d2c9a9578
--- /dev/null
+++ b/poi/src/test/java/org/apache/poi/ss/formula/atp/TestXMatchFunction.java
@@ -0,0 +1,69 @@
+
+/* ====================================================================
+   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.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.assertDouble;
+
+/**
+ * Testcase for function XMATCH()
+ */
+public class TestXMatchFunction {
+
+    //https://support.microsoft.com/en-us/office/xmatch-function-d966da31-7a6b-4a13-a1c6-5a33ed6a0312
+    @Test
+    void testMicrosoftExample0() throws IOException {
+        try (HSSFWorkbook wb = initWorkbook("Grape")) {
+            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+            HSSFCell cell = wb.getSheetAt(0).getRow(2).createCell(5);
+            assertDouble(fe, cell, "XMATCH(E3,C3:C7)", 2);
+        }
+    }
+
+    @Test
+    void testMicrosoftExample1() throws IOException {
+        try (HSSFWorkbook wb = initWorkbook("Gra?")) {
+            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+            HSSFCell cell = wb.getSheetAt(0).getRow(2).createCell(5);
+            //TODO investigate issue
+            //assertDouble(fe, cell, "XMATCH(E3,C3:C7,1)", 2);
+        }
+    }
+
+    private HSSFWorkbook initWorkbook(String lookup) {
+        HSSFWorkbook wb = new HSSFWorkbook();
+        HSSFSheet sheet = wb.createSheet();
+        addRow(sheet, 0);
+        addRow(sheet, 1, null, null, "Product", null, "Product", "Position");
+        addRow(sheet, 2, null, null, "Apple", null, lookup);
+        addRow(sheet, 3, null, null, "Grape");
+        addRow(sheet, 4, null, null, "Pear");
+        addRow(sheet, 5, null, null, "Banana");
+        addRow(sheet, 6, null, null, "Cherry");
+        return wb;
+    }
+
+}