Spreadsheet 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 |
---|---|
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 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 few onbefore
events you can use to intercept, validate or cancel a user action.
Event | Description |
---|---|
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.
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). |
~~defaultColAlign: string~~ | ~~The default column text alignment. Default: center.~~ Deprecated from v8.2.0+ (the default is based on a CSS class) |
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 |
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
Render method
Using the method render to transform the information visible to the user.
<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/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/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('MWQxYjg0YWJmMjMyMTRjZDQwODliMDhkZGYwMGQ3NGM5YWRiYzdjODNjMTZlYTg3ZTBlOTVlNjc3NDAzNzA1ZjI0ZDE4YzM1NTU1YmExMDYzNDliYjdhMmY0NmNlNzhiNzhhZTg4ZjljMTk2YmExMWNhNWI3YjA1ZjBmZjI2YWYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNek5UUXpOVE01TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
// Adding an arbitrary number leading zeros.
let pad = function(cell, value, x, y, instance, options) {
if (value !== '') {
let 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.
<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/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<div id="spreadsheet"></div>
<br/>
<ol class='example cursor'>
<li><a id="button1">Insert a new blank column at the end</a></li>
<li><a id="button2">Insert two new blank columns at the beginning</a></li>
<li><a id="button3">Click to insert a new column with pre-populated values at the end of the table</a></li>
<li><a id="button4">Click to delete the last column</a></li>
<li><a id="button5">Click to move the first column to the third position</a></li>
<li><a id="button6">Hide the first column</a></li>
<li><a id="button7">Show the first column</a></li>
</ol>
<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('MWQxYjg0YWJmMjMyMTRjZDQwODliMDhkZGYwMGQ3NGM5YWRiYzdjODNjMTZlYTg3ZTBlOTVlNjc3NDAzNzA1ZjI0ZDE4YzM1NTU1YmExMDYzNDliYjdhMmY0NmNlNzhiNzhhZTg4ZjljMTk2YmExMWNhNWI3YjA1ZjBmZjI2YWYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNek5UUXpOVE01TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
// Create the spreadsheet
let worksheets = jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
data: [
['US', 'Cheese', 1000 ],
['CA', 'Apples', 1200 ],
['CA', 'Carrots', 2000 ],
['BR', 'Oranges', 3800 ],
]
}],
});
document.getElementById("button1").onclick = () => worksheets[0].insertColumn();
document.getElementById("button2").onclick = () => worksheets[0].insertColumn(2, 0, 1);
document.getElementById("button3").onclick = () => worksheets[0].insertColumn([ '0.99', '1.22', '3.11', '2.21' ]);
document.getElementById("button4").onclick = () => worksheets[0].deleteColumn();
document.getElementById("button5").onclick = () => worksheets[0].moveColumn(0, 2);
document.getElementById("button6").onclick = () => worksheets[0].hideColumn(0);
document.getElementById("button7").onclick = () => worksheets[0].showColumn(0);
</script>
</html>