This plugin adds spreadsheet 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.
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 function are of format $FUNCNAME(parameter)
Changed:
< <
Functions may be nested, e.g. %CALC{"$SUM( R2:C$COLUMN(0)..R$ROW(-1):C$COLUMN(0) )"}%
> >
Functions may be nested, such as %CALC{"$SUM( R2:C$COLUMN(0)..R$ROW(-1):C$COLUMN(0) )"}%
Functions are evaluated from left to right, and from inside to outside if nested
The function parameter can be text; a mathematical formula; a cell address; or a range of cell addresses
Changed:
< <
Multiple parameters form a list; they are separated by a comma, followed by optional space, e.g. %CALC{"$SUM( 3, 5, 7 )"}%
> >
Multiple parameters form a list; they are separated by a comma, followed by optional space, such as %CALC{"$SUM( 3, 5, 7 )"}%
A table cell can be addressed as R1:C1. Table address matrix:
R1:C1
R1:C2
R1:C3
R1:C4
R2:C1
R2:C2
R2:C3
R2:C4
A table cell range is defined by two cell addresses separated by "..", e.g. "row 1 through 20, column 3" is: R1:C3..R20:C3
Changed:
< <
Lists can refer to values and/or table cell ranges, e.g. %CALC{"$SUM( 3, 5, $T(R1:C7), R1:C11..R1:C15 )"}%
> >
Lists can refer to values and/or table cell ranges, such as %CALC{"$SUM( 3, 5, $T(R1:C7), R1:C11..R1:C15 )"}%
Formulae can only reference cells in the current or preceeding row of the current table; they may not reference cells below the current table row
Formulae can also be placed outside of tables; they can reference cells in the preceeding table
Plain text can be added, such as %CALC{"Total: $SUM($ABOVE()) kg"}%
Built-in Functions
Line: 429 to 430
NOP( text ) -- no-operation
Changed:
< <
Useful to change the order of plugin execution. For example, it allows preprossing to be done before %SEARCH{}% is evaluated. The percent character '%' can be escaped with $per
> >
Useful to change the order of plugin execution. For example, it allows preprocessing to be done before %SEARCH{}% is evaluated. The percent character '%' can be escaped with $percnt. The quote character '"' can be escaped with $quot.
Syntax: $NOP( text )
Line: 627 to 628
TIME( text ) -- convert a date string into a serialized date number
Changed:
< <
Serialized date is seconds since the Epoch, e.g. midnight, 01 Jan 1970. Current time is taken if the date string is empty. Supported date formats: 31 Dec 2009; 31 Dec 2009 GMT; 31 Dec 09; 31-Dec-2009; 31/Dec/2009; 2009/12/31; 2009-12-31; 2009/12/31; 2009/12/31 23:59; 2009/12/31 - 23:59; 2009-12-31-23-59; 2009/12/31 - 23:59:59; 2009.12.31.23.59.59. Date is assumed to be server time; add GMT to indicate Greenwich time zone
> >
Serialized date is seconds since the Epoch, e.g. midnight, 01 Jan 1970. Current time is taken if the date string is empty. Supported date formats: 31 Dec 2009; 31 Dec 2009 GMT; 31 Dec 09; 31-Dec-2009; 31/Dec/2009; 31 Dec 2003 - 23:59; 31 Dec 2003 - 23:59:59; 2009/12/31; 2009-12-31; 2009/12/31; 2009/12/31 23:59; 2009/12/31 - 23:59; 2009-12-31-23-59; 2009/12/31 - 23:59:59; 2009.12.31.23.59.59. DOY (Day of Year) formats: DOY2003.365, DOY2003.365.23.59, DOY2003.365.23.59.59. Date is assumed to be server time; add GMT to indicate Greenwich time zone
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:
> >
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:
<!--
Line: 729 to 730
Total: 0
Changed:
< <
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.
> >
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
Line: 737 to 738
a plugin setting write %<plugin>_<setting>%, i.e. %SPREADSHEETPLUGIN_SHORTDESCRIPTION%
- 26 Mar 2009
Copyright © 1999-2024 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.