| 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. |
|
EVAL( formula ) -- evaluate a simple mathematical formula
- Addition, substraction, multiplication, division and modulus of numbers are supported. Any nesting is permitted
|
|
> > |
- Numbers may be decimal integers (
1234 ), binary integers (0b1110011 ), octal integers (01234 ), hexadecimal integers (0x1234 ) or of exponential notation (12.34e-56 )
|
| |
|
EXISTS( topic ) -- check if topic exists |
|
< < |
- Topic can be
TopicName or a Web.TopicName
|
> > |
- Topic can be
TopicName or a Web.TopicName . Current web is used if web is not specified.
|
|
- 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
- EXP is the inverse of the LN function
- Syntax:
$EXP( num )
- Example:
%CALC{"$EXP(1)"}% returns 2.71828182845905
- Related:
$LN() , $LOG()
|
|
FIND( string, text, start ) -- find one string within another string
- Finds one text
string , within another text , and returns the number of the starting position of string , from the first character of text . This search is case sensitive and is not a regular expression search; use $SEARCH() for regular expression searching. Starting position is 1; a 0 is returned if nothing is matched.
|
|
|
|
< < | FORMAT( type, prec, number ) -- format a number to a certain type and precision
- Type can be COMMA for comma format, DOLLAR for Dollar format, KB for Kilo Byte format, MB for Mega Byte format, KBMB for Kilo/Mega/Giga/Tera Byte auto-adjust format, NUMBER for number, and PERCENT for percent format
|
> > | FORMAT( type, precision, number ) -- format a number to a certain type and precision
- Supported
type :
-
COMMA for comma format, such as 12,345.68
-
DOLLAR for Dollar format, such as $12,345.68
-
KB for Kilo Byte format, such as 1205.63 KB
-
MB for Mega Byte format, such as 1.18 MB
-
KBMB for Kilo/Mega/Giga/Tera Byte auto-adjust format
-
NUMBER for number, such as 12345.7
-
PERCENT for percent format, such as 12.3%
- The
precision indicates the the number of digits after the dot
|
|
- Syntax:
$FORMAT( type, prec, number )
- Example:
%CALC{"$FORMAT(COMMA, 2, 12345.6789)"}% returns 12,345.68
- Example:
%CALC{"$FORMAT(DOLLAR, 2, 12345.67)"}% returns $12,345.68
|
|
- Example:
%CALC{"$FORMAT(KBMB, 2, 1234567890)"}% returns 1.15 GB
- Example:
%CALC{"$FORMAT(NUMBER, 1, 12345.67)"}% returns 12345.7
- Example:
%CALC{"$FORMAT(PERCENT, 1, 0.1234567)"}% returns 12.3%
|
|
< < | |
> > |
FORMATGMTIME( serial, text ) -- convert a serialized date into a GMT date string |
|
FORMATTIME( serial, text ) -- convert a serialized date into a date string
- The following variables in
text are expanded: $second (seconds, 00..59); $minute (minutes, 00..59); $hour (hours, 00..23); $day (day of month, 01..31); $month (month, 01..12); $mon (month in text format, Jan..Dec); $year (4 digit year, 1999); $ye (2 digit year, 99), $wd (day number of the week, 1 for Sunday, 2 for Monday, etc), $wday (day of the week, Sun..Sat), $weekday (day of the week, Sunday..Saturday), $yearday (day of the year, 1..365, or 1..366 in leap years). Date is assumed to be server time; add GMT to indicate Greenwich time zone.
- Syntax:
$FORMATTIME( serial, text )
- Example:
%CALC{"$FORMATTIME(0, $year/$month/$day GMT)"}% returns 1970/01/01 GMT
|
|
< < | |
> > | |
| |
|
< < |
FORMATGMTIME( serial, text ) -- convert a serialized date into a GMT date string
- The date string represents the time in Greenwich time zone. Same variable expansion as in
$FORMATTIME() .
- Syntax:
$FORMATGMTIME( serial, text )
- Example:
%CALC{"$FORMATGMTIME(1041379200, $day $mon $year)"}% returns 01 Jan 2003
- Related:
$FORMATTIME() , $TIME() , $TIMEADD() , $TIMEDIFF() , $TODAY()
|
> > |
FORMATTIMEDIFF( unit, precision, time ) -- convert elapsed time to a string
- Convert elapsed
time to a human readable format, such as: 12 hours and 3 minutes
- The input
unit can be second , minute , hour , day , month , year . Note: An approximation is used for month and year calculations.
- The
precision indicates the number of output units to use
- Syntax:
$FORMATTIMEDIFF( unit, precision, time )
- Example:
%CALC{"$FORMATTIMEDIFF(min, 1, 200)"}% returns 3 hours
- Example:
%CALC{"$FORMATTIMEDIFF(min, 2, 200)"}% returns 3 hours and 20 minutes
- Example:
%CALC{"$FORMATTIMEDIFF(min, 1, 1640)"}% returns 1 day
- Example:
%CALC{"$FORMATTIMEDIFF(min, 2, 1640)"}% returns 1 day and 3 hours
- Example:
%CALC{"$FORMATTIMEDIFF(min, 3, 1640)"}% returns 1 day, 3 hours and 20 minutes
- Related:
$FORMATTIME() , $TIME() , $TIMEADD() , $TIMEDIFF()
|
|
GET( name ) -- get the value of a previously set variable |
|
INT( formula ) -- evaluate formula and round down to nearest integer
- Addition, substraction, multiplication, division and modulus of numbers are supported. Any nesting is permitted
|
|
> > |
- Numbers may be decimal integers (
1234 ), binary integers (0b1110011 ), octal integers (01234 ), hexadecimal integers (0x1234 ) or of exponential notation (12.34e-56 )
- If you expect a single decimal integer value with leading zeros, use
$INT( $VALUE( number ) )
|
|
- Syntax:
$INT( formula )
- Example:
%CALC{"$INT(10 / 4)"}% returns 2
|
|
> > |
- Example:
%CALC{"$INT($VALUE(09))"}% returns 9
|
|
|
|
- Example:
%CALC{"$LISTUNIQUE(Apple, Orange, Apple, Kiwi)"}% returns Apple, Orange, Kiwi
- Related:
$COUNTITEMS() , $COUNTSTR() , $LIST() , $LISTIF() , $LISTITEM() , $LISTMAP() , $LISTREVERSE() , $LISTSIZE() , $LISTSORT() , $SUM()
|
|
> > |
LN( num ) -- natural logarithm of a number
- LN is the inverse of the EXP function
- Syntax:
$LN( num )
- Example:
%CALC{"$LN(10)"}% returns 2.30258509299405
- Related:
$EXP() , $LOG()
LOG( num, base ) -- logarithm of a number to a given base
- base-10 logarithm of a number (if base is 0 or not specified), else logarithm of a number to the given base
- Syntax:
$LOG( num, base )
- Example:
%CALC{"$LOG(1000)"}% returns 3
- Example:
%CALC{"$LOG(16, 2)"}% returns 4
- Related:
$EXP() , $LN()
|
|
LOWER( text ) -- lower case string of a text |
| |
|
> > |
PI( ) -- mathematical constant Pi, 3.14159265358979
- Syntax:
$PI( )
- Example:
%CALC{"$PI()"}% returns 3.14159265358979
|
|
PRODUCT( list ) -- product of a list or range of cells |
| PROPER( text ) -- properly capitalize text
- Capitalize letters that follow any character other than a letter; convert all other letters to lowercase letters
- Syntax:
$PROPER( text )
|
|
< < |
- Example:
%CALC{"PROPER(a small STEP)"}% returns A Small Step
- Example:
%CALC{"PROPER(f1 (formula-1))"}% returns F1 (Formula-1)
|
> > |
- Example:
%CALC{"$PROPER(a small STEP)"}% returns A Small Step
- Example:
%CALC{"$PROPER(f1 (formula-1))"}% returns F1 (Formula-1)
|
|
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()
|
|
SEARCH( string, text, start ) -- search a string within a text |
|
< < |
- Finds one text
string , within another text , and returns the number of the starting position of string , from the first character of text . This search is a RegularExpression search; use $FIND() for non-regular expression searching. Starting position is 1; a 0 is returned if nothing is matched
|
> > |
- Finds one text
string , within another text , and returns the number of the starting position of string , from the first character of text . This search is a RegularExpression search; use $FIND() for non-regular expression searching. Starting position is 1; a 0 is returned if nothing is matched
|
|
- Syntax:
$SEARCH( string, text, start )
- Example:
%CALC{"$SEARCH([uy], fluffy)"}% returns 3
- Example:
%CALC{"$SEARCH([uy], fluffy, 3)"}% returns 6
|
| |
|
> > |
SQRT( num ) -- square root of a number
- Syntax:
$SQRT( num )
- Example:
%CALC{"$SQRT(16)"}% returns 4
|
|
SUBSTITUTE( text, old, new, instance, option ) -- substitute text |
|
< < |
- Substitutes
new text for old text in a text string. instance specifies which occurance of old you want to replace. If you specify instance , only that instance is replaced. Otherwise, every occurance is changed to the new text. A literal search is performed by default; a RegularExpression search if the option is set to r
|
> > |
- Substitutes
new text for old text in a text string. instance specifies which occurance of old you want to replace. If you specify instance , only that instance is replaced. Otherwise, every occurance is changed to the new text. A literal search is performed by default; a RegularExpression search if the option is set to r
|
|
- Syntax:
$SUBSTITUTE( text, old, new, instance, option )
- Example:
%CALC{"$SUBSTITUTE(Good morning, morning, day)"}% returns Good day
- Example:
%CALC{"$SUBSTITUTE(Q2-2002,2,3)"}% returns Q3-3003
|
|
- Serialized date is seconds since the Epoch, e.g. midnight, 01 Jan 1970. Current time is taken if the date string is empty. Supported date formats:
31 Dec 2009 ; 31 Dec 2009 GMT ; 31 Dec 09 ; 31-Dec-2009 ; 31/Dec/2009 ; 2009/12/31 ; 2009-12-31 ; 2009/12/31 ; 2009/12/31 23:59 ; 2009/12/31 - 23:59 ; 2009-12-31-23-59 ; 2009/12/31 - 23:59:59 ; 2009.12.31.23.59.59 . Date is assumed to be server time; add GMT to indicate Greenwich time zone
- Syntax:
$TIME( text )
- Example:
%CALC{"$TIME(2003/10/14 GMT)"}% returns 1066089600
|
|
< < | |
> > | |
|
TIMEADD( serial, value, unit ) -- add a value to a serialized date |
|
< < |
- The unit is seconds if not specified; unit can be
second , minute , hour , day , week , month , year . Note: An approximation is used for month and year calculations
|
> > |
- The
unit is seconds if not specified; unit can be second , minute , hour , day , week , month , year . Note: An approximation is used for month and year calculations
|
|
TIMEDIFF( serial_1, serial_2, unit ) -- time difference between two serialized dates |
|
< < |
- The unit is seconds if not specified; unit can be specified as in
$TIMEADD() . Note: An approximation is used for month and year calculations. Use $FORMAT() or $INT() to format real numbers
|
> > |
- The
unit is seconds if not specified; unit can be specified as in $TIMEADD() . Note: An approximation is used for month and year calculations. Use $FORMAT() , $FORMATTIMEDIFF() or $INT() to format real numbers
|
|
- Syntax:
$TIMEDIFF( serial_1, serial_2, unit )
- Example:
%CALC{"$TIMEDIFF($TIME(), $EVAL($TIME()+90), minute)"}% returns 1.5
|
|
< < | |
> > | |
|
TODAY( ) -- serialized date of today at midnight GMT |
|
Can I use CALC in a formatted search? |
|
< < | Specifically, how can I output some conditional text in a FormattedSearch? |
> > | Specifically, how can I output some conditional text in a FormattedSearch? |
| |
|
< < | You need to escape the CALC so that it executes once per search hit. This can be done by escaping the % signs of %CALC{...}% with $percnt . For example, to execute $IF($EXACT($formfield(Tested), Yes), %PUBURL%/%TWIKIWEB%/TWikiDocGraphics/choice-yes.gif, %PUBURL%/%TWIKIWEB%/TWikiDocGraphics/choice-no.gif) in the format="" parameter, write this: |
> > | You need to escape the CALC so that it executes once per search hit. This can be done by escaping the % signs of %CALC{...}% with $percnt . For example, to execute $IF($EXACT($formfield(Tested), Yes), %PUBURL%/%SYSTEMWEB%/TWikiDocGraphics/choice-yes.gif, %PUBURL%/%SYSTEMWEB%/TWikiDocGraphics/choice-no.gif) in the format="" parameter, write this: |
| |
|
< < | %SEARCH{ .... format="| $topic | $percntCALC{$IF($EXACT($formfield(Tested), Yes), %PUBURL%/%TWIKIWEB%/TWikiDocGraphics/choice-yes.gif, %PUBURL%/%TWIKIWEB%/TWikiDocGraphics/choice-no.gif)}$percnt |" }% |
> > | %SEARCH{ .... format="| $topic | $percntCALC{$IF($EXACT($formfield(Tested), Yes), %PUBURL%/%SYSTEMWEB%/TWikiDocGraphics/choice-yes.gif, %PUBURL%/%SYSTEMWEB%/TWikiDocGraphics/choice-no.gif)}$percnt |" }% |
|
How can I easily repeat a formula in a table? |
| Plugin settings are stored as preferences variables. To reference
a plugin setting write %<plugin>_<setting>% , i.e. %SPREADSHEETPLUGIN_SHORTDESCRIPTION% |
|
< < | |
> > | |
|
-
- Set SHORTDESCRIPTION = Add spreadsheet calculation like
"$SUM( $ABOVE() )" to TWiki tables and other topic text
- Debug plugin: (See output in
data/debug.txt )
|
|
|
|
< < |
- WikiWords to exclude from being spaced out by the
$PROPERSPACE(text) function. This comma delimited list can be overloaded by a DONTSPACE preferences variable:
|
> > |
- WikiWords to exclude from being spaced out by the
$PROPERSPACE(text) function. This comma delimited list can be overloaded by a DONTSPACE preferences variable:
|
|
|
| Plugin Info
|
|
< < |
|
> > |
Copyright: |
© 2001-2007, Peter Thoeny, TWIKI.NET |
|
|
|
|
< < |
Plugin Version: |
23 Jan 2007 (r12607) |
|
> > |
Plugin Version: |
13 Oct 2007 (15270) |
|
|
Change History: |
<-- specify latest version first --> |
|
|
> > |
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 |
14 Apr 2007: |
Fixing bug in $EXISTS() that required full web.topic instead of just topic |
11 Mar 2007: |
Fixing bug in $VALUE() and $INT(), introduced by version 09 Mar 2007 |
09 Mar 2007: |
Added $EXP(), $LN(), $LOG(), $PI(), $SQRT(); fixed $ROUND() bug, contributed by TWiki:Main/SergejZnamenskij |
|
|
23 Jan 2007: |
Enhanced documentation |
18 Dec 2006: |
Added $LISTRAND(), $LISTSHUFFLE(), $LISTTRUNCATE(); fixed spurious newline at end of topic, contributed by TWiki:Main/MichaelDaum |
10 Oct 2006: |
Enhanced documentation |
|
|
16 Apr 2001: |
Fixed div by 0 bug in $AVERAGE() |
17 Mar 2001: |
Initial version with $ABOVE(), $AVERAGE(), $COLUMN(), $COUNTITEMS(), $EVAL(), $INT(), $LEFT(), $LOWER(), $MAX(), $MIN(), $ROW(), $SUM(), $T(), $UPPER() |
CPAN Dependencies: |
none |
|
|
< < |
|
> > |
|
|
|
|
< < | Related Topics: TWikiPreferences, TWikiPlugins |
> > | Related Topics: TWikiPreferences, TWikiPlugins, VarCALC |
| |
|
< < | -- TWiki:Main/PeterThoeny - 23 Jan 2007 |
> > | -- TWiki:Main/PeterThoeny - 13 Oct 2007 |
| |