Products

Back to the Documentation

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('ZmMxNGNmNjk1OTUxMjk2ZWQ2NGQwYjg4OTQzMTMwMzVmYjUxYjc1ZmM5MmFhNWFjNDE0MzI3MGVjYjUxY2EwZmQwNjBkMzNhZWFkZTQ0ZThiMDNlNDdjZDQwY2VjODJjNDQ5NDA3YWE2ZTM2ZmU3ZDA4MmNlZjdjZGQzYmNjODYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNMU1UTXdPVEl6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');

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 coordinates
setValueFromCoords(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 cells
copy(cut: Boolean) => void
@param {bool} Clear the data before pasting.
paste Paste the data to the current cursor position
paste(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('ZmMxNGNmNjk1OTUxMjk2ZWQ2NGQwYjg4OTQzMTMwMzVmYjUxYjc1ZmM5MmFhNWFjNDE0MzI3MGVjYjUxY2EwZmQwNjBkMzNhZWFkZTQ0ZThiMDNlNDdjZDQwY2VjODJjNDQ5NDA3YWE2ZTM2ZmU3ZDA4MmNlZjdjZGQzYmNjODYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNMU1UTXdPVEl6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');

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('ZmMxNGNmNjk1OTUxMjk2ZWQ2NGQwYjg4OTQzMTMwMzVmYjUxYjc1ZmM5MmFhNWFjNDE0MzI3MGVjYjUxY2EwZmQwNjBkMzNhZWFkZTQ0ZThiMDNlNDdjZDQwY2VjODJjNDQ5NDA3YWE2ZTM2ZmU3ZDA4MmNlZjdjZGQzYmNjODYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNMU1UTXdPVEl6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');

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('ZmMxNGNmNjk1OTUxMjk2ZWQ2NGQwYjg4OTQzMTMwMzVmYjUxYjc1ZmM5MmFhNWFjNDE0MzI3MGVjYjUxY2EwZmQwNjBkMzNhZWFkZTQ0ZThiMDNlNDdjZDQwY2VjODJjNDQ5NDA3YWE2ZTM2ZmU3ZDA4MmNlZjdjZGQzYmNjODYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNMU1UTXdPVEl6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');

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('ZmMxNGNmNjk1OTUxMjk2ZWQ2NGQwYjg4OTQzMTMwMzVmYjUxYjc1ZmM5MmFhNWFjNDE0MzI3MGVjYjUxY2EwZmQwNjBkMzNhZWFkZTQ0ZThiMDNlNDdjZDQwY2VjODJjNDQ5NDA3YWE2ZTM2ZmU3ZDA4MmNlZjdjZGQzYmNjODYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNMU1UTXdPVEl6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');

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('ZmMxNGNmNjk1OTUxMjk2ZWQ2NGQwYjg4OTQzMTMwMzVmYjUxYjc1ZmM5MmFhNWFjNDE0MzI3MGVjYjUxY2EwZmQwNjBkMzNhZWFkZTQ0ZThiMDNlNDdjZDQwY2VjODJjNDQ5NDA3YWE2ZTM2ZmU3ZDA4MmNlZjdjZGQzYmNjODYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNMU1UTXdPVEl6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');

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

Edit this page on GitHub