org.apache.poi.hssf.record.formula.eval
Class OperandResolver

java.lang.Object
  org.apache.poi.hssf.record.formula.eval.OperandResolver

public final class OperandResolver
extends java.lang.Object

Provides functionality for evaluating arguments to functions and operators.

Author:

Josh Micich

Method Summary

static ValueEval	chooseSingleElementFromArea(AreaEval ae, int srcCellRow, short srcCellCol) Implements (some perhaps not well known) Excel functionality to select a single cell from an area depending on the coordinates of the calling cell.
static double	coerceValueToDouble(ValueEval ev) Applies some conversion rules if the supplied value is not already a number.
static int	coerceValueToInt(ValueEval ev) Applies some conversion rules if the supplied value is not already an integer. Value is first coerced to a double ( See coerceValueToDouble() ). Excel typically converts doubles to integers by truncating toward negative infinity. The equivalent java code is: return (int)Math.floor(d); not: return (int)d; // wrong - rounds toward zero
static java.lang.String	coerceValueToString(ValueEval ve)
static ValueEval	getSingleValue(Eval arg, int srcCellRow, short srcCellCol) Retrieves a single value from a variety of different argument types according to standard Excel rules.
static java.lang.Double	parseDouble(java.lang.String pText) Converts a string to a double using standard rules that Excel would use. Tolerates currency prefixes, commas, leading and trailing spaces. Some examples: " 123 " -> 123.0 ".123" -> 0.123 These not supported yet: " $ 1,000.00 " -> 1000.0 "$1.25E4" -> 12500.0 "5**2" -> 500 "250%" -> 2.5

Methods inherited from class java.lang.Object

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

Method Detail

getSingleValue

public static ValueEval getSingleValue(Eval arg,
                                       int srcCellRow,
                                       short srcCellCol)
                                throws EvaluationException

Retrieves a single value from a variety of different argument types according to standard Excel rules. Does not perform any type conversion.

Parameters:

arg - the evaluated argument as passed to the function or operator.

srcCellRow - used when arg is a single column AreaRef

srcCellCol - used when arg is a single row AreaRef

Returns:

a NumberEval, StringEval, BoolEval or BlankEval. Never null or ErrorEval.

Throws:

EvaluationException(#VALUE!) - if srcCellRow or srcCellCol do not properly index into an AreaEval. If the actual value retrieved is an ErrorEval, a corresponding EvaluationException is thrown.

EvaluationException

chooseSingleElementFromArea

public static ValueEval chooseSingleElementFromArea(AreaEval ae,
                                                    int srcCellRow,
                                                    short srcCellCol)
                                             throws EvaluationException

Implements (some perhaps not well known) Excel functionality to select a single cell from an area depending on the coordinates of the calling cell. Here is an example demonstrating both selection from a single row area and a single column area in the same formula.

	A	B	C	D
1	15	20	25
2				200
3				300
3				400

If the formula "=1000+A1:B1+D2:D3" is put into the 9 cells from A2 to C4, the spreadsheet will look like this:

	A	B	C	D
1	15	20	25
2	1215	1220	#VALUE!	200
3	1315	1320	#VALUE!	300
4	#VALUE!	#VALUE!	#VALUE!	400

Note that the row area (A1:B1) does not include column C and the column area (D2:D3) does not include row 4, so the values in C1(=25) and D4(=400) are not accessible to the formula as written, but in the 4 cells A2:B3, the row and column selection works ok.

The same concept is extended to references across sheets, such that even multi-row, multi-column areas can be useful.

Of course with carefully (or carelessly) chosen parameters, cyclic references can occur and hence this method can throw a 'circular reference' EvaluationException. Note that this method does not attempt to detect cycles. Every cell in the specified Area ae has already been evaluated prior to this method call. Any cell (or cells) part of ae that would incur a cyclic reference error if selected by this method, will already have the value ErrorEval.CIRCULAR_REF_ERROR upon entry to this method. It is assumed logic exists elsewhere to produce this behaviour.

Returns:

whatever the selected cell's evaluated value is. Never null. Never ErrorEval.

Throws:

EvaluationException - if there is a problem with indexing into the area, or if the evaluated cell has an error.

coerceValueToInt

public static int coerceValueToInt(ValueEval ev)
                            throws EvaluationException

Applies some conversion rules if the supplied value is not already an integer.
Value is first coerced to a double ( See coerceValueToDouble() ).

Excel typically converts doubles to integers by truncating toward negative infinity.
The equivalent java code is:
return (int)Math.floor(d);
not:
return (int)d; // wrong - rounds toward zero

Throws:

EvaluationException

coerceValueToDouble

public static double coerceValueToDouble(ValueEval ev)
                                  throws EvaluationException

Applies some conversion rules if the supplied value is not already a number. Note - BlankEval is not supported and must be handled by the caller.

Parameters:

ev - must be a NumberEval, StringEval or BoolEval

Returns:

actual, parsed or interpreted double value (respectively).

Throws:

EvaluationException(#VALUE!) - only if a StringEval is supplied and cannot be parsed as a double (See parseDouble() for allowable formats).

java.lang.RuntimeException - if the supplied parameter is not NumberEval, StringEval or BoolEval

EvaluationException

parseDouble

public static java.lang.Double parseDouble(java.lang.String pText)

Converts a string to a double using standard rules that Excel would use.
Tolerates currency prefixes, commas, leading and trailing spaces.

Some examples:
" 123 " -> 123.0
".123" -> 0.123
These not supported yet:
" $ 1,000.00 " -> 1000.0
"$1.25E4" -> 12500.0
"5**2" -> 500
"250%" -> 2.5

Parameters:

text -

Returns:

null if the specified text cannot be parsed as a number

coerceValueToString

public static java.lang.String coerceValueToString(ValueEval ve)

Parameters:

ve - must be a NumberEval, StringEval, BoolEval, or BlankEval

Returns:

the converted string value. never null