Columns

The column settings help to define the behavior of all cells in that column. It defines the input data type editor and many other properties such as read-only, masks, render, and much more. There are several methods and events related to the columns, as follows.


Documentation

Methods

The following methods interact with the columns programmatically.
MethodDescription
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 which the primaryKey property is defined as true. Return false if no column is defined as the primaryKey
getPrimaryKey() => 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 multiple 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 define 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
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 few onbefore events you can use to intercept, validate or cancel a user action.
EventDescription
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.


Initial Settings

The following column-related properties are available through the initialization of the online spreadsheet.
PropertyDescription
allowInsertColumn: booleanEnable the user to enter new columns. Default: true
allowManualInsertColumn: booleanA new column is automatically inserted when the user press the tab key in the last column. Default: true
allowDeleteColumn: booleanAllow the user to delete columns. Default: true
allowRenameColumn: booleanAllow the user to rename columns. Default: true
columnDrag: booleanAllow the user to change the position of one column by drag and drop. Default: true
columnSorting: booleanAllow the user to sort columns. Default: true
columnResize: booleanAllow the user to resize columns. Default: true
defaultColWidth: numberThe default column width. Default: 100px (version 8+) or 50px (version7 and before).
defaultColAlign: stringThe default column text alignment. Default: center.
minSpareCols: numberThe mandatory number of blank columns in the end of the spreadsheet. Default: none.


Available properties

Please bear in mind that each column type can bring specific properties to the columns. The following are the most commonly available.
PropertyDescription
type: string|functionDefine the column type. Can be a string to define a native editor, or a method to define the custom editor plugin.
title: stringDefine the column header title.
name: stringThe name or a path of a property when the data is a JSON object.
tooltip: stringDefine the onmouseover tooltip for the column header.
width: stringDefine the width of a cell in pixels. Default: 100px
visible: booleanVisibility of the column.
align: stringText alignment: left|center|right. Default: center
filterOptions: functionIt is a method to overwrite the column definitions in real-time just before the column edition.
url: stringLoad the items from the dropdown from a remote URL.
source: arrayDefine the items in the dropdown and autocomplete column type.
multiple: booleanDefine the dropdown or autocomplete to accept multiple options.
delimiter: characterDefine the dropdown delimiter. Default: ';'
mask: stringDefine the input mask for the data cell.
decimal: stringDecimal representation character.
truncate: numberTruncate the string in the cell by any number of characters.
disabledMaskOnEdition: booleanDisable the mask when editing.
render: string|functionIt defines a renderer method or rule for the cell content.
format: stringDefine the format of the date or numbers in the cell. Default for the calendar: DD/MM/YYYY

For more calendar valid formats, please visit the javascript calendar quick reference
primaryKey: booleanDefine the column as primaryKey.
options: objectExtended configuration for one column.
readOnly: booleanThe column is read-only
process: booleanProcess the raw data when copy or download. Default: true



Examples

A basic spreadsheet and a few programmatic methods available.



  1. Click to insert a new blank column at the end of the table
  2. Click to insert two new blank columns at the beginning of the table
  3. Click to insert a new column with pre-populated values at the end of the table
  4. Click to delete the last column
  5. Click to move the first column to the third position
  6. Hide the first column
  7. 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="spreadsheet.insertColumn()">Click to insert a new blank column at the end of the table</a></li>
    <li><a onclick="spreadsheet.insertColumn(2, 0, 1);">Click to insert two new blank columns at the beginning of the table</a></li>
    <li><a onclick="spreadsheet.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="spreadsheet.deleteColumn();">Click to delete the last column</a></li>
    <li><a onclick="spreadsheet.moveColumn(0, 2);">Click to move the first column to the third position</a></li>
    <li><a onclick="spreadsheet.hideColumn(0);">Hide the first column</a></li>
    <li><a onclick="spreadsheet.showColumn(0);">Show the first column</a></li>
</ol>

<script>
var spreadsheet = jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [{
        data: [
            ['US', 'Cheese', 1000 ],
            ['CA', 'Apples', 1200 ],
            ['CA', 'Carrots', 2000 ],
            ['BR', 'Oranges', 3800 ],
        ]
    }],
    license: 'MWEzMTE4MGFkNWY5YzQzNjE4NjZiNmE1NThhM2M0Yjc1NmUyNGM2N2YzZjU2NDQ5ZjM1MGFiYWNmOTFkNTkwODFiYmYwNDE1YjhhM2ViNGUyMzM2YjYzY2Q4NTcyMWE4MGQ4YjVjNjI2NWY4NWYyMTBjMWU5M2ZmNTU4OGI1MDQsZXlKdVlXMWxJam9pY0dGMWJDNW9iMlJsYkNJc0ltUmhkR1VpT2pFMk5UZzVOakk0TURBc0ltUnZiV0ZwYmlJNld5SnFjM0J5WldGa2MyaGxaWFF1WTI5dElpd2lZM05pTG1Gd2NDSXNJbXB6YUdWc2JDNXVaWFFpTENKc2IyTmhiR2h2YzNRaVhTd2ljR3hoYmlJNklqSWlMQ0p6WTI5d1pTSTZXeUoyTnlJc0luWTRJaXdpY0dGeWMyVnlJaXdpYzJobFpYUnpJaXdpWm05eWJYTWlMQ0p5Wlc1a1pYSWlMQ0ptYjNKdGRXeGhJbDE5',
});
</script>
</html>