diff options
Diffstat (limited to 'poi')
| -rw-r--r-- | poi/src/main/java/org/apache/poi/ss/formula/atp/XLookupFunction.java | 30 | ||||
| -rw-r--r-- | poi/src/test/java/org/apache/poi/ss/formula/atp/TestXLookupFunction.java | 25 |
2 files changed, 35 insertions, 20 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 ba86b6cec0..fd27a8dc8a 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 @@ -24,8 +24,6 @@ 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() * @@ -63,16 +61,12 @@ final class XLookupFunction implements FreeRefFunction, ArrayFunction { if (args.length < 3) { return ErrorEval.VALUE_INVALID; } - Optional<String> notFound = Optional.empty(); + ValueEval notFound = BlankEval.instance; 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); - } + if (notFoundValue != null) { + notFound = notFoundValue; } } catch (EvaluationException e) { return e.getErrorEval(); @@ -106,7 +100,7 @@ final class XLookupFunction implements FreeRefFunction, ArrayFunction { } private ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval lookupEval, ValueEval indexEval, - ValueEval returnEval, Optional<String> notFound, LookupUtils.MatchMode matchMode, + ValueEval returnEval, ValueEval notFound, LookupUtils.MatchMode matchMode, LookupUtils.SearchMode searchMode, boolean isSingleValue) { try { ValueEval lookupValue = OperandResolver.getSingleValue(lookupEval, srcRowIndex, srcColumnIndex); @@ -116,16 +110,16 @@ final class XLookupFunction implements FreeRefFunction, ArrayFunction { matchedRow = LookupUtils.xlookupIndexOfValue(lookupValue, LookupUtils.createColumnVector(tableArray, 0), matchMode, searchMode); } catch (EvaluationException e) { if (ErrorEval.NA.equals(e.getErrorEval())) { - if (notFound.isPresent()) { + if (notFound != BlankEval.instance) { if (returnEval instanceof AreaEval) { AreaEval area = (AreaEval)returnEval; int width = area.getWidth(); if (isSingleValue || width <= 1) { - return new StringEval(notFound.get()); + return notFound; } - return notFoundAreaEval(notFound.get(), width); + return notFoundAreaEval(notFound, width); } else { - return new StringEval(notFound.get()); + return notFound; } } return ErrorEval.NA; @@ -147,11 +141,7 @@ final class XLookupFunction implements FreeRefFunction, ArrayFunction { } } - private String laxValueToString(ValueEval eval) { - return (eval instanceof MissingArgEval) ? "" : OperandResolver.coerceValueToString(eval); - } - - private AreaEval notFoundAreaEval(String notFound, int width) { + private AreaEval notFoundAreaEval(ValueEval notFound, int width) { return new AreaEval() { @Override public int getFirstRow() { @@ -176,7 +166,7 @@ final class XLookupFunction implements FreeRefFunction, ArrayFunction { @Override public ValueEval getAbsoluteValue(int row, int col) { if (col == 0) { - return new StringEval(notFound); + return notFound; } return new StringEval(""); } 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 761d5b1fef..ab66de7c5e 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 @@ -121,6 +121,18 @@ public class TestXLookupFunction { } } + @Test + void testReverseBinarySearchWithInvalidValues() throws IOException { + try (HSSFWorkbook wb = initReverseWorkbook4WithInvalidIncomes()) { + 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,-2)", 0.37); + //TODO next assertion is not working and needs investigation + //assertDouble(fe, cell, "XLOOKUP(9700,C2:C7,B2:B7,0,0,-2)", 0.1); + assertDouble(fe, cell, "XLOOKUP(39474,C2:C7,B2:B7,0,0,-2)", 0); + } + } + private HSSFWorkbook initWorkbook1() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); @@ -183,4 +195,17 @@ public class TestXLookupFunction { return wb; } + private HSSFWorkbook initReverseWorkbook4WithInvalidIncomes() { + 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.37, 510300, null, 46523); + addRow(sheet, 2, null, 0.35, "invalid"); + addRow(sheet, 3, null, 0.32, "invalid"); + addRow(sheet, 4, null, 0.24, "invalid"); + addRow(sheet, 5, null, 0.22, "invalid"); + addRow(sheet, 6, null, 0.10, 9700); + return wb; + } + } |