From c8a3870064b49ce280e87da67740696e001488fd Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Thu, 2 Dec 2021 19:10:14 +0000 Subject: [PATCH] [github-243] basic version of XLookup git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1895499 13f79535-47bb-0310-9956-ffa450edef68 --- .../apache/poi/xssf/TestXLookupFunction.java | 78 +++++ .../ss/formula/OperationEvaluatorFactory.java | 33 ++- .../poi/ss/formula/UserDefinedFunction.java | 11 +- .../poi/ss/formula/atp/AnalysisToolPak.java | 1 + .../poi/ss/formula/atp/XLookupFunction.java | 266 ++++++++++++++++++ .../poi/ss/formula/functions/Hlookup.java | 2 +- .../poi/ss/formula/functions/Lookup.java | 4 +- .../poi/ss/formula/functions/LookupUtils.java | 233 +++++++++++++-- .../poi/ss/formula/functions/Vlookup.java | 2 +- .../ss/formula/atp/TestXLookupFunction.java | 147 ++++++++++ 10 files changed, 744 insertions(+), 33 deletions(-) create mode 100644 poi-ooxml/src/test/java/org/apache/poi/xssf/TestXLookupFunction.java 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-ooxml/src/test/java/org/apache/poi/xssf/TestXLookupFunction.java b/poi-ooxml/src/test/java/org/apache/poi/xssf/TestXLookupFunction.java new file mode 100644 index 0000000000..932629255c --- /dev/null +++ b/poi-ooxml/src/test/java/org/apache/poi/xssf/TestXLookupFunction.java @@ -0,0 +1,78 @@ + +/* ==================================================================== + 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.xssf; + +import org.apache.poi.ss.util.CellRangeAddress; +import org.apache.poi.ss.util.CellReference; +import org.apache.poi.xssf.usermodel.*; +import org.junit.jupiter.api.Test; + +import java.io.IOException; +import java.util.Locale; + +import static org.apache.poi.ss.util.Utils.*; +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 testMicrosoftExample2() throws IOException { + String formulaText = "XLOOKUP(B2,B5:B14,C5:D14)"; + try (XSSFWorkbook wb = initWorkbook2()) { + XSSFFormulaEvaluator fe = new XSSFFormulaEvaluator(wb); + XSSFSheet sheet = wb.getSheetAt(0); + XSSFRow row1 = sheet.getRow(1); + String col1 = CellReference.convertNumToColString(2); + String col2 = CellReference.convertNumToColString(3); + String cellRef = String.format(Locale.ENGLISH, "%s2:%s2", col1, col2); + sheet.setArrayFormula(formulaText, CellRangeAddress.valueOf(cellRef)); + fe.evaluateAll(); + try (java.io.FileOutputStream fos = new java.io.FileOutputStream("/tmp/xlook.xlsx")) { + wb.write(fos); + } + assertEquals("Dianne Pugh", row1.getCell(2).getStringCellValue()); + assertEquals("Finance", row1.getCell(3).getStringCellValue()); + } + } + + + private XSSFWorkbook initWorkbook2() { + XSSFWorkbook wb = new XSSFWorkbook(); + XSSFSheet 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/main/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java b/poi/src/main/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java index 4dfcb78e12..0ee0b23e50 100644 --- a/poi/src/main/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java +++ b/poi/src/main/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java @@ -121,25 +121,36 @@ final class OperationEvaluatorFactory { } } if (result != null) { - EvaluationSheet evalSheet = ec.getWorkbook().getSheet(ec.getSheetIndex()); - EvaluationCell evalCell = evalSheet.getCell(ec.getRowIndex(), ec.getColumnIndex()); - if (evalCell != null && result instanceof ArrayFunction) { + if (result instanceof ArrayFunction) { ArrayFunction func = (ArrayFunction) result; - if(evalCell.isPartOfArrayFormulaGroup()){ - // array arguments must be evaluated relative to the function defining range - CellRangeAddress ca = evalCell.getArrayFormulaRange(); - return func.evaluateArray(args, ca.getFirstRow(), ca.getFirstColumn()); - } else if (ec.isArraymode()){ - return func.evaluateArray(args, ec.getRowIndex(), ec.getColumnIndex()); + ValueEval eval = evaluateArrayFunction(func, args, ec); + if (eval != null) { + return eval; } } return result.evaluate(args, ec.getRowIndex(), ec.getColumnIndex()); - } else if (udfFunc != null){ - return udfFunc.evaluate(args, ec); + } else if (udfFunc != null) { + return udfFunc.evaluate(args, ec); } throw new RuntimeException("Unexpected operation ptg class (" + ptg.getClass().getName() + ")"); } + + static ValueEval evaluateArrayFunction(ArrayFunction func, ValueEval[] args, + OperationEvaluationContext ec) { + EvaluationSheet evalSheet = ec.getWorkbook().getSheet(ec.getSheetIndex()); + EvaluationCell evalCell = evalSheet.getCell(ec.getRowIndex(), ec.getColumnIndex()); + if (evalCell != null) { + if (evalCell.isPartOfArrayFormulaGroup()) { + // array arguments must be evaluated relative to the function defining range + CellRangeAddress ca = evalCell.getArrayFormulaRange(); + return func.evaluateArray(args, ca.getFirstRow(), ca.getFirstColumn()); + } else if (ec.isArraymode()){ + return func.evaluateArray(args, ec.getRowIndex(), ec.getColumnIndex()); + } + } + return null; + } } diff --git a/poi/src/main/java/org/apache/poi/ss/formula/UserDefinedFunction.java b/poi/src/main/java/org/apache/poi/ss/formula/UserDefinedFunction.java index cccb3fab29..10a2a1c8bf 100644 --- a/poi/src/main/java/org/apache/poi/ss/formula/UserDefinedFunction.java +++ b/poi/src/main/java/org/apache/poi/ss/formula/UserDefinedFunction.java @@ -20,7 +20,9 @@ package org.apache.poi.ss.formula; import org.apache.poi.ss.formula.eval.FunctionNameEval; import org.apache.poi.ss.formula.eval.NotImplementedFunctionException; import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.formula.functions.ArrayFunction; import org.apache.poi.ss.formula.functions.FreeRefFunction; + /** * * Common entry point for all user-defined (non-built-in) functions (where @@ -53,9 +55,16 @@ final class UserDefinedFunction implements FreeRefFunction { if (targetFunc == null) { throw new NotImplementedFunctionException(functionName); } - int nOutGoingArgs = nIncomingArgs -1; + int nOutGoingArgs = nIncomingArgs - 1; ValueEval[] outGoingArgs = new ValueEval[nOutGoingArgs]; System.arraycopy(args, 1, outGoingArgs, 0, nOutGoingArgs); + if (targetFunc instanceof ArrayFunction) { + ArrayFunction func = (ArrayFunction) targetFunc; + ValueEval eval = OperationEvaluatorFactory.evaluateArrayFunction(func, outGoingArgs, ec); + if (eval != null) { + return eval; + } + } return targetFunc.evaluate(outGoingArgs, ec); } } 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 afaa1eec0e..ccd44d6261 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 @@ -180,6 +180,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..69f96184b5 --- /dev/null +++ b/poi/src/main/java/org/apache/poi/ss/formula/atp/XLookupFunction.java @@ -0,0 +1,266 @@ +/* ==================================================================== + 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.ArrayFunction; +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 having return values with multiple columns and just takes the first cell + * right now. + * + * Syntax
+ * XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

+ * + * @since POI 5.2.0 + */ +final class XLookupFunction implements FreeRefFunction, ArrayFunction { + + public static final FreeRefFunction instance = new XLookupFunction(ArgumentsEvaluator.instance); + + private final ArgumentsEvaluator evaluator; + + private XLookupFunction(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, ec.isSingleValue()); + } + + @Override + public ValueEval evaluateArray(ValueEval[] args, int srcRowIndex, int srcColumnIndex) { + return _evaluate(args, srcRowIndex, srcColumnIndex, false); + } + + private ValueEval _evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex, boolean isSingleValue) { + if (args.length < 3) { + return ErrorEval.VALUE_INVALID; + } + Optional 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(); + } + } + LookupUtils.MatchMode matchMode = LookupUtils.MatchMode.ExactMatch; + if (args.length > 4) { + try { + ValueEval matchModeValue = OperandResolver.getSingleValue(args[4], 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 > 5) { + try { + ValueEval searchModeValue = OperandResolver.getSingleValue(args[5], 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], args[2], notFound, matchMode, searchMode, isSingleValue); + } + + private ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval lookupEval, ValueEval indexEval, + ValueEval returnEval, Optional notFound, LookupUtils.MatchMode matchMode, + LookupUtils.SearchMode searchMode, boolean isSingleValue) { + try { + ValueEval lookupValue = OperandResolver.getSingleValue(lookupEval, srcRowIndex, srcColumnIndex); + TwoDEval tableArray = LookupUtils.resolveTableArrayArg(indexEval); + int matchedRow; + try { + matchedRow = LookupUtils.xlookupIndexOfValue(lookupValue, LookupUtils.createColumnVector(tableArray, 0), matchMode, searchMode); + } catch (EvaluationException e) { + if (ErrorEval.NA.equals(e.getErrorEval())) { + if (notFound.isPresent()) { + if (returnEval instanceof AreaEval) { + AreaEval area = (AreaEval)returnEval; + int width = area.getWidth(); + if (isSingleValue || width <= 1) { + return new StringEval(notFound.get()); + } + return notFoundAreaEval(notFound.get(), width); + } else { + return new StringEval(notFound.get()); + } + } + return ErrorEval.NA; + } else { + return e.getErrorEval(); + } + } + if (returnEval instanceof AreaEval) { + AreaEval area = (AreaEval)returnEval; + if (isSingleValue) { + return area.getRelativeValue(matchedRow, 0); + } + return area.offset(matchedRow, matchedRow,0, area.getWidth() - 1); + } else { + return returnEval; + } + } catch (EvaluationException e) { + return e.getErrorEval(); + } + } + + private String laxValueToString(ValueEval eval) { + return (eval instanceof MissingArgEval) ? "" : OperandResolver.coerceValueToString(eval); + } + + private AreaEval notFoundAreaEval(String notFound, int width) { + return new AreaEval() { + @Override + public int getFirstRow() { + return 0; + } + + @Override + public int getLastRow() { + return 0; + } + + @Override + public int getFirstColumn() { + return 0; + } + + @Override + public int getLastColumn() { + return width - 1; + } + + @Override + public ValueEval getAbsoluteValue(int row, int col) { + if (col == 0) { + return new StringEval(notFound); + } + return new StringEval(""); + } + + @Override + public boolean contains(int row, int col) { + return containsRow(row) && containsColumn(col); + } + + @Override + public boolean containsColumn(int col) { + return col < width; + } + + @Override + public boolean containsRow(int row) { + return row == 0; + } + + @Override + public int getWidth() { + return width; + } + + @Override + public int getHeight() { + return 1; + } + + @Override + public ValueEval getRelativeValue(int relativeRowIndex, int relativeColumnIndex) { + return getAbsoluteValue(relativeRowIndex, relativeColumnIndex); + } + + @Override + public AreaEval offset(int relFirstRowIx, int relLastRowIx, int relFirstColIx, int relLastColIx) { + return null; + } + + @Override + public ValueEval getValue(int sheetIndex, int rowIndex, int columnIndex) { + return getAbsoluteValue(rowIndex, columnIndex); + } + + @Override + public int getFirstSheetIndex() { + return 0; + } + + @Override + public int getLastSheetIndex() { + return 0; + } + + @Override + public ValueEval getValue(int rowIndex, int columnIndex) { + return getAbsoluteValue(rowIndex, columnIndex); + } + + @Override + public boolean isColumn() { + return false; + } + + @Override + public TwoDEval getRow(int rowIndex) { + return null; + } + + @Override + public TwoDEval getColumn(int columnIndex) { + return null; + } + + @Override + public boolean isSubTotal(int rowIndex, int columnIndex) { + return false; + } + + @Override + public boolean isRowHidden(int rowIndex) { + return false; + } + }; + } +} diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/Hlookup.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/Hlookup.java index 91a7b6f5a5..d01f802e5b 100644 --- a/poi/src/main/java/org/apache/poi/ss/formula/functions/Hlookup.java +++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/Hlookup.java @@ -55,7 +55,7 @@ public final class Hlookup extends Var3or4ArgFunction { ValueEval lookupValue = OperandResolver.getSingleValue(arg0, srcRowIndex, srcColumnIndex); TwoDEval tableArray = LookupUtils.resolveTableArrayArg(arg1); boolean isRangeLookup = LookupUtils.resolveRangeLookupArg(arg3, srcRowIndex, srcColumnIndex); - int colIndex = LookupUtils.lookupIndexOfValue(lookupValue, LookupUtils.createRowVector(tableArray, 0), isRangeLookup); + int colIndex = LookupUtils.lookupFirstIndexOfValue(lookupValue, LookupUtils.createRowVector(tableArray, 0), isRangeLookup); int rowIndex = LookupUtils.resolveRowOrColIndexArg(arg2, srcRowIndex, srcColumnIndex); ValueVector resultCol = createResultColumnVector(tableArray, rowIndex); return resultCol.getItem(colIndex); diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/Lookup.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/Lookup.java index f3a332b1a1..8c2ff6f1af 100644 --- a/poi/src/main/java/org/apache/poi/ss/formula/functions/Lookup.java +++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/Lookup.java @@ -65,7 +65,7 @@ public final class Lookup extends Var2or3ArgFunction { // 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); + int index = LookupUtils.lookupFirstIndexOfValue(lookupValue, lookupVector, true); return resultVector.getItem(index); } catch (final EvaluationException e) { return e.getErrorEval(); @@ -86,7 +86,7 @@ public final class Lookup extends Var2or3ArgFunction { // Excel seems to handle this by accessing past the end of the result vector. throw new RuntimeException("Lookup vector and result vector of differing sizes not supported yet"); } - int index = LookupUtils.lookupIndexOfValue(lookupValue, lookupVector, true); + int index = LookupUtils.lookupFirstIndexOfValue(lookupValue, lookupVector, true); return resultVector.getItem(index); } catch (EvaluationException e) { 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 d6a92b3e1f..c605b00d87 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 @@ -17,6 +17,8 @@ package org.apache.poi.ss.formula.functions; +import java.util.HashMap; +import java.util.Map; import java.util.regex.Matcher; import java.util.regex.Pattern; @@ -40,6 +42,64 @@ import org.apache.poi.util.Internal; @Internal public final class LookupUtils { + public enum MatchMode { + ExactMatch(0), + ExactMatchFallbackToSmallerValue(-1), + ExactMatchFallbackToLargerValue(1), + WildcardMatch(2); + + private final int intValue; + + MatchMode(final int intValue) { + this.intValue = intValue; + } + + public int getIntValue() { return intValue; } + } + + public enum SearchMode { + IterateForward(1), + IterateBackward(-1), + BinarySearchForward(2), + BinarySearchBackward(-2); + + private final int intValue; + + SearchMode(final int intValue) { + this.intValue = intValue; + } + + public int getIntValue() { return intValue; } + } + + private static Map matchModeMap = new HashMap<>(); + private static Map searchModeMap = new HashMap<>(); + + static { + for (MatchMode mode : MatchMode.values()) { + matchModeMap.put(mode.getIntValue(), mode); + } + for (SearchMode mode : SearchMode.values()) { + searchModeMap.put(mode.getIntValue(), mode); + } + } + + public static MatchMode matchMode(int m) { + MatchMode mode = matchModeMap.get(m); + if (mode == null) { + throw new IllegalArgumentException("unknown match mode " + m); + } + return mode; + } + + public static SearchMode searchMode(int s) { + SearchMode mode = searchModeMap.get(s); + if (mode == null) { + throw new IllegalArgumentException("unknown search mode " + s); + } + return mode; + } + /** * Represents a single row or column within an {@code AreaEval}. */ @@ -284,13 +344,12 @@ public final class LookupUtils { protected abstract String getValueAsString(); } + private static class StringLookupComparer extends LookupValueComparerBase { - private static final class StringLookupComparer extends LookupValueComparerBase { - - private final String _value; - private final Pattern _wildCardPattern; - private final boolean _matchExact; - private final boolean _isMatchFunction; + protected final String _value; + protected final Pattern _wildCardPattern; + protected final boolean _matchExact; + protected final boolean _isMatchFunction; protected StringLookupComparer(StringEval se, boolean matchExact, boolean isMatchFunction) { super(se); @@ -300,11 +359,14 @@ public final class LookupUtils { _isMatchFunction = isMatchFunction; } - @Override - protected CompareResult compareSameType(ValueEval other) { + protected String convertToString(ValueEval other) { StringEval se = (StringEval) other; + return se.getStringValue(); + } - String stringValue = se.getStringValue(); + @Override + protected CompareResult compareSameType(ValueEval other) { + String stringValue = convertToString(other); if (_wildCardPattern != null && (_isMatchFunction || !_matchExact)) { Matcher matcher = _wildCardPattern.matcher(stringValue); boolean matches = matcher.matches(); @@ -319,6 +381,27 @@ public final class LookupUtils { return _value; } } + + private static final class TolerantStringLookupComparer extends StringLookupComparer { + + static StringEval convertToStringEval(ValueEval eval) { + if (eval instanceof StringEval) { + return (StringEval)eval; + } + String sv = OperandResolver.coerceValueToString(eval); + return new StringEval(sv); + } + + protected TolerantStringLookupComparer(ValueEval eval, boolean matchExact, boolean isMatchFunction) { + super(convertToStringEval(eval), matchExact, isMatchFunction); + } + + @Override + protected String convertToString(ValueEval other) { + return OperandResolver.coerceValueToString(other); + } + } + private static final class NumberLookupComparer extends LookupValueComparerBase { private final double _value; @@ -493,13 +576,13 @@ public final class LookupUtils { throw new RuntimeException("Unexpected eval type (" + valEval + ")"); } - public static int lookupIndexOfValue(ValueEval lookupValue, ValueVector vector, boolean isRangeLookup) throws EvaluationException { + public static int lookupFirstIndexOfValue(ValueEval lookupValue, ValueVector vector, boolean isRangeLookup) throws EvaluationException { LookupValueComparer lookupComparer = createLookupComparer(lookupValue, isRangeLookup, false); int result; if(isRangeLookup) { result = performBinarySearch(vector, lookupComparer); } else { - result = lookupIndexOfExactValue(lookupComparer, vector); + result = lookupFirstIndexOfValue(lookupComparer, vector, MatchMode.ExactMatch); } if(result < 0) { throw new EvaluationException(ErrorEval.NA); @@ -507,26 +590,129 @@ public final class LookupUtils { return result; } + public static int xlookupIndexOfValue(ValueEval lookupValue, ValueVector vector, MatchMode matchMode, SearchMode searchMode) throws EvaluationException { + LookupValueComparer lookupComparer = createTolerantLookupComparer(lookupValue, true, true); + int result; + if (searchMode == SearchMode.IterateBackward || searchMode == SearchMode.BinarySearchBackward) { + result = lookupLastIndexOfValue(lookupComparer, vector, matchMode); + } else { + result = lookupFirstIndexOfValue(lookupComparer, vector, matchMode); + } + if(result < 0) { + throw new EvaluationException(ErrorEval.NA); + } + return result; + } /** - * Finds first (lowest index) exact occurrence of specified value. + * Finds first (lowest index) matching occurrence of specified value. * @param lookupComparer the value to be found in column or row vector * @param vector the values to be searched. For VLOOKUP this is the first column of the * tableArray. For HLOOKUP this is the first row of the tableArray. + * @param matchMode * @return zero based index into the vector, -1 if value cannot be found */ - private static int lookupIndexOfExactValue(LookupValueComparer lookupComparer, ValueVector vector) { + private static int lookupFirstIndexOfValue(LookupValueComparer lookupComparer, ValueVector vector, + MatchMode matchMode) { // find first occurrence of lookup value int size = vector.getSize(); + int bestMatchIdx = -1; + ValueEval bestMatchEval = null; for (int i = 0; i < size; i++) { - if(lookupComparer.compareTo(vector.getItem(i)).isEqual()) { + ValueEval valueEval = vector.getItem(i); + CompareResult result = lookupComparer.compareTo(valueEval); + if(result.isEqual()) { return i; } - } - return -1; + switch (matchMode) { + case ExactMatchFallbackToLargerValue: + if (result.isLessThan()) { + if (bestMatchEval == null) { + bestMatchIdx = i; + bestMatchEval = valueEval; + } else { + LookupValueComparer matchComparer = createTolerantLookupComparer(valueEval, true, true); + if (matchComparer.compareTo(bestMatchEval).isLessThan()) { + bestMatchIdx = i; + bestMatchEval = valueEval; + } + } + } + break; + case ExactMatchFallbackToSmallerValue: + if (result.isGreaterThan()) { + if (bestMatchEval == null) { + bestMatchIdx = i; + bestMatchEval = valueEval; + } else { + LookupValueComparer matchComparer = createTolerantLookupComparer(valueEval, true, true); + if (matchComparer.compareTo(bestMatchEval).isGreaterThan()) { + bestMatchIdx = i; + bestMatchEval = valueEval; + } + } + } + break; + } + } + return bestMatchIdx; } + /** + * Finds last (greatest index) matching occurrence of specified value. + * @param lookupComparer the value to be found in column or row vector + * @param vector the values to be searched. For VLOOKUP this is the first column of the + * tableArray. For HLOOKUP this is the first row of the tableArray. + * @param matchMode + * @return zero based index into the vector, -1 if value cannot be found + */ + private static int lookupLastIndexOfValue(LookupValueComparer lookupComparer, ValueVector vector, + MatchMode matchMode) { + + // find last occurrence of lookup value + int size = vector.getSize(); + int bestMatchIdx = -1; + ValueEval bestMatchEval = null; + for (int i = size - 1; i >= 0; i--) { + ValueEval valueEval = vector.getItem(i); + CompareResult result = lookupComparer.compareTo(valueEval); + if (result.isEqual()) { + return i; + } + switch (matchMode) { + case ExactMatchFallbackToLargerValue: + if (result.isLessThan()) { + if (bestMatchEval == null) { + bestMatchIdx = i; + bestMatchEval = valueEval; + } else { + LookupValueComparer matchComparer = createTolerantLookupComparer(valueEval, true, true); + if (matchComparer.compareTo(bestMatchEval).isLessThan()) { + bestMatchIdx = i; + bestMatchEval = valueEval; + } + } + } + break; + case ExactMatchFallbackToSmallerValue: + if (result.isGreaterThan()) { + if (bestMatchEval == null) { + bestMatchIdx = i; + bestMatchEval = valueEval; + } else { + LookupValueComparer matchComparer = createTolerantLookupComparer(valueEval, true, true); + if (matchComparer.compareTo(bestMatchEval).isGreaterThan()) { + bestMatchIdx = i; + bestMatchEval = valueEval; + } + } + } + break; + } + } + return bestMatchIdx; + } /** * Encapsulates some standard binary search functionality so the unusual Excel behaviour can @@ -652,7 +838,7 @@ public final class LookupUtils { return maxIx - 1; } - public static LookupValueComparer createLookupComparer(ValueEval lookupValue, boolean matchExact, boolean isMatchFunction) { + static LookupValueComparer createLookupComparer(ValueEval lookupValue, boolean matchExact, boolean isMatchFunction) { if (lookupValue == BlankEval.instance) { // blank eval translates to zero @@ -672,4 +858,17 @@ public final class LookupUtils { } throw new IllegalArgumentException("Bad lookup value type (" + lookupValue.getClass().getName() + ")"); } + + private static LookupValueComparer createTolerantLookupComparer(ValueEval lookupValue, boolean matchExact, boolean isMatchFunction) { + if (lookupValue == BlankEval.instance) { + return new TolerantStringLookupComparer(new StringEval(""), matchExact, isMatchFunction); + } + if (lookupValue instanceof BoolEval) { + return new BooleanLookupComparer((BoolEval) lookupValue); + } + if (matchExact && lookupValue instanceof NumberEval) { + return new NumberLookupComparer((NumberEval) lookupValue); + } + return new TolerantStringLookupComparer(lookupValue, matchExact, isMatchFunction); + } } diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/Vlookup.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/Vlookup.java index 3816abbbc7..2e1490b435 100644 --- a/poi/src/main/java/org/apache/poi/ss/formula/functions/Vlookup.java +++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/Vlookup.java @@ -60,7 +60,7 @@ public final class Vlookup extends Var3or4ArgFunction { } catch(RuntimeException e) { isRangeLookup = true; } - int rowIndex = LookupUtils.lookupIndexOfValue(lookupValue, LookupUtils.createColumnVector(tableArray, 0), isRangeLookup); + int rowIndex = LookupUtils.lookupFirstIndexOfValue(lookupValue, LookupUtils.createColumnVector(tableArray, 0), isRangeLookup); int colIndex = LookupUtils.resolveRowOrColIndexArg(col_index, srcRowIndex, srcColumnIndex); ValueVector resultCol = createResultColumnVector(tableArray, colIndex); return resultCol.getItem(rowIndex); 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..834a90f424 --- /dev/null +++ b/poi/src/test/java/org/apache/poi/ss/formula/atp/TestXLookupFunction.java @@ -0,0 +1,147 @@ + +/* ==================================================================== + 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.*; +import org.apache.poi.ss.usermodel.*; +import org.apache.poi.ss.util.CellRangeAddress; +import org.apache.poi.ss.util.CellReference; +import org.junit.jupiter.api.Test; + +import java.io.IOException; +import java.util.Locale; + +import static org.apache.poi.ss.util.Utils.*; +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); + assertString(fe, cell, "XLOOKUP(F2,B2:B11,D2:D11)", "+55"); + } + } + + @Test + void testMicrosoftExample2() throws IOException { + String formulaText = "XLOOKUP(B2,B5:B14,C5:D14)"; + try (HSSFWorkbook wb = initWorkbook2(8389)) { + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); + HSSFSheet sheet = wb.getSheetAt(0); + HSSFRow row1 = sheet.getRow(1); + String col1 = CellReference.convertNumToColString(2); + String col2 = CellReference.convertNumToColString(3); + String cellRef = String.format(Locale.ENGLISH, "%s2:%s2", col1, col2); + sheet.setArrayFormula(formulaText, CellRangeAddress.valueOf(cellRef)); + fe.evaluateAll(); + assertEquals("Dianne Pugh", row1.getCell(2).getStringCellValue()); + assertEquals("Finance", row1.getCell(3).getStringCellValue()); + } + } + + @Test + void testMicrosoftExample3() throws IOException { + try (HSSFWorkbook wb = initWorkbook2(999999)) { + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); + HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100); + assertError(fe, cell, "XLOOKUP(B2,B5:B14,C5:D14)", FormulaError.NA); + + String formulaText = "XLOOKUP(B2,B5:B14,C5:D14,\"not found\")"; + assertString(fe, cell, formulaText, "not found"); + + HSSFSheet sheet = wb.getSheetAt(0); + HSSFRow row1 = sheet.getRow(1); + String col1 = CellReference.convertNumToColString(2); + String col2 = CellReference.convertNumToColString(3); + String cellRef = String.format(Locale.ENGLISH, "%s2:%s2", col1, col2); + sheet.setArrayFormula(formulaText, CellRangeAddress.valueOf(cellRef)); + fe.evaluateAll(); + assertEquals("not found", row1.getCell(2).getStringCellValue()); + assertEquals("", row1.getCell(3).getStringCellValue()); + } + } + + @Test + void testMicrosoftExample4() throws IOException { + try (HSSFWorkbook wb = initWorkbook4()) { + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); + HSSFCell cell = wb.getSheetAt(0).getRow(1).createCell(6); + assertDouble(fe, cell, "XLOOKUP(E2,C2:C7,B2:B7,0,1,1)", 0.24); + assertDouble(fe, cell, "XLOOKUP(E2,C2:C7,B2:B7,0,1,2)", 0.24); + assertDouble(fe, cell, "XLOOKUP(E2,C2:C7,B2:B7,0,1,-1)", 0.24); + } + } + + + 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 HSSFWorkbook initWorkbook2(int empId) { + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet = wb.createSheet(); + addRow(sheet, 0, null, "Emp Id", "Employee Name", "Department"); + addRow(sheet, 1, null, empId); + 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; + } + + private HSSFWorkbook initWorkbook4() { + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet = wb.createSheet(); + addRow(sheet, 0, null, "Tax Rate", "Max Income", null, "Income", "Tax Rate"); + addRow(sheet, 1, null, 0.10, 9700, null, 46523); + addRow(sheet, 2, null, 0.22, 39475); + addRow(sheet, 3, null, 0.24, 84200); + addRow(sheet, 4, null, 0.32, 160726); + addRow(sheet, 5, null, 0.35, 204100); + addRow(sheet, 5, null, 0.37, 510300); + return wb; + } + +} -- 2.39.5