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.

Method	Description
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.

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.

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 press 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 drag and drop. 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).
defaultColAlign: string	The default column text alignment. Default: center.
minSpareCols: number	The 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.

Property	Description
type: string|function	Define the column type. Can be a string to define a native editor, or a method to define the custom editor plugin.
title: string	Define the column header title.
name: string	The name or a path of a property when the data is a JSON object.
tooltip: string	Define the onmouseover tooltip for the column header.
width: string	Define 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	It is a method to overwrite the column definitions in real-time just before the column edition.
url: string	Load the items from the dropdown from a remote URL.
source: array	Define the items in the dropdown and autocomplete column type.
multiple: boolean	Define the dropdown or autocomplete to accept multiple options.
delimiter: character	Define the dropdown delimiter. Default: ';'
mask: string	Define 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	It defines a renderer method or rule for the cell content.
format: string	Define 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: boolean	Define 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 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>