Columns
The column settings define the behavior of all cells in that column. It is possible to specify the input data type and many other properties such as read-only, masks, render, and much more. There are several methods and events related to the columns.Documentation
Methods
The following methods interact with the columns programmatically.Method | Description |
---|---|
getWidth | Get the column width or the width of all columns.getWidth(colNumber?: Number) => number | array |
setWidth | Set the column width.setWidth(colNumber: Number, width: Number) => void |
getColumnIdByName | Get the column position number by name.getColumnIdByName(cellName: String) => Number |
getColumn | Get the column settings by number.getColumn(colNumber: Number) => Object |
getColumnIdByName | Get the column position number by name.getColumnIdByName(cellName: String) => Number |
getPrimaryKey | Get a column in which the primaryKey property is defined as true. Return false if no column is defined as the primaryKeygetPrimaryKey() => mixed |
getSelectedColumns | Get the selected columns.getSelectedColumns() => Array |
moveColumn | Change the position of one column.moveColumn(columnNumber: Number, newPositionNumber: Number) => void |
insertColumn | Create one or more new columns.insertColumn(mixed: Mixed, columnNumber: Number, insertBefore: Boolean, properties: Array, data: Array) => void
@param {number|array} - number of columns to be added | array with the data to be added (it will create a new single column with this data) @param {number?} columnNumber - The new column reference position | null refers to the last column in the spreadsheet @param {boolean} insertBefore - The new column(s) should be included before or after the columnNumber defined @param {array} properties - Array with the new columns settings @param {array} data - Array with the new column data |
deleteColumn | Delete one or more columns.deleteColumn(columnNumber: Number, numOfColumns: Number) => void
@param {number} columnNumber - Which column should be excluded starting on zero @param {number} numOfColumns - Number of columns to be excluded from the defined column reference |
showColumn | Show a column by number.showColumn(columnNumber: Number) => void
@param {number} columnNumber - Show column by the number |
hideColumn | Hide a column by number.hideColumn(columnNumber: Number) => void
@param {number} columnNumber - Hide column by the number |
showIndex | Show the index column for the spreadsheet.showIndex() => void
|
hideIndex | Hide the index column for the spreadsheet.hideIndex(columnNumber: Number) => void
|
getProperties | Get the column settings by number.getProperties(colNumber: Number) => object |
setProperties | Set the column settings by number.setProperties(colNumber: Number, Properties: Object) => object |
Events
There are several events related to the column in your spreadsheet. There are a fewonbefore
events you can use to intercept, validate or cancel a user action.Event | Description |
---|---|
onbeforeinsertcolumn | onbeforeinsertcolumn(worksheet: Object, columnNumber: Number, numOfColumns: Number, insertBefore: Boolean) => Boolean Before a new column is inserted. Return false to cancel the user action. |
oninsertcolumn | oninsertcolumn(worksheet: Object, columnNumber: Number, numOfColumns: Number, historyRecords: Array, insertBefore: Boolean) After a new column is inserted. |
onbeforedeletecolumn | onbeforedeletecolumn(worksheet: Object, columnNumber: Number, numOfColumns: Number) Before a column is excluded. Return false to cancel the user action. |
ondeletecolumn | ondeletecolumn(worksheet: Object, columnNumber: Number, numOfColumns: Number, affectedDOMElements: Array, historyProperties: Array, cellAttributes: Array) After a column is excluded. |
onmovecolumn | onmovecolumn(worksheet: Object, origin: Number, destination: Number) After a column is moved to a new position. |
onresizecolumn | onresizecolumn(worksheet: Object, column: Mixed, width: Mixed, oldWidth: Mixed) After a column width change for one or more columns. |
oncreatecolumn | oncreatecolumn(worksheet: Object, columnNumber: number, td: HTMLElement, options: Object) When a new column is created. |
Initial Settings
The following column-related properties are available through the initialization of the online spreadsheet.Property | Description |
---|---|
allowInsertColumn: boolean | Enable the user to enter new columns. Default: true |
allowManualInsertColumn: boolean | A new column is automatically inserted when the user presses the tab key in the last column. Default: true |
allowDeleteColumn: boolean | Allow the user to delete columns. Default: true |
allowRenameColumn: boolean | Allow the user to rename columns. Default: true |
columnDrag: boolean | Allow the user to change the position of one column by dragging and dropping. Default: true |
columnSorting: boolean | Allow the user to sort columns. Default: true |
columnResize: boolean | Allow the user to resize columns. Default: true |
defaultColWidth: number | The default column width. Default: 100px (version 8+) or 50px (version7 and before). |
minSpareCols: number | The mandatory number of blank columns at the end of the spreadsheet. Default: none. |
Available properties
Please bear in mind that each column type can hold specific properties. The following are the most commonly available.Property | Description |
---|---|
type: string|function | Defines the column type. Can be a string to define a native editor, or a method to define the custom editor plugin. |
title: string | Defines the column header title. |
name: string | The name or a path of a property when the data is a JSON object. |
tooltip: string | Defines the onmouseover tooltip for the column header. |
width: string | Defines the width of a cell in pixels. Default: 100px |
visible: boolean | Visibility of the column. |
align: string | Text alignment: left|center|right. Default: center |
filterOptions: function | A method to overwrite the column definitions in real-time just before the cell edition. |
url: string | Load the items in the dropdown from a remote URL. |
source: array | Defines the items in the dropdown and autocomplete column type. |
multiple: boolean | Defines whether the dropdown or autocomplete accept multiple options. |
delimiter: character | Defines the dropdown delimiter. Default: ';' |
mask: string | Defines the input mask for the data cell. |
decimal: string | Decimal representation character. |
truncate: number | Truncate the string in the cell by any number of characters. |
disabledMaskOnEdition: boolean | Disable the mask when editing. |
render: string|function | Defines a renderer method or rule for the cell content. |
format: string | Defines the format of the date or numbers in the cell. Default for the calendar: DD/MM/YYYY For more valid calendar formats, please visit the JavaScript calendar quick reference |
primaryKey: boolean | Defines the column as primaryKey. |
options: object | Extended configuration for one column. |
readOnly: boolean | The column is read-only |
process: boolean | Process the raw data when copied or downloaded. Default: true |
autoCasting: boolean | Try to extract numbers from a string. Default: true |
shiftFormula: boolean | To update formulas on data cloning when the column is a custom editor. Default: true |
wrap: boolean | Wrap the text inside the column. |
Examples
A basic spreadsheet and a few programmatic methods available.- Click to insert a new blank column at the end of the table
- Click to insert two new blank columns at the beginning of the table
- Click to insert a new column with pre-populated values at the end of the table
- Click to delete the last column
- Click to move the first column to the third position
- Hide the first column
- Show the first column
Source code
<html> <script src="https://jspreadsheet.com/v8/jspreadsheet.js"></script> <link rel="stylesheet" href="https://jspreadsheet.com/v8/jspreadsheet.css" type="text/css" /> <script src="https://jsuites.net/v5/jsuites.js"></script> <link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" /> <div id="spreadsheet"></div> <ol class='example cursor'> <li><a onclick="worksheets[0].insertColumn()">Insert a new blank column at the end</a></li> <li><a onclick="worksheets[0].insertColumn(2, 0, 1);">Insert two new blank columns at the beginning</a></li> <li><a onclick="worksheets[0].insertColumn([ '0.99', '1.22', '3.11', '2.21' ]);">Click to insert a new column with pre-populated values at the end of the table</a></li> <li><a onclick="worksheets[0].deleteColumn();">Click to delete the last column</a></li> <li><a onclick="worksheets[0].moveColumn(0, 2);">Click to move the first column to the third position</a></li> <li><a onclick="worksheets[0].hideColumn(0);">Hide the first column</a></li> <li><a onclick="worksheets[0].showColumn(0);">Show the first column</a></li> </ol> <script> // Set the JSS spreadsheet license jspreadsheet.setLicense('MmNlYWQ4MGJlNGM1M2Q2ZTk4OGIxNTFhNTUwODg5MjlhNDY5YWU2OWQyMGUxZTIyMjQ2M2MwM2QzNjdmZGVhZWRiNjhmZGQwNTA4MzU3MzdiMWIzZDhjYWEzNTkzYzZiYTc5Nzk1NjgwZTAxYTEyNzhjMzZjYzlmMTNhODZlZTQsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOalV3TVRRM01Dd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09'); // Create the spreadsheet var worksheets = jspreadsheet(document.getElementById('spreadsheet'), { worksheets: [{ data: [ ['US', 'Cheese', 1000 ], ['CA', 'Apples', 1200 ], ['CA', 'Carrots', 2000 ], ['BR', 'Oranges', 3800 ], ] }], }); </script> </html>