Data
This section is dedicated to the methods, events and settings related to data and its operations.
Create a new spreadsheet
There are different ways to create a new spreadsheet and there are different ways to load the data, such as:
- From an existing HTML table;
- Load data from a CSV file
- Load data from a JSON string or remote JSON file
- Load data from a JavaScript Array.
- Load data from an XLSX file (This requires an extension and there are known limitations).
Cell binding
It is possible to bind the spreadsheet cells data to an array or a single or multi-level object. In the following code, the changes in the spreadsheet cell will reflect in the data variable automatically.
See a Data binding example
<html>
<script src="https://jspreadsheet.com/v9/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v9/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<div id='spreadsheet'></div>
<div id='console'></div>
<input type='button' id='showonconsole' value='console.log on data'/>
<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NWZhMmQ2ZDA1MGJhMThiNDJmYzdhZmZjNGExOGQ2NTc4MjU0NTEyN2M4MmFhNjI2YmI4M2RkYjNiNWVkNzllZDYzMGUzNWY2ZTczNjQ4ODc3OWJmM2QyYmYzMjE1Mjg4MzI0ODdkMTc0NWNkMjdjZGFiNzE0ODg2NjgxMTMxNmYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNek5UUTRNamcxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
let data = [
{
name: 'Jorge',
address: {
number: '201',
city: 'New York'
}
},
{
name: 'Paul',
address: {
number: '1',
city: 'New Jersey'
}
},
];
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
data: data,
columns: [{
name: 'name',
title: 'Fullname',
type: 'text',
width: '200px',
},
{
name: 'address.number',
title: 'Number',
type: 'text',
width: '200px',
},
{
name: 'address.city',
title: 'City',
type: 'text',
width: '300px',
}]
}]
});
document.getElementById('showonconsole').onclick = function () { document.getElementById('console').innerHTML = JSON.stringify(data) }
</script>
</html>
Documentation
Methods
Up to version 7, the method setData would destroy and recreate the table losing information such as comments and style. From version 8, setData only replaces the data from the worksheet, keeping the other cell properties.
Event | Description |
---|---|
getValue | Get the value of a cell.getValue(cellName: string, processed: Boolean) => any @param {string} cellName - The string to represent a cellname, such as A1,B1, etc. @param {bool} Get the raw data (false). Get the processed data (true) |
getValueFromCoords | Get the value of a cell from its coordinates.getValueFromCoords(x: Number, y: Number, processed: Boolean) => any |
setValue | Set the value of a cell.setValue(cellName: String, value: String|Number, force: Boolean) => void @param {string} cellName - The string to represent a cellname, such as A1,B1, etc. @param {value} - The cell new value @param {bool} - Help update values on read-only cells. |
setValueFromCoords | Set the value of a cell from its coordinatessetValueFromCoords(x: Number, y: Number, value: String|Number, force: Boolean) => void |
setData | Replace the data from the spreadsheet.setData(data: Array) => void @param {array} The most basic format is a matrix of lines and columns. It is also possible to use the advanced data format to add IDs to the spreadsheet. |
loadData | Reset the data from the JavaScript grid.loadData(data: Array) => void @param {array} The most basic format is a matrix of lines and columns, but it can be defined as a JSON. |
getData | Extract the data from the spreadsheet.getData(highlighted?: Boolean, processed?: Boolean, delimiter?: String, asJson?: Boolean) => array @param {bool} Get the data from the highlighted cells only. @param {bool} Get the raw data (false). Get the processed data (true). Delimiter for exporting. |
getRowData | Get the data from one row by its number starting on zero.getRowData(rowNumber: Number, processed: Boolean) => array @param {number} Row number. |
setRowData | Set the data for one row by its number starting on zero.getRowData(rowNumber: Number, data: Array) => void @param {number} Row number @param {array} The new data. |
getColumnData | Get the data from one column by its number starting on zero.getColumnData(colNumber: Number, processed: Boolean) => array @param {number} Column number. |
setColumnData | Set the data for one column by its number starting on zero.setColumnData(rowNumber: Number, data: Array, force: Boolean) => void @param {number} Column number. @param {array} The new data. @param {boolean} Force update on readonly cells. |
refresh | Refresh the data, it will send an HTTP request when the data is loaded from an external source.refresh() => void |
download | Download the data from a worksheet in a CSV format.download(includeHeaders: Boolean, processed: Boolean) => void @param {bool} Include the headers @param {bool} Returns the raw data when false or Returns the processed data and formulas when true. |
copy | Copy the selected cellscopy(cut: Boolean) => void @param {bool} Clear the data before pasting. |
paste | Paste the data to the current cursor positionpaste(x: number, y: number, data: string|array) => void |
Advanced data format
It is possible to use the advanced data format to load specific rows, with specific ids, and the data for the columns as follows:
table.setData([
{ id: 1000, row: 0, data:[1,2,3] },
{ id: 2000, row: 1, data:[4,5,6] },
{ id: 2001, row: 2, data:[7,8,9] },
]);
Events
Events related to operations with the spreadsheet data.
Event | Description |
---|---|
onbeforechange | onbeforechange(worksheet: Object, cell: DOMElement, x: Number, y: Number, value: Value) => void Before changing the cell value. This can be used to intercept, change or cancel the user action. |
onchange | onchange(worksheet: Object, cell:DOMElement, x: Number, y: Number, newValue: Any, oldValue: Any) After a new value is updated. |
onafterchanges | onafterchanges(worksheet: Object, records: Array) An array of cells affected. |
oncopy | oncopy(worksheet: Object, highlighted: Boolean, str: String) The data copied to the clipboard. |
onbeforepaste | onbeforepaste(worksheet: Object, data: Array, x: Number, y: Number, style: Array) This happens before pasting the data in the spreadsheet, can be used to intercept, change or cancel the user action. |
onpaste | onpaste(worksheet: Object, records: Array) After the paste action. |
Initial Settings
Property | Description |
---|---|
data: Array|Object | Define the new data from a local JSON or array. |
url: String | Load the data from an external file. |
csv: String | Load the data from an external CSV file. |
csvHeaders: Boolean | The first row of the CSV file is the headers |
csvDelimiter: String | CSV divisor. Default: ',' |
Examples
Create a web-based spreadsheet
How to create a new spreadsheet from an HTML table element, a JS array, a CSV or a JSON file, as shown below:
Create a spreadsheet from a JavaScript array
<html>
<script src="https://jspreadsheet.com/v9/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v9/jspreadsheet.css" type="text/css" />
<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>
jspreadsheet.setLicense('NWZhMmQ2ZDA1MGJhMThiNDJmYzdhZmZjNGExOGQ2NTc4MjU0NTEyN2M4MmFhNjI2YmI4M2RkYjNiNWVkNzllZDYzMGUzNWY2ZTczNjQ4ODc3OWJmM2QyYmYzMjE1Mjg4MzI0ODdkMTc0NWNkMjdjZGFiNzE0ODg2NjgxMTMxNmYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNek5UUTRNamcxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
data: [
['Mazda', 2001, 2000],
['Peugeot', 2010, 5000],
['Honda Fit', 2009, 3000],
['Honda CRV', 2010, 6000],
],
columns: [
{ title:'Model', width:'300px' },
{ title:'Price', width:'80px' },
{ title:'Model', width:'100px' }
]
}]
});
</script>
</html>
Create a spreadsheet from a JSON object
<html>
<script src="https://jspreadsheet.com/v9/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v9/jspreadsheet.css" type="text/css" />
<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>
jspreadsheet.setLicense('NWZhMmQ2ZDA1MGJhMThiNDJmYzdhZmZjNGExOGQ2NTc4MjU0NTEyN2M4MmFhNjI2YmI4M2RkYjNiNWVkNzllZDYzMGUzNWY2ZTczNjQ4ODc3OWJmM2QyYmYzMjE1Mjg4MzI0ODdkMTc0NWNkMjdjZGFiNzE0ODg2NjgxMTMxNmYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNek5UUTRNamcxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
data: [
{ name:'Jorge', id:'3', age:'40', gender:'Male' },
{ name:'Cosme Sergio', id:'4', age:'48', gender:'Male' },
{ name:'Jorgina Santos', id:'5', age:'32', gender:'Female' },
],
columns: [
{ type:'text', width:'300px', name:'id' },
{ type:'text', width:'200px', name:'name' },
{ type:'text', width:'100px', name:'age' },
{ type:'hidden', name:'gender' },
]
}]
});
</script>
</html>
Create a spreadsheet from a CSV file
<html>
<script src="https://jspreadsheet.com/v9/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v9/jspreadsheet.css" type="text/css" />
<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>
jspreadsheet.setLicense('NWZhMmQ2ZDA1MGJhMThiNDJmYzdhZmZjNGExOGQ2NTc4MjU0NTEyN2M4MmFhNjI2YmI4M2RkYjNiNWVkNzllZDYzMGUzNWY2ZTczNjQ4ODc3OWJmM2QyYmYzMjE1Mjg4MzI0ODdkMTc0NWNkMjdjZGFiNzE0ODg2NjgxMTMxNmYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNek5UUTRNamcxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
csv: '/jspreadsheet/demo.csv',
csvHeaders: true,
tableOverflow: true,
tableWidth: 600,
columns: [
{ width: '300px' },
{ width: '100px' },
{ width: '100px' },
]
}]
});
</script>
</html>
Create a spreadsheet from a existing HTML table element
<html>
<script src="https://jspreadsheet.com/v9/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v9/jspreadsheet.css" type="text/css" />
<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" />
<h4>The Official Top biggest albums of 2019</h4>
<table id="spreadsheet">
<thead>
<tr>
<td>POS</td>
<td>TITLE</td>
<td>ARTIST</td>
<td>PEAK</td>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>DIVINELY UNINSPIRED TO A HELLISH EXTENT</td>
<td>LEWIS CAPALDI</td>
<td>1</td>
</tr>
<tr>
<td>2</td>
<td>NO 6 COLLABORATIONS PROJECT</td>
<td>ED SHEERAN</td>
<td>1</td>
</tr>
<tr>
<td>3</td>
<td>THE GREATEST SHOWMAN</td>
<td>MOTION PICTURE CAST RECORDING</td>
<td>1</td>
</tbody>
</table>
<br>
<script>
jspreadsheet.setLicense('NWZhMmQ2ZDA1MGJhMThiNDJmYzdhZmZjNGExOGQ2NTc4MjU0NTEyN2M4MmFhNjI2YmI4M2RkYjNiNWVkNzllZDYzMGUzNWY2ZTczNjQ4ODc3OWJmM2QyYmYzMjE1Mjg4MzI0ODdkMTc0NWNkMjdjZGFiNzE0ODg2NjgxMTMxNmYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNek5UUTRNamcxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
jspreadsheet(document.getElementById('spreadsheet'));
</script>
</html>
This feature requires an extension that is part of the Premium and Enterprise license.
<html>
<script src="https://jspreadsheet.com/v9/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v9/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<script src="https://cdn.jsdelivr.net/npm/jszip@3.6.0/dist/jszip.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/parser@2.1.0/dist/index.min.js"></script>
<script>
jspreadsheet.setLicense('NWZhMmQ2ZDA1MGJhMThiNDJmYzdhZmZjNGExOGQ2NTc4MjU0NTEyN2M4MmFhNjI2YmI4M2RkYjNiNWVkNzllZDYzMGUzNWY2ZTczNjQ4ODc3OWJmM2QyYmYzMjE1Mjg4MzI0ODdkMTc0NWNkMjdjZGFiNzE0ODg2NjgxMTMxNmYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNek5UUTRNamcxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
// Bind the XSLX parser to your JSS distribution
jspreadsheet.setExtensions({ parser });
// Create spreadsheet from the XLSX
jspreadsheet.parser({
url: '/tests/Samples/sample.xlsx',
onload: function(config) {
// Create the spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), config);
},
});
</script>
</html>
This extension is subject to the XLSX version and the operations included in the file. You might experience differences in the final render or even limitations. We are constantly improving. So, if you wish to investigate limitations you are experiencing, please send a message to contact@jspreadsheet.com