Difference: SpreadSheetPlugin (13 vs. 14)

Revision 1419 Dec 2006 - PeterThoeny

Line: 1 to 1
 

TWiki Spreadsheet Plugin

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.

Line: 232 to 232
 
  • Convert the content of a range of cells into a flat list, delimited by comma. Cells containing commas are merged into the list
  • Syntax: $LIST( range )
  • Example: %CALC{"$LIST($LEFT())"}% returns Apples, Lemons, Oranges, Kiwis assuming the cells to the left contain | Apples | Lemons, Oranges | Kiwis |
Changed:
<
<
>
>
 

LISTIF( condition, list ) -- remove elements from a list that do not meet a condition

Line: 249 to 249
 
  • Syntax: $LISTITEM( index, list )
  • Example: %CALC{"$LISTITEM(2, Apple, Orange, Apple, Kiwi)"}% returns Orange
  • Example: %CALC{"$LISTITEM(-1, Apple, Orange, Apple, Kiwi)"}% returns Kiwi
Changed:
<
<
>
>
 

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

Line: 265 to 265
 
Added:
>
>

LISTRAND( list ) -- get one random element of a list

 

LISTREVERSE( list ) -- opposite order of a list

  • Syntax: $LISTREVERSE( list )
Line: 275 to 281
 

LISTSIZE( list ) -- number of elements in a list

  • Syntax: $LISTSIZE( list )
  • Example: %CALC{"$LISTSIZE(Apple, Orange, Apple, Kiwi)"}% returns 4
Changed:
<
<
>
>

LISTSHUFFLE( list ) -- shuffle element of a list in random order

 

LISTSORT( list ) -- sort a list

  • Sorts a list in ASCII order, or numerically if all elements are numeric
  • Syntax: $LISTSORT( list )
  • Example: %CALC{"$LISTSORT(Apple, Orange, Apple, Kiwi)"}% returns Apple, Apple, Kiwi, Orange
Changed:
<
<
>
>

LISTTRUNCATE( size, list ) -- truncate list to size

 

LISTUNIQUE( list ) -- remove all duplicates from a list

Line: 383 to 402
 

RAND( max ) -- random number

  • Random number, evenly distributed between 0 and max, or 0 and 1 if max is not specified
  • Syntax: $RAND( max )
Changed:
<
<
>
>
 
Changed:
<
<

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

  • Syntax: $REPEAT( text )
>
>

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

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

Line: 557 to 576
 
Added:
>
>

FAQ

Can I use CALC in a formatted search?

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

You need to escape the CALC so that it executes once per search hit. This can be done by escaping the % signs of %CALC{...}% with $percnt. For example, to execute $IF($EXACT($formfield(Tested), Yes), %PUBURL%/%TWIKIWEB%/TWikiDocGraphics/choice-yes.gif, %PUBURL%/%TWIKIWEB%/TWikiDocGraphics/choice-no.gif) in the format="" parameter, write this:

%SEARCH{ .... format="| $topic | $percntCALC{$IF($EXACT($formfield(Tested), Yes), %PUBURL%/%TWIKIWEB%/TWikiDocGraphics/choice-yes.gif, %PUBURL%/%TWIKIWEB%/TWikiDocGraphics/choice-no.gif)}$percnt |" }%

How can I easily repeat a formula in a table?

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:

<!--
   * Set MYFORMULA = $EVAL($SUBSTITUTE(...etc...))
-->
| A | 1 | %CALC{%MYFORMULA%}% |
| B | 2 | %CALC{%MYFORMULA%}% |
| C | 3 | %CALC{%MYFORMULA%}% |
 

Bug Tracking Example

Bug#: Priority: Subject: Status: Days to fix
Line: 586 to 628
 a plugin setting write %<plugin>_<setting>%, i.e. %SPREADSHEETPLUGIN_SHORTDESCRIPTION%

Changed:
<
<
    • Set SHORTDESCRIPTION = Add spreadsheet calculation like "$SUM( $ABOVE() )" to tables located in TWiki topics.
>
>
    • Set SHORTDESCRIPTION = Add spreadsheet calculation like "$SUM( $ABOVE() )" to TWiki tables and other topic text
 
  • Debug plugin: (See output in data/debug.txt)
    • Set DEBUG = 0
Line: 618 to 660
 
Plugin Author: TWiki:Main/PeterThoeny
Copyright: © 2001-2006, Peter@Thoeny.org, StructuredWikis LLC
License: GPL (GNU General Public License)
Changed:
<
<
Plugin Version: 13 May 2006 (10197)
>
>
Plugin Version: 18 Dec 2006 (r12315)
 
Change History:
<-- specify latest version first -->
 
Added:
>
>
18 Dec 2006: Added $LISTRAND(), $LISTSHUFFLE(), $LISTTRUNCATE(); fixed spurious newline at end of topic, contributed by TWiki:Main/MichaelDaum
10 Oct 2006: Enhanced documentation
 
13 May 2006: Added $SETIFEMPTY(); fixes in documentation
17 Jun 2005: Added $NOEXEC(), $EXEC()
25 Mar 2005: Fixed evaluation bug when using SpeedyCGI accelerator; code refactor to load module only when needed, contributed by TWiki:Main/CrawfordCurrie
Line: 661 to 705
  Related Topics: TWikiPreferences, TWikiPlugins
Changed:
<
<
-- TWiki:Main/PeterThoeny - 13 May 2006
>
>
-- TWiki:Main/PeterThoeny - 18 Dec 2006
 
 
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