TWiki Spreadsheet Plugin

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

Example:

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

Bug Tracking Example

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 Text Formatting Rules topic:
  • Set SHORTDESCRIPTION = Add spreadsheet calculation like "$SUM( $ABOVE() )" to tables located in linux.org.ua 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

Related Topics: TWiki Preferences, TWiki Plugins

-- Peter Thoeny - 07 Jun 2002