TWiki Spreadsheet Plugin

This Plugin adds speadsheet 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.

Example:

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

      Interactive example:

Formula: %CALC{""}%  
Result:     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 not enabled.)

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.

Built-in Functions

Conventions for Syntax:

ABOVE( ) -- address range of cells above the current cell

ABS( num ) -- absolute value of a number

AND( list ) -- logcial AND of a list

AVERAGE( list ) -- average of a list or a range of cells

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

EVAL( formula ) -- evaluate a simple formula

EXACT( text1, text2 ) -- compare two text strings

FIND( string, text, start ) -- find one string within another string

FORMAT( type, prec, number ) -- format a number to a certain type and precision

FORMATTIME( serial, text ) -- convert a serialized date into a date string

FORMATGMTIME( serial, text ) -- convert a serialized date into a GMT date string

GET( name ) -- get the value of a previously set variable

IF( condition, value if true, value if 0 ) -- return a value based on a condition

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

LEFT( ) -- address range of cells to the left of the current cell

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

LISTMAP( formula, list ) -- evaluate and update each element of a list

LISTREVERSE( list ) -- opposite order of a list

LISTSIZE( list ) -- number of elements in a list

LISTSORT( list ) -- sort a list

LISTUNIQUE( list ) -- remove all duplicates from a list

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

NOP( text ) -- no-operation

NOT( num ) -- reverse logic of a number

OR( list ) -- logcial OR of a list

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

PROPER( text ) -- properly capitalize trxt

PROPERSPACE( text ) -- properly space out WikiWords

RAND( max ) -- random number

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

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

SETM( name, formula ) -- update an existing variable based on a formula

SIGN( num ) -- sign of a number

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

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

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

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

TRIM( text ) -- trim spaces from text

UPPER( text ) -- upper case string of a text

VALUE( text ) -- convert text to number

WORKINGDAYS( serial_1, serial_2 ) -- working days between two serialized dates

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

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

Plugin Installation Instructions

Note: You do not need to install anything on the browser to use this plugin. Below installation instructions are for the administrator who needs to install this plugin on the TWiki server.

Plugin Info

Plugin Author: TWiki:Main/PeterThoeny
Plugin Version: 17 Jul 2004
Change History:  
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
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

Related Topics: TWikiPreferences, TWikiPlugins

-- TWiki:Main/PeterThoeny - 17 Jul 2004