TWiki Variables » Search » Category: Tables & Spreadsheets

CALCULATE{"formula"} -- handle spreadsheet calculations outside tables

• The %CALCULATE{formula}% variable is handled by the SpreadSheetPlugin. Over 100 functions are available, such as $ABS(), $EXACT(), $EXISTS(), $GET()/$SET(), $IF(), $LOG(), $LOWER(), $PERCENTILE(), $TIME(), $VALUE().
• Syntax: %CALC{formula}%
• Examples:
  • %CALC{$EXISTS(Web.SomeTopic)}% returns 1 if the topic exists
  • %CALC{$UPPER(Collaboration)}% returns COLLABORATION
• Note: The CALCULATE variable is handled inside-out & left-to-right like ordinary TWiki variables, but it does not support functions that refer to table cells, such as $LEFT() or $T(). Use CALC instead.
• Category: DevelopmentVariables, TablesAndSpreadsheetsVariables
• Related: CALC, IF, IfStatements, SpreadSheetPlugin (this topic)

TWIKISHEET{} -- enable TWiki Sheet (spreadsheet) and set options

• The %TWIKISHEET{}% variable is handled by the TWikiSheetPlugin
• Syntax: %TWIKISHEET{ parameters }%
• Supported parameters:

  Parameter	Description	Example	Default
  mode	Mode of operation: â\x{fffd}¢ mode="classic" - regular TWiki table and an edit button; once pressed, the table switches into spreadsheet edit mode. â\x{fffd}¢ mode="toggle" - spreadsheet in read-only mode and an edit button; once pressed, the table switches into spreadsheet edit mode. â\x{fffd}¢ mode="toggle-edit" - like "toggle" but initial state is spreadsheet edit mode. â\x{fffd}¢ mode="edit" - the table is always in spreadsheet edit mode.	mode="edit"	{Plugins} {TWikiSheetPlugin} {Mode} 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. â\x{fffd}¢ concurrent="0" - disable concurrent editing; changes by others will be shown after a page reload. â\x{fffd}¢ concurrent="1" - enable concurrent editing; changes by others will show up while editing TWiki Sheet.	concurrent="1"	{Plugins} {TWikiSheetPlugin} {ConcurrentEdit} 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: â\x{fffd}¢ Number value: Specify the number, such as width="500" â\x{fffd}¢ String value: Enclose the string in single quotes, such as preventOverflow="'horizontal'" â\x{fffd}¢ Boolean value: Specify "true" or "false", such as manualRowResize="true" â\x{fffd}¢ Array value: Specify the array, such as manualRowResize="[40, 50]" â\x{fffd}¢ Object value: Specify the object, such as columnSorting="{ column: 2, sortOrder: true }" â\x{fffd}¢ Function: Specify the JavaScript function, such as:    cells="function( row, col, prop ) { var cp = {}; if( row===0 ) { cp.readOnly = true; } return cp; }"

• Example:

  %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) |

• Category: DatabaseAndFormsVariables, EditingAndContentUpdateVariables, FormattingAndRenderingVariables, TablesAndSpreadsheetsVariables
• Related: See TWikiSheetPlugin for more details (this topic)

Total: 2 variables

Related Topics: TWikiVariables, TWikiVariablesSearch, TWikiVariablesQuickStart