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/08/08 09:44:38 UTC
svn commit: r1892107 - in /poi/trunk/poi/src:
main/java/org/apache/poi/ss/formula/atp/
test/java/org/apache/poi/ss/formula/atp/ test/java/org/apache/poi/ss/util/
Author: fanningpj
Date: Sun Aug 8 09:44:38 2021
New Revision: 1892107
URL: http://svn.apache.org/viewvc?rev=1892107&view=rev
Log:
init support for XLOOKUP
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/TextJoinFunction.java
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
poi/trunk/poi/src/test/java/org/apache/poi/ss/util/Utils.java
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/TextJoinFunction.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/TextJoinFunction.java?rev=1892107&r1=1892106&r2=1892107&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/TextJoinFunction.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/TextJoinFunction.java Sun Aug 8 09:44:38 2021
@@ -96,14 +96,14 @@ final class TextJoinFunction implements
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
}
}
- private String coerceValueToString(ValueEval eval) {
+ private String laxValueToString(ValueEval eval) {
return (eval instanceof MissingArgEval) ? "" : OperandResolver.coerceValueToString(eval);
}
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=1892107&r1=1892106&r2=1892107&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 Aug 8 09:44:38 2021
@@ -21,11 +21,13 @@ import org.apache.poi.ss.formula.Operati
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 F
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 F
}
}
}
- 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 F
}
return -1;
}
+
+ private String laxValueToString(ValueEval eval) {
+ return (eval instanceof MissingArgEval) ? "" : OperandResolver.coerceValueToString(eval);
+ }
}
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=1892107&r1=1892106&r2=1892107&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 Aug 8 09:44:38 2021
@@ -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;
}
+
}
Modified: poi/trunk/poi/src/test/java/org/apache/poi/ss/util/Utils.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/util/Utils.java?rev=1892107&r1=1892106&r2=1892107&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/util/Utils.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/util/Utils.java Sun Aug 8 09:44:38 2021
@@ -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);
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org