|
>
> | <--
Contributions to this plugin are appreciated. Please update the plugin page
at http://twiki.org/cgi-bin/view/Plugins/SpreadSheetPlugin or provide feedback
at http://twiki.org/cgi-bin/view/Plugins/SpreadSheetPluginDev.
If you are a TWiki contributor please update the plugin in the SVN repository.
--> |
| | TWiki Spreadsheet Plugin |
|
<
< | This Plugin adds spreadsheet 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. |
>
> | This plugin adds spreadsheet 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. |
| |
|
| | |
|
<
< | The formula next to "Total" is %CALC{"$SUM( $ABOVE() )"}%.
(you see the formula instead of the sum in case the Plugin is not installed or not enabled.) |
>
> | The formula next to "Total" is %CALC{"$SUM( $ABOVE() )"}%.
(you see the formula instead of the sum in case the plugin is not installed or not enabled.) |
| |
On this page:
- Syntax Rules
- Built-in Functions
- ABOVE( ) -- address range of cells above the current cell
- ABS( num ) -- absolute value of a number
- AND( list ) -- logical AND of a list
- AVERAGE( list ) -- average of a list or a range of cells
- CHAR( number ) -- ASCII character represented by number
- CODE( text ) -- ASCII numeric value of character
- COLUMN( offset ) -- current column number
- COUNTITEMS( list ) -- count individual items in a list
- COUNTSTR( list, str ) -- count the number of cells in a list equal to a given string
- DEF( list ) -- find first non-empty list item or cell
- EMPTY( text ) -- test for empty text
- EVAL( formula ) -- evaluate a simple mathematical formula
- EVEN( num ) -- test for even number
- EXACT( text1, text2 ) -- compare two text strings
- EXEC( formula ) -- execute a spreadsheet formula
- EXISTS( topic ) -- check if topic exists
- EXP( num ) -- exponent (e) raised to the power of a number
- FIND( string, text, start ) -- find one string within another string
- FORMAT( type, precision, number ) -- format a number to a certain type and precision
- FORMATGMTIME( serial, text ) -- convert a serialized date into a GMT date string
- FORMATTIME( serial, text ) -- convert a serialized date into a date string
- FORMATTIMEDIFF( unit, precision, time ) -- convert elapsed time to a string
- GET( name ) -- get the value of a previously set variable
- IF( condition, value if true, value if 0 ) -- return a value based on a condition
- INSERTSTRING( text, start, new ) -- insert a string into a text string
- INT( formula ) -- evaluate formula and round down to nearest integer
- LEFT( ) -- address range of cells to the left of the current cell
- LEFTSTRING( text, num ) -- extract characters at the beginning of a text string
- LENGTH( text ) -- length of text in bytes
- LIST( range ) -- convert content of a cell range into a list
- LISTIF( condition, list ) -- remove elements from a list that do not meet a condition
- LISTITEM( index, list ) -- get one element of a list
- LISTJOIN( separator, list ) -- convert a list into a string
- LISTMAP( formula, list ) -- evaluate and update each element of a list
- LISTRAND( list ) -- get one random element of a list
- LISTREVERSE( list ) -- opposite order of a list
- LISTSIZE( list ) -- number of elements in a list
- LISTSHUFFLE( list ) -- shuffle element of a list in random order
- LISTSORT( list ) -- sort a list
- LISTTRUNCATE( size, list ) -- truncate list to size
- LISTUNIQUE( list ) -- remove all duplicates from a list
- LN( num ) -- natural logarithm of a number
- LOG( num, base ) -- logarithm of a number to a given base
- LOWER( text ) -- lower case string of a text
- MAX( list ) - biggest value of a list or range of cells
- MEDIAN( list ) -- median of a list or range of cells
- MIN( list ) -- smallest value of a list or range of cells
- MOD( num, divisor ) -- reminder after dividing num by divisor
- NOEXEC( formula ) -- do not execute a spreadsheet formula
- NOP( text ) -- no-operation
- NOT( num ) -- reverse logic of a number
- ODD( num ) -- test for odd number
- OR( list ) -- logical OR of a list
- PERCENTILE( num, list ) -- percentile of a list or range of cells
- PI( ) -- mathematical constant Pi, 3.14159265358979
- PRODUCT( list ) -- product of a list or range of cells
- PROPER( text ) -- properly capitalize text
- PROPERSPACE( text ) -- properly space out WikiWords
- RAND( max ) -- random number
- REPEAT( text, num ) -- repeat text a number of times
- REPLACE( text, start, num, new ) -- replace part of a text string
- RIGHT( ) -- address range of cells to the right of the current cell
- RIGHTSTRING( text, num ) -- extract characters at the end of a text string
- ROUND( formula, digits ) -- round a number
- ROW( offset ) -- current row number
- SEARCH( string, text, start ) -- search a string within a text
- SET( name, value ) -- set a variable for later use
- SETIFEMPTY( name, value ) -- set a variable only if empty
- SETM( name, formula ) -- update an existing variable based on a formula
- SIGN( num ) -- sign of a number
- SQRT( num ) -- square root of a number
- SUBSTITUTE( text, old, new, instance, option ) -- substitute text
- SUBSTRING( text, start, num ) -- extract a substring out of a text string
- SUM( list ) -- sum of a list or range of cells
- SUMDAYS( list ) -- sum the days in a list or range of cells
- SUMPRODUCT( list, list ) -- scalar product on ranges of cells
- T( address ) -- content of a cell
- TRANSLATE( text, from, to ) -- translate text from one set of characters to another
- TIME( text ) -- convert a date string into a serialized date number
- TIMEADD( serial, value, unit ) -- add a value to a serialized date
- TIMEDIFF( serial_1, serial_2, unit ) -- time difference between two serialized dates
- TODAY( ) -- serialized date of today at midnight GMT
- TRIM( text ) -- trim spaces from text
- UPPER( text ) -- upper case string of a text
- VALUE( text ) -- convert text to number
- WORKINGDAYS( serial_1, serial_2 ) -- working days between two serialized dates
- FAQ
- Bug Tracking Example
- Plugin Settings
- Plugin Installation Instructions
- Plugin Info
Syntax Rules |
|
<
< | 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. |
>
> | 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) )"}%
|
| |
- Lists can refer to values and/or table cell ranges, e.g.
%CALC{"$SUM( 3, 5, $T(R1:C7), R1:C11..R1:C15 )"}%
- Formulae can only reference cells in the current or preceeding row of the current table; they may not reference cells below the current table row
- Formulae can also be placed outside of tables; they can reference cells in the preceeding table
|
|
>
> | |
| |
Built-in Functions |
| | |
|
>
> |
EMPTY( text ) -- test for empty text
- Returns
1 if text is empty, or 0 if not
- Syntax:
$EMPTY( text )
- Example:
%CALC{"$EMPTY(foo)"}% returns 0
- Example:
%CALC{"$EMPTY()"}% returns 1
- Example:
%CALC{"$EMPTY($TRIM( ))"}% returns 1
- Related:
$EXACT(), $IF(), $TRIM()
|
| |
EVAL( formula ) -- evaluate a simple mathematical formula
- Addition, substraction, multiplication, division and modulus of numbers are supported. Any nesting is permitted
|
| |
- Syntax:
$EXACT( text1, text2 )
- Example:
%CALC{"$EXACT(foo, Foo)"}% returns 0
- Example:
%CALC{"$EXACT(foo, $LOWER(Foo))"}% returns 1
|
|
<
< | |
>
> | |
| |
EXEC( formula ) -- execute a spreadsheet formula |
| |
- Syntax:
$EXISTS( topic )
- Example:
%CALC{"$EXISTS(WebHome)"}% returns 1
- Example:
%CALC{"$EXISTS(ThisDoesNotExist)"}% returns 0
|
|
>
> | |
| |
EXP( num ) -- exponent (e) raised to the power of a number |
| |
- Example:
%CALC{"$FIND(f, fluffy)"}% returns 1
- Example:
%CALC{"$FIND(f, fluffy, 2)"}% returns 4
- Example:
%CALC{"$FIND(@, fluffy, 1)"}% returns 0
|
|
<
< | |
>
> | |
| |
FORMAT( type, precision, number ) -- format a number to a certain type and precision |
| |
- Example:
%CALC{"$IF($T(R1:C5) > 1000, Over Budget, OK)"}% returns Over Budget if value in R1:C5 is over 1000, OK if not
- Example:
%CALC{"$IF($EXACT($T(R1:C2),), empty, $T(R1:C2))"}% returns the content of R1:C2 or empty if empty
- Example:
%CALC{"$SET(val, $IF($T(R1:C2) == 0, zero, $T(R1:C2)))"}% sets a variable conditionally
|
|
<
< | |
>
> |
INSERTSTRING( text, start, new ) -- insert a string into a text string
- Insert
new string into text string text to the right of start position. Position starts at 1; use a negative start to count from the end of the text
- Syntax:
$INSERTSTRING( text, start, new )
- Example:
%CALC{"$INSERTSTRING(abcdefg, 2, XYZ)"}% returns abXYZcdefg
- Example:
%CALC{"$INSERTSTRING(abcdefg, -2, XYZ)"}% returns abcdeXYZfg
- Related:
$FIND(), $LEFTSTRING(), $REPLACE(), $RIGHTSTRING(), $SEARCH(), $SUBSTITUTE(), $SUBSTRING(), $TRANSLATE()
|
| |
INT( formula ) -- evaluate formula and round down to nearest integer |
| |
- Example:
%CALC{"$SUM($LEFT())"}% returns the sum of cells to the left of the current cell
- Related:
$ABOVE(), $RIGHT()
|
|
>
> |
LEFTSTRING( text, num ) -- extract characters at the beginning of a text string |
| |
LENGTH( text ) -- length of text in bytes |
| |
- Example:
%CALC{"$LISTIF($item > 12, 14, 7, 25)"}% returns 14, 25
- Example:
%CALC{"$LISTIF($NOT($EXACT($item,)), A, B, , E)"}% returns non-empty elements A, B, E
- Example:
%CALC{"$LISTIF($index > 2, A, B, C, D)"}% returns C, D
|
|
<
< | |
>
> |
- Related:
$EMPTY(), $EXACT(), $IF(), $LIST(), $LISTITEM(), $LISTMAP(), $LISTREVERSE(), $LISTSIZE(), $LISTSORT(), $LISTUNIQUE(), $SUM()
|
| |
LISTITEM( index, list ) -- get one element of a list |
| |
NOP( text ) -- no-operation |
|
<
< |
- Useful to change the order of Plugin execution. For example, it allows preprossing to be done before
%SEARCH{}% is evaluated. The percent character '%' can be escaped with $per
|
>
> |
- Useful to change the order of plugin execution. For example, it allows preprossing to be done before
%SEARCH{}% is evaluated. The percent character '%' can be escaped with $per
|
| |
|
| |
- Returns 0 if
num is not zero, 1 if zero
- Syntax:
$NOT( num )
- Example:
%CALC{"$NOT(0)"}% returns 1
|
|
<
< | |
>
> | |
| |
ODD( num ) -- test for odd number |
| |
PROPERSPACE( text ) -- properly space out WikiWords |
|
<
< |
- Properly spaces out WikiWords preceeded by white space, parenthesis, or
][. Words listed in the DONTSPACE TWikiPreferences variable or DONTSPACE Plugins setting are excluded
|
>
> |
- Properly spaces out WikiWords preceeded by white space, parenthesis, or
][. Words listed in the DONTSPACE TWikiPreferences variable or DONTSPACE plugins setting are excluded
|
| |
- Syntax:
$PROPERSPACE( text )
- Example: Assuming DONTSPACE contains MacDonald:
%CALC{"$PROPERSPACE(Old MacDonald had a ServerFarm, EeEyeEeEyeOh)"}% returns Old MacDonald had a Server Farm, Ee Eye Ee Eye Oh
- Related:
$LOWER(), $PROPER(), $TRIM(), $UPPER()
|
| |
- Replace
num number of characters of text string text, starting at start, with new text new. Starting position is 1; use a negative start to count from the end of the text
- Syntax:
$REPLACE( text, start, num, new )
- Example:
%CALC{"$REPLACE(abcdefghijk,6,5,*)"}% returns abcde*k
|
|
<
< | |
>
> | |
| |
RIGHT( ) -- address range of cells to the right of the current cell |
| |
- Example:
%CALC{"$SUM($RIGHT())"}% returns the sum of cells to the right of the current cell
- Related:
$ABOVE(), $LEFT()
|
|
>
> |
RIGHTSTRING( text, num ) -- extract characters at the end of a text string |
| |
ROUND( formula, digits ) -- round a number
- Evaluates a simple
formula and rounds the result up or down to the number of digits if digits is positive; to the nearest integer if digits is missing; or to the left of the decimal point if digits is negative
|
| |
- Example:
%CALC{"$SEARCH([uy], fluffy)"}% returns 3
- Example:
%CALC{"$SEARCH([uy], fluffy, 3)"}% returns 6
- Example:
%CALC{"$SEARCH([abc], fluffy,)"}% returns 0
|
|
<
< | |
>
> | |
| |
SET( name, value ) -- set a variable for later use |
| |
- Example:
%CALC{"$SUBSTITUTE(Q2-2002,2,3)"}% returns Q3-3003
- Example:
%CALC{"$SUBSTITUTE(Q2-2002,2,3,3)"}% returns Q2-2003
- Example:
%CALC{"$SUBSTITUTE(abc123def,[0-9],9,,r)"}% returns abc999def
|
|
<
< | |
>
> |
SUBSTRING( text, start, num ) -- extract a substring out of a text string
- Extract
num number of characters of text string text, starting at start. Starting position is 1; use a negative start to count from the end of the text
- Syntax:
$SUBSTRING( text, start, num )
- Example:
%CALC{"$SUBSTRING(abcdefghijk, 3, 5)"}% returns cdefg
- Related:
$FIND(), $INSERTSTRING(), $LEFTSTRING(), $REPLACE(), $RIGHTSTRING(), $SEARCH(), $SUBSTITUTE(), $TRANSLATE()
|
| |
SUM( list ) -- sum of a list or range of cells |
| |
- Syntax:
$TRANSLATE( text, from, to )
- Example:
%CALC{"$TRANSLATE(boom,bm,cl)"}% returns cool
- Example:
%CALC{"$TRANSLATE(one, two,$comma,;)"}% returns one; two
|
|
<
< | |
>
> | |
| |
TIME( text ) -- convert a date string into a serialized date number |
| |
- Removes all spaces from text except for single spaces between words
- Syntax:
$TRIM( text )
- Example:
%CALC{"$TRIM( eat spaces )"}% returns eat spaces
|
|
<
< | |
>
> | |
| |
UPPER( text ) -- upper case string of a text |
| |
FAQ |
|
>
> | |
| | Can I use CALC in a formatted search?
Specifically, how can I output some conditional text in a FormattedSearch? |
| |
|
|
<
< | Above table is created manually. Another Plugin could build the table dynamically, e.g. by pulling data out of a bug tracking system. The Spreadsheet Plugin can be used to display table data statistics. |
>
> | Above table is created manually. Another plugin could build the table dynamically, e.g. by pulling data out of a bug tracking system. The Spreadsheet plugin can be used to display table data statistics. |
| |
Plugin Settings |
| |
data/TWiki/SpreadSheetPlugin.txt |
Plugin topic |
data/TWiki/SpreadSheetPlugin.txt,v |
Plugin topic repository |
lib/TWiki/Plugins/SpreadSheetPlugin.pm |
Plugin Perl module |
|
|
<
< |
- TWiki 4 and up: Visit
configure in your TWiki installation, and enable the Plugin in the {Plugins} section.
|
>
> |
- TWiki 4 and up: Visit
configure in your TWiki installation, and enable the plugin in the {Plugins} section.
|
| |
- Test if the "Total" in the first table in this topic is correct.
Plugin Info
|
|
<
< |
| Copyright: |
© 2001-2007, Peter Thoeny, TWIKI.NET |
|
>
> |
| Copyright: |
© 2001-2009, Peter Thoeny, TWIKI.NET |
|
| |
|
|
<
< |
| Plugin Version: |
13 Oct 2007 (15270) |
|
>
> |
| Plugin Version: |
26 Mar 2009 (17910) |
|
| |
| Change History: |
<-- specify latest version first --> |
|
|
>
> |
| 26 Mar 2009: |
Added $INSERTSTRING() |
| 25 Mar 2009: |
Added $EMPTY(), $LEFTSTRING(), $RIGHTSTRING(), $SUBSTRING() |
| 24 Mar 2009: |
Fixed bug in $REPLACE() if to-be-replaced string is "0"; fixed bug in $SUBSTITUTE() if replace string is empty; improved docs |
|
| |
| 13 Oct 2007: |
Added $FORMATTIMEDIFF() |
| 09 Sep 2007: |
Enhanced documentation for $EVAL() and $INT() |
| 02 Jun 2007: |
Added VarCALC to have %CALC{}% listed in TWikiVariables |
|
| |
Related Topics: TWikiPreferences, TWikiPlugins, VarCALC |
|
<
< | -- TWiki:Main/PeterThoeny - 13 Oct 2007 |
>
> | -- TWiki:Main/PeterThoeny - 26 Mar 2009 |
