org.zkoss.poi.ss.formula.eval

Class OperandResolver

java.lang.Object

org.zkoss.poi.ss.formula.eval.OperandResolver

public final class OperandResolver
extends Object

Provides functionality for evaluating arguments to functions and operators.

Author:

Josh Micich, Brendan Nolan, henrichen@zkoss.org: handle HYPERLINK function

Method Summary

Modifier and Type	Method and Description
static ValueEval	chooseMultipleElementsFromArea(AreaEval ae, int srcCellRow, int srcCellCol)
static ValueEval	chooseSingleElementFromArea(AreaEval ae, int srcCellRow, int 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 Boolean	coerceValueToBoolean(ValueEval ve, boolean stringsAreBlanks)
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() ).
static long	coerceValueToLong(ValueEval ev) Applies some conversion rules if the supplied value is not already a long integer. Value is first coerced to a double ( See coerceValueToDouble() ).
static String	coerceValueToString(ValueEval ve)
static ValueEval	getMultipleValue(ValueEval arg, int srcCellRow, int srcCellCol)
static ValueEval	getSingleValue(ValueEval arg, int srcCellRow, int srcCellCol) Retrieves a single value from a variety of different argument types according to standard Excel rules.
static Double	parseDouble(String pText) Converts a string to a double using standard rules that Excel would use. Tolerates leading and trailing spaces, Doesn't support currency prefixes, commas, percentage signs or arithmetic operations strings.

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(ValueEval arg,
                                       int srcCellRow,
                                       int 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,
                                                    int 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() ). Note - BlankEval is converted to 0.

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 converted to NumberEval.ZERO.

Parameters:

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

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).

RuntimeException - if the supplied parameter is not NumberEval, StringEval, BoolEval or BlankEval

EvaluationException

parseDouble

public static Double parseDouble(String pText)

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

Doesn't support currency prefixes, commas, percentage signs or arithmetic operations strings. Some examples:
" 123 " -> 123.0
".123" -> 0.123
"1E4" -> 1000
"-123" -> -123.0
These not supported yet:
" $ 1,000.00 " -> 1000.0
"$1.25E4" -> 12500.0
"5**2" -> 500
"250%" -> 2.5

Returns:

null if the specified text cannot be parsed as a number

coerceValueToString

public static String coerceValueToString(ValueEval ve)

Parameters:

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

Returns:

the converted string value. never null

coerceValueToBoolean

public static Boolean coerceValueToBoolean(ValueEval ve,
                                           boolean stringsAreBlanks)
                                    throws EvaluationException

Returns:

null to represent blank values

Throws:

EvaluationException - if ve is an ErrorEval, or if a string value cannot be converted

coerceValueToLong

public static long coerceValueToLong(ValueEval ev)
                              throws EvaluationException

Applies some conversion rules if the supplied value is not already a long integer.
Value is first coerced to a double ( See coerceValueToDouble() ). Note - BlankEval is converted to 0.

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

Throws:

EvaluationException

chooseMultipleElementsFromArea

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

Throws:

EvaluationException

getMultipleValue

public static ValueEval getMultipleValue(ValueEval arg,
                                         int srcCellRow,
                                         int srcCellCol)
                                  throws EvaluationException

Throws:

EvaluationException