TWiki Variables » Search » Category: Tables & Spreadsheets

CALC{"formula"} -- add spreadsheet calculations to tables

• The %CALC{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{$SUM($ABOVE())}% returns the sum of all cells above the current cell
  • %CALC{$EXISTS(Web.SomeTopic)}% returns 1 if the topic exists
  • %CALC{$UPPER(Collaboration)}% returns COLLABORATION
• Note: The CALC variable is handled with delay compared to other TWiki variables. You may get unexpected results if you nest CALC inside other variables (such as %INCLUDE{%CALC{...}%}%) because it does not get evaluated inside-out & left-to-right like ordinary TWiki variables. Use CALCULATE if you nest variables.
• Category: DevelopmentVariables, TablesAndSpreadsheetsVariables
• Related: CALCULATE, IF, IfStatements, SpreadSheetPlugin (this topic)

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)

EDITTABLE{ attributes } -- edit TWiki tables using edit fields and other input fields

• The %EDITTABLE{}% variable is handled by the EditTablePlugin
• Syntax: %EDITTABLE{ attributes }%

• Supported attributes:

  Attribute	Comment	Default
  header	Specify the header format of a new table like "|*Food*|*Drink*|". Useful to start a table with only a button	(no header)
  format	The format of one column when editing the table. A cell can be a text input field, or any of these edit field types: • Text input field (1 line):   | text, <size>, <initial value> | • Textarea input field:   | textarea, <rows>x<columns>, <initial value> | • Drop down box:   | select, <size>, <option 1>, <option 2>, etc* |   * only one item can be selected • Radio buttons:   | radio, <size*>, <option 1>, <option 2>, etc |   * size indicates the number of buttons per line in edit mode • Checkboxes:   | checkbox, <size*>, <option 1>, <option 2>, etc |   * size indicates the number of checkboxes per line in edit mode • Fixed label:   | label, 0, <label text> | • Row number:   | row, <offset> | • Date:   | date, <size>, <initial value>, <DHTML date format*> |   * see Date Field Type	"text, 16" for all cells
  changerows	Rows can be added and removed if "on" Rows can be added but not removed if "add" Rows cannot be added or removed if "off"	CHANGEROWS plugin setting
  quietsave	Quiet Save button is shown if "on", hidden if "off"	QUIETSAVE plugin setting
  include	Other topic defining the EDITTABLE parameters. The first %EDITTABLE% in the topic is used. This is useful if you have many topics with the same table format and you want to update the format in one place.	(none)
  helptopic	Topic name containing help text shown below the table when editing a table. The %STARTINCLUDE% and %STOPINCLUDE% variables can be used in the topic to specify what is shown.	(no help text)
  headerislabel	Table header cells are read-only (labels) if "on"; header cells can be edited if "off" or "0"	"on"
  editbutton	Set edit button text, e.g. "Edit this table"; set button image with alt text, e.g. "Edit table, %PUBURL%/%SYSTEMWEB%/TWikiDocGraphics/edittopic.gif"; hide edit button at the end of the table with "hide" (Note: Button is automatically hidden if an edit button is present in a cell)	EDITBUTTON plugin setting
  buttonrow	Set to top to put the edit buttons above the table.	bottom
  javascriptinterface	Use javascript to directly move and delete row without page refresh. Enable with "on", disable with "off".	JAVASCRIPTINTERFACE plugin setting
  redirectto	Set up a return page after saving changes. e.g By setting redirectto="%BASEPAGE%", you can return to base page if your editable table is included by another page.	(none)

• Example:
  %EDITTABLE{ format="| text, 20 | select, 1, one, two, three |" changerows="on" }%
| *Name* | *Type* |
| Foo | two |
• Category: DatabaseAndFormsVariables, EditingAndContentUpdateVariables, TablesAndSpreadsheetsVariables
• Related: See EditTablePlugin for more details (this topic)

TABLE{ attributes } -- control attributes of tables and sorting of table columns

• The %TABLE{}% variable is handled by the TablePlugin
• Syntax: %TABLE{ attributes }%

• Supported attributes:

  Argument	Comment	Default value	Example
  valign	Vertical alignment of cells and headers, set to "top", "middle", "bottom" or "baseline".	unspecified	valign="top"
  tablewidth	Table width: Percentage of window width, or absolute pixel value.	unspecified	tablewidth="100%"
  tablerules	Table rules, set to "none" (no rules), "groups" (rules will appear between row groups and column groups only), "rows" (rules will appear between rows only), "cols" (rules will appear between columns only), "all" (rules will appear between all rows and columns).	unspecified	tablerules="rows"
  tableframe	Table frame, set to "void" (no sides), "above" (the top side only), "below" (the bottom side only), "hsides" (the top and bottom sides only), "lhs" (the left-hand side only), "rhs" (the right-hand side only), "vsides" (the right and left sides only), "box" (all four sides), "border" (all four sides).	unspecified	tableframe="hsides"
  tableborder	Table border width (pixels).	"1"	tableborder="2"
  summary	Table summary used by screenreaders: A summary of what the table presents. It should provide an orientation for someone who listens to the table.	unspecified	summary="List of subscribed users"
  sort	Set table sorting by clicking headers "on" or "off".	unspecified	sort="on"
  initsort	Column to sort initially ("1" to number of columns).	unspecified	initsort="2"
  initdirection	Initial sorting direction for initsort, set to "up" (descending) or "down" (ascending).	unspecified	initdirection="up"
  id	Unique table identifier string, used for targeting a table with CSS.	tableN (where N is the table order number on the page)	id="userTable"
  headervalign	Vertical alignment of header cells; overrides valign.	unspecified	headervalign="top"
  headerrows	Number of header rows to exclude from sort. (will be rendered in a HTML thead section)	(determined automatically)	headerrows="2"
  headercolor	Header cell text color.	"#ffffff"	headercolor="#0000cc"
  headerbgsorted	Header cell background color of a sorted column.	the value of headerbg	headerbgsorted="#32596c"
  headerbg	Header cell background color.	"#6b7f93"	headerbg="#999999"
  headeralign	Header cell alignment, one value for all columns, or a comma separated list for different alignment of individual columns. Set to "left", "center", "right" or "justify". Overrides individual cell settings.	unspecified	headeralign="left,right"
  footerrows	Number of footer rows to exclude from sort. (will be rendered in a HTML tfoot section)	"0"	footerrows="1"
  disableallsort	Disable all sorting, both initsort and header sort. This is mainly used by plugins such as the EditTablePlugin to disable sorting in a table while editing the table.	unspecified	disableallsort="on"
  datavalign	Vertical alignment of data cells; overrides valign.	unspecified	datavalign="top"
  datacolor	Data cell text color, a comma separated list.	unspecified	datacolor="#0000CC, #000000"
  databgsorted	Data cell background color of a sorted column; see databg.	the values of databg	databgsorted="#d4e8e4,#e5f5ea"
  databg	Data cell background color, a comma separated list. Specify "none" for no color, that is to use the color/background of the page the table is on.	"#edf4f9,#ffffff"	databg="#f2f2f2,#ffffff"
  dataalign	Data cell alignment, one value for all columns, or a comma separated list for different alignment of individual columns. Set to "left", "center", "right" or "justify". Overrides individual cell settings.	unspecified	dataalign="center"
  columnwidths	Column widths: Comma delimited list of column widths, percentage or absolute pixel value.	unspecified	columnwidths="80%,20%"
  cellspacing	Cell spacing (pixels).	"0"	cellspacing="3"
  cellpadding	Cell padding (pixels).	"0"	cellpadding="0"
  cellborder	Cell border width (pixels).	unspecified	cellborder="0"
  caption	Table caption: A title that will be displayed just above the table.	unspecified	caption="Users"

• Example:
  %TABLE{ tableborder="0" cellpadding="4" cellspacing="3" cellborder="0" }%
| *A1* | *B1* |
| A2 | B2 |
• Category: FormattingAndRenderingVariables, TablesAndSpreadsheetsVariables
• Related: See TablePlugin for more details (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: • 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} {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. • 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} {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: • 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; }"

• 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: 5 variables

Related Topics: TWikiVariables, TWikiVariablesSearch, TWikiVariablesQuickStart