You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by fa...@apache.org on 2021/12/05 11:14:42 UTC
svn commit: r1895595 - in /poi/trunk/poi/src: main/java/org/apache/poi/ss/formula/atp/XLookupFunction.java test/java/org/apache/poi/ss/formula/atp/TestXLookupFunction.java
Author: fanningpj
Date: Sun Dec 5 11:14:42 2021
New Revision: 1895595
URL: http://svn.apache.org/viewvc?rev=1895595&view=rev
Log:
add xlookup tests
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/XLookupFunction.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestXLookupFunction.java
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/XLookupFunction.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/XLookupFunction.java?rev=1895595&r1=1895594&r2=1895595&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/XLookupFunction.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/XLookupFunction.java Sun Dec 5 11:14:42 2021
@@ -24,8 +24,6 @@ import org.apache.poi.ss.formula.functio
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 F
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 F
}
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 F
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 F
}
}
- 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 F
@Override
public ValueEval getAbsoluteValue(int row, int col) {
if (col == 0) {
- return new StringEval(notFound);
+ return notFound;
}
return new StringEval("");
}
Modified: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestXLookupFunction.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestXLookupFunction.java?rev=1895595&r1=1895594&r2=1895595&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestXLookupFunction.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestXLookupFunction.java Sun Dec 5 11:14:42 2021
@@ -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();
@@ -182,5 +194,18 @@ public class TestXLookupFunction {
addRow(sheet, 6, null, 0.10, 9700);
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;
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org