DokuWiki

It's better when it's simple

User Tools

Site Tools


plugin:tablecalc

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision
Previous revision
plugin:tablecalc [2018-04-11 10:41] – [ChangeLog] stalkerplugin:tablecalc [2024-01-21 20:13] (current) – [III] fix typo Aleksandr
Line 6: Line 6:
 email      : stalker@narezka.org email      : stalker@narezka.org
 type       : syntax type       : syntax
-lastupdate : 2017-02-09 +lastupdate : 2020-08-27 
-compatible : Lemming, Anteater, Rincewind, Hrun, Detritus, Elenor of Tsort, Frusterick Manners+compatible : Lemming, Anteater, Rincewind, Hrun, Detritus, Elenor of Tsort, Frusterick Manners, Greebo, Hogfather, Igor, Jack Jackrum
 depends    :  depends    : 
 conflicts  conflicts 
Line 14: Line 14:
  
 downloadurl: https://narezka.org/cfd/msgdb/740/tablecalc.zip downloadurl: https://narezka.org/cfd/msgdb/740/tablecalc.zip
 +bugtracker : 
 +sourcerepo : 
 +donationurl: 
 +
 +screenshot_img: 
 ---- ----
  
 ===== Overview ===== ===== Overview =====
-Sometimes one may need to do simple calculations in DokuWiki table. The idea was taken from [[plugin: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+ 
 +Sometimes one may need to do simple calculations in DokuWiki table. The idea was taken from [[plugin:Tablemath]] Plugin, but conception changed a little bit. With the help of this plugin you would be able to insert [[wp>Microsoft Excel|Microsoft Excel (XL)]] styles formulas into the table
  
 ===== Download and Installation ===== ===== Download and Installation =====
  
-Download and install the plugin using the [[plugin:plugin|Plugin Manager]] using the following URL. Refer to [[:Plugins]] on how to install plugins manually.+Search and install the plugin using the [[plugin:extension|Extension Manager]]. Refer to [[:Plugins]] on how to install plugins manually.
  
   * [[http://narezka.org/cfd/msgdb/740/tablecalc.zip|TableCalc.zip]]   * [[http://narezka.org/cfd/msgdb/740/tablecalc.zip|TableCalc.zip]]
- 
  
 ===== Syntax ===== ===== Syntax =====
  
-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:+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:
 <code> <code>
 r0c4 r0c4
 </code> </code>
-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:+ 
 +:!: Please note, that row (''r'') and column (''c'') index starts from **zero** (''0''). 
 + 
 +^    0      1      2     ^ 
 +^  0  |  r0c0  |  r0c1  |  r0c2  | 
 +^  1  |  r1c0  |  r1c1  |  r1c2  | 
 +^  2  |  r2c0  |  r2c1  |  r2c2  | 
 + 
 +Row and column prefixes can be swapped. The following is equal of the above:
 <code> <code>
 c4r0 c4r0
Line 65: Line 78:
 The following functions are implemented: The following functions are implemented:
  
-''x'' = column, ''y'' = row +^ Func   Description  
- +| average(range)  | Returns average of the specified range  
-^ Func                                      Description                                                                         ^ +cell(column,row | Returns numeric value of (column,rowcell 
-| cell(x,y)                    | Returns numeric value of (x,y) cell                                                              | +| check(condition,true,false) | Executes ''true'' statement, if ''condition'' is not zero 
-| row()                        | Returns current row                                                                              | +| col() | Returns current column  
-| col()                        | Returns current column                                                                           | +| compare(a,b,operation)  | Do math compare for ''a'' and ''b''. Returns zero when conditions for the ''operation'' are not met 
-| range(x1,y1,x2,y2)           | Returns internal range for other functions                                                       | +| count(range)  | Returns number of elements in the specified range 
-| sum(range)                   | Returns sum of the specified range                                                               | +| countif(range,b,operation)  | Count values in ''range''. Counts only such values from ''range'' which meets comparation with ''b''. The comparation type is specified in ''operation''  | 
-| count(range)                 | Returns number of elements in the specified range                                                | +| label(string) | Binds label to the table  | 
-| round(number;decimals)       | Returns number, rounded to specified decimals                                                    | +| max(range)  | Returns minimum value within specified range  | 
-| label(string)                | Binds label to the table                                                                         | +| min(range)  | Returns minimum value within specified range  | 
-| average(range)               | Returns average of the specified range                                                           +| range(column1,row1,column2,row2)  | Returns internal range for other functions 
-min(range                  | Returns minimum value within specified range                                                     | +| round(number,decimals)  | Returns number, rounded to specified decimals | 
-| max(range                  | Returns minimum value within specified range                                                     +| row() | Returns current row | 
-| check(condition;true;false)  | Executes ''true'' statement, if ''condition'' is not zero                                        +| sum(range)  | Returns sum of the specified range  
-| compare(a;b;operation)       | Do math compare for ''a'' and ''b''. Returns zero when conditions for the ''operation'' are not met  | +| calc()  | Depreciated. Not needed anymore |
-| countif(range;b;operation)   | Count values in ''range''. Counts only such values from ''range'' which meets comparation with ''b''. The comparation type is specified in ''operation''+
-| calc()                       | Depreciated. Not needed anymore  |+
  
 Though you can use colon as delimiter in functions semi-colon is preferred and recommended. Though you can use colon as delimiter in functions semi-colon is preferred and recommended.
- 
  
 ===== Operators ===== ===== Operators =====
Line 92: Line 102:
  
 ^  Operator  ^  Description  ^ ^  Operator  ^  Description  ^
-|  +         | Addition and unary plus | +|  +         | Addition and unary plus  
-|  -         | Subtraction and unary negative | +|  -         | Subtraction and unary negative  
-|  *         | Multiplication | +|  *         | Multiplication  | 
-|  /         | Division | +|  %%**%%     | Exponentiation  
-|  %         | Modulus (division remainder) | +|  /         | Division  
-|  &         | Logical AND | +|  %         | Modulus (division remainder)  
-|  %%<<%%    | Shift left | +|  &         | Logical AND  
-|  %%>>%%    | Shift right |+|  %%<<%%    | Shift left  
 +|  %%>>%%    | Shift right  | 
 + 
 +===== Examples =====
  
-=====Examples===== 
  
 ==== I ==== ==== I ====
Line 110: Line 122:
  
 | 1  | 2  | 3  | 11.7  | | 1  | 2  | 3  | 11.7  |
 +
  
 ==== II ==== ==== II ====
 +
 <code> <code>
 | 1 | 2 | | 1 | 2 |
Line 121: Line 135:
 | 3 | 4 | | 3 | 4 |
 | 10 || | 10 ||
- 
  
  
 ==== III ==== ==== III ====
 +
 <code> <code>
 | 1 | 2 | 3 | 4 | | 1 | 2 | 3 | 4 |
 | 5 | 6 | 7 | 8 | | 5 | 6 | 7 | 8 |
-| **~~=sum(r0c0:r1c1,r0c3:r1c4)~~** ||||+ **~~=sum(r0c0:r1c1,r0c3:r1c3)~~**  ||||
 </code> </code>
  
Line 137: Line 151:
  
 ==== IV ==== ==== IV ====
 +
 <code> <code>
 |1| |1|
Line 169: Line 184:
  
 ==== V ==== ==== V ====
 +
 <code> <code>
 |1| |1|
Line 195: Line 211:
 |11|     |11|    
 |5.5| |5.5|
 +
  
 ==== VI ==== ==== VI ====
 +
 <code> <code>
 | ~~=label(ex6_1)~~1 | 2 | | ~~=label(ex6_1)~~1 | 2 |
Line 209: Line 227:
  
 Sum: **10** Sum: **10**
 +
  
 ==== VII ==== ==== VII ====
 +
 <code> <code>
 | **~~=label(ex7_1)~~11** | ~~=sum(ex7_2.c0r0:c99r99)~~ | | **~~=label(ex7_1)~~11** | ~~=sum(ex7_2.c0r0:c99r99)~~ |
Line 229: Line 249:
  
 Sum: **48** Sum: **48**
 +
  
 ==== VIII ==== ==== VIII ====
 +
 <code> <code>
 | **~~=min(c0r1:c0r3)~~** | **~~=max(c1r1:c1r3)~~** | | **~~=min(c0r1:c0r3)~~** | **~~=max(c1r1:c1r3)~~** |
Line 245: Line 267:
  
 ==== IX ==== ==== IX ====
 +
 <code> <code>
 | 1 | ~~=check(cell(0,row()),#True,#False)~~ | | 1 | ~~=check(cell(0,row()),#True,#False)~~ |
Line 253: Line 276:
 </code> </code>
  
-| 1 | True | +| 1  | True   
-| 0 | False | +| 0  | False  
-| x | False | +| x  | False  
- | False | +   | False  
- | False |+   | False  |
  
  
 ==== X ==== ==== X ====
 +
 <code> <code>
 | 1 | 2 | 1=2 | ~~=check(compare(cell(0,row()),cell(1,row()),#=),#True,#False)~~ | | 1 | 2 | 1=2 | ~~=check(compare(cell(0,row()),cell(1,row()),#=),#True,#False)~~ |
Line 280: Line 304:
 | 11 | 11 | 11≤11 | True | | 11 | 11 | 11≤11 | True |
 | 12 | 12 | 12≠12 | False | | 12 | 12 | 12≠12 | False |
 +
  
 ==== XI ==== ==== XI ====
  
-^  Operator  ^  Equation         ^ Result ^ +^  Operator  ^  Equation          ^ Result ^ 
-|  +         | %%~~= 3 + 2 ~~%%  |  5  | +|  +         | %%~~= 3 + 2 ~~%%   |  5  | 
-|  -         | %%~~= 3 - 2 ~~%%  |  1  | +|  -         | %%~~= 3 - 2 ~~%%   |  1  | 
-|  *         | %%~~= 3 * 2 ~~%%  |  6  | +|  *         | %%~~= 3 * 2 ~~%%   |  6  | 
-|  /         | %%~~= 6 / 2 ~~%%  |  3  | +|  %%**%%    | %%~~= 3 ** 2 ~~%%  |  9  | 
-|  %         | %%~~= 7 % 2 ~~%%  |  1  | +|  /         | %%~~= 6 / 2 ~~%%   |  3  | 
-|  unary +   | %%~~= +2 ~~%%     |  2  | +|  %         | %%~~= 7 % 2 ~~%%   |  1  | 
-|  unary -   | %%~~= -2 ~~%%     |  -2  | +|  unary +   | %%~~= +2 ~~%%      |  2  | 
-|  &         | %%~~= 3 & 2 ~~%%  |  2  | +|  unary -   | %%~~= -2 ~~%%      |  -2  | 
-|  %%<<%%    | %%~~= 2 << 7 ~~%% |  256  | +|  &         | %%~~= 3 & 2 ~~%%   |  2  | 
-|  %%>>%%    | %%~~= 8 >> 2 ~~%% |  2  |+|  %%<<%%    | %%~~= 2 << 7 ~~%%  |  256  | 
 +|  %%>>%%    | %%~~= 8 >> 2 ~~%%  |  2  | 
  
 ==== XII ==== ==== XII ====
Line 300: Line 327:
 |~~=r0c1+5~~|6| |~~=r0c1+5~~|6|
 </code> </code>
 +
 | 11 | 6 | | 11 | 6 |
 +
  
 ==== XIII ==== ==== XIII ====
 +
 <code> <code>
 | ~~=label(ex13_1)~~apples | 32 | | ~~=label(ex13_1)~~apples | 32 |
Line 318: Line 348:
  
 Apples: 2 Apples: 2
 +
 +
 ===== ChangeLog ===== ===== ChangeLog =====
-__11.04.2017__ 
  
-    * Fixed php-7 compatibility issues +__26.08.2020__ 
-    * Added plugin.info.txt +  * Fixed Hogfather compatibility issues 
-    * Fixed cross references between tables+ 
 + 
 +__11.04.2018__ 
 + 
 +  * Fixed php-7 compatibility issues 
 +  * Added plugin.info.txt 
 +  * Fixed cross references between tables
  
 __05.07.2017__ __05.07.2017__
Line 331: Line 368:
 __09.02.2017__ __09.02.2017__
  
-    * Added countif() function+  * Added countif() function
  
 __31.01.2017__ __31.01.2017__
  
-    * Fixed min(),max(),round() functions as suggested +  * Fixed min(),max(),round() functions as suggested 
-    * Plugin execution code was significantly revisioned and corrected+  * Plugin execution code was significantly revisioned and corrected
  
 __14.04.2010__ __14.04.2010__
  
-    * Added labels and cross-table references +  * Added labels and cross-table references 
-    * Added cross-table resolver and forward calculations +  * Added cross-table resolver and forward calculations 
-    * Added min(),max() and average() functions +  * Added min(),max() and average() functions 
-    * Added ability to use semi-colon as a function parameters separator +  * Added ability to use semi-colon as a function parameters separator 
-    * Added compare functions +  * Added compare functions 
-    * Added string escaping (#) +  * Added string escaping (#) 
-    * Fixed javascript/CPU float point calculation bug +  * Fixed javascript/CPU float point calculation bug 
-    * Fixed invalid HTML ID's usage+  * Fixed invalid HTML ID's usage
  
 __07.09.2009__ __07.09.2009__
-    * Initial release+  * Initial release
  
 ===== Discussion ===== ===== Discussion =====
Line 470: Line 507:
 --- [[fidokomik@gmail.com|fidokomik]] //2013/07/17// --- [[fidokomik@gmail.com|fidokomik]] //2013/07/17//
 ==== Bug report ==== ==== Bug report ====
-=== Plugin creates no output ===+ 
 +=== + Doesn't work in cell reference === 
 +When using + to reference a cell the + doesn't work. 
 +<code> 
 +~~=cell(col(),row()+1)~~ 
 +</code> 
 +Will give nothing back, but using a - will work 
 +<code> 
 +~~=cell(col(),row()-1)~~ 
 +</code> 
 + 
 +--- [[user>shshsh|SH]] //2019/07/08 // 
 + 
 +== Plugin creates no output ===
 None of the formulas outputs results for me (on Anteater). Any known conflicts with other plug-ins? None of the formulas outputs results for me (on Anteater). Any known conflicts with other plug-ins?
  --- [[user>mubed|mubed]] //2012/05/30 14:28//  --- [[user>mubed|mubed]] //2012/05/30 14:28//
Line 576: Line 626:
 --- //[[stalker@narezka.org|Gryaznov Sergey]] 2018/04/11// --- //[[stalker@narezka.org|Gryaznov Sergey]] 2018/04/11//
  
 +>> Above was fixed, but similar bug now appears in Chrome etc. browsers, but not Firefox etc. browsers. 
 + --- [[user>mopani|mopani]] //2022-11-14 16:47//
 +==== Looping ====
 +Unfortunately the new version loops on my page, which is more complex than the example I give above.  I can send it to you privately for debugging, but I do not want to publish it here because it contains private data. --- [[user>pot|pot]] //2018-06-02 14:16//
 +
 +==== Decimals ====
 +
 +>I've seen that the request for comma as a decimal separator is further above, somewhere after that comes a "fixed all of the above". Looks like this isn't part of this, is it?
 +>
 +>Also, what about delimiters in general? Large numbers like 1000000 are easily readable as 1.000.000,00 (or 1,000,000.00 for that matter). But the plugin doesn't really seem a) calculating those values correctly or b) giving a similar output, meaning that the output of 500,000.50+500,000.40 would be 1,000,000.90 instead of 1000000.90. Is there a way to achieve this? Or a possibility to get this supported? :)\\
 +--- [[user>benas|benas]] //2020-09-30 17:45//
 +
 +> The following code in your [[devel:javascript#javascript_loading|/dokuwiki/conf/userscript.js]] will turn all occurences of dots "." into commas "," !
 +>
 +> <code php>   if( jQuery("[id^=__tablecalc]").length > 0 ) {
 +      setTimeout(function() {
 +         jQuery("#dokuwiki__content th").add("#dokuwiki__content td").each(function() {   
 +            var $calc_decimal = jQuery(this).text().split('.').join(',');
 +            jQuery(this).text($calc_decimal);
 +         });
 +      }, 100);
 +   } </code>
 +>Limitations:
 +> ・ at least ONE cell of ANY table on the page needs to be set up with tablecalc syntax: ~~= ... ~~
 +> ・ then ALL dots in ALL tables on the page are converted to commas on pageload (only for visual display)
 +> ・ in page edit mode (or edittable) decimal delimiters are still to be filled in as dots "." (as before), if intended to be used for calculations with tablecalc!
 +> --- [[user>Chris75|Chris75]] //2022-11-18 12:25//
 +
 +==== Incompatibility with Chrome and Brave? ====
 +
 +I have been using TableCalc for months now and haven't had too many problems until recently. When using Chrome or Brave, the tables randomly stop calculating after a few entries and show the wrong results. The second link has an example table. The result should show 446 Total and 0 (zero) Current. This is accurate in Firefox. In Chrome/Brave, I get many various results BUT 446 and 0. Is there a way to fix this?\\
 +
 +https://forum.dokuwiki.org/d/20026-tablecalc-stops-sum-after-too-many-entries\\
 +http://www.shadowsoverthesouth.net/characterwiki/doku.php?id=app:output:test\\
 + --- [[user>Feathers|Feathers]] //2022-03-24 23:07//
 +
 +> There appears to be a javascript problem in Chrome/Brave/Vivaldi/Edge that does not appear in Firefox or PaleMoon (Gecko/Goanna). For example, a table that sums a previous table using a ''label'' does not work in Chrome etc. if there are more than one or two lines in the previous table, but all work fine in Firefox etc. 
 +>
 +> See an example at https://www.metatek.org/test_tablecalc 
 +>
 +> This is the Tablecalc issue that I run into most frequently; I suspect the above is similarly related to the way that Chrome etc. handle the javascript created by Tablecalc pages. 
 +> --- [[user>mopani|mopani]] //2022-11-13 22:59//
 +
 +>That's because as browsers are ever getting faster at loading pages javascript is starting to fall behind. 
 +>PHP can only show you results on the page when Javascript is ready with calculation. Since this is a javascript plugin, some timeout has to be adjusted. The more math work is given to tablecalc, the more timeout it needs to complete the task before the page can be loaded by PHP using those results. Look for this line of code in **/dokuwiki/lib/plugins/tabelcalc/script.js** and set the timeout to 100 milliseconds, like so:
 +>
 +> <code php>    setTimeout(tablecalc_final,100) </code>
 +>Save your file, close and restart your browser and navigate back to the wiki page. Now, it will work in Edge, Chrome, Firefox and IE11. (Tested with DokuWiki-on-a-stick, release Igor, Sprintdoc template, latest version of tablecalc plugin and with the wiki page syntax from the example above.) 
 +>
 +>EDIT: At least part of the above problem seems to be related to using non-existing cells which was discouraged here before. (Probably, because it gives tablecalc just too much mathwork it cannot handle.) I found the following useful for autosum of all of the cells above the current cell (assuming you have exactly one header row): 
 +><code>~~=round(sum(range(col(),1,col(),row()-1)),2)~~ </code>
 +>--- [[user>Chris75|Chris75]] //2022-11-17 21:47//
 +
 +==== Feature requests ====
 +
 +>Feature: label for single cells - similar to label for table 
 +>Application: to make reference to single values more dynamic
 +>
 +>--- [[user>Chris75|Chris75]] //2022-11-17 21:47//
plugin/tablecalc.1523436081.txt.gz · Last modified: 2018-04-11 10:41 by stalker

Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
CC Attribution-Share Alike 4.0 International Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki