Difference: SpreadSheetPlugin (19 vs. 20)

Revision 202010-05-23 - TWikiContributor

Line: 1 to 1
Added:
>
>

TWiki Spreadsheet Plugin

 
Deleted:
<
<

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: 25 to 25
 
Changed:
<
<
Formula: %CALC{"TWikiGuest)" />"}%  
>
>
Formula: %CALC{"TWikiGuest)" class="twikiInputField" />"}%
 Result:     TWiki Guest
Deleted:
<
<
 
Line: 41 to 42
 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
  • Formulae can be placed in a FormattedSearch, but the CALC needs to be escaped. Learn how to use a CALC in a formatted search
Changed:
<
<
>
>
  • 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
 
Line: 697 to 698
 

How can I easily repeat a formula in a table?

Changed:
<
<
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%

Changed:
<
<
    • Set SHORTDESCRIPTION = Add spreadsheet calculation like "$SUM( $ABOVE() )" to TWiki tables and other topic text
>
>
    • Set SHORTDESCRIPTION = Add spreadsheet calculation like "$SUM( $ABOVE() )" to TWiki tables or anywhere in topic text
 
  • Debug plugin: (See output in data/debug.txt)
    • Set DEBUG = 0
Line: 767 to 768
 

Plugin Info

Plugin Author: TWiki:Main/PeterThoeny
Changed:
<
<
Copyright: © 2001-2009, Peter Thoeny, TWIKI.NET
>
>
Copyright: © 2001-2010, Peter Thoeny, Twiki, Inc.
 
License: GPL (GNU General Public License)
Changed:
<
<
Plugin Version: 09 May 2009 (18078)
>
>
Plugin Version: 18825 (2010-05-29) (2010-05-15)
 
Change History:
<-- specify latest version first -->
 
Added:
>
>
2010-05-22: TWikibug:Item6472 - adding support for DOY in $TIME(), contributed by TWiki:Main/EmanueleCupido
2010-05-15: TWikibug:Item6433 - doc improvements; replacing TWIKIWEB with SYSTEMWEB
2010-02-27: Doc improvements
2009-11-22: Enhanced $NOP(): Added $quot replacement for quote character, changed $per replacement with $percnt, contributed by TWiki:Main/HorstEsser
 
09 May 2009: Fixed bug in $WORKINGDAYS(): Incorrect number of days if daylight savings time change happens between start date and end date
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()
Changed:
<
<
02 Jun 2007: Added VarCALC to have %CALC{}% listed in TWikiVariables
>
>
02 Jun 2007: Added VarCALC to have %CALC{}% listed in TWikiVariables
 
14 Apr 2007: Fixing bug in $EXISTS() that required full web.topic instead of just topic
11 Mar 2007: Fixing bug in $VALUE() and $INT(), introduced by version 09 Mar 2007
09 Mar 2007: Added $EXP(), $LN(), $LOG(), $PI(), $SQRT(); fixed $ROUND() bug, contributed by TWiki:Main/SergejZnamenskij
Line: 823 to 828
 
Feedback: http://TWiki.org/cgi-bin/view/Plugins/SpreadSheetPluginDev
Appraisal: http://TWiki.org/cgi-bin/view/Plugins/SpreadSheetPluginAppraisal
Changed:
<
<
Related Topics: TWikiPreferences, TWikiPlugins, VarCALC

-- TWiki:Main/PeterThoeny - 26 Mar 2009

>
>
Related Topics: TWikiPreferences, TWikiPlugins, VarCALC, VarIF
 
This site is powered by the TWiki collaboration platform Powered by PerlCopyright © 1999-2024 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.