Difference: SpreadSheetPlugin (15 vs. 16)

Revision 162007-10-13 - 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)
 
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
 

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

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

Line: 309 to 341
 
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
 
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)
 

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

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
 

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

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:
<
<
>
>
 
    • 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:
 
Line: 658 to 715
 

Plugin Info

Plugin Author: TWiki:Main/PeterThoeny
Changed:
<
<
Copyright: © 2001-2007, Peter@Thoeny.org, StructuredWikis LLC
>
>
Copyright: © 2001-2007, Peter Thoeny, TWIKI.NET
 
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
 
 
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