Foswiki Spreadsheet Plugin

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

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

EXP( num ) -- exponent (e) raised to the power of a number

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.

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

Specify the variable name (alphanumeric characters and underscores). An empty string is returned if the variable does not exist. Use $SET() to set a variable first. Unlike table ranges, variables live for the time of the page view and persist across tables, i.e. you can use it to summarize results across several tables.

Syntax: $GET( name )

Example: %CALC{"$GET(my_total)"}% returns the value of the my_total variable

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

The condition can be a number (where 0 means condition not met), or two numbers with a comparison operator < (less than), <= (less than or equal), == (equal), != (not equal), >= (greater than or equal), > (greater than).

Syntax: $IF( condition, value if true, value if 0 )

Example: %CALC{"$IF($T(R1:C5) > 1000, Over Budget, OK)"}% returns Over Budget if value in R1:C5 is over 1000, OK if not

Example: %CALC{"$IF($EXACT($T(R1:C2),), empty, $T(R1:C2))"}% returns the content of R1:C2 or empty if empty

INSERTSTRING( text, start, new ) -- insert a string into a text string

Insert new string into text string text to the right of start position. Position starts at 0 (insert before first character). Use a negative start to count from the end of the text. If start is greater than the length of the text the original text is returned.

Retrieve the num of characters from the left end of text. The leftmost character is returned if num is missing. If num is greater than the length of text the entire text string is returned with no additional spaces added. If num is negative num characters are removed from the end of the string. If num is zero or num is a negative value with the number greater than the length of text an empty string is returned.

LISTJOIN( separator, list ) -- convert a list into a string

By default, list items are separated by a comma and a space. Use this function to indicate a specific separator string, which may include $comma for comma, $n for newline, $sp for space, and $nop for no separator between list items.

RAND( max ) -- random number

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

Generate a random string from a set of characters; the set may contain sequences like a..z; default is a..zA..Z0..9_. The format defines the string length or the output format; specify a number to indicate the length of the random string; default is 8 characters. Alternatively, specify a format string with x as placeholders for random characters, such xxxx-xxxx-xxxx-xxxx.

Example: %CALCULATE{$RANDSTRING(A..NP..Z1..9, xxxx-xxxx-xxxx-xxxx)}% returns four sets of random strings, separated by dashes, where each set has four characters composed of uppercase letters and numbers, excluding letter O and number 0

REPEAT( text, num ) -- repeat text a number of times

Syntax: $REPEAT( text, num )

Example: %CALC{"$REPEAT(/\, 5)"}% returns /\/\/\/\/\

REPLACE( text, start, num, new ) -- replace part of a text string

Replace num number of characters of text string text, starting at start, with new text new. Starting position is 1; use a negative start to count from the end of the text. In the new parameter you can write $comma to escape comma, $sp to escape space.

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.

Retrieve the num of characters from the right end of text. The rightmost character is returned if num is missing.

ROUND( formula, digits ) -- round a number

Evaluates a simple formula and rounds the result up or down to the number of digits if digits is positive; to the nearest integer if digits is missing; or to the left of the decimal point if digits is negative

SEARCH( string, text, start ) -- search a string within a text

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

SET( name, value ) -- set a variable for later use

Specify the variable name (alphanumeric characters and underscores) and the value. The value may contain a formula; formulae are evaluated before the variable assignment; see $NOEXEC() if you want to prevent that. This function returns no output. Use $GET() to retrieve variables. Unlike table ranges, variables live for the time of the page view and persist across tables, i.e. you can use it to summarize results across several tables and also across included topics

Syntax: $SET( name, value )

Example: %CALC{"$SET(my_total, $SUM($ABOVE()))"}% sets the my_total variable to the sum of all table cells located above the current cell and returns an empty string

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

Specify the variable name (alphanumeric characters and underscores) and the formula. The formula must start with an operator to + (add), - (subtract), * (multiply), or / (divide) something to the variable. This function returns no output. Use $GET() to retrieve variables

Syntax: $SETM( name, formula )

Example: %CALC{"$SETM(total, + $SUM($LEFT()))"}% adds the sum of all table cells on the left to the total variable, and returns an empty string

SIGN( num ) -- sign of a number

SPLIT( separator, text ) -- split a string into a list

Split text into a list using separator as a delimiter. The separator may be a regular expression and may include $comma for comma, $sp for space and $empty to split at each character. Default separator is one or more spaces ($sp$sp*). Leading and trailing spaces are removed from the string before splitting.

SQRT( num ) -- square root of a number

Syntax: $SQRT( num )

Example: %CALC{"$SQRT(16)"}% returns 4

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

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. The text may not contain commas. In the old and new parameters you can write $comma to escape comma, $sp to escape space. A literal search is performed by default; a RegularExpression search if the option is set to r

Extract num number of characters of text string text, starting at start. Starting position is 1; use a negative start to count from the end of the text. If start or num is zero an empty string is returned. If num is greater than the length of the text the entire text string is returned without any extra spaces added.

SUMDAYS( list ) -- sum the days in a list or range of cells

The total number of days in a list or range of cells containing numbers of hours, days or weeks. The default unit is days; units are indicated by a h, hours, d, days, w, weeks suffix. One week is assumed to have 5 working days, one day 8 hours

T( address ) -- content of a cell

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

The translation is done from a set to a set, one character by one. The text may contain commas; all three parameters are required. In the from and to parameters you can write $comma to escape comma, $sp to escape space

Note: Date is assumed to be GMT unless SPREADSHEETPLUGIN_TIMEISLOCAL is set (default 0). Add GMT to force Greenwich time zone. Add LOCAL to force the timezone of the server. Note that if you use LOCAL or SPREADSHEETPLUGIN_TIMEISLOCAL is set to 1, dates entered by users on servers placed to the east of Greenwich will be converted to the day before which will often be undesired. It is recommended to keep SPREADSHEETPLUGIN_TIMEISLOCAL = 0 which is the default.

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

WHILE( condition, do ) -- do something while a condition is true

The condition can be a number (where 0 means condition not met), or two numbers with a comparison operator < (less than), <= (less than or equal), == (equal), != (not equal), >= (greater than or equal), > (greater than).

The condition and do are evaluated in each cycle; a $counter starting at 1 can be used in condition and do.

FAQ

Can I use CALCULATE in a formatted search?

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:

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.

Settings

You can override some default settings in the plugin by setting the following preferences.

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

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

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