%CALC{"formula"}% variables are expanded at view time.
Example:
| Region: | Sales: |
|---|---|
| Northeast | 320 |
| Northwest | 580 |
| South | 240 |
| Europe | 610 |
| Asia | 220 |
| Total: | 1970 |
%CALC{"$SUM( $ABOVE() )"}%. %CALC{"formula"}% variable can contain built-in functions.
$FUNCNAME(parameter)%.
%CALC{"$SUM( R2:C$COL(0)..R$ROW(-1):C$COL(0) )"}%.
R1:C1. Example addresses:
R1:C1 | R1:C2 | R1:C3 | R1:C4 |
R2:C1 | R2:C2 | R2:C3 | R2:C4 |
"..". I.e. "row 1 through 20, column 3" is: R1:C3..R20:C3
| 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() )"}% |
"$COUNTSTR(list, str)" | Counts the number of cells in a list equal to a given string (if str is specified), or counts the number of non empty cells in a list. Example: To count the number of non empty cells above the current cell, write %CALC{"$COUNTSTR( $ABOVE() )"}%; to count the number of cells equal to DONE, write %CALC{"$COUNTSTR( $ABOVE(), DONE )"}%; |
"$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, multiplication, division and modulus of numbers are supported. Any nesting is permitted. Example: %CALC{"$EVAL( (5 * 3) / 2 + 1.1 )"}% returns 8.6 |
"$EXACT(text1, text2)" | Compares two text strings and returns 1 if they are exactly the same, or 0 if not. Example: %CALC{"$EXACT( foo, Foo )"}% returns 0; %CALC{"$EXACT( foo, $LOWER(Foo) )"}% returns 1 |
"$FIND(search_string, text, start_index)" | Finds one text string search_string, within another text string text, and returns the number of the starting position of search_string, from the first character of text. This search is case sensitive and is not a regular expression search; use $SEARCH() for regular expression searching. Starting position is 1; an empty string is returned if nothing is matched. Examples: %CALC{"$FIND(f, fluffy)"}% returns 1 %CALC{"$FIND(f, fluffy, 2)"}% returns 4 %CALC{"$FIND(@, fluffy, 1)"}% returns an empty string |
"$FORMAT(type, prec, number)" | Formats a number to a certain type and precision. Types with examples: - %CALC{"$FORMAT( COMMA, 2, 12345.6789 )"}% returns 12,345.68 - %CALC{"$FORMAT( DOLLAR, 2, 12345.67 )"}% returns $12,345.68 - %CALC{"$FORMAT( NUMBER, 1, 12345.67 )"}% returns 12345.7 - %CALC{"$FORMAT( PERCENT, 1, 0.1234567 )"}% returns 12.3% |
"$IF(condition, value if true, value if 0)" | Returns one value if a condition is met, and another value if not. The condition can be a number (where 0 means condition not met), or two numbers with a comparison operator < (less then), <= (less then or equal), == (equal), != (not equal), >= (greater then or equal), >= (greater then). Example: %CALC{"$IF( $T(R1:C5) > 1000, Over Budget, OK )"}% returns Over Budget if value in R1:C5 is over 1000, OK if not |
"$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() )"}% |
"$NOP(text)" | A no-operation. Allows one to defy the order of Plugin execution. For example, it will allow preprossing to be done before %SEARCH{}% is evaluated. Use $per to escape '%'. |
"$REPLACE(text, start_num, num_chars, new_text)" | Replaces part of text string text, based on the starting position start_num, and the number of characters to replace num_chars. The characters are replaced with new_text. Starting position is 1; use a negative start_num to count from the end of the text. See also $SUBSTITUTE(), $TRANSLATE(). Example: %CALC{"$REPLACE(abcdefghijk,6,5,*)"}% returns abcde*k |
"$RIGHT()" | The address range of cells to the right of the current cell |
"$ROUND(formula)" | Evaluates a simple formula and rounds the result up or down to the nearest integer. Example: %CALC{"$INT( 10 / 6 )"}% returns 2 |
"$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)"}% |
"$SEARCH(search_string, text, start_index)" | Finds one text string search_string, within another text string text, and returns the number of the starting position of search_string, from the first character of text. This search a Regular Expression search; use $FIND() for non-regular expression searching. Starting position is 1; an empty string is returned if nothing is matched. Examples: %CALC{"$SEARCH([uy], fluffy)"}% returns 3 %CALC{"$SEARCH([uy], fluffy, 3)"}% returns 6 %CALC{"$SEARCH([abc], fluffy,)"}% returns an empty string |
"$SUBSTITUTE(text, old, new, instance, option)" | Substitutes new text for old text in a text string. instance specifies which occurance of old you want to replace. If you specify instance, only that instance is replaced. Otherwise, every occurance is changed to the new text. A literal search is performed by default; a Regular Expression search if the option is set to r. See also $REPLACE(), $TRANSLATE(). Examples: %CALC{"$SUBSTITUTE(Good morning, morning, day)"}% returns Good day %CALC{"$SUBSTITUTE(Q2-2002,2,3)"}% returns Q3-3003 %CALC{"$SUBSTITUTE(Q2-2002,2,3,3)"}% returns Q2-2003 %CALC{"$SUBSTITUTE(abc123def,[0-9],9,,r)"}% returns abc999def |
"$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 |
"$TRANSLATE(text, from, to)" | Translate text from a set of characters to another set, one character by one. The text may contain commas; the last two comma separated parameters are required. For from and to parameters, you can write $comma to escape comma, $sp to escape space. See also $REPLACE(), $SUBSTITUTE(). Examples: %CALC{"$TRANSLATE(boom,bm,cl)"}% returns cool %CALC{"$TRANSLATE(one, two,$comma,;)"}% returns one; two |
"$TRIM(text)" | Removes all spaces from text except for single spaces between words. Example: %CALC{"$TRIM( eat spaces )"}% returns eat spaces. |
"$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#: | 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 |
| 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.
%<plugin>_<setting>%, i.e. %SPREADSHEETPLUGIN_SHORTDESCRIPTION%
"$SUM( $ABOVE() )" to tables located in jEdit Community Wiki topics.
data/debug.txt)
%CALC{}% tag in included topic while including topic: (default: 1)
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 |
| Plugin Author: | Peter Thoeny |
| Plugin Version: | 30 Jul 2003 |
| Change History: | |
| 30 Jul 2003: | Added $TRANSLATE() |
| 19 Jul 2003: | Added $FIND(), $NOP(), $REPLACE(), $SEARCH(), $SUBSTITUTE(), contributed by TWiki:Main/PaulineCheung |
| 19 Apr 2003: | Added $COUNTSTR(), $EXACT(), $IF(), $ROUND(), $TRIM(); added $FORMAT(), contributed by TWiki:Main/JimStraus; support % modulus operator in $EVAL, $INT, and $ROUND; fixed bug in $DEF |
| 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 |
| Topic SpreadSheetPlugin . { Edit | Attach | Ref-By | Printable | Diffs | r1.17 | > | r1.16 | > | r1.15 | More } |
| Revision r1.17 - 31 Jul 2003 - 06:20 GMT - Peter Thoeny |
Copyright © 1999-2004 by the contributing authors.
All material on this collaboration platform is the property of the contributing authors. Ideas, requests, problems regarding jEdit Community Wiki? Send feedback. |