org.apache.poi.ss.formula.eval
Class OperandResolver

java.lang.Object
  extended by org.apache.poi.ss.formula.eval.OperandResolver

public final class OperandResolver
extends java.lang.Object

Provides functionality for evaluating arguments to functions and operators.

Author:
Josh Micich, Brendan Nolan

Method Summary
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 java.lang.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 java.lang.String coerceValueToString(ValueEval ve)
           
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 java.lang.Double parseDouble(java.lang.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 
1152025 
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 
1152025 
212151220#VALUE!200
313151320#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).
java.lang.RuntimeException - if the supplied parameter is not NumberEval, StringEval, BoolEval or BlankEval
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 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 java.lang.String coerceValueToString(ValueEval ve)
Parameters:
ve - must be a NumberEval, StringEval, BoolEval, or BlankEval
Returns:
the converted string value. never null

coerceValueToBoolean

public static java.lang.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


Copyright 2012 The Apache Software Foundation or its licensors, as applicable.