From 65d7486cc6ccb3d64636f0f19d7809aba0774645 Mon Sep 17 00:00:00 2001
From: PJ Fanning <fanningpj@apache.org>
Date: Sun, 8 Aug 2021 15:40:23 +0000
Subject: [PATCH] init support for XLOOKUP

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1892116 13f79535-47bb-0310-9956-ffa450edef68
---
 .../poi/ss/formula/atp/XLookupFunction.java   | 73 +++++++++++--------
 .../poi/ss/formula/functions/LookupUtils.java |  2 +-
 2 files changed, 44 insertions(+), 31 deletions(-)

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
index 940ec64fd0..1e08ff23f7 100644
--- 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
@@ -18,15 +18,17 @@
 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;
 
 import java.util.Optional;
 
 /**
  * Implementation of Excel function XLOOKUP()
  *
- * POI does not currently support have return values with multiple columns and just takes the first cell
+ * POI does not currently support having return values with multiple columns and just takes the first cell
  * right now.
  *
  * <b>Syntax</b><br>
@@ -66,48 +68,59 @@ final class XLookupFunction implements FreeRefFunction {
                 return e.getErrorEval();
             }
         }
-        return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1], args[2], notFound);
+        int matchMode = 0;
+        if (args.length > 4) {
+            try {
+                ValueEval matchModeValue = OperandResolver.getSingleValue(args[4], srcRowIndex, srcColumnIndex);
+                matchMode = OperandResolver.coerceValueToInt(matchModeValue);
+            } catch (EvaluationException e) {
+                return e.getErrorEval();
+            }
+        }
+        int searchMode = 1;
+        if (args.length > 5) {
+            try {
+                ValueEval searchModeValue = OperandResolver.getSingleValue(args[5], srcRowIndex, srcColumnIndex);
+                searchMode = OperandResolver.coerceValueToInt(searchModeValue);
+            } catch (EvaluationException e) {
+                return e.getErrorEval();
+            }
+        }
+        return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1], args[2], notFound, matchMode, searchMode);
     }
 
     private ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval lookupEval, ValueEval indexEval,
-                               ValueEval returnEval, Optional<String> notFound) {
+                               ValueEval returnEval, Optional<String> notFound, int matchMode, int searchMode) {
         try {
             ValueEval lookupValue = OperandResolver.getSingleValue(lookupEval, srcRowIndex, srcColumnIndex);
-            String lookup = laxValueToString(lookupValue);
-            int matchedRow = matchedIndex(indexEval, lookup);
-            if (matchedRow != -1) {
-                if (returnEval instanceof AreaEval) {
-                    AreaEval area = (AreaEval)returnEval;
-                    //TODO to fully support XLOOKUP, we should return the full row
-                    //but POI does not currently support functions returning multiple cell values
-                    return area.getRelativeValue(matchedRow, 0);
+            TwoDEval tableArray = LookupUtils.resolveTableArrayArg(indexEval);
+            boolean isRangeLookup = false;
+            int matchedRow;
+            try {
+                matchedRow = LookupUtils.lookupIndexOfValue(lookupValue, LookupUtils.createColumnVector(tableArray, 0), isRangeLookup);
+            } catch (EvaluationException e) {
+                if (ErrorEval.NA.equals(e.getErrorEval())) {
+                    if (notFound.isPresent()) {
+                        return new StringEval(notFound.get());
+                    }
+                    return ErrorEval.NA;
+                } else {
+                    return e.getErrorEval();
                 }
             }
-            if (notFound.isPresent()) {
-                return new StringEval(notFound.get());
+            if (returnEval instanceof AreaEval) {
+                AreaEval area = (AreaEval)returnEval;
+                //TODO to fully support XLOOKUP, we should return the full row
+                //but POI does not currently support functions returning multiple cell values
+                return area.getRelativeValue(matchedRow, 0);
+            } else {
+                return ErrorEval.VALUE_INVALID;
             }
-            return ErrorEval.NA;
         } 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;
-    }
-
     private String laxValueToString(ValueEval eval) {
         return  (eval instanceof MissingArgEval) ? "" : OperandResolver.coerceValueToString(eval);
     }
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/LookupUtils.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/LookupUtils.java
index 9ac7ca0d47..8710991dcc 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/functions/LookupUtils.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/LookupUtils.java
@@ -36,7 +36,7 @@ import org.apache.poi.ss.formula.eval.ValueEval;
 /**
  * Common functionality used by VLOOKUP, HLOOKUP, LOOKUP and MATCH
  */
-final class LookupUtils {
+public final class LookupUtils {
 
     /**
      * Represents a single row or column within an {@code AreaEval}.
-- 
2.39.5