org.apache.poi.ss.formula.functions

Class LookupUtils

java.lang.Object

org.apache.poi.ss.formula.functions.LookupUtils

@Internal
public final class LookupUtils
extends java.lang.Object

Common functionality used by VLOOKUP, HLOOKUP, LOOKUP and MATCH

Nested Class Summary

Nested Classes

Modifier and Type	Class and Description
static class	LookupUtils.CompareResult Enumeration to support 4 valued comparison results.
static interface	LookupUtils.LookupValueComparer
static class	LookupUtils.MatchMode
static class	LookupUtils.SearchMode
static interface	LookupUtils.ValueVector Represents a single row or column within an AreaEval.

Constructor Summary

Constructors

Constructor and Description
LookupUtils()

Method Summary

Modifier and Type	Method and Description
static LookupUtils.ValueVector	createColumnVector(TwoDEval tableArray, int relativeColumnIndex)
static LookupUtils.ValueVector	createRowVector(TwoDEval tableArray, int relativeRowIndex)
static LookupUtils.ValueVector	createVector(RefEval re)
static LookupUtils.ValueVector	createVector(TwoDEval ae)
static int	lookupFirstIndexOfValue(ValueEval lookupValue, LookupUtils.ValueVector vector, boolean isRangeLookup)
static LookupUtils.MatchMode	matchMode(int m)
static boolean	resolveRangeLookupArg(ValueEval rangeLookupArg, int srcCellRow, int srcCellCol) Resolves the last (optional) parameter (range_lookup) to the VLOOKUP and HLOOKUP functions.
static int	resolveRowOrColIndexArg(ValueEval rowColIndexArg, int srcCellRow, int srcCellCol) Processes the third argument to VLOOKUP, or HLOOKUP (col_index_num or row_index_num respectively). Sample behaviour: Sample behaviour Input ReturnValue Thrown Error 54 2.92 "5"4 "2.18e1"21 "-$2"-3* FALSE-1* TRUE0 "TRUE" #REF! "abc" #REF! "" #REF! <blank> #VALUE! Note - out of range errors (result index too high) are handled by the caller.
static TwoDEval	resolveTableArrayArg(ValueEval eval) The second argument (table_array) should be an area ref, but can actually be a cell ref, in which case it is interpreted as a 1x1 area ref.
static LookupUtils.SearchMode	searchMode(int s)
static int	xlookupIndexOfValue(ValueEval lookupValue, LookupUtils.ValueVector vector, LookupUtils.MatchMode matchMode, LookupUtils.SearchMode searchMode)

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Constructor Detail

LookupUtils

public LookupUtils()

Method Detail

matchMode

public static LookupUtils.MatchMode matchMode(int m)

searchMode

public static LookupUtils.SearchMode searchMode(int s)

createRowVector

public static LookupUtils.ValueVector createRowVector(TwoDEval tableArray,
                                                      int relativeRowIndex)

createColumnVector

public static LookupUtils.ValueVector createColumnVector(TwoDEval tableArray,
                                                         int relativeColumnIndex)

createVector

public static LookupUtils.ValueVector createVector(TwoDEval ae)

Returns:

null if the supplied area is neither a single row nor a single column

createVector

public static LookupUtils.ValueVector createVector(RefEval re)

resolveRowOrColIndexArg

public static int resolveRowOrColIndexArg(ValueEval rowColIndexArg,
                                          int srcCellRow,
                                          int srcCellCol)
                                   throws EvaluationException

Processes the third argument to VLOOKUP, or HLOOKUP (col_index_num or row_index_num respectively).
Sample behaviour:

Sample behaviour

Input Return	Value	Thrown Error
5	4
2.9	2
"5"	4
"2.18e1"	21
"-$2"	-3	*
FALSE	-1	*
TRUE	0
"TRUE"		#REF!
"abc"		#REF!
""		#REF!
<blank>		#VALUE!

Note - out of range errors (result index too high) are handled by the caller.

Returns:

column or row index as a zero-based value, never negative.

Throws:

EvaluationException - when the specified arg cannot be coerced to a non-negative integer

resolveTableArrayArg

public static TwoDEval resolveTableArrayArg(ValueEval eval)
                                     throws EvaluationException

The second argument (table_array) should be an area ref, but can actually be a cell ref, in which case it is interpreted as a 1x1 area ref. Other scalar values cause #VALUE! error.

Throws:

EvaluationException

resolveRangeLookupArg

public static boolean resolveRangeLookupArg(ValueEval rangeLookupArg,
                                            int srcCellRow,
                                            int srcCellCol)
                                     throws EvaluationException

Resolves the last (optional) parameter (range_lookup) to the VLOOKUP and HLOOKUP functions.

Parameters:

rangeLookupArg - must not be null

Throws:

EvaluationException

lookupFirstIndexOfValue

public static int lookupFirstIndexOfValue(ValueEval lookupValue,
                                          LookupUtils.ValueVector vector,
                                          boolean isRangeLookup)
                                   throws EvaluationException

Throws:

EvaluationException

xlookupIndexOfValue

public static int xlookupIndexOfValue(ValueEval lookupValue,
                                      LookupUtils.ValueVector vector,
                                      LookupUtils.MatchMode matchMode,
                                      LookupUtils.SearchMode searchMode)
                               throws EvaluationException

Throws:

EvaluationException