http://www.dokuwiki.org/plugin:tablecalc

tablecalc plugin


plugin


description: Adds ability to use Excel-style formulas in tables author : Gryaznov Sergey email : stalker@os2.ru type : syntax lastupdate : 2010-04-14 compatible : depends : conflicts : similar : tablemath tags : tables calculation math


Sometimes one may need to do simple calculations in DokuWiki table. The idea was taken from tablemath plugin, but conception changed a little bit. With the help of this plugin you would be able to insert Excel styles formulas into the table

Download and install the plugin using the Plugin Manager using the following URL. Refer to Plugins on how to install plugins manually.

To perform a calculation you need to insert XL-style formula into work sheet. Any expression must be started with ~~= and finished by ~~. You can use direct range specification for functions (like XL does) or a special range() function. The range consists of a reference to the start cell and to the finish cell, like this:

r0c4

Please note, that row (r) and column (c) index starts from zero. Row and column prefixes can be swapped. The following is equal of the above:

c4r0

You can also reference to multiple cells in one range:

|r0c0:r1c1||

Furthermore you can use multiple ranges:

r0c0:r1c1,r0c3:r1c4

There is a recommendation not to use references for non-existing cells. For example, this is not correct (though it will work, returning “3”):

| 1 |
| 2 |
| ~~=sum(r0c0:r99c99)~~ |

Instead use constructions like this:

| 1 |
| 2 |
| ~~=sum(range(0,0,col(),row()-1))~~ |

The following functions are implemented:

Func Description
cell(x;y) Returns numeric value of (x,y) cell
row() Returns current row
col() Returns current column
range(x1;y1;x2;y2) Returns internal range for other functions
sum(range) Returns sum of the specified range
count(range) Returns number of elements in the specified range
round(number;decimals) Returns number, rounded to specified decimals
label(string) Binds label to the table
average(range) Returns average of the specified range
min(range) Returns minimum value within specified range
max(range) Returns minimum value within specified range
check(condition;true;false) Executes true statement, if condition is not zero
compare(a;b;operation) Do math compare for a and b. Returns zero when conditions for the operation doesn't met

Though you can use colon as delimiter in functions semi-colon is preferred and recommended.

| 1 | 2 | ~~=r0c0+r0c1~~ | ~~=10.2+1.5~~ |
1 2 3 11.7
| 1 | 2 |
| 3 | 4 |
| ~~=sum(r0c0:r1c1)~~ ||
1 2
3 4
10
| 1 | 2 | 3 | 4 |
| 5 | 6 | 7 | 8 |
| **~~=sum(r0c0:r1c1,r0c3:r1c4)~~** ||||
1 2 3 4
5 6 7 8
26
|1|
|2|
|3|   
|4|   
|5.74|
|6|
|7|   
|8|   
|9|   
|10|    
|11|   
|~~=sum(range(col(),0,col(),row()-1))~~|
1
2
3
4
5.74
6
7
8
9
10
11
65.74
|1|
|2|
|3|   
|4|   
|5|
|6|
|7|   
|8|   
|9|   
|10|    
|~~=average(range(col(),0,col(),row()-1))~~|
1
2
3
4
5
6
7
8
9
10
5.5
| ~~=label(ex6_1)~~1 | 2 |
| 3 | 4 |

Sum: **~~=sum(ex6_1.c0r0:c99r99)~~**
1 2
3 4

Sum: 10

| **~~=label(ex7_1)~~11** | ~~=sum(ex7_2.c0r0:c99r99)~~ |
| 13 | 14 |

| ~~=label(ex7_2)~~1 | 2 |
| 3 | 4 |

Sum: **~~=sum(ex7_1.c0r0:c1r1)~~**
11 10
13 14
1 2
3 4

Sum: 48

| **~~=min(c0r1:c0r3)~~** | **~~=max(c1r1:c1r3)~~** |
| 1 | 7 |
| 2 | 8 |
| 3 | 9 |

~~=calc()~~
1 9
1 7
2 8
3 9
| 1 | ~~=check(cell(0,row()),#True,#False)~~ |
| 0 | ~~=check(cell(0,row()),#True,#False)~~ |
| x | ~~=check(cell(0,row()),#True,#False)~~ |
|   | ~~=check(cell(0,row()),#True,#False)~~ |
| **** | ~~=check(cell(0,row()),#True,#False)~~ |
1 True
0 False
x False
False
False
| 1 | 2 | 1=2 | ~~=check(compare(cell(0,row()),cell(1,row()),#=),#True,#False)~~ |
| 3 | 3 | 3=3 | ~~=check(compare(cell(0,row()),cell(1,row()),#=),#True,#False)~~ |
| 4 | 5 | 4<5 | ~~=check(compare(cell(0,row()),cell(1,row()),#<),#True,#False)~~ |
| 6 | 7 | 6>7 | ~~=check(compare(cell(0,row()),cell(1,row()),#>),#True,#False)~~ |
| 8 | 9 | 8>9 | ~~=check(compare(cell(0,row()),cell(1,row()),#>),#True,#False)~~ |
| 10 | 10 | 10≥10 | ~~=check(compare(cell(0,row()),cell(1,row()),#>=),#True,#False)~~ |
| 11 | 11 | 11≤11 | ~~=check(compare(cell(0,row()),cell(1,row()),#>=),#True,#False)~~ |
| 12 | 12 | 12≠12 | ~~=check(compare(cell(0,row()),cell(1,row()),#!=),#True,#False)~~ |
1 2 1=2 False
3 3 3=3 True
4 5 4<5 True
6 7 6>7 False
8 9 8>9 False
10 10 10≥10 True
11 11 11≤11 True
12 12 12≠12 False

14.04.2010

  • Added labels and cross-table references
  • Added cross-table resolver and forward calculations
  • Added min(),max() and average() functions
  • Added ability to use semi-colon as a function parameters separator
  • Added comparation functions
  • Added string escaping (#)
  • Fixed javascript/CPU float point calculation bug
  • Fixed invalid HTML ID's usage</todo>

07.09.2009

  • Initial release
very nice indeed, I need to borrow your syntax a bit, so I don't need to use ~~tm: James Lin08/09/2009
Some coding advice: You're working with a blacklist to avoid script inclusion, a whitelist might be more secure. You're using the output of rand() as a HTML ID. Numbers alone are not allowed as IDs in XHTML, you should prefix them with your plugin name. Also have a look at using_idsAndreas Gohr 2009/09/09 15:42
The plugin definitely needs max/min and conditional functions and/or :)
Fixed all of the above — Gryaznov Sergey 2010/04/14

Can you add support for comma as decimal separator? madenate 2010/06/25

  • wiki/plugins/excel.txt
  • Last modified: 2012/03/29 04:10
  • (external edit)