Difference: SpreadSheetPlugin (5 vs. 6)

Revision 621 Mar 2004 - Main.PeterThoeny

Line: 1 to 1
 

TWiki Spreadsheet Plugin

Changed:
<
<
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:
Line: 18 to 18
 

Syntax Rules

Changed:
<
<
  • 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
Line: 56 to 57
 
"$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 |
Added:
>
>
"$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
Line: 71 to 73
 
"$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)
Changed:
<
<
"$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
Line: 135 to 137
 
  • WikiWords to exclude from being spaced out by the $PROPERSPACE(text) function. This comma delimited list can be overloaded by a DONTSPACE preferences variable:
Changed:
<
<
    • Set DONTSPACE = CodeWarrior? , McIntosh? , RedHat? , SuSE?
>
>
    • Set DONTSPACE = CodeWarrior? , MacDonald? , McIntosh? , RedHat? , SuSE?
 
Line: 154 to 156
 

Plugin Info

Plugin Author: TWiki:Main/PeterThoeny
Changed:
<
<
Plugin Version: 16 Mar 2004
>
>
Plugin Version: 21 Mar 2004
 
Change History:
<-- specify latest version first -->
 
Changed:
<
<
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()
Line: 186 to 189
 Related Topics: TWikiPreferences, TWikiPlugins
Changed:
<
<
-- TWiki:Main/PeterThoeny - 16 Mar 2004
>
>
-- TWiki:Main/PeterThoeny - 21 Mar 2004
 
This site is powered by the TWiki collaboration platformCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback
Note: Please contribute updates to this topic on TWiki.org at TWiki:TWiki.SpreadSheetPlugin