| | TWiki Spreadsheet Plugin | |
<
< | This Plugin adds speadsheet capabilities to TWiki topics. Formulas like %CALC{"$INT(7/3)"}% are evaluated at page view time. They can be placed in table cells and outside of tables. | >
> | This Plugin adds speadsheet capabilities to TWiki topics. Formulae like %CALC{"$INT(7/3)"}% are evaluated at page view time. They can be placed in table cells and outside of tables. In other words, this Plugin provides general formula evaluation capability, not just classic spreadsheet functions. | | | Example: | | | Syntax Rules | |
<
< |
- The formula in the
%CALC{"formula"}% variable can contain built-in functions
- Built-in function are of format
$FUNCNAME(parameter)%
| >
> | The action of this Plugin is triggered by the %CALC{"..."}% variable, which gets rendered according to the built-in function(s) found between the quotes.
- Built-in function are of format
$FUNCNAME(parameter)
| | |
- Functions may be nested, e.g.
%CALC{"$SUM( R2:C$COLUMN(0)..R$ROW(-1):C$COLUMN(0) )"}%
- Functions are evaluated from left to right, and from inside to outside if nested
- The function parameter can be text; a mathematical formula; a cell address; or a range of cell addresses
| | |
"$LEFT()" | The address range of cells to the left of the current cell |
"$LENGTH(text)" | The length in bytes of text. Example: %CALC{"$LENGTH(abcd)"}% returns 4 |
"$LIST(range)" | Converts the content of a range of cells into a flat list, delimited by comma. Cells containing commas are merged into the list. Example: %CALC{"$LIST( $LEFT() )"}% returns Apples, Lemons, Oranges, Kiwis assuming the cells to the left contain | Apples | Lemons, Oranges | Kiwis | |
| |
>
> |
"$LISTITEM(index, list)" | Get one item of a list. Index is 1 to size of list; use a negative number to count from the end of the list. Examples:
%CALC{"$LISTITEM(2, Apple, Orange, Apple, Kiwi)"}% returns Orange
%CALC{"$LISTITEM(-1, Apple, Orange, Apple, Kiwi)"}% returns Kiwi |
| | |
"$LISTMAP(formula, list)" | Evaluate and update each element of a list. In the formla, $item indicates the element, $index the index of the list starting at 1. Example: %CALC{"$LISTMAP($index: $EVAL(2 * $item), 3, 5, 7, 11)"}% returns 1: 6, 2: 10, 3: 14, 4: 22 |
"$LISTREVERSE(list)" | The opposite order of a list. Example: %CALC{"$LISTREVERSE(Apple, Orange, Apple, Kiwi)"}% returns Kiwi, Apple, Orange, Apple |
"$LISTSIZE(list)" | The number of elements in a list. Example: %CALC{"$LISTSIZE(Apple, Orange, Apple, Kiwi)"}% returns 4 |
| | |
"$OR(list)" | The logcial OR of a list. Example: %CALC{"$OR(1, 0, 1)"}% returns 1 |
"$PRODUCT(list)" | The product of a list or range of cells. Example: to calculate the product of the cells to the left of the current one use %CALC{"$PRODUCT($LEFT())"}% |
"$PROPER(text)" | Capitalizes letters in text that follow any character other than a letter. Converts all other letters to lowercase letters. Examples:
%CALC{"PROPER(a small STEP)"}% returns A Small Step
%CALC{"PROPER(f1 (formula-1))"}% returns F1 (Formula 1) |
| |
<
< |
"$PROPERSPACE(text)" | Properly spaces out WikiWords preceeded by white space, parenthesis, or ][. Words listed in the DONTSPACE TWikiPreferences variable or DONTSPACE Plugins setting are excluded. Example, assuming DONTSPACE contains McIntosh: %CALC{"PROPERSPACE(McIntosh likes WikiWord links like WebHome and [[WebHome][WebHome]])"}% returns McIntosh likes Wiki Word links like Web Home and Web Home |
| >
> |
"$PROPERSPACE(text)" | Properly spaces out WikiWords preceeded by white space, parenthesis, or ][. Words listed in the DONTSPACE TWikiPreferences variable or DONTSPACE Plugins setting are excluded. Example, assuming DONTSPACE contains McIntosh: %CALC{"PROPERSPACE(Old MacDonald had a ServerFarm, EeEyeEeEyeOh"}% returns Old MacDonald had a Server Farm, Ee Eye Ee Eye Oh |
| | |
"$RAND(max)" | Random number, evenly distributed between 0 and max, or 0 and 1 if max is not specified. |
"$REPEAT(text)" | Repeat text a number of times. Example: %CALC{"$REPEAT(/\, 5)"}% returns /\/\/\/\/\ |
"$REPLACE(text, start_num, num_chars, new_text)" | Replaces part of text string text, based on the starting position start_num, and the number of characters to replace num_chars. The characters are replaced with new_text. Starting position is 1; use a negative start_num to count from the end of the text. See also $SUBSTITUTE(), $TRANSLATE(). Example:
%CALC{"$REPLACE(abcdefghijk,6,5,*)"}% returns abcde*k |
| | |
- WikiWords to exclude from being spaced out by the
$PROPERSPACE(text) function. This comma delimited list can be overloaded by a DONTSPACE preferences variable:
| |
<
< |
-
- Set DONTSPACE = CodeWarrior? , McIntosh? , RedHat? , SuSE?
| >
> |
-
- Set DONTSPACE = CodeWarrior? , MacDonald? , McIntosh? , RedHat? , SuSE?
| | | | | | Plugin Info
| |
<
< |
| Plugin Version: | 16 Mar 2004 |
| >
> |
| Plugin Version: | 21 Mar 2004 |
| | |
| Change History: | <-- specify latest version first --> |
| |
<
< |
| 16 Mar 2004: | Added $LISTMAP(), $LISTREVERSE(), $LISTSIZE(), $LISTSORT(), $LISTUNIQUE(), $SETM(); retired $COUNTUNIQUE() in favor of $COUNTITEMS($LISTUNIQUE(); fixed evaluation order issue of $IF(); fixed missing eval error messages suppressed since version 06 Mar 2004; redirect stderr messages to warning |
| >
> |
| 21 Mar 2004: | Added $LISTINDEX(); fixed call to inofficial function |
| 16 Mar 2004: | Added $LISTMAP(), $LISTREVERSE(), $LISTSIZE(), $LISTSORT(), $LISTUNIQUE(), $SETM(); retired $COUNTUNIQUE() in favor of $COUNTITEMS($LISTUNIQUE()); fixed evaluation order issue of $IF(); fixed missing eval error messages suppressed since version 06 Mar 2004; redirect stderr messages to warning |
| | |
| 08 Mar 2004: | Added $LIST() |
| 06 Mar 2004: | Added $AND(), $MOD(), $NOT(), $OR(), $PRODUCT(), $PROPER(), $PROPERSPACE(), $RAND(), $REPEAT(), $SIGN(), $VALUE(); added digits parameter to $ROUND(); renamed $MULT() to $PRODUCT(); $MULT() is deprecated and undocumented |
| 27 Feb 2004: | Added $COUNTUNIQUE() |
| | | Related Topics: TWikiPreferences, TWikiPlugins | |
<
< | -- TWiki:Main/PeterThoeny - 16 Mar 2004 | >
> | -- TWiki:Main/PeterThoeny - 21 Mar 2004 |
|