Package org.apache.poi.ss.formula.eval

Class OperandResolver

  • public final class OperandResolver
extends Object
    Provides functionality for evaluating arguments to functions and operators.

    • 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 - if srcCellRow or srcCellCol do not properly index into an AreaEval. If the actual value retrieved is an ErrorEval, a corresponding EvaluationException is thrown.

      • getElementFromArray

        public static ValueEval getElementFromArray​(AreaEval ae,
                                            EvaluationCell cell)
        Retrieves a single value from an area evaluation utilizing the 2D indices of the cell within its own area reference to index the value in the area evaluation.
        Parameters:
        ae - area reference after evaluation
        cell - the source cell of the formula that contains its 2D indices
        Returns:
        a NumberEval, StringEval, BoolEval or BlankEval. or ErrorEval Never null.

      • 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.
        sample spreadsheet
          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:
        sample spreadsheet
          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

      • 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

      • parseDateTime

        public static Double parseDateTime​(String pText)

      • 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