TWiki Spreadsheet Plugin

This plugin offers simple speadsheet capabilities for tables located in Open-org topics. Table cells containing %CALC{"formula"}% variables are expanded at view time.

Example:

Region: Sales:

Northeast 320

Northwest 580

South 240

Europe 610

Asia 220

Total: 1970

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

The formula next to "Total" is %CALC{"$SUM( $ABOVE() )"}%.
(In case the plugin is not installed or not enabled you see the formula instead of the sum.)

Syntax Rules

The formula in the %CALC{"formula"}% variable can contain built-in functions.
Built-in function are of format $FUNCNAME(parameter)%.
Built-in functions may be nested, e.g. %CALC{"$SUM( R2:C$COL(0)..R$ROW(-1):C$COL(0) )"}%.
The function parameter can be text; a mathematical formula; a cell address; or a range of cell addresses.
A table cell can be addressed as R1:C1. Example addresses:

R1:C1 R1:C2 R1:C3 R1:C4

R2:C1 R2:C2 R2:C3 R2:C4
A table cell range (aka list) is defined by two cell addresses separated by "..". I.e. "row 1 through 20, column 3" is: R1:C3..R20:C3

Built-in Functions

Function Description

"$ABOVE()" The address range of cells above the current cell

"$AVERAGE(list)" The average of the content of a range of cells. Example: To get the average of column 5 excluding the title row, write in the last row: %CALC{"$AVERAGE( R2:C5..R$ROW(-1):C5 )"}%

"$CHAR(number)" The ASCII character represented by number. Example: %CALC{"$CHAR(97)"}% returns a

"$CODE(text)" The ASCII numeric value of the first character in text. Example: %CALC{"$CODE(abc)"}% returns 97

"$COLUMN(offset)" The current column number with an optional offset

"$COUNTITEMS(list)" The count of individual items in a list. Example: To count the items of all cells above the current cell, write %CALC{"$COUNTITEMS( $ABOVE() )"}%

"$DEF(list)" Returns the first cell reference that is not empty. Example: %CALC{"$DEF( R1:C1..R1:C3 )"}%

"$EVAL(formula)" Evaluates a simple formula. Only addition, substraction, multipliation and division of numbers are supported. Any nesting is permitted. Example: %CALC{"$EVAL( (5 * 3) / 2 + 1.1 )"}% returns 8.6

"$INT(formula)" Evaluates a simple formula and rounds the result down to the nearest integer. Example: %CALC{"$INT( 10 / 4 )"}% returns 2

"$LEFT()" The address range of cells to the left of the current cell

"$LENGTH(text)" The length in bytes of text. Example: %CALC{"$LENGTH(abcd)"}% returns 4

"$LOWER(text)" The lower case string of a text. Example: %CALC{"$LOWER( $T(R1:C5) )"}% returns the lower case string of the text in cell R1:C5

"$MAX(list)" The biggest value of a range of cells. Example: To find the biggest number to the left of the current cell, write: %CALC{"$MAX( $LEFT() )"}%

"$MIN(list)" The smallest value of a range of cells. Example: To find the smallest number to the left of the current cell, write: %CALC{"$MIN( $LEFT() )"}%

"$RIGHT()" The address range of cells to the right of the current cell

"$ROW(offset)" The current row number with an offset. Example: To get the number of rows excluding table heading ( first row) and summary row (last row you are in), write: %CALC{"$ROW(-2)"}%

"$SUM(list)" The sum of a list or range of cells. Example: To sum up column 5 excluding the title row, write: %CALC{"$SUM( R2:C5..R$ROW(-1):C5 )"}% in the last row; or simply %CALC{"$SUM( $ABOVE() )"}%

"$T(address)" The content of a cell. Example: %CALC{"$T(R1:C5)"}% returns the text in cell R1:C5

"$UPPER(text)" The upper case string of a text. Example: %CALC{"$UPPER( $T(R1:C5) )"}% returns the upper case string of the text in cell R1:C5

Function	Description
`"$ABOVE()"`	The address range of cells above the current cell
`"$AVERAGE(list)"`	The average of the content of a range of cells. Example: To get the average of column 5 excluding the title row, write in the last row: `%CALC{"$AVERAGE( R2:C5..R$ROW(-1):C5 )"}%`
`"$CHAR(number)"`	The ASCII character represented by number. Example: `%CALC{"$CHAR(97)"}%` returns `a`
`"$CODE(text)"`	The ASCII numeric value of the first character in text. Example: `%CALC{"$CODE(abc)"}%` returns `97`
`"$COLUMN(offset)"`	The current column number with an optional offset
`"$COUNTITEMS(list)"`	The count of individual items in a list. Example: To count the items of all cells above the current cell, write `%CALC{"$COUNTITEMS( $ABOVE() )"}%`
`"$DEF(list)"`	Returns the first cell reference that is not empty. Example: `%CALC{"$DEF( R1:C1..R1:C3 )"}%`
`"$EVAL(formula)"`	Evaluates a simple formula. Only addition, substraction, multipliation and division of numbers are supported. Any nesting is permitted. Example: `%CALC{"$EVAL( (5 * 3) / 2 + 1.1 )"}%` returns `8.6`
`"$INT(formula)"`	Evaluates a simple formula and rounds the result down to the nearest integer. Example: `%CALC{"$INT( 10 / 4 )"}%` returns `2`
`"$LEFT()"`	The address range of cells to the left of the current cell
`"$LENGTH(text)"`	The length in bytes of text. Example: `%CALC{"$LENGTH(abcd)"}%` returns `4`
`"$LOWER(text)"`	The lower case string of a text. Example: `%CALC{"$LOWER( $T(R1:C5) )"}%` returns the lower case string of the text in cell `R1:C5`
`"$MAX(list)"`	The biggest value of a range of cells. Example: To find the biggest number to the left of the current cell, write: `%CALC{"$MAX( $LEFT() )"}%`
`"$MIN(list)"`	The smallest value of a range of cells. Example: To find the smallest number to the left of the current cell, write: `%CALC{"$MIN( $LEFT() )"}%`
`"$RIGHT()"`	The address range of cells to the right of the current cell
`"$ROW(offset)"`	The current row number with an offset. Example: To get the number of rows excluding table heading ( first row) and summary row (last row you are in), write: `%CALC{"$ROW(-2)"}%`
`"$SUM(list)"`	The sum of a list or range of cells. Example: To sum up column 5 excluding the title row, write: `%CALC{"$SUM( R2:C5..R$ROW(-1):C5 )"}%` in the last row; or simply `%CALC{"$SUM( $ABOVE() )"}%`
`"$T(address)"`	The content of a cell. Example: `%CALC{"$T(R1:C5)"}%` returns the text in cell `R1:C5`
`"$UPPER(text)"`	The upper case string of a text. Example: `%CALC{"$UPPER( $T(R1:C5) )"}%` returns the upper case string of the text in cell `R1:C5`

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

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 typed in statically. As an idea, another plugin could be created that pulls out data from a bug tracking system and updates the table rows accordingly. The spreadsheet plugin can be used to do some statistics on the table.

SpreadSheetPlugin Settings

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

One line description, is shown in the TextFormattingRules topic:
- Set SHORTDESCRIPTION = Add spreadsheet calculation like "$SUM( $ABOVE() )" to tables located in Open-org topics.

Debug plugin: (See output in data/debug.txt)
- Set DEBUG = 0

Do not handle %CALC{}% tag in included topic while including topic: (default: 1)
- Set SKIPINCLUDE = 1

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.

Download the ZIP file from the SpreadSheetPlugin home

Unzip SpreadSheetPlugin.zip in your twiki installation directory. Content:

File:	Description:
`data/TWiki/SpreadSheetPlugin.txt`	Plugin topic
`data/TWiki/SpreadSheetPlugin.txt,v`	Plugin topic repository
`lib/TWiki/Plugins/SpreadSheetPlugin.pm`	Plugin Perl module

Test if the "Total" in the first table in this topic is correct.

Plugin Info

Plugin Author: PeterThoeny

Plugin Version: 12 Mar 2002

Change History:

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

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

-- PeterThoeny - 07 Jun 2002
to top

End of topic
Skip to action links | Back to top

Edit | Attach image or document | Printable version | Raw text | More topic actions
Revisions: | r1.12 | > | r1.11 | > | r1.10 | Total page history | Backlinks

You are here: TWiki > SpreadSheetPlugin

to top

Plugin Author:	PeterThoeny
Plugin Version:	12 Mar 2002
Change History:
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
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

`R1:C1`	`R1:C2`	`R1:C3`	`R1:C4`
`R2:C1`	`R2:C2`	`R2:C3`	`R2:C4`