class |
BooleanExpression |
Evaluates boolean expressions.
It uses the following grammar:
expr_list ::= '=' expr_list expr_part | expr_part ;
expr_part ::= expr ;
expr ::= ( expr )
# data types
| number
| string
| boolean
| date
# 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) )
| ifmissing ( variable , expr (default value if variable is missing) )
| has ( variable )
| isNaN ( expr )
# arithmetics
| expr + expr
| expr - expr
| expr * expr
| expr / expr
| expr ^ expr (power of)
| expr % expr (modulo)
;
# numeric functions
| abs ( expr )
| sqrt ( expr )
| cbrt ( expr )
| log ( expr )
| log10 ( expr )
| exp ( expr )
| sin ( expr )
| sinh ( expr )
| cos ( expr )
| cosh ( expr )
| tan ( expr )
| tanh ( expr )
| atan ( expr )
| atan2 ( exprY , exprX )
| hypot ( exprX , exprY )
| signum ( expr )
| rint ( expr )
| floor ( expr )
| pow[er] ( expr , expr )
| ceil ( expr )
| min ( expr1 , expr2 )
| max ( expr1 , expr2 )
| rand () (unseeded double, 0-1)
| rand ( seed ) (seeded double, 0-1)
| randint ( bound ) (unseeded int from 0 to bound-1)
| randint ( seed, bound ) (seeded int from 0 to bound-1)
| year ( expr )
| month ( expr )
| day ( expr )
| hour ( expr )
| minute ( expr )
| second ( expr )
| weekday ( expr )
| weeknum ( expr )
# 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 )
| trim ( expr )
| len[gth] ( str )
| find ( search , expr [, pos] ) (find 'search' in 'expr', return 1-based position)
| 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)
| replace ( str , pos , len , newstr )
| replaceall ( str , regexp , replace ) (applies regular expression to 'str' and replaces all matches with 'replace')
| substitute ( str , find , replace [, occurrences] )
| str ( expr )
| str ( expr , numdecimals )
| str ( expr , decimalformat )
| ext ( file_str ) (extracts extension from file)
| replaceext ( file_str, ext_str ) (replaces the extension of the file with the new one)
# array functions
| len[gth] ( array )
| get ( array , index )
;
Notes:
- Variables are either all alphanumeric and _, starting with uppercase letter (e.g., "ABc_12"),
any character apart from "]" enclosed by "[" and "]" (e.g., "[Hello World]") or
enclosed by single quotes (e.g., "'Hello World'").
- 'start' and 'end' for function 'substr' are indices that start at 1.
- 'index' for function 'get' starts 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.
|
class |
LookUpUpdate |
Evaluates lookup update rules updating the spreadsheet.
The following grammar is used:
expr_list ::= expr_list expr_part | expr_part
expr_part ::= conditional | assignment
conditional ::= if expr then assignments end
| if expr then assignments else assignments end
assignments ::= assignments assignment | assignment
assignment ::=
VARIABLE := expr;
| all ( "regexp" ) := expr;
expr ::= ( expr )
| NUMBER
| STRING
| BOOLEAN
| VARIABLE
| true
| false
| -expr
| expr < expr
| expr <= expr
| expr > expr
| expr >= expr
| expr = expr
| expr != expr
| not expr
| expr and expr
| expr or expr
# 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 )
| len[gth] ( str )
| find ( search , expr [, pos] ) (find 'search' in 'expr', return 1-based position)
| contains ( str , find ) (checks whether 'str' string contains 'find' string)
| replace ( str , pos , len , newstr )
| replaceall ( str , regexp , replace ) (applies regular expression to 'str' and replaces all matches with 'replace')
| substitute ( str , find , replace [, occurrences] )
| str ( expr )
| str ( expr , numdecimals )
| str ( expr , decimalformat )
| expr + expr
| expr - expr
| expr * expr
| expr / expr
| expr % expr
| expr ^ expr
| abs ( expr )
| sqrt ( expr )
| cbrt ( expr )
| log ( expr )
| log10 ( expr )
| exp ( expr )
| sin ( expr )
| sinh ( expr )
| cos ( expr )
| cosh ( expr )
| tan ( expr )
| tanh ( expr )
| atan ( expr )
| atan2 ( exprY , exprX )
| hypot ( exprX , exprY )
| signum ( expr )
| rint ( expr )
| floor ( expr )
| pow[er] ( expr , expr )
| ceil ( expr )
| min ( expr1 , expr2 )
| max ( expr1 , expr2 )
| has ( variable )
Notes:
- Variables are either all alphanumeric and -/_ (e.g., "ABc_1-2"), any character
apart from "]" enclosed by "[" and "]" (e.g., "[Hello World]") or
enclosed by single quotes (e.g., "'Hello World'").
- The 'all' method applies the value to all the values in the lookup table
that match the regular expression.
- Positions are 1-based.
- 'str' uses java.text.DecimalFormat when supplying a format string
- Variables starting with '_' (inside the [] or '') are considered local and don't get transferred back out.
- The 'has' function checks whether a variable/symbol is present.
|
class |
MathematicalExpression |
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
# 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) )
| ifmissing ( variable , expr (default value if variable is missing) )
| has ( variable )
| isNaN ( expr )
# arithmetics
| expr + expr
| expr - expr
| expr * expr
| expr / expr
| expr ^ expr (power of)
| expr % expr (modulo)
;
# numeric functions
| abs ( expr )
| sqrt ( expr )
| cbrt ( expr )
| log ( expr )
| log10 ( expr )
| exp ( expr )
| sin ( expr )
| sinh ( expr )
| cos ( expr )
| cosh ( expr )
| tan ( expr )
| tanh ( expr )
| atan ( expr )
| atan2 ( exprY , exprX )
| hypot ( exprX , exprY )
| signum ( expr )
| rint ( expr )
| floor ( expr )
| pow[er] ( expr , expr )
| ceil ( expr )
| min ( expr1 , expr2 )
| max ( expr1 , expr2 )
| rand () (unseeded double, 0-1)
| rand ( seed ) (seeded double, 0-1)
| randint ( bound ) (unseeded int from 0 to bound-1)
| randint ( seed, bound ) (seeded int from 0 to bound-1)
| year ( expr )
| month ( expr )
| day ( expr )
| hour ( expr )
| minute ( expr )
| second ( expr )
| weekday ( expr )
| weeknum ( expr )
# 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 )
| trim ( expr )
| len[gth] ( str )
| find ( search , expr [, pos] ) (find 'search' in 'expr', return 1-based position)
| 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)
| replace ( str , pos , len , newstr )
| replaceall ( str , regexp , replace ) (applies regular expression to 'str' and replaces all matches with 'replace')
| substitute ( str , find , replace [, occurrences] )
| str ( expr )
| str ( expr , numdecimals )
| str ( expr , decimalformat )
| ext ( file_str ) (extracts extension from file)
| replaceext ( file_str, ext_str ) (replaces the extension of the file with the new one)
;
Notes:
- Variables are either all alphanumeric and _, starting with uppercase letter (e.g., "ABc_12"),
any character apart from "]" enclosed by "[" and "]" (e.g., "[Hello World]") or
enclosed by single quotes (e.g., "'Hello World'").
- '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.
|
class |
SpreadSheetFormula |
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.
|
class |
SpreadSheetQuery |
Evaluates spreadsheet subset queries.
The following grammar is used:
expr_list ::= expr_list expr_part | expr_part;
expr_part ::= select | update | delete;
select ::= SELECT col_list [limit]
| SELECT col_list WHERE cond_list [limit]
| SELECT col_list ORDER BY order_list [limit]
| SELECT col_list WHERE cond_list ORDER BY order_list [limit]
| SELECT agg_list
| SELECT agg_list GROUP BY col_list
| SELECT agg_list HAVING cond_list
| SELECT agg_list GROUP BY col_list HAVING cond_list
;
update ::= UPDATE SET upd_list
| UPDATE SET upd_list WHERE cond_list
;
delete ::= DELETE WHERE cond_list
;
col_list ::= col_list COMMA col
| col
| SELECT NUMBER [subsample: <1 = percent; >= 1 number of rows]
;
col ::= *
| COLUMN
| COLUMN AS COLUMN
;
upd_list ::= upd_list COMMA upd | upd;
upd ::= COLUMN = value
;
order_list::= order_list COMMA order | order;
order ::= COLUMN
| COLUMN ASC
| COLUMN DESC
;
cond_list ::= cond_list cond
| cond
;
cond ::= COLUMN < value
| COLUMN <= value
| COLUMN = value
| COLUMN <> value
| COLUMN >= value
| COLUMN > value
| COLUMN REGEXP STRING
| COLUMN IS NULL
| CELLTYPE ( COLUMN ) = "numeric|long|double|boolean|string|time|date|datetime|timestamp|object|missing"
| ( cond )
| cond:c1 AND cond:c2
| cond:c1 OR cond:c2
| NOT cond
;
value ::= NUMBER
| STRING
| PARSE ( "number" , STRING )
| PARSE ( "date" , STRING )
| PARSE ( "time" , STRING )
| PARSE ( "timestamp" , STRING )
;
limit ::= LIMIT NUMBER:max
| LIMIT NUMBER:offset , NUMBER:max
;
agg_list ::= agg_list COMMA agg
| agg
;
agg ::= COUNT [(*)|(COLUMN)] [AS COLUMN]
| MIN ( COLUMN ) [AS COLUMN]
| MAX ( COLUMN ) [AS COLUMN]
| RANGE ( COLUMN ) [AS COLUMN] (= MIN - MAX)
| MEAN ( COLUMN ) [AS COLUMN]
| AVERAGE ( COLUMN ) [AS COLUMN]
| STDEV ( COLUMN ) [AS COLUMN]
| STDEVP ( COLUMN ) [AS COLUMN]
| SUM ( COLUMN ) [AS COLUMN]
| IQR ( COLUMN ) [AS COLUMN]
| INTERQUARTILE ( COLUMN ) [AS COLUMN]
Notes:
- time format: 'HH:mm'
- date format: 'yyyy-MM-dd'
- timestamp format: 'yyyy-MM-dd HH:mm'
- STRING is referring to characters enclosed by double quotes
- COLUMN is either a string with no blanks (consisting of letters, numbers, hyphen or underscore; eg 'MyCol-1') or a bracket enclosed string when containing blanks (eg '[Some other col]')
- columns used in the ORDER BY clause must be present in the SELECT part; also, any alias given to them in SELECT must be used instead of original column name
|
class |
StringExpression |
Evaluates string expressions.
It uses the following grammar:
expr_list ::= '=' expr_list expr_part | expr_part ;
expr_part ::= expr ;
expr ::= ( expr )
# data types
| number
| string
| boolean
| date
# 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) )
| ifmissing ( variable , expr (default value if variable is missing) )
| has ( variable )
| isNaN ( expr )
# arithmetics
| expr + expr
| expr - expr
| expr * expr
| expr / expr
| expr ^ expr (power of)
| expr % expr (modulo)
;
# numeric functions
| abs ( expr )
| sqrt ( expr )
| cbrt ( expr )
| log ( expr )
| log10 ( expr )
| exp ( expr )
| sin ( expr )
| sinh ( expr )
| cos ( expr )
| cosh ( expr )
| tan ( expr )
| tanh ( expr )
| atan ( expr )
| atan2 ( exprY , exprX )
| hypot ( exprX , exprY )
| signum ( expr )
| rint ( expr )
| floor ( expr )
| pow[er] ( expr , expr )
| ceil ( expr )
| min ( expr1 , expr2 )
| max ( expr1 , expr2 )
| year ( expr )
| month ( expr )
| day ( expr )
| hour ( expr )
| minute ( expr )
| second ( expr )
| weekday ( expr )
| weeknum ( expr )
# 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 )
| trim ( expr )
| len[gth] ( str )
| find ( search , expr [, pos] ) (find 'search' in 'expr', return 1-based position)
| 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)
| replace ( str , pos , len , newstr )
| replaceall ( str , regexp , replace ) (applies regular expression to 'str' and replaces all matches with 'replace')
| substitute ( str , find , replace [, occurrences] )
| str ( expr )
| str ( expr , numdecimals )
| str ( expr , decimalformat )
| ext ( file_str ) (extracts extension from file)
| replaceext ( file_str, ext_str ) (replaces the extension of the file with the new one)
# array functions
| len[gth] ( array )
| get ( array , index )
;
Notes:
- Variables are either all alphanumeric and _, starting with uppercase letter (e.g., "ABc_12"),
any character apart from "]" enclosed by "[" and "]" (e.g., "[Hello World]") or
enclosed by single quotes (e.g., "'Hello World'").
- 'start' and 'end' for function 'substr' are indices that start at 1.
- 'index' for function 'get' starts 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.
|