init support for XLOOKUP

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

4 files changed, 84 insertions, 18 deletions

diff --git a/poi/src/main/java/org/apache/poi/ss/formula/atp/TextJoinFunction.java b/poi/src/main/java/org/apache/poi/ss/formula/atp/TextJoinFunction.java
index 85a527261f..6a4b59c573 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/atp/TextJoinFunction.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/atp/TextJoinFunction.java
@@ -96,14 +96,14 @@ final class TextJoinFunction implements FreeRefFunction {
             if (delimiterArgs.size() == 0) {
                 return new StringEval(String.join("", textValues));
             } else if (delimiterArgs.size() == 1) {
-                String delimiter = coerceValueToString(delimiterArgs.get(0));
+                String delimiter = laxValueToString(delimiterArgs.get(0));
                 return new StringEval(String.join(delimiter, textValues));
             } else {
                 //https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c
                 //see example 3 to see why this is needed
                 List<String> delimiters = new ArrayList<>();
                 for (ValueEval delimiterArg: delimiterArgs) {
-                    delimiters.add(coerceValueToString(delimiterArg));
+                    delimiters.add(laxValueToString(delimiterArg));
                 }
                 StringBuilder sb = new StringBuilder();
                 for (int i = 0; i < textValues.size(); i++) {
@@ -120,7 +120,7 @@ final class TextJoinFunction implements FreeRefFunction {
         }
     }
 
-    private String coerceValueToString(ValueEval eval) {
+    private String laxValueToString(ValueEval eval) {
         return  (eval instanceof MissingArgEval) ? "" : OperandResolver.coerceValueToString(eval);
     }
 
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 a9bef0407b..9dad4b8837 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
@@ -21,11 +21,13 @@ import org.apache.poi.ss.formula.OperationEvaluationContext;
 import org.apache.poi.ss.formula.eval.*;
 import org.apache.poi.ss.formula.functions.FreeRefFunction;
 
+import java.util.Optional;
+
 /**
  * Implementation of Excel function XLOOKUP()
  *
  * <b>Syntax</b><br>
- * <b>XLOOKUP</b><p>
+ * <b>XLOOKUP</b>(<b>lookup_value</b>, <b>lookup_array</b>, <b>return_array</b>, <b>[if_not_found]</b>, <b>[match_mode]</b>, <b>[search_mode]</b>)<p>
  *
  * @since POI 5.0.1
  */
@@ -46,18 +48,33 @@ final class XLookupFunction implements FreeRefFunction {
         if (args.length < 3) {
             return ErrorEval.VALUE_INVALID;
         }
-        return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1], args[2]);
+        Optional<String> notFound = Optional.empty();
+        if (args.length > 3) {
+            try {
+                ValueEval notFoundValue = OperandResolver.getSingleValue(args[3], srcRowIndex, srcColumnIndex);
+                String notFoundText = laxValueToString(notFoundValue);
+                if (notFoundText != null) {
+                    String trimmedText = notFoundText.trim();
+                    if (trimmedText.length() > 0) {
+                        notFound = Optional.of(trimmedText);
+                    }
+                }
+            } catch (EvaluationException e) {
+                return e.getErrorEval();
+            }
+        }
+        return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1], args[2], notFound);
     }
 
     private ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval lookupEval, ValueEval indexEval,
-                               ValueEval valueEval) {
+                               ValueEval returnEval, Optional<String> notFound) {
         try {
             ValueEval lookupValue = OperandResolver.getSingleValue(lookupEval, srcRowIndex, srcColumnIndex);
-            String lookup = OperandResolver.coerceValueToString(lookupValue);
+            String lookup = laxValueToString(lookupValue);
             int matchedRow = matchedIndex(indexEval, lookup);
             if (matchedRow != -1) {
-                if (valueEval instanceof AreaEval) {
-                    AreaEval area = (AreaEval)valueEval;
+                if (returnEval instanceof AreaEval) {
+                    AreaEval area = (AreaEval)returnEval;
                     if (area.getWidth() == 1) {
                         return area.getRelativeValue(matchedRow, 0);
                     } else {
@@ -65,7 +82,10 @@ final class XLookupFunction implements FreeRefFunction {
                     }
                 }
             }
-            return ErrorEval.NUM_ERROR;
+            if (notFound.isPresent()) {
+                return new StringEval(notFound.get());
+            }
+            return ErrorEval.NA;
         } catch (EvaluationException e) {
             return e.getErrorEval();
         }
@@ -86,4 +106,8 @@ final class XLookupFunction implements FreeRefFunction {
         }
         return -1;
     }
+
+    private String laxValueToString(ValueEval eval) {
+        return  (eval instanceof MissingArgEval) ? "" : OperandResolver.coerceValueToString(eval);
+    }
 }
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
index 88e238cd66..133c0ffa7f 100644
--- 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
@@ -26,7 +26,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.*;
 import static org.junit.jupiter.api.Assertions.assertEquals;
 
 /**
@@ -40,7 +40,29 @@ public class TestXLookupFunction {
         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");
+            assertString(fe, cell, "XLOOKUP(F2,B2:B11,D2:D11)", "+55");
+        }
+    }
+
+    @Test
+    void testMicrosoftExample2() throws IOException {
+        try (HSSFWorkbook wb = initWorkbook2()) {
+            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+            HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
+            cell.setCellFormula("XLOOKUP(B2,B5:B14,C5:D14)");
+            fe.notifyUpdateCell(cell);
+            CellValue result = fe.evaluate(cell);
+            //TODO add assertions
+        }
+    }
+
+    @Test
+    void testMicrosoftExample3() throws IOException {
+        try (HSSFWorkbook wb = initWorkbook2()) {
+            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+            HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
+            assertError(fe, cell, "XLOOKUP(999999,B2:B11,D2:D11)", FormulaError.NA);
+            assertString(fe, cell, "XLOOKUP(999999,B2:B11,D2:D11,\"not found\")", "not found");
         }
     }
 
@@ -61,11 +83,23 @@ public class TestXLookupFunction {
         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());
+    private HSSFWorkbook initWorkbook2() {
+        HSSFWorkbook wb = new HSSFWorkbook();
+        HSSFSheet sheet = wb.createSheet();
+        addRow(sheet, 0, null, "Emp Id", "Employee Name", "Department");
+        addRow(sheet, 1, null, 8389);
+        addRow(sheet, 3, null, "Emp Id", "Employee Name", "Department");
+        addRow(sheet, 4, null, 4390, "Ned Lanning", "Marketing");
+        addRow(sheet, 5, null, 8604, "Margo Hendrix", "Sales");
+        addRow(sheet, 6, null, 8389, "Dianne Pugh", "Finance");
+        addRow(sheet, 7, null, 4937, "Earlene McCarty", "Accounting");
+        addRow(sheet, 8, null, 8299, "Mia Arnold", "Operation");
+        addRow(sheet, 9, null, 2643, "Jorge Fellows", "Executive");
+        addRow(sheet, 10, null, 5243, "Rose Winters", "Sales");
+        addRow(sheet, 11, null, 9693, "Carmela Hahn", "Finance");
+        addRow(sheet, 12, null, 1636, "Delia Cochran", "Accounting");
+        addRow(sheet, 13, null, 6703, "Marguerite Cervantes", "Marketing");
+        return wb;
     }
+
 }
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 b498ffb5fb..81ad9e5725 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
@@ -55,6 +55,14 @@ public class Utils {
         }
     }
 
+    public static void assertString(FormulaEvaluator fe, Cell 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());
+    }
+
     public static void assertDouble(FormulaEvaluator fe, Cell cell, String formulaText, double expectedResult) {
         cell.setCellFormula(formulaText);
         fe.notifyUpdateCell(cell);