Niki SpreadSheet
Niki provides a limited spreadsheet functionality. The spreadsheet is interpreted if it is enabled in the NikiSettings. Furthermore error messages are shown if they are enabled in the NikiSettings.
Fields
Each field of a table is interpreted as an entry of a spreadsheet. The value of an entry is calculated if the first non-blank character is = or $variable= (see NikiVariables )
The fields are addresses by $A1, $A2, ... $B1, ... $U00, ...
A .. U are the columns
1 .. 100 .. are the rows
$A1 denotes the value of the first column of the first row.
The column Y addresses the current column ( $AY ).
The row X addresses the current row ( $X3 ).
Formulas
Numerical formulas like 5 + $A8 / ($B4 + 5.99) are accepted.
String can be concatenated by the . (dot) operator: $A4 . '$'
Pay attention that string constants have to by surrounded by ' (tick).
Functions
There are some PredefinedFunctions like
- the numerical abs, int, round, cos
- the sting manipulating lc, uc, ucfirst, substr, numeric
- date functions day, month, year, minute, hour, localtime, time
- simple decisions using = day() < 25 ? 'have money' : 'no money'
- numerical comparisons like <, >, >=, <=, ==, !=
- string comparisons like lt, gt, ge, lt, eq, ne and $A1 =~ m/a.*b/
- spreadsheet functions like SUM, MAX, MIN, COUNT, CELL, ROW, COLUMN
NikiVariables
Spreadsheets support NikiVariable in expressions like :$Variable:.
Furthermore a variable can be set using $Variable=...
This sets the $Variable and the cell to the value of the expression.
Error
If an error raises while the values of the spreadsheet are calculated the field get the value #error. This may be because of a syntax error in the field or illegal values or circular dependency of the fields. Some errors raise an error message in NikiMessages.
Example
-|| =CELL() || =COLUMN() || =COLUMN() || =COLUMN()
-|+ =ROW() || part || price || acceptable
-|| =ROW() || limit || 25.00 || ='if < ' . $C3
-|| =ROW() || mouse 1 || 15.00 || =$CY < $C3 ? 'yes' : 'no'
-|| =ROW() || mouse 2 || 20.00 || =$CY < $C3 ? 'yes' : 'no'
-|| =ROW() || mouse 4 || 25.00 || =$CY < $C3 ? 'yes' : 'no'
-|| =ROW() || mouse 5 || 30.00 || =$CY < $C3 ? 'yes' : 'no'
-|| =ROW() || mouse 7 || 35.00 || =$CY < $C3 ? 'yes' : 'no'
-|| =ROW() || || ||
-|+ =ROW() || result || ='min=' . MIN($C4:C8)
|| $the_result = COUNT ( 'yes', $D4:D8 ) . ' of ' .
COUNT ( '', $D4:D8 )
There are :$the_result: mice acceptable.
yields
=CELL() | =COLUMN() | =COLUMN() | =COLUMN() |
=ROW() | part | price | acceptable |
|---|
=ROW() | limit | 25.00 | ='if < ' . $C3 |
=ROW() | mouse 1 | 15.00 | =$CY < $C3 ? 'yes' : 'no' |
=ROW() | mouse 2 | 20.00 | =$CY < $C3 ? 'yes' : 'no' |
=ROW() | mouse 4 | 25.00 | =$CY < $C3 ? 'yes' : 'no' |
=ROW() | mouse 5 | 30.00 | =$CY < $C3 ? 'yes' : 'no' |
=ROW() | mouse 7 | 35.00 | =$CY < $C3 ? 'yes' : 'no' |
=ROW() |
=ROW() | result | ='min=' . MIN($C4:C8) | $the_result = COUNT ( 'yes', $D4:D8 ) . ' of ' . COUNT ( '', $D4:D8 ) |
|---|
There are ERROR('the_result') mice acceptable.
