Difference: SpreadSheetPlugin (17 vs. 18)

Revision 182009-03-27 - Main.TWikiContributor

Line: 1 to 1
Added:
>
>
<--
   Contributions to this plugin are appreciated. Please update the plugin page
   at http://twiki.org/cgi-bin/view/Plugins/SpreadSheetPlugin or provide feedback
   at http://twiki.org/cgi-bin/view/Plugins/SpreadSheetPluginDev.
   If you are a TWiki contributor please update the plugin in the SVN repository.
-->
 

TWiki Spreadsheet Plugin

Changed:
<
<
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.
>
>
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.
 
Example:
Line: 25 to 31
 
Changed:
<
<
The formula next to "Total" is %CALC{"$SUM( $ABOVE() )"}%.
(you see the formula instead of the sum in case the Plugin is not installed or not enabled.)
>
>
The formula next to "Total" is %CALC{"$SUM( $ABOVE() )"}%.
(you see the formula instead of the sum in case the plugin is not installed or not enabled.)
 

On this page:

Syntax Rules

Changed:
<
<
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.
>
>
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)
  • Functions may be nested, e.g. %CALC{"$SUM( R2:C$COLUMN(0)..R$ROW(-1):C$COLUMN(0) )"}%
Line: 46 to 52
 
  • Lists can refer to values and/or table cell ranges, e.g. %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
Added:
>
>
 

Built-in Functions

Line: 118 to 126
 
Added:
>
>

EMPTY( text ) -- test for empty text

  • Returns 1 if text is empty, or 0 if not
  • Syntax: $EMPTY( text )
  • Example: %CALC{"$EMPTY(foo)"}% returns 0
  • Example: %CALC{"$EMPTY()"}% returns 1
  • Example: %CALC{"$EMPTY($TRIM( ))"}% returns 1
  • Related: $EXACT(), $IF(), $TRIM()
 

EVAL( formula ) -- evaluate a simple mathematical formula

  • Addition, substraction, multiplication, division and modulus of numbers are supported. Any nesting is permitted
Line: 138 to 155
 
  • Syntax: $EXACT( text1, text2 )
  • Example: %CALC{"$EXACT(foo, Foo)"}% returns 0
  • Example: %CALC{"$EXACT(foo, $LOWER(Foo))"}% returns 1
Changed:
<
<
>
>
 

EXEC( formula ) -- execute a spreadsheet formula

Line: 155 to 172
 
  • Syntax: $EXISTS( topic )
  • Example: %CALC{"$EXISTS(WebHome)"}% returns 1
  • Example: %CALC{"$EXISTS(ThisDoesNotExist)"}% returns 0
Added:
>
>
 

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

Line: 170 to 188
 
  • Example: %CALC{"$FIND(f, fluffy)"}% returns 1
  • Example: %CALC{"$FIND(f, fluffy, 2)"}% returns 4
  • Example: %CALC{"$FIND(@, fluffy, 1)"}% returns 0
Changed:
<
<
>
>
 

FORMAT( type, precision, number ) -- format a number to a certain type and precision

Line: 235 to 253
 
  • 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
  • Example: %CALC{"$SET(val, $IF($T(R1:C2) == 0, zero, $T(R1:C2)))"}% sets a variable conditionally
Changed:
<
<
>
>

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

 

INT( formula ) -- evaluate formula and round down to nearest integer

Line: 253 to 279
 
  • Example: %CALC{"$SUM($LEFT())"}% returns the sum of cells to the left of the current cell
  • Related: $ABOVE(), $RIGHT()
Added:
>
>

LEFTSTRING( text, num ) -- extract characters at the beginning of a text string

 

LENGTH( text ) -- length of text in bytes

  • Syntax: $LENGTH( text )
Line: 273 to 307
 
  • Example: %CALC{"$LISTIF($item > 12, 14, 7, 25)"}% returns 14, 25
  • Example: %CALC{"$LISTIF($NOT($EXACT($item,)), A, B, , E)"}% returns non-empty elements A, B, E
  • Example: %CALC{"$LISTIF($index > 2, A, B, C, D)"}% returns C, D
Changed:
<
<
>
>
 

LISTITEM( index, list ) -- get one element of a list

Line: 395 to 429
 

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 preprossing to be done before %SEARCH{}% is evaluated. The percent character '%' can be escaped with $per
 
  • Syntax: $NOP( text )

Line: 403 to 437
 
  • Returns 0 if num is not zero, 1 if zero
  • Syntax: $NOT( num )
  • Example: %CALC{"$NOT(0)"}% returns 1
Changed:
<
<
>
>
 

ODD( num ) -- test for odd number

Line: 445 to 479
 

PROPERSPACE( text ) -- properly space out WikiWords

Changed:
<
<
  • Properly spaces out WikiWords preceeded by white space, parenthesis, or ][. Words listed in the DONTSPACE TWikiPreferences variable or DONTSPACE Plugins setting are excluded
>
>
  • Properly spaces out WikiWords preceeded by white space, parenthesis, or ][. Words listed in the DONTSPACE TWikiPreferences variable or DONTSPACE plugins setting are excluded
 
  • Syntax: $PROPERSPACE( text )
  • Example: Assuming DONTSPACE contains MacDonald: %CALC{"$PROPERSPACE(Old MacDonald had a ServerFarm, EeEyeEeEyeOh)"}% returns Old MacDonald had a Server Farm, Ee Eye Ee Eye Oh
  • Related: $LOWER(), $PROPER(), $TRIM(), $UPPER()
Line: 466 to 500
 
  • 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
  • Syntax: $REPLACE( text, start, num, new )
  • Example: %CALC{"$REPLACE(abcdefghijk,6,5,*)"}% returns abcde*k
Changed:
<
<
>
>
 

RIGHT( ) -- address range of cells to the right of the current cell

Line: 474 to 508
 
  • Example: %CALC{"$SUM($RIGHT())"}% returns the sum of cells to the right of the current cell
  • Related: $ABOVE(), $LEFT()
Added:
>
>

RIGHTSTRING( text, num ) -- extract characters at the end of a text string

 

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
Line: 498 to 540
 
  • Example: %CALC{"$SEARCH([uy], fluffy)"}% returns 3
  • Example: %CALC{"$SEARCH([uy], fluffy, 3)"}% returns 6
  • Example: %CALC{"$SEARCH([abc], fluffy,)"}% returns 0
Changed:
<
<
>
>
 

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

Line: 541 to 583
 
  • Example: %CALC{"$SUBSTITUTE(Q2-2002,2,3)"}% returns Q3-3003
  • Example: %CALC{"$SUBSTITUTE(Q2-2002,2,3,3)"}% returns Q2-2003
  • Example: %CALC{"$SUBSTITUTE(abc123def,[0-9],9,,r)"}% returns abc999def
Changed:
<
<
>
>

SUBSTRING( text, start, num ) -- extract a substring out of a text string

 

SUM( list ) -- sum of a list or range of cells

Line: 574 to 623
 
  • Syntax: $TRANSLATE( text, from, to )
  • Example: %CALC{"$TRANSLATE(boom,bm,cl)"}% returns cool
  • Example: %CALC{"$TRANSLATE(one, two,$comma,;)"}% returns one; two
Changed:
<
<
>
>
 

TIME( text ) -- convert a date string into a serialized date number

Line: 609 to 658
 
  • Removes all spaces from text except for single spaces between words
  • Syntax: $TRIM( text )
  • Example: %CALC{"$TRIM( eat  spaces  )"}% returns eat spaces
Changed:
<
<
>
>
 

UPPER( text ) -- upper case string of a text

Line: 635 to 684
 

FAQ

Added:
>
>
 

Can I use CALC in a formatted search?

Specifically, how can I output some conditional text in a FormattedSearch?

Line: 677 to 727
 
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. 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.
 

Plugin Settings

Line: 709 to 759
 
data/TWiki/SpreadSheetPlugin.txt Plugin topic
data/TWiki/SpreadSheetPlugin.txt,v Plugin topic repository
lib/TWiki/Plugins/SpreadSheetPlugin.pm Plugin Perl module
Changed:
<
<
  • TWiki 4 and up: Visit configure in your TWiki installation, and enable the Plugin in the {Plugins} section.
>
>
  • TWiki 4 and up: Visit configure in your TWiki installation, and enable the plugin in the {Plugins} section.
 
  • Test if the "Total" in the first table in this topic is correct.

Plugin Info

Plugin Author: TWiki:Main/PeterThoeny
Changed:
<
<
Copyright: © 2001-2007, Peter Thoeny, TWIKI.NET
>
>
Copyright: © 2001-2009, Peter Thoeny, TWIKI.NET
 
License: GPL (GNU General Public License)
Changed:
<
<
Plugin Version: 13 Oct 2007 (15270)
>
>
Plugin Version: 26 Mar 2009 (17910)
 
Change History:
<-- specify latest version first -->
 
Added:
>
>
26 Mar 2009: Added $INSERTSTRING()
25 Mar 2009: Added $EMPTY(), $LEFTSTRING(), $RIGHTSTRING(), $SUBSTRING()
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
Line: 769 to 822
  Related Topics: TWikiPreferences, TWikiPlugins, VarCALC
Changed:
<
<
-- TWiki:Main/PeterThoeny - 13 Oct 2007
>
>
-- TWiki:Main/PeterThoeny - 26 Mar 2009
 
This site is powered by the TWiki collaboration platformCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback
Note: Please contribute updates to this topic on TWiki.org at TWiki:TWiki.SpreadSheetPlugin