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 15:40:23 UTC
svn commit: r1892116 - in
/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula:
atp/XLookupFunction.java functions/LookupUtils.java
Author: fanningpj
Date: Sun Aug 8 15:40:23 2021
New Revision: 1892116
URL: http://svn.apache.org/viewvc?rev=1892116&view=rev
Log:
init support for XLOOKUP
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/XLookupFunction.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/LookupUtils.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=1892116&r1=1892115&r2=1892116&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 15:40:23 2021
@@ -18,15 +18,17 @@
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.FreeRefFunction;
+import org.apache.poi.ss.formula.functions.LookupUtils;
import java.util.Optional;
/**
* Implementation of Excel function XLOOKUP()
*
- * POI does not currently support have return values with multiple columns and just takes the first cell
+ * POI does not currently support having return values with multiple columns and just takes the first cell
* right now.
*
* <b>Syntax</b><br>
@@ -66,48 +68,59 @@ final class XLookupFunction implements F
return e.getErrorEval();
}
}
- return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1], args[2], notFound);
+ int matchMode = 0;
+ if (args.length > 4) {
+ try {
+ ValueEval matchModeValue = OperandResolver.getSingleValue(args[4], srcRowIndex, srcColumnIndex);
+ matchMode = OperandResolver.coerceValueToInt(matchModeValue);
+ } catch (EvaluationException e) {
+ return e.getErrorEval();
+ }
+ }
+ int searchMode = 1;
+ if (args.length > 5) {
+ try {
+ ValueEval searchModeValue = OperandResolver.getSingleValue(args[5], srcRowIndex, srcColumnIndex);
+ searchMode = OperandResolver.coerceValueToInt(searchModeValue);
+ } catch (EvaluationException e) {
+ return e.getErrorEval();
+ }
+ }
+ return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1], args[2], notFound, matchMode, searchMode);
}
private ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval lookupEval, ValueEval indexEval,
- ValueEval returnEval, Optional<String> notFound) {
+ ValueEval returnEval, Optional<String> notFound, int matchMode, int searchMode) {
try {
ValueEval lookupValue = OperandResolver.getSingleValue(lookupEval, srcRowIndex, srcColumnIndex);
- String lookup = laxValueToString(lookupValue);
- int matchedRow = matchedIndex(indexEval, lookup);
- if (matchedRow != -1) {
- if (returnEval instanceof AreaEval) {
- AreaEval area = (AreaEval)returnEval;
- //TODO to fully support XLOOKUP, we should return the full row
- //but POI does not currently support functions returning multiple cell values
- return area.getRelativeValue(matchedRow, 0);
+ TwoDEval tableArray = LookupUtils.resolveTableArrayArg(indexEval);
+ boolean isRangeLookup = false;
+ int matchedRow;
+ try {
+ matchedRow = LookupUtils.lookupIndexOfValue(lookupValue, LookupUtils.createColumnVector(tableArray, 0), isRangeLookup);
+ } catch (EvaluationException e) {
+ if (ErrorEval.NA.equals(e.getErrorEval())) {
+ if (notFound.isPresent()) {
+ return new StringEval(notFound.get());
+ }
+ return ErrorEval.NA;
+ } else {
+ return e.getErrorEval();
}
}
- if (notFound.isPresent()) {
- return new StringEval(notFound.get());
+ if (returnEval instanceof AreaEval) {
+ AreaEval area = (AreaEval)returnEval;
+ //TODO to fully support XLOOKUP, we should return the full row
+ //but POI does not currently support functions returning multiple cell values
+ return area.getRelativeValue(matchedRow, 0);
+ } else {
+ return ErrorEval.VALUE_INVALID;
}
- return ErrorEval.NA;
} catch (EvaluationException e) {
return e.getErrorEval();
}
}
- private int matchedIndex(ValueEval areaEval, String lookup) {
- if (areaEval instanceof AreaEval) {
- AreaEval area = (AreaEval)areaEval;
- for (int r = 0; r <= area.getHeight(); r++) {
- for (int c = 0; c <= area.getWidth(); c++) {
- ValueEval cellEval = area.getRelativeValue(r, c);
- String cellValue = OperandResolver.coerceValueToString(cellEval);
- if (lookup.equals(cellValue)) {
- return r;
- }
- }
- }
- }
- return -1;
- }
-
private String laxValueToString(ValueEval eval) {
return (eval instanceof MissingArgEval) ? "" : OperandResolver.coerceValueToString(eval);
}
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/LookupUtils.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/LookupUtils.java?rev=1892116&r1=1892115&r2=1892116&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/LookupUtils.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/LookupUtils.java Sun Aug 8 15:40:23 2021
@@ -36,7 +36,7 @@ import org.apache.poi.ss.formula.eval.Va
/**
* Common functionality used by VLOOKUP, HLOOKUP, LOOKUP and MATCH
*/
-final class LookupUtils {
+public final class LookupUtils {
/**
* Represents a single row or column within an {@code AreaEval}.
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org