Foswiki Spreadsheet Plugin

Add spreadsheet calculations like "$SUM($ABOVE())" to Foswiki tables and other topic text

This Plugin adds spreadsheet capabilities to Foswiki 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.


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

      Interactive example:

Formula: %CALC{""}%  
Result:     Wiki Admin

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

On this page:

Syntax Rules

The action of this Plugin is triggered two macros:

Unless there is a requirement to process table rows, %CALCULATE{"..."}% is the preferred form of the macro.

For both macros:

For the %CALC{"..."}% macro only:

Built-in Functions

Conventions for Syntax:

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

BIN2DEC( num ) -- convert a binary number to decimal

BITXOR( text ) or BITXOR( integer, list ) -- bit-wise XOR

This function has two modes of operation.

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

DEC2BIN( num, width ) -- convert a decimal number to binary

DEC2HEX( num, width ) -- convert a decimal number to hexadecimal

DEC2OCT( num, width ) -- convert a decimal number to octal

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

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

FILTER( expression, text ) -- filter out characters from text

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

HEX2DEC( num ) -- convert a hexadecimal number to decimal

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 round down to nearest integer

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

LISTEACH( formula, list ) -- evaluate and update each element of 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

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

LISTSHUFFLE( list ) -- shuffle element of a list in random order

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

OCT2DEC( num ) -- convert an octal number to decimal

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

RANDSTRING( set, format ) -- random string & password generator

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. num must be a positive number. Negative values of num are interpreted as zero. If num is larger than the length of the text the entire text is returned with no additional spaces.

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

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

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

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 integers in a list

Each integer is treated as true/false and then logically xor'd together. The acutal value of the integer other than 0 or non-zero doesn't matter. Non-integer items in the list are ignored.


Can I use CALCULATE in a formatted search?

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

You need to escape the CALCULATE macro so that it executes once per search hit. This can be done by escaping the % signs of %CALCULATE{...}% with $percnt. For example, to execute $IF($EXACT($formfield(Tested), Yes), %PUBURL%/%SYSTEMWEB%/DocumentGraphics/choice-yes.gif, %PUBURL%/%SYSTEMWEB%/DocumentGraphics/choice-no.gif) in the format="" parameter, write this:

%SEARCH{ .... format="| $topic | $percntCALCULATE{$IF($EXACT($formfield(Tested), Yes), %PUBURL%/%SYSTEMWEB%/DocumentGraphics/choice-yes.gif, %PUBURL%/%SYSTEMWEB%/DocumentGraphics/choice-no.gif)}$percnt |" }%

TIP CALC can also be used, but under some conditions the macro might not be expanded. For best results, use CALCULATE. CALC would only be recommended if the SEARCH was also outputting a Table.

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 hidden in HTML comments. Example:

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

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.


You can override some default settings in the plugin by setting the following preferences.
Preference Meaning Default
SPREADSHEETPLUGIN_DEBUG Debug plugin: (See output in Foswiki logs) 0
SPREADSHEETPLUGIN_SKIPINCLUDE Do not handle %CALC{}% variable in included topic while including topic 1
SPREADSHEETPLUGIN_DONTSPACE Comma-delimited list of WikiWords to exclude from being spaced out by the $PROPERSPACE(text) function. CodeWarrior, MacDonald, McIntosh, RedHat, SuSE
SPREADSHEETPLUGIN_TIMEISLOCAL Makes the TIME function assume input is local time and converts the entered time to GMT unless the date has 'GMT' appended. Note that this behavior creates problems for users using servers in time zones to the east of Greenwich. The setting is present for compatibility. 0
SPREADSHEETPLUGIN_ALLOWHTML Permits CALC and CALCULATE macros to emit unencoded < and >. If not enabled, < will be encoded as &lt; and > will be encoded as &gt; It is strongly recommended that this be left undefined or disabled, especially if input to the CALC / CALCULATE Macro includes any URLPARAM data. 0

Note that the DONTSPACE global preference overrides the SPREADSHEETPLUGIN_DONTSPACE preference for historical reasons.

Installation Instructions

You do not need to install anything in the browser to use this extension. The following instructions are for the administrator who installs the extension on the server.

Open configure, and open the "Extensions" section. "Extensions Operation and Maintenance" Tab -> "Install, Update or Remove extensions" Tab. Click the "Search for Extensions" button. Enter part of the extension name or description and press search. Select the desired extension(s) and click install. If an extension is already installed, it will not show up in the search results.

You can also install from the shell by running the extension installer as the web server user: (Be sure to run as the webserver user, not as root!)
cd /path/to/foswiki
perl tools/extension_installer <NameOfExtension> install

If you have any problems, or if the extension isn't available in configure, then you can still install manually from the command-line. See for more help.

Change History

19 Jul 2018: (1.24) Foswikitask:Item14741: EVAL(0) should return 0 not the empty string
20 Jan 2017: (1.23) Released with Foswiki 2.1.3
Foswikitask:Item13892: Clarify documentation on CALC vs. CALCULATE
08 Apr 2016: (1.22) Foswikitask:Item13883: Fix typo in documentation.
15 Oct 2015: (1.21) Foswikitask:Item13811: Fixed memory leak using CALCULATE in a persistent perl environment.
Foswikitask:Item13796: Encode < and > when emitted by CALC or CALCULATE.
27 Jul 2015: (1.20) Copyright update, released with Foswiki 2.0.1
28 May 2015: (1.19) Foswikitask:item12739: Added ISDIGIT(), ISLOWER(), ISUPPER(), ISWIKIWORD() and FILTER() functions.
Foswikitask:Item13440: Document issues with BITXOR. Foswiki unicode core won't support a simple byte based xor of strings.
02 Jan 2015: (1.18) Foswikitask:Item12739: fixed use of uninitialized value, Add triple-quote for escaped strings, Add RANDSTRING() function.
Add the base conversion functions: DEC2BIN() BIN2DEC() DEC2HEX() HEX2DEC() DEC2OCT() and OCT2DEC().
Add LISTEACH() as an alias for LISTMAP()
05 Nov 2012: Foswikitask:Item8417: add VarCALCULATE macro to be used within the normal macro evaluation order. (name chosen for comaptibility )
15 Oct 2012: Foswikitask:Item12153: Use proper format of %SCRIPTURL% in CALC demo.
06 Apr 2012: (1.1.6) Foswikitask:Item11523 - support $comma, $sp in SUBSTITUTE, REPLACE.
Foswikitask:Item11524 - Add FLOOR and CEILING macros.
17 Jan 2012: (1.1.5) Foswikitask:Item11450 - ABOVE and LEFT return R0 and C0 respectively. Should be R1 and C1
05 Dec 2011: (1.1.4) Foswiki release 1.1.4 - Foswikitask:Item11079: leading/trailing spaces break $LISTUNIQUE().
Foswikitask:Item11183: Sync up with TWiki version.
Add Day of Year (DOY) support to $TIME() and $isoweek formats to $FORMATTIME() and $FORMATGMTIME().
10 Apr 2011: Version released with Foswiki 1.1.3. Only a minor change related to how the plugin is being upgraded
06 Nov 2010: Foswikitask:Item9963: Revert usage of $percent back to $percnt
09 Aug 2010: Foswikitask:Item1998: fix for $RIGHT() inclusion of cells to left as well as right
04 Aug 2010: Foswikitask:Item9415: Minor documentation updates
21 Jul 2010: Foswikitask:Item5163: prevent misrendering of empty rows, Foswikitask:Item9400: correct $LIST for cells containing commas, as per doc
29 Dec 2009: Foswikitask:Item2301: added $nop to $LISTJOIN() for better empty parameter
10 Nov 2009: Added unit tests
27 Oct 2009: Foswikitask:Item2301: Fixed $LISTJOIN() to accept an empty separator
20 Sep 2009: Minor documentation update. trunk and release branch code synced (mainly perltidy - all functional changes have been in sync).
17 Sep 2009: Foswikitask:Item2087: SpreadSheetPlugin forgets about zeros being floats as well
11 May 2009: Fixed the calculation of WORKINGDAYS. Changed the default behavior of TIME back to not converting dates to GMT as this creates surprising effects for users living to the east of Greenwich. Added SPREADSHEETPLUGIN_TIMEISLOCAL so users depending on the old behavior keep the old behavior if TIME. Added the feature 'local' to TIME so conversion behavior can be used on demand.
22 Apr 2009: Removed support for settings in the plugin topic which is a bad idea anyway as they get overwritten at each Foswiki upgrade. Define the global settings in Main.SitePreferences instead.
Foswikitask:Item5471: Fixed replacing 0 in REPLACE. Fixed FIND/SEARCH handling of empty strings and corrected documentation for SEARCH
06 Jan 2009: Foswikitask:Item4835: Allow SUBSTITUTE and REPLACE to return values 0 and ''
16 Dec 2008: Foswiki version - no new features
13 Oct 2007: Added $FORMATTIMEDIFF()
09 Sep 2007: Enhanced documentation for $EVAL() and $INT()
02 Jun 2007: Added VarCALC to have %CALC{}% listed in Macros
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 Foswiki: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 Foswiki: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 Foswiki:Main/ArthurClemens; added $FORMAT(KBMB), $EVEN(), $ODD()
17 Jul 2004: Added $WORKINGDAYS(), contributed by Foswiki: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 Foswiki: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
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()

Related Topics: DefaultPreferences, SitePreferences, Plugins, VarCALC