|
>
> | <--
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
- Use CALC or CALCULATE
- Built-in Spreadsheet Plugin Functions
- ABOVE( ) -- address range of cells above the current cell
- ABS( num ) -- absolute value of a number
- ADDLIST( name, list ) -- append a list to a list variable
- AND( list ) -- logical AND of a list
- AVERAGE( list ) -- average of a list or a range of cells
- BIN2DEC( num ) -- convert a binary number to decimal
- BITXOR( text ) -- bit-wise XOR of text
- CEILING( num ) -- return the smallest integer following a number
- 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
- DEC2BIN( num, width ) -- convert a decimal number to binary
- DEC2HEX( num, width ) -- convert a decimal number to hexadecimal
- DEC2OCT( num, width ) -- convert a decimal number to octal
- 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
- FILTER( expression, text ) -- filter out characters from text
- FIND( string, text, start ) -- find one string within another string
- FLOOR( num ) -- return the largest integer preceding a number
- FORMAT( type, precision, number ) -- format a number
- FORMATGMTIME( serial, text ) -- convert a serialized date
- FORMATTIME( serial, text ) -- convert a serialized date
- FORMATTIMEDIFF( unit, precision, time, option ) -- convert elapsed time
- GET( name ) -- get the value of a variable
- GETHASH( name, key ) -- get the value of a previously set hash key
- GETLIST( name ) -- get the list from a list variable
- HASH2LIST( name, format ) -- convert a hash to a list
- HASHCOPY( from, to ) -- copy a hash
- HASHEACH( formula, name ) -- evaluate and update each hash element
- HASHEXISTS( name, key ) -- test if a hash exists
- HASHREVERSE( name ) -- reverse a hash
- HEX2DEC( num ) -- convert a hexadecimal number to decimal
- HEXDECODE( hexcode ) -- convert hexadecimal code to string
- HEXENCODE( text ) -- encode text into hexadecimal code
- IF( condition, then, else ) -- return a value based on a condition
- INSERTSTRING( text, start, new ) -- insert a string into a text string
- INT( formula ) -- evaluate formula and return integer truncated towards 0
- ISDIGIT( text ) -- test for digits
- ISLOWER( text ) -- test for lower case text
- ISUPPER( text ) -- test for upper case text
- ISWIKIWORD( text ) -- test for WikiWord
- LEFT( ) -- address range of cells to the left of the current cell
- LEFTSTRING( text, num ) -- extract characters at the beginning
- LENGTH( text ) -- length of text in bytes
- LIST( range ) -- convert content of a cell range into a list
- LIST2HASH( name, list ) -- create a hash from a list
- LISTEACH( formula, list ) -- evaluate and update each element of a list
- LISTIF( condition, list ) -- remove elements from a list
- LISTITEM( index, list ) -- get one element of a list
- LISTJOIN( separator, list ) -- convert a list into a string
- LISTNONEMPTY( list ) -- remove all empty elements from a list
- LISTRAND( list ) -- get one random element of a list
- LISTREVERSE( list ) -- opposite order of a list
- LISTSHUFFLE( list ) -- shuffle element of a list in random order
- LISTSIZE( list ) -- number of elements in a list
- 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
- OCT2DEC( num ) -- convert an octal number to decimal
- 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
- 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
- SETHASH( name, key, value ) -- set a hash value for later use
- SETIFEMPTY( name, value ) -- set a variable only if empty
- SETLIST( name, list ) -- save a list for later use
- SETM( name, formula ) -- modify an existing variable
- SETMHASH( name, key, formula ) -- modify an existing hash
- SIGN( num ) -- sign of a number
- SPLIT( separator, text ) -- split a string into a list
- SQRT( num ) -- square root of a number
- STDEV( list ) -- standard deviation based on a sample
- STDEVP( list ) -- standard deviation based on the entire population
- 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
- 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
- TODAY( ) -- serialized date of today at midnight GMT
- TRANSLATE( text, from, to ) -- translate text
- TRIM( text ) -- trim spaces from text
- UPPER( text ) -- upper case string of a text
- VALUE( text ) -- convert text to number
- VAR( list ) -- variance based on a sample
- VARP( list ) -- variance based on the entire population
- WHILE( condition, do ) -- do something while a condition is true
- WORKINGDAYS( serial_1, serial_2 ) -- working days
- XOR( list ) -- logical XOR of a list
- FAQ
- CALC in Included Topics
- 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 |
Copyright © 1999-2024 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.