The Caboteria / TWiki Web / SpreadSheetPlugin (12 Nov 2012, TWikiContributor)

TWiki Spreadsheet Plugin

This plugin adds spreadsheet capabilities to TWiki topics. Functions such as %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 function evaluation capability, not just classic spreadsheet functions. The plugin currently has 104 functions.

Example:

Region: Sales:
Northeast 320
Northwest 580
South 240
Europe 610
Asia 220
Total: 1970

      Interactive example:

Formula:
%CALCULATE{ }%
Result:  TWiki Guest
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 is not enabled.)

On this page:

Syntax Rules

This plugin handles the %CALC{...}% and %CALCULATE{...}% variables. Built-in functions found between the quotes are evaluated as follows:

Use CALC or CALCULATE

Table Use %CALC{...}% in table cells:
The CALC variable handles all functions, but it gets handled with delay compared to other TWikiVariables: It gets executed after internal variables and plugin variables that use the register tag handler. You may get unexpected results if you nest CALC inside other variables (such as %INCLUDE{%CALC{...}%}%) because it does not get evaluated inside-out & left-to-right like ordinary TWiki variables.

Index list Use %CALCULATE{...}% outside tables:
The CALCULATE variable is handled inside-out & left-to-right like ordinary TWiki variables, but it does not support functions that refer to table cells, such as $LEFT() or $T().

Built-in Spreadsheet Plugin Functions

The plugin currently has 104 functions. Convention for parameters:

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

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

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

HEXDECODE( hexcode ) -- convert hexadecimal code to string

HEXENCODE( text ) -- encode text into hexadecimal code

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

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

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

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 between two serialized dates

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

TRANSLATE( text, from, to ) -- translate text from one set of characters to another

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 between two serialized dates

XOR( list ) -- logical XOR of a list

FAQ

Can I use CALC in a formatted search?

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), %ICONURL{choice-yes}%, %ICONURL{choice-no}%) in the format="" parameter, write this:

%SEARCH{ .... format="| $topic | $percntCALC{$IF($EXACT($formfield(Tested), Yes), %ICONURL{choice-yes}%, %ICONURL{choice-no}%)}$percnt |" }%

How can I easily repeat a formula in a table?

To repeat the same formula in all cells of a table row define the formula once in a preferences setting and use that in the CALC. The preferences setting can be defined at the site level, web level or topic level, and may be hidden in HTML comments. Example:

<!--
   * Set MYFORMULA = $EVAL($SUBSTITUTE(...etc...))
-->
| A | 1 | %CALC{%MYFORMULA%}% |
| B | 2 | %CALC{%MYFORMULA%}% |
| C | 3 | %CALC{%MYFORMULA%}% |

CALC in Included Topics

By default, CALCs in an included topic are evaluated with delay. The SKIPINCLUDE setting tells the plugin to evaluate the CALCs once all INCLUDEs are processed. This default behavior is chosen so that it is possible to compose a bigger table from several includes and do some spreadsheet calculation over the whole table.

ALERT! Attention: You can get unexpected results if you INCLUDE a topic that has other variables taking action on CALCs. For example, a CHART in an included topic sees unprocessed CALCs, which may result in a chart with incorrect values. To get he desired result you need to set the following preference setting in the topic that includes the topic containing the CHART:

This setting tells the SpreadSheetPlugin to process the CALCs in the included page, e.g. it will not delay the evaluation of the functions.

Bug Tracking Example

Bug#: Priority: Subject: Status: Days to fix
Bug:1231 Low File Open ... Open 3
Bug:1232 High Memory Window ... Fixed 2
Bug:1233 Medium Usability issue ... Assigned 5
Bug:1234 High No arrange ... Fixed 1
Total: 4 High: 2
Low: 1
Medium: 1
. Assigned: 1
Fixed: 2
Open: 1
Total: 11

The last row is defined as:

| Total: %CALC{$ROW(-2)}% \ 
  | %CALC{$COUNTITEMS( R2:C$COLUMN()..R$ROW(-1):C$COLUMN() )}% | . \ 
  | %CALC{$COUNTITEMS( R2:C$COLUMN()..R$ROW(-1):C$COLUMN() )}% \ 
  |  Total: %CALC{$SUM( R2:C$COLUMN()..R$ROW(-1):C$COLUMN() )}% |

Above table is created manually. The table can be build dynamically with a formatted search, or by a plugin that pulls data from an external source, such as a bug tracking system.

Plugin Settings

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

Plugin Installation Instructions

Note: This plugin is pre-installed. TWiki administrators can upgrade it as needed on the TWiki server.

Plugin Info

Plugin Author: TWiki:Main.PeterThoeny
Copyright: © 2001-2012 Peter Thoeny, TWiki.org
© 2008-2012 TWiki:TWiki.TWikiContributor
License: GPL (GNU General Public License)
Plugin Version: 2012-11-11
Change History:  
2012-11-11: TWikibug:Item7020: Categorize TWiki Variables CALC and CALCULATE
2012-11-05: TWikibug:Item7023: Added $STDEV(), $STDEVP(), $VAR(), $VARP()
2012-11-03: TWikibug:Item7018: Added SpreadSheetPluginTestCases topic; refactor plugin to use function hash for better performance
2012-10-06: TWikibug:Item6960: Fixed form action of interactive formula evaluation feature -- TWiki:Main.HideyoImazu
2012-06-30: TWikibug:Item6898: Added $FILTER()
2012-06-29: TWikibug:Item6897: Added $ISDIGIT(), $ISLOWER(), $ISUPPER(), $ISWIKIWORD()
2012-04-04: TWikibug:Item6866: Added CALCULATE variable using register tag handler to support proper inside-out, left-to-right eval order like ordinary TWiki variables
2012-01-13: TWikibug:Item6804: Added $FLOOR() and $CEILING()
2011-09-07: TWikibug:Item6803: Fix for EVAL function bug with zeroes after decimal point
2011-07-09: TWikibug:Item6725: Change global package variables from "use vars" to "our"
2011-04-25: TWikibug:Item6690: Added $BITXOR(), $HEXDECODE(), $HEXENCODE(), $XOR()
2011-04-08: TWikibug:Item6681: Added $WHILE()
2011-04-06: TWikibug:Item6679: Fixed small issue with $LISTRAND(), where last item only got 50% of fair share to get picked
2011-03-25: TWikibug:Item6669: Added $LISTNONEMPTY()
2011-03-24: TWikibug:Item6668: Fixed $LIST() not flattening a list in a cell
TWikibug:Item6667: Fixed $RIGHT() having wrong result due to incorrect start cell
2011-03-22: TWikibug:Item6666: Added $SPLIT(); renamed $nop separator token of $LISTJOIN() to $empty (keeping $nop as undocumented feature)
2010-08-27: TWikibug:Item6526: Added ISO 8601 week number to $FORMATTIME(), contributed by TWiki:Main.PeterPayne
2010-08-04: TWikibug:Item6537: Fixed for $EVAL(2+08) returning "illegal octal digit" error instead of 10
2010-07-17: TWikibug:Item6525: Added $n token to TRANSLATE for newline
2010-05-27: TWikibug:Item6506: Document delayed evaluation of CALC in included topics
2010-05-26: TWikibug:Item6504: Added empty ($nop) separator to $LISTJOIN()
2010-06-25: TWikibug:Item6493: Fixed $PRODUCT(0,4) returning 4 instead of 0
TWikibug:Item5163: Fix for plugin causing table to misrender an empty "||" row
2010-05-22: TWikibug:Item6472: Added support for DOY in $TIME(), contributed by TWiki:Main/EmanueleCupido
2010-05-15: TWikibug:Item6433: Doc improvements; replacing TWIKIWEB with SYSTEMWEB
2010-02-27: Doc improvements
2009-11-22: Enhanced $NOP(): Added $quot replacement for quote character, changed $per replacement with $percnt, contributed by TWiki:Main/HorstEsser
09 May 2009: Fixed bug in $WORKINGDAYS(): Incorrect number of days if daylight savings time change happens between start date and end date
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
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
13 May 2006: Added $SETIFEMPTY(); fixes in documentation
17 Jun 2005: Added $NOEXEC(), $EXEC()
25 Mar 2005: Fixed evaluation bug when using SpeedyCGI accelerator; code refactor to load module only when needed, contributed by TWiki:Main/CrawfordCurrie
24 Oct 2004: Added $EXISTS(), contributed by TWiki:Main/RodrigoChandia; added $PERCENTILE()
18 Oct 2004: Added $LISTJOIN()
26 Sep 2004: Added $FORMAT(KB), $FORMAT(MB), contributed by TWiki:Main/ArthurClemens; added $FORMAT(KBMB), $EVEN(), $ODD()
17 Jul 2004: Added $WORKINGDAYS(), contributed by TWiki:Main/CrawfordCurrie
24 May 2004: Refactored documentation (no code changes)
03 Apr 2004: Added $ABS(), $LISTIF(); fixed $VALUE() to remove leading zeros; changed $FIND() and $SEARCH() to return 0 instead of empty string if no match
21 Mar 2004: Added $LISTITEM(); fixed call to unofficial function
16 Mar 2004: Added $LISTMAP(), $LISTREVERSE(), $LISTSIZE(), $LISTSORT(), $LISTUNIQUE(), $SETM(); retired $COUNTUNIQUE() in favor of $COUNTITEMS($LISTUNIQUE()); fixed evaluation order issue of $IF(); fixed missing eval error messages suppressed since version 06 Mar 2004; redirect stderr messages to warning
08 Mar 2004: Added $LIST()
06 Mar 2004: Added $AND(), $MOD(), $NOT(), $OR(), $PRODUCT(), $PROPER(), $PROPERSPACE(), $RAND(), $REPEAT(), $SIGN(), $VALUE(); added digits parameter to $ROUND(); renamed $MULT() to $PRODUCT(); $MULT() is deprecated and undocumented
27 Feb 2004: Added $COUNTUNIQUE()
24 Oct 2003: Added $SET(), $GET(), $MEDIAN(); added $SUMPRODUCT(), inspired by TWiki:Main/RobertWithrow; added $SUMDAYS(), contributed by TWiki:Main/SvenDowideit
21 Oct 2003: Added support for lists (1, 2, 3) and lists of table ranges (R1:C1..R1:C5, R3:C1..R3:C5) for all functions that accept a table range; added $TIMEADD(); in $TIMEDIFF() added week unit; in $FORMATTIME() changed $weekday to $wd and added $wday and $weekday
14 Oct 2003: Added $TIME(), $TODAY(), $FORMATTIME(), $FORMATGMTIME(), $TIMEDIFF()
13 Oct 2003: Added $MULT(), contributed by TWiki:Main/GerritJanBaarda
30 Jul 2003: Added $TRANSLATE()
19 Jul 2003: Added $FIND(), $NOP(), $REPLACE(), $SEARCH(), $SUBSTITUTE(), contributed by TWiki:Main/PaulineCheung
19 Apr 2003: Added $COUNTSTR(), $EXACT(), $IF(), $ROUND(), $TRIM(); added $FORMAT(), contributed by TWiki:Main/JimStraus; support % modulus operator in $EVAL(), $INT(), and $ROUND(); fixed bug in $DEF()
07 Jun 2002: Added $DEF(), contributed by TWiki:Main/MartinFuzzey; allow values with HTML formatting like <u>102</u>, suggested by TWiki:Main/GladeDiviney; added SKIPINCLUDE setting
12 Mar 2002: Support for multiple functions per nesting level
15 Jan 2002: Added $CHAR(), $CODE() and $LENGTH()
12 Nov 2001: Added $RIGHT()
12 Aug 2001: Fixed bug of disappearing multi-column cells
19 Jul 2001: Fixed incorrect $SUM() calculation of cell with value 0
14 Jul 2001: Changed to plug & play
01 Jun 2001: Fixed insecure dependencies for $MIN() and $MAX()
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
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

Related Topics: SpreadSheetPluginTestCases, TWikiPreferences, TWikiPlugins, VarCALC, VarCALCULATE, VarIF

Edit | Attach | Print version | History: r26 < r25 < r24 < r23 < r22 | Backlinks | Raw View | Raw edit | More topic actions
Copyright © 1999-2024 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding The Caboteria? Send feedback
Note: Please contribute updates to this topic on TWiki.org at TWiki:TWiki.SpreadSheetPlugin.