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.
MethodDescription
autoWidth Resize the given column numbers based on their content.
autoWidth(columns: number[]) => void
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 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 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 few onbefore events you can use to intercept, validate or cancel a user action.
EventDescription
oncreatecolumn oncreatecolumn(worksheet: Object, columnNumber: number, td: HTMLElement, options: Object)
When a new column is created.
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.
onchangecolumnvisibility onchangecolumnvisibility(worksheet: Object, state: boolean: affected: []]) => void
When the visibility of cols changes.


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 presses 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 dragging and dropping. 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. Deprecated from v8.2.0+ (the default is based on a CSS class)
minSpareCols: numberThe 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.
PropertyDescription
type: string|functionDefines the column type. Can be a string to define a native editor, or a method to define the custom editor plugin.
title: stringDefines the column header title.
name: stringThe name or a path of a property when the data is a JSON object.
tooltip: stringDefines the onmouseover tooltip for the column header.
width: stringDefines the width of a cell in pixels. Default: 100px
visible: booleanVisibility of the column.
align: stringText alignment: left|center|right. Default: center
filterOptions: functionA method to overwrite the column definitions in real-time just before the cell edition.
url: stringLoad the items in the dropdown from a remote URL.
source: arrayDefines the items in the dropdown and autocomplete column type.
multiple: booleanDefines whether the dropdown or autocomplete accept multiple options.
delimiter: characterDefines the dropdown delimiter. Default: ';'
mask: stringDefines 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|functionDefines a renderer method or rule for the cell content.
format: stringDefines 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: booleanDefines the column as primaryKey.
options: objectExtended configuration for one column.
readOnly: booleanThe column is read-only
process: booleanProcess the raw data when copied or downloaded. Default: true
autoCasting: booleanTry to extract numbers from a string. Default: true
shiftFormula: booleanTo update formulas on data cloning when the column is a custom editor. Default: true
wrap: booleanWrap the text inside the column.



Examples


Render method

Using the method render to transform the information visible to the user.



Source code

<html>
<script src="https://jspreadsheet.com/v9/jspreadsheet.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v9/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" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />

<div id="spreadsheet"></div>

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('ZmExNmRkZTFmODI0NzkxNTNiMTg4ZWI1OWIzMmY5Mjk1YTE2MzFjYTI5NTY5ZGY5ZDllYWQ0YjI4YzQ2NDY0NDA2YTFkYWVlM2NjZDViYzJiNzU4OWZhMmM4NTNmZGUyMWU1NDMzMGUzMTkyOTI1MTk0MmUxMTQ4MTI1MjkzZGUsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTNNRFkyTWpVM05pd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpJaXdpYzJOdmNHVWlPbHNpZGpjaUxDSjJPQ0lzSW5ZNUlpd2lZMmhoY25Seklpd2labTl5YlhNaUxDSm1iM0p0ZFd4aElpd2ljR0Z5YzJWeUlpd2ljbVZ1WkdWeUlpd2lZMjl0YldWdWRITWlMQ0pwYlhCdmNuUWlMQ0ppWVhJaUxDSjJZV3hwWkdGMGFXOXVjeUlzSW5ObFlYSmphQ0pkTENKa1pXMXZJanAwY25WbGZRPT0=');

// Adding an arbitrary number leading zeros.
var pad = function(cell, value, x, y, instance, options) {
    if (value !== '') {
        var size = options.digits||0;
        value = value.toString();
        while (value.length < size) {
            value = "0" + value;
        }
        cell.innerText = value;
    }
}

// Create the spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
    tabs: true,
    toolbar: true,
    worksheets: [{
        data: [[1]],
        minDimensions: [8,8],
        columns: [{ render: pad, digits: 6 }]
    }],
});
</script>
</html>


Programmatic methods

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/v9/jspreadsheet.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v9/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" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />

<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 your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('ZmExNmRkZTFmODI0NzkxNTNiMTg4ZWI1OWIzMmY5Mjk1YTE2MzFjYTI5NTY5ZGY5ZDllYWQ0YjI4YzQ2NDY0NDA2YTFkYWVlM2NjZDViYzJiNzU4OWZhMmM4NTNmZGUyMWU1NDMzMGUzMTkyOTI1MTk0MmUxMTQ4MTI1MjkzZGUsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTNNRFkyTWpVM05pd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpJaXdpYzJOdmNHVWlPbHNpZGpjaUxDSjJPQ0lzSW5ZNUlpd2lZMmhoY25Seklpd2labTl5YlhNaUxDSm1iM0p0ZFd4aElpd2ljR0Z5YzJWeUlpd2ljbVZ1WkdWeUlpd2lZMjl0YldWdWRITWlMQ0pwYlhCdmNuUWlMQ0ppWVhJaUxDSjJZV3hwWkdGMGFXOXVjeUlzSW5ObFlYSmphQ0pkTENKa1pXMXZJanAwY25WbGZRPT0=');

// 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>



Create applications with spreadsheet-like controls