Package adams.parser

Class SpreadSheetFormula

  • All Implemented Interfaces:
    Destroyable, GlobalInfoSupporter, LoggingLevelHandler, LoggingSupporter, OptionHandler, SizeOfHandler, GrammarSupplier, Serializable
    public class SpreadSheetFormula
extends AbstractSymbolEvaluator<Object>
    Evaluates mathematical expressions.

    The following grammar is used:

    expr_list ::= '=' expr_list expr_part | expr_part ;
    expr_part ::= expr ;

    expr ::= ( expr )

    # data types
    | number
    | string
    | boolean
    | date
    | cell

    # constants
    | true
    | false
    | pi
    | e
    | now()
    | today()

    # negating numeric value
    | -expr

    # comparisons
    | expr < expr
    | expr <= expr
    | expr > expr
    | expr >= expr
    | expr = expr
    | expr != expr (or: expr <> expr)

    # boolean operations
    | ! expr (or: not expr)
    | expr & expr (or: expr and expr)
    | expr | expr (or: expr or expr)
    | if[else] ( expr , expr (if true) , expr (if false) )

    # arithmetics
    | expr + expr
    | expr - expr
    | expr * expr
    | expr / expr
    | expr ^ expr (power of)
    | expr % expr (modulo)
    ;

    # numeric functions
    | abs ( expr | cell )
    | sqrt ( expr | cell )
    | log ( expr | cell )
    | exp ( expr | cell )
    | sin ( expr | cell )
    | cos ( expr | cell )
    | tan ( expr | cell )
    | rint ( expr | cell )
    | floor ( expr | cell )
    | pow[er] ( expr | cell , expr | cell )
    | ceil ( expr | cell )
    | sum ( cell1 : cell2 )
    | min ( cell1 : cell2 )
    | max ( cell1 : cell2 )
    | average ( cell1 : cell2 )
    | stdev ( cell1 : cell2 )
    | stdevp ( cell1 : cell2 )
    | countif ( cell1 : cell2 ; expr )
    | sumif ( cell1 : cell2 ; expr )
    | sumif ( cell1 : cell2 ; expr : sumCell1 : sumCell2 )
    | intercept ( cellY1 : cellY2 ; cellX1 : cellX2 )
    | slope ( cellY1 : cellY2 ; cellX1 : cellX2 )
    | countblank ( cell1 : cell2 )
    | year ( expr | cell )
    | month ( expr | cell )
    | day ( expr | cell )
    | hour ( expr | cell )
    | minute ( expr | cell )
    | second ( expr | cell )
    | weekday ( expr | cell )
    | weeknum ( expr | cell )

    # string functions
    | substr ( expr , start [, end] )
    | left ( expr , len )
    | mid ( expr , start , len )
    | right ( expr , len )
    | rept ( expr , count )
    | concatenate ( expr1 , expr2 [, expr3-5] )
    | lower[case] ( expr )
    | upper[case] ( expr )
    | trim ( expr )
    | matches ( expr , regexp )
    | contains ( str , find ) (checks whether 'str' string contains 'find' string)
    | startswith ( str , find ) (checks whether 'str' string starts with 'find' string)
    | endswith ( str , find ) (checks whether 'str' string ends with 'find' string)
    | trim ( expr )
    | len[gth] ( str )
    | find ( search , expr [, pos] )
    | replace ( str , pos , len , newstr )
    | substitute ( str , find , replace [, occurrences] )
    ;

    # obtaining native cell content
    | cellobj ( cell )

    # obtaining cell content as string
    | cellstr ( cell )

    Notes:
    - Cells are denoted by column in letter and row in digit, e.g., 'C12'.
    - 'start' and 'end' for function 'substr' are indices that start at 1.
    - Index 'end' for function 'substr' is excluded (like Java's 'String.substring(int,int)' method)
    - Line comments start with '#'.
    - Semi-colons (';') or commas (',') can be used as separator in the formulas,
    e.g., 'pow(2,2)' is equivalent to 'pow(2;2)'
    - dates have to be of format 'yyyy-MM-dd' or 'yyyy-MM-dd HH:mm:ss'
    - times have to be of format 'HH:mm:ss' or 'yyyy-MM-dd HH:mm:ss'
    - the characters in square brackets in function names are optional:
    e.g. 'len("abc")' is the same as 'length("abc")'

    A lot of the functions have been modeled after LibreOffice:
    https://help.libreoffice.org/Calc/Functions_by_Category

    Additional functions:
    - env(String): String
       First argument is the name of the environment variable to retrieve.
       The result is the value of the environment variable.

    Additional procedures:
    - println(...)
       One or more arguments are printed as comma-separated list to stdout.
       If no argument is provided, a simple line feed is output.


    Code example 1: 
     String expr = "pow(BASE,EXPONENT)*MULT";
 HashMap symbols = new HashMap();
 symbols.put("BASE", 2.0);
 symbols.put("EXPONENT", 9.0);
 symbols.put("MULT", 0.1);
 double result = SpreadSheetFormula.evaluate(expr, symbols);
 System.out.println(expr + " and " + symbols + " = " + result);
    Code Example 2 (uses the "ifelse" construct): 
     String expr = "ifelse(I<0,pow(BASE,I*0.5),pow(BASE,I))";
 SpreadSheetFormula.TreeNode tree = SpreadSheetFormula.parse(expr);
 HashMap symbols = new HashMap();
 symbols.put("BASE", 2.0);
 for (int i = -10; i <= 10; i++) {
   symbols.put("I", (double) i);
   double result = SpreadSheetFormula.evaluate(expr, symbols);
   System.out.println(expr + " and " + symbols + " = " + result);
 }
 
    -logging-level <OFF|SEVERE|WARNING|INFO|CONFIG|FINE|FINER|FINEST> (property: loggingLevel)
    The logging level for outputting errors and debugging output.
    default: WARNING
    min-user-mode: Expert
 
    -env <java.lang.String> (property: environment)
    The class to use for determining the environment.
    default: adams.env.Environment
 
    -expression <java.lang.String> (property: expression)
    The spreadsheet formula to evaluate (must evaluate to a double).
    default: = 42
 
    -symbol <adams.core.base.BaseString> [-symbol ...] (property: symbols)
    The symbols to initialize the parser with, key-value pairs: name=value.
    default:
 
    -reader <adams.data.io.input.SpreadSheetReader> (property: reader)
    The spreadsheet reader for loading the spreadsheet to work on.
    default: adams.data.io.input.CsvSpreadSheetReader -data-row-type adams.data.spreadsheet.DenseDataRow -spreadsheet-type adams.data.spreadsheet.DefaultSpreadSheet
 
    -input <adams.core.io.PlaceholderFile> (property: input)
    The input file to load with the specified reader; ignored if pointing to 
    directory.
    default: ${CWD}
    Author:
    FracPete (fracpete at waikato dot ac dot nz)
    See Also:
    Serialized Form

    • Field Detail

      • m_Sheet

        protected SpreadSheet m_Sheet
        the spreadsheet to use.

      • m_Reader

        protected SpreadSheetReader m_Reader
        the spreadsheet reader for loading the spreadsheet.

    • Constructor Detail

      • SpreadSheetFormula

        public SpreadSheetFormula()

    • Method Detail

      • getGrammar

        public String getGrammar()
        Returns a string representation of the grammar.
        Returns:
        the grammar, null if not available

      • setReader

        public void setReader​(SpreadSheetReader value)
        Sets the spreadsheet reader that loads the sheet.
        Parameters:
        value - the reader

      • getReader

        public SpreadSheetReader getReader()
        Returns the spreadsheet reader that loads the sheet.
        Returns:
        the reader

      • readerTipText

        public String readerTipText()
        Returns the tip text for this property.
        Returns:
        tip text for this property suitable for displaying in the GUI or for listing the options.

      • setInput

        public void setInput​(PlaceholderFile value)
        Sets the spreadsheet file to load, ignored if pointing to directory.
        Parameters:
        value - the input file

      • getInput

        public PlaceholderFile getInput()
        Returns the spreadsheet file to load, ignored if pointing to directory.
        Returns:
        the input file

      • inputTipText

        public String inputTipText()
        Returns the tip text for this property.
        Returns:
        tip text for this property suitable for displaying in the GUI or for listing the options.

      • setSheet

        public void setSheet​(SpreadSheet value)
        Sets the underlying spreadsheet.
        Parameters:
        value - the spreadsheet

      • getSheet

        public SpreadSheet getSheet()
        Returns the underlying spreadsheet.
        Returns:
        the spreadsheet

      • initializeSymbol

        protected Object initializeSymbol​(String name,
                                  String value)
        Initializes the symbol.
        Specified by:
        initializeSymbol in class AbstractSymbolEvaluator<Object>
        Parameters:
        name - the name of the symbol
        value - the string representation of the symbol
        Returns:
        the object representation of the symbol

      • loadSheet

        protected void loadSheet()
        Loads the spreadsheet from disk, if possible.

      • evaluate

        public static Object evaluate​(String expr,
                              HashMap symbols,
                              SpreadSheet sheet)
                       throws Exception
        Parses and evaluates the given expression. Returns the result of the mathematical expression, based on the given values of the symbols.
        Parameters:
        expr - the expression to evaluate
        symbols - the symbol/value mapping
        Returns:
        the evaluated result
        Throws:
        Exception - if something goes wrong

      • main

        public static void main​(String[] args)
        Runs the evaluator from command-line.
        Parameters:
        args - the command-line options, use "-help" to list them