close
Documentation
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/v4/jsuites.js"></script> <link rel="stylesheet" href="https://jsuites.net/v4/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('NzVmOGUwMDk3Mzc4YzRlOTEyZTgzOWExMGZiM2RiOTQ1NTA4YTk5MzQxOWE2MmNiNWM0NmUxNjAwOTRiMTEwNDA1NTI2ODA4ZDA5NzQwYTdiOGVlYWJmNjRjZDE2ZjdkZmRmODczNGJjYjA3NmFhZTQ3ZTQ2ZDQ3OTk5NzgxYjUsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTNOVGs1TnpjNU5Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpJaXdpYzJOdmNHVWlPbHNpZGpjaUxDSjJPQ0lzSW5ZNUlpd2lZMmhoY25Seklpd2labTl5YlhNaUxDSm1iM0p0ZFd4aElpd2ljR0Z5YzJWeUlpd2ljbVZ1WkdWeUlpd2lZMjl0YldWdWRITWlMQ0pwYlhCdmNuUWlMQ0ppWVhJaUxDSjJZV3hwWkdGMGFXOXVjeUlzSW5ObFlYSmphQ0pkTENKa1pXMXZJanAwY25WbGZRPT0='); // 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>