Daya Bay Reactor Neutrino Experiment TWiki > SpreadSheetPlugin Daya Bay webs:
Public | 中文 | Internal | Help

Log In or Register
    Page contents    

TWiki Spreadsheet Plugin

This plugin adds spreadsheet capabilities to TWiki topics. Functions such as %CALCULATE{$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 123 functions.


Region: Sales:
Americas 732
Europe 611
Asia 228
Total: 1571
  Raw text:

| *Region:* | *Sales:* |
| Americas  |  732 |
| Europe    |  611 |
| Asia      |  228 |
| Total: |  %CALC{$SUM($ABOVE())}% |

Interactive example:

Result:  TWiki Guest

Syntax Rules

This plugin handles the %CALC{...}% and %CALCULATE{...}% variables. Embedded formulas are evaluated as follows:


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 123 functions. Convention for parameters:

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

ABS( num ) -- absolute value of a number

ADDLIST( name, list ) -- append a list to a list variable

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

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

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, option ) -- convert elapsed time to a string

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

GETHASH( name, key ) -- get the value of a previously set hash key

GETLIST( name ) -- get the list from a list variable

HASH2LIST( name, format ) -- convert a hash to a list

HASHCOPY( from, to ) -- copy a hash

HASHEACH( formula, name ) -- evaluate and update each hash element

HASHEXISTS( name, key ) -- test if a hash exists

HASHREVERSE( name ) -- reverse a hash

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

HEXDECODE( hexcode ) -- convert hexadecimal code to string

HEXENCODE( text ) -- encode text into hexadecimal code

IF( condition, then, else ) -- 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

LIST2HASH( name, list ) -- create a hash from 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

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

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

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

SETHASH( name, key, value ) -- set a hash value for later use, or delete a hash

SETIFEMPTY( name, value ) -- set a variable only if empty

SETLIST( name, list ) -- save a list for later use

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

SETMHASH( name, key, formula ) -- modify an existing hash 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


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

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

What are hashes and what are they used for?

Hashes are named sets of key & value pairs. For example, a hash called age may have first names as keys and ages as values. In TWiki's case you might do a FormattedSearch and store the result in hashes, then display the result in various formats. For example, you want to show feature requests in multiple tables, organized by status. Adding a search for each status is possible, but it can be slow. The page loads faster if you search only once, and store the status, summary, date and other fields in hashes. Then you use those hashes to render each table by status.


%CALCULATE{$SETHASH()}% <!-- clear all hashes -->
 format="$percntCALCULATE{$SETHASH(status, $topic, $formfield(Status))$SETHASH(summary, $topic, '''$summary''')$SETHASH(date, $topic, '''$date''')}$percnt"
---++ Proposed Features
| *Feature* | *Updated* |
      | [[$item]]: $GETHASH(summary, $item) | $GETHASH(date, $item) |,
          $GETHASH(status, $item),
---++ Accepted Features
| *Feature* | *Updated* |
      | [[$item]]: $GETHASH(summary, $item) | $GETHASH(date, $item) |,
          $GETHASH(status, $item),

First we search all features and store the status, summary and date in hashes, using the topic name as the key. Then we build a table that shows all proposed features, followed by a table showing all accepted features. Reading the CALCULATE formula from inside out:

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() )}% | . \ 
  |  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

Show details toggleopen.gif Hide details toggleclose.gif

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

Plugin Installation Instructions

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

Show details toggleopen.gif Hide details toggleclose.gif

Plugin Info

Plugin Author: TWiki:Main.PeterThoeny
Copyright: © 2001-2014 Peter Thoeny, External link mark
© 2008-2014 TWiki:TWiki.TWikiContributor
© 2014 Wave Systems Corp.
Sponsor: Wave Systems Corp. External link mark for hash functions
License: GPL (GNU General Public License External link mark)
Plugin Version: 2014-09-23
Show Change History toggleopen.gif Hide Change History toggleclose.gif
2014-09-23: TWikibug:Item7553: Allow newlines and indent around functions and function parameters
2014-09-22: TWikibug:Item7552: Allow newlines in triple-quoted strings
2014-03-04: TWikibug:Item7445: Add FORMAT(CURRENY, ...) with support for currency symbol
2014-01-22: TWikibug:Item7419: Add ADDLIST(), GETLIST(), SETLIST()
2014-01-22: TWikibug:Item7418: Fix VALUE function bug with incorrect exponential number; ability to delete SET variable; do not strip trailing spaces in SETIFEMPTY
2013-10-10: TWikibug:Item7154: Doc update: Put TOC on top right for easy reference
2013-09-14: TWikibug:Item7299: Fix plural of month to months in FORMATTIMEDIFF
2013-08-21: TWikibug:Item7322: TRANSLATE supporting double quote and apostrophe-quote (single quote) escape tokens
2013-07-18: TWikibug:Item7299: Add short and compact format to FORMATTIMEDIFF
2013-06-20: TWikibug:Item7154: Fix encoding of URL parameter in the interactive example so that $IF(1>2, true, false) works properly
2013-05-21: TWikibug:Item7154: Small doc fixes
2013-04-09: TWikibug:Item7221: Fix for CALC referencing preceding table returning incorrect last cell value
2013-04-07: TWikibug:Item7218: Support and document ROW() and COLUMN() below a table
2013-03-26: TWikibug:Item7203: Support '''triple quoted''' parameters for strings that contain comma and parenthesis
2013-03-25: TWikibug:Item7199: Remove restriction on permitted characters for hash key
2013-03-24: TWikibug:Item7199: Added HASCOPY(), HASHEACH(); renamed LISTMAP() to LISTEACH() while keeping LISTMAP() as an undocumented feature
2013-03-23: TWikibug:Item7199: Added LIST2HASH(), HASH2LIST(), HASHEXISTS(), HASHREVERSE(), SETMHASH()
2013-03-21: TWikibug:Item7199: Added SETHASH(), GETHASH()
2013-03-14: TWikibug:Item7196: Added BIN2DEC(), DEC2BIN(), DEC2HEX(), DEC2OCT(), HEX2DEC(), OCT2DEC()
2013-03-14: TWikibug:Item7190: Enumeration of function brackets should never be negative
2013-03-11: TWikibug:Item7184: Allow commas in SUBSTRING input string
2013-01-09: TWikibug:Item7091: Use TWISTY in variable section, installation instructions and 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()
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
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
Plugin Benchmarks: GoodStyle 99%, FormattedSearch 99%, SpreadSheetPlugin 95%
Other Dependencies: none
Perl Version: 5.000 and up
Plugin Home:

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

Revision: r29 - 2014-09-23 - 15:36:09 - TWikiContributor

Copyright © 1999-2017 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding Daya Bay? Send feedback
Note: Please contribute updates to this topic on at TWiki:TWiki.SpreadSheetPlugin.