Difference: SpreadSheetPlugin (15 vs. 16)

Revision 1612 Oct 2007 - PeterThoeny

Line: 1 to 1

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.

Line: 121 to 121

EVAL( formula ) -- evaluate a simple mathematical formula

Addition, substraction, multiplication, division and modulus of numbers are supported. Any nesting is permitted

Added:

>
>

Numbers may be decimal integers (1234), binary integers (0b1110011), octal integers (01234), hexadecimal integers (0x1234) or of exponential notation (12.34e-56)

Syntax: $EVAL( formula )
Example: %CALC{"$EVAL( (5 * 3) / 2 + 1.1 )"}% returns 8.6
Related: $EXEC(), $INT(), $MOD(), $ROUND(), $VALUE()

Line: 150 to 151

EXISTS( topic ) -- check if topic exists

Changed:

<
<

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

Added:

>
>

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.

Line: 165 to 173

Related: $REPLACE(), $SEARCH()

Changed:

<
<

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

Line: 176 to 192

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%

Changed:

<
<

Related: $ROUND()

>
>

Related: $FORMATTIME(), $FORMATTIMEDIFF(), $ROUND()

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(), $FORMATTIMEDIFF(), $TIME(), $TIMEADD(), $TIMEDIFF(), $TODAY()

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

Changed:

<
<

Related: $FORMATGMTIME(), $TIME(), $TIMEADD(), $TIMEDIFF(), $TODAY()

>
>

Related: $FORMATGMTIME(), $TIME(), $FORMATTIMEDIFF(), $TIMEADD(), $TIMEDIFF(), $TODAY()

Changed:

<
<

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

Line: 211 to 240

INT( formula ) -- evaluate formula and round down to nearest integer

Addition, substraction, multiplication, division and modulus of numbers are supported. Any nesting is permitted

Added:

>
>

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

Added:

>
>

Example: %CALC{"$INT($VALUE(09))"}% returns 9

Related: $EVAL(), $ROUND(), $VALUE()

Line: 309 to 341

Example: %CALC{"$LISTUNIQUE(Apple, Orange, Apple, Kiwi)"}% returns Apple, Orange, Kiwi
Related: $COUNTITEMS(), $COUNTSTR(), $LIST(), $LISTIF(), $LISTITEM(), $LISTMAP(), $LISTREVERSE(), $LISTSIZE(), $LISTSORT(), $SUM()

Added:

>
>

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

Syntax: $LOWER(text)

Line: 377 to 424

Example: %CALC{"$PERCENTILE(75, 400, 200, 500, 100, 300)"}% returns 450
Related: $LIST(), $MAX(), $MEDIAN(), $MIN()

Added:

>
>

PI( ) -- mathematical constant Pi, 3.14159265358979

Syntax: $PI( )
Example: %CALC{"$PI()"}% returns 3.14159265358979

PRODUCT( list ) -- product of a list or range of cells

Syntax: $PRODUCT( list )

Line: 387 to 439

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 )

Changed:

<
<

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)

Related: $LOWER(), $PROPERSPACE(), $TRIM(), $UPPER()

PROPERSPACE( text ) -- properly space out WikiWords

Changed:

<
<

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()

Line: 441 to 493

SEARCH( string, text, start ) -- search a string within a text

Changed:

<
<

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

Line: 476 to 528

Example: %CALC{"$SIGN(-12.5)"}% returns -1
Related: $ABS(), $EVAL(), $EVEN(), $INT(), $NOT(), $ODD()

Added:

>
>

SQRT( num ) -- square root of a number

Syntax: $SQRT( num )
Example: %CALC{"$SQRT(16)"}% returns 4

SUBSTITUTE( text, old, new, instance, option ) -- substitute text

Changed:

<
<

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

Line: 524 to 581

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

Changed:

<
<

Related: $FORMATTIME(), $FORMATGMTIME(), $TIMEADD(), $TIMEDIFF(), $TODAY(), $WORKINGDAYS()

>
>

Related: $FORMATGMTIME(), $FORMATTIME(), $FORMATTIMEDIFF(), $TIMEADD(), $TIMEDIFF(), $TODAY(), $WORKINGDAYS()

TIMEADD( serial, value, unit ) -- add a value to a serialized date

Changed:

<
<

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

Syntax: $TIMEADD( serial, value, unit )
Example: %CALC{"$TIMEADD($TIME(), 2, week)"}% returns the serialized date two weeks from now
Related: $FORMATTIME(), $FORMATGMTIME(), $TIME(), $TIMEDIFF(), $TODAY()

TIMEDIFF( serial_1, serial_2, unit ) -- time difference between two serialized dates

Changed:

<
<

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

Changed:

<
<

Related: $FORMAT(), $FORMATTIME(), $FORMATGMTIME(), $INT(), $TIME(), $TIMEADD(), $TODAY(), $WORKINGDAYS()

>
>

Related: $FORMAT(), $FORMATGMTIME(), $FORMATTIME(), $FORMATTIMEDIFF(), $INT(), $TIME(), $TIMEADD(), $TODAY(), $WORKINGDAYS()

TODAY( ) -- serialized date of today at midnight GMT

Line: 580 to 637

Can I use CALC in a formatted search?

Changed:

<
< Specifically, how can I output some conditional text in a FormattedSearch?

>
> Specifically, how can I output some conditional text in a FormattedSearch?

Changed:

<
< 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:

Changed:

<
< %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?

Line: 627 to 684

Plugin settings are stored as preferences variables. To reference a plugin setting write %<plugin>_<setting>%, i.e. %SPREADSHEETPLUGIN_SHORTDESCRIPTION%

Changed:

<
<

One line description, is shown in the TextFormattingRules topic:

>
>

One line description, is shown in the TextFormattingRules topic:

- Set SHORTDESCRIPTION = Add spreadsheet calculation like "$SUM( $ABOVE() )" to TWiki tables and other topic text

Debug plugin: (See output in data/debug.txt)

Line: 637 to 694

- Set SKIPINCLUDE = 1

Changed:

<
<

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:

- Set DONTSPACE = CodeWarrior? , MacDonald? , McIntosh? , RedHat? , SuSE?

Line: 658 to 715

Plugin Info

Plugin Author:

TWiki:Main/PeterThoeny

Changed:

<
<

>
>

License:

GPL (GNU General Public License)

Changed:

<
<

Plugin Version:

23 Jan 2007 (r12607)

>
>

Plugin Version:

13 Oct 2007 (15270)

Change History:

<-- specify latest version first -->

Added:

>
>

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

Line: 697 to 760

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

Changed:

<
<

TWiki:Plugins/Benchmark:

GoodStyle 99%, FormattedSearch 99%, SpreadSheetPlugin 95%

>
>

TWiki:Plugins/Benchmark:

GoodStyle 99%, FormattedSearch 99%, SpreadSheetPlugin 95%

Other Dependencies:	none
Perl Version:	5.000 and up
Plugin Home:	http://TWiki.org/cgi-bin/view/Plugins/SpreadSheetPlugin
Feedback:	http://TWiki.org/cgi-bin/view/Plugins/SpreadSheetPluginDev
Appraisal:	http://TWiki.org/cgi-bin/view/Plugins/SpreadSheetPluginAppraisal

Changed:

<
< Related Topics: TWikiPreferences, TWikiPlugins

>
> Related Topics: TWikiPreferences, TWikiPlugins, VarCALC

Changed:

<
< -- TWiki:Main/PeterThoeny - 23 Jan 2007

>
> -- TWiki:Main/PeterThoeny - 13 Oct 2007

View topic | History: r17 < r16 < r15 < r14 | More topic actions...

Copyright � 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