=SUM(A2:A6)
can be added to cells. A cell range can be selected, copied and pasted between TWiki Sheet, Excel and Google Sheets. Changes to cells are saved automatically in the background, e.g. there is no explicit save step. Concurrent editing can be enabled, e.g. changes by others will magically show up in TWiki Sheet.
%CALC{$SUM($ABOVE())}%
. The syntax is TWiki specific, and the function are evaluated server side. We recommend using the SpreadSheetPlugin for TWiki applications that deliver ready-made tables.
2. The pre-installed EditTablePlugin offers table editing for TWiki tables using a conventional edit, modify, save sequence. This is done with HTML forms, e.g. has the look and feel of a web application. The EditTablePlugin does not offer spreadsheet functions, however, it is possible to add SpreadSheetPlugin functions.
We recommend using TWiki Sheet if you want to easily work with spreadsheets in TWiki, as you would in Excel and Google Sheets.
%TWIKISHEET{ }% | | 16Q1 | 16Q2 | 16Q3 | | East: | 163 | 182 | 208 | | Center: | 82 | 97 | 126 | | West: | 217 | 231 | 294 | | Total: | =SUM(B2:B4) | =SUM(C2:C4) | =SUM(D2:D4) |
%TWIKISHEET{ }%
before a TWiki table to turn that table into a TWiki Sheet, as can be seen to the right. Multiple tables in a TWiki topic can be changed into TWiki Sheets.
A TWiki Sheet can be in edit mode by default (as in Excel or Google Sheets), or can be set to toggle between view mode and edit mode.
The user interface of TWiki Sheet is done client side with a Javascript widget; for details see the technical detail section below.
%TWIKISHEET{ }%
variable supports the following parameters:
Parameter | Description | Example | Default | |
---|---|---|---|---|
mode |
Mode of operation: • mode="classic" - regular TWiki table and an edit button; once pressed, the table switches into spreadsheet edit mode. • mode="toggle" - spreadsheet in read-only mode and an edit button; once pressed, the table switches into spreadsheet edit mode. • mode="toggle-edit" - like "toggle" but initial state is spreadsheet edit mode. • mode="edit" - the table is always in spreadsheet edit mode. |
mode="edit" |
{Plugins} configure setting ( "toggle" ) |
|
concurrent |
Concurrent editing. If enabled, multiple people can edit TWiki Sheet and see each other's changes. This includes editing cells, pasting a range of cells, drag-filling cells, adding/removing rows, adding/removing columns, and undo. • concurrent="0" - disable concurrent editing; changes by others will be shown after a page reload. • concurrent="1" - enable concurrent editing; changes by others will show up while editing TWiki Sheet. |
concurrent="1" |
{Plugins} configure setting ( "0" ) |
|
save |
Optionally disable save; useful to demo the plugin | save="0" |
save="1" |
|
width |
Table width in pixels; a horizontal scrollbar is added if needed | width="500" |
(full window width) | |
height |
Table height in pixels; a vertical scrollbar is added if needed | height="300" |
(full table height) | |
colHeaders |
Set to "false" to disable the default column headers (A, B, C); set to a function for custom headers, such as:colHeaders="function(index) { return String.fromCharCode(65+index) + ' (' + (index + 1) + ')'; }" , which will show headers A (1) , B (2) , C (3) , ... |
colHeaders="false" |
colHeaders="true" |
|
rowHeaders |
Set to "false" to disable the default row headers (1, 2, 3) |
rowHeaders="false" |
rowHeaders="true" |
|
contextMenu |
Defines the right-click context menu; set to "false" to disable; set to array of available strings, such as:contextMenu="['row_above', 'row_below', 'col_left', 'col_right', 'remove_row', 'remove_col', '---------', 'undo', 'redo']" |
contextMenu="false" |
contextMenu="true" |
|
fixedRowsTop |
Fixed number of rows shown on top; takes effect if height parameter is used |
fixedRowsTop="1" |
(none) | |
fixedColumnsLeft |
Fixed number of columns shown on the left; takes effect if width parameter is used |
fixedColumnsLeft="2" |
(none) | |
formulas |
Formula support; set to "false" to disable spreadsheet calculations in table cells, such as: =SUM(A1:A8) |
formulas="false" |
formulas="true" |
|
maxCols |
Maximum number of columns | maxCols="10" |
(unlimited) | |
maxRows |
Maximum number of rows | maxRows="1000" |
(unlimited) | |
minSpareCols |
When set to 1 (or more), automatically add a new column at the right if there are no more empty columns | minSpareCols="1" |
minSpareCols="0" |
|
minSpareRows |
When set to 1 (or more), automatically add a new row at the bottom if there are no more empty rows | minSpareRows="1" |
minSpareRows="0" |
|
wordWrap |
Word wrap; set to "false" to disable cell content wrapping if it does not fit in the fixed column width |
wordWrap="false" |
wordWrap="true" |
|
more... | Additional Handsontable options can be used. Notes on types of values: • Number value: Specify the number, such as width="500" • String value: Enclose the string in single quotes, such as preventOverflow="'horizontal'" • Boolean value: Specify "true" or "false" , such as manualRowResize="true" • Array value: Specify the array, such as manualRowResize="[40, 50]" • Object value: Specify the object, such as columnSorting="{ column: 2, sortOrder: true }" • Function: Specify the JavaScript function, such as: cells="function( row, col, prop ) { var cp = {}; if( row===0 ) { cp.readOnly = true; } return cp; }" |
=
equal sign indicates a formula cell, such as =SUM(B2:B10)
=
equal sign and add a formula like you would in Excel or Google Sheets.
Many functions are available, see http://handsontable.github.io/ruleJS/
Formulas are considered alpha at this time; for example, cell ranges are not adjusted on copy & paste.
mode=""
parameter:
mode="classic"
- classic TWiki mode: mode="toggle"
- toggle mode: mode="toggle-edit"
- toggle-edit mode: "toggle"
mode, but initial state is TWiki Sheet edit mode.
mode="edit"
- always on mode: %WIKINAME%
are not expanded in TWiki Sheet. That this, they are preserved properly, but you can't see the expanded value unless mode="classic"
is used.
{Plugins}{TWikiSheetPlugin}{Mode}
configure setting, which is initially set to "classic"
.
concurrent=""
parameter:
concurrent="0"
- disable concurrent editing; changes by others will be shown after a page reload.
concurrent="1"
- enable concurrent editing; changes by others will show up while editing TWiki Sheet.
{Plugins}{TWikiSheetPlugin}{ConcurrentEdit}
configure setting, which is initially set to "0"
.
{Plugins}{TWikiSheetPlugin}{ConcurrentEditRefresh}
configure setting, which is initially set to "10"
seconds.
Raw text:
%TWIKISHEET{ mode="edit" save="0" }% | | 16Q1 | 16Q2 | 16Q3 | 16Q4 | | East: | 163 | 182 | 208 | 193 | | Center: | 82 | 97 | 126 | 91 | | West: | 217 | 231 | 294 | 249 | | Total: | =SUM(B2:B4) | =SUM(C2:C4) | =SUM(D2:D4) | =SUM(E2:E4) | | Min: | =MIN(B2:B4) | =MIN(C2:C4) | =MIN(D2:D4) | =MIN(E2:E4) | | Max: | =MAX(B2:B4) | =MAX(C2:C4) | =MAX(D2:D4) | =MAX(E2:E4) | |
Renders as TWiki Sheet (if installed):
|
Screenshot:
|
Raw text:
%TWIKISHEET{ mode="toggle" save="0" }% | | 16Q1 | 16Q2 | 16Q3 | 16Q4 | | East: | 163 | 182 | 208 | 193 | | Center: | 82 | 97 | 126 | 91 | | West: | 217 | 231 | 294 | 249 | | Total: | =SUM(B2:B4) | =SUM(C2:C4) | =SUM(D2:D4) | =SUM(E2:E4) | | Min: | =MIN(B2:B4) | =MIN(C2:C4) | =MIN(D2:D4) | =MIN(E2:E4) | | Max: | =MAX(B2:B4) | =MAX(C2:C4) | =MAX(D2:D4) | =MAX(E2:E4) | |
Renders as TWiki Sheet (if installed):
|
Screenshot:
|
Raw text:
%TWIKISHEET{ mode="classic" save="0" }% | | 16Q1 | 16Q2 | 16Q3 | 16Q4 | | East: | 163 | 182 | 208 | 193 | | Center: | 82 | 97 | 126 | 91 | | West: | 217 | 231 | 294 | 249 | | Names: | WEB | TOPIC | BULLET | WIKINAME | | Variables: | %WEB% | %TOPIC% | %BULLET% | %WIKINAME% | |
Renders as TWiki table or TWiki Sheet (if installed):
|
Screenshot:
|
mode="classic"
is used. That this, they are preserved as is, but that means they do not show the actual value.
2. Cell span and row span are not supported.
3. Formulas are considered alpha at this time. For example, cell ranges are not adjusted on copy & paste.
var tableData = [ ["", "Maserati", "Mazda", "Mercedes", "Mini"], [2013, 2941, 4303, 3154, 2814], [2014, 2905, 2867, 4128, 2195], [2015, 2517, 4822, 3552, 3127], [2016, 2174, 3184, 3912, 2651] ];A Handsontable is initialized as follows:
<script src="/common/handsontable/dist/handsontable.full.js"></script> <link href="/common/handsontable/dist/handsontable.full.css" rel="stylesheet" media="screen" /> <div id="tableContainer"></div> <script> var container = $( '#tableContainer' ); container.handsontable({ data: tableData, rowHeaders: true, colHeaders: true, contextMenu: true, manualColumnResize: true, afterChange: function( changes, source ) { // save changes to server } }); </script>A
%TWIKISHEET{}%
followed by a TWiki table is converted to an array-of-an-array JavaScript object, which is used to initialize a Handsontable table.
On each cell change, an Ajax POST is initiated that calls the REST API of the TWikiSheetPlugin on the TWiki server. Here is the code snippet that does the Ajax call:
function twSheetAfterChange( n, changes, source ) { if( changes ) { var tws = twSheet[n]; if( $authenticated && tws.save ) { var sendData = { action: 'change', webTopic: tws.webTopic, tableNumber: n, tableData: JSON.stringify(tws.data), changes: JSON.stringify(changes) } var jqxhr = $.ajax({ url: '/do/rest/TWikiSheetPlugin/save', method: 'POST', data: sendData }) .done(function( result ) { console.log( '- save ok: ' + JSON.stringify( result, null, ' ') ); }) .fail(function() { alert( 'TWiki Sheet Error: Failed to save changes' ); }); } } }The
rest/TWikiSheetPlugin/save
API takes the table data, and updates the TWiki topic if the user has permission.
Note to plugin maintainer: The formula code has a bug that prevents the fill-handle from functioning properly. Patch to fix:
--- pub/TWiki/TWikiSheetPlugin/handsontable-ruleJS/src/handsontable.formula.js (revision 30103) +++ pub/TWiki/TWikiSheetPlugin/handsontable-ruleJS/src/handsontable.formula.js (working copy) @@ -130,7 +130,7 @@ var cellId = instance.plugin.utils.translateCellCoords({row: row, col: col}); // if changed value, all references cells should be recalculated - if (value[0] !== '=' || prevValue !== value) { + if (value && (value[0] !== '=' || prevValue !== value)) { instance.plugin.matrix.removeItem(cellId); // get referenced cells @@ -155,13 +155,20 @@ var instance = this; var r = index.row, - c = index.col, - value = data[r][c], + c = index.col; + if( !data || !data[r] || !data[r][c]) { + return { + value: value, + iterators: iterators + }; + } + + var value = data[r][c], delta = 0, rlength = data.length, // rows clength = data ? data[0].length : 0; //cols - if (value[0] === '=') { // formula + if (value && value[0] === '=') { // formula if (['down', 'up'].indexOf(direction) !== -1) { delta = rlength * iterators.row;
TWikiSheetPlugin.zip
in your twiki installation directory. Content: File: | Description: |
---|---|
data/TWiki/TWikiSheetPlugin.txt | Plugin topic |
data/TWiki/VarTWIKISHEET.txt | Variable documentation topic |
lib/TWiki/Plugins/TWikiSheetPlugin.pm | Plugin Perl module |
lib/TWiki/Plugins/TWikiSheetPlugin/Config.spec | Configuration spec file |
lib/TWiki/Plugins/TWikiSheetPlugin/Core.pm | Plugin core module |
pub/TWiki/TWikiSheetPlugin/handsontable/ | Directory with Handsontable JavaScript widget |
pub/TWiki/TWikiSheetPlugin/handsontable-ruleJS/ | Directory with Handsontable plugin |
pub/TWiki/TWikiSheetPlugin/ruleJS/ | Directory with RuleJS JavaScript library |
pub/TWiki/TWikiSheetPlugin/screenshot*.png | Image files |
pub/TWiki/TWikiSheetPlugin/twSheet.css | Plugin style sheet |
%TWIKISHEET{}%
. Change cells, then reload the page to verify that the changes were saved.
Author: | TWiki:Main.PeterThoeny, TWiki.org |
Copyright: | © 2016-2019 TWiki:Main.PeterThoeny © 2016-2019 TWiki:TWiki.TWikiContributor |
License: | GPL (GNU General Public License) |
Handsontable: | Version: 0.24.1; License: MIT; Copyright: © 2012-2014 Marcin Warpechowskiy; © 2015 Handsoncode sp. z o.o., hello@handsoncode[dot]net |
RuleJS: | Version: 0.0.3; License: MIT; Copyright: © 2012-2014 Marcin Warpechowskiy, hello@handsoncode[dot]net |
Jison: | Version: 0.4.15; License: MIT; Copyright: © 2009-2014 Zachary Carter |
formula.js: | Version: 1.0.5; License: MIT & ASF; Copyright: © 2014 Sutoiku, Inc. |
Version: | 2019-09-25 |
2019-09-25: | TWikibug:Item7851: Sanitize input & fix taint issue on CentOS 7.6 |
2019-05-04: | TWikibug:Item7861: Warn user if JSON module is missing |
2018-07-15: | TWikibug:Item7845: Add missing screenshots to manifest; set default mode to classic |
2018-07-06: | TWikibug:Item7842: Update HTML table after edit session in classic mode; Item7841: Copyright update to 2018 |
2016-05-13: | TWikibug:Item7737: Document JSON module dependency |
2016-04-17: | TWikibug:Item7737: The same user can now concurrent-edit the same TWiki Sheet in multiple windows |
2016-04-14: | TWikibug:Item7737: Concurrent editing support - see each others changes while editing TWiki Sheet |
2016-04-09: | TWikibug:Item7737: Aggregate changes to a moving time window of 500ms - this is to prevent saving changes out of sync due to network timing issues, which would cause corrupted tables; fix another formula.js bug to prevent a crash on undo |
2016-04-06: | TWikibug:Item7737: Support Handsontable options to customize the TWiki Sheet; fix VBAR and BR issue |
2016-04-02: | TWikibug:Item7737: Add modes of operation: "classic" , "toggle" , "toggle-edit" and "edit" ; preserve TWiki variables; support TWiki Sheets in included topics; fix formula.js bug that prevented fill-handle from functioning properly |
2016-03-30: | TWikibug:Item7737: Protect VBAR and BR by converting them to vertical bar and \n |
2016-03-29: | TWikibug:Item7737: Initial version of TWikiSheetPlugin |
TWiki Dependency: | $TWiki::Plugins::VERSION 1.2 |
CPAN Dependencies: | CPAN:JSON >= 2.0 |
Other Dependencies: | JavaScript libraries: Handsontable (included); RuleJS (included); formula.js (included) |
Perl Version: | 5.008 |
Plugin Benchmark: | GoodStyle nn%, FormattedSearch nn%, TWikiSheetPlugin nn% |
Home: | http://TWiki.org/cgi-bin/view/Plugins/TWikiSheetPlugin |
Feedback: | http://TWiki.org/cgi-bin/view/Plugins/TWikiSheetPluginDev |
Appraisal: | http://TWiki.org/cgi-bin/view/Plugins/TWikiSheetPluginAppraisal |