Data

This section is dedicated to themethods, 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 file remote JSON file
  • Load data from a JavaScript Array.
  • Load data from a XLSX file (It requires a extension and there are known limitations).


Cell binding

It is possible to bind the spreadsheet cells with a reference of one array or properties inside a single or multi-level JSON. In the following code, the changes in the spreadsheet cell will reflect in the data variable automatically.
var 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',
        }]
    ]
})


Documentation

Methods

Up to version 7, the method setData would destroy and recreate the table losing information such as comments, style. From version 8, setData replaces the data of the worksheet only, keeping the other cell properties.
EventDescription
getValue Get the value from 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 from a cell from its coodinates.
getValueFromCoords(x: Number, y: Number, processed: Boolean) => any
setValue Set the value from 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 from 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 use the advanced data format to bring IDs to the spreadsheet.
getData Extract the data from the spreadsheet.
getData(highlighted: Boolean, processed: Boolean) => array
@param {bool} Get the data from the highlighted cells only.
@param {bool} Get the raw data (false). Get the processed data (true)
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 use the advanced data format to bring IDs to the spreadsheet.
getRowData Get the data of one row by its number starting on zero.
getRowData(rowNumber: Number) => array
@param {number} Row number.
setRowData Set the data of 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 of one column by its number starting on zero.
getColumnData(colNumber: Number) => array
@param {number} Column number.
setColumnData Set the data of 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 a HTTP request when the data was load 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 on the current cursor
paste() => void


Advance 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 follow:
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.

EventDescription
onbeforechange onbeforechange(worksheet: Object, cell: DOMElement, x: Number, y: Number, value: Value) => void
Before change 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)
It happen before paste the data to 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

PropertyDescription
data: Array|ObjectDefine the new data from a local JSON or array.
url: StringLoad the data from an external file.
csv: StringLoad the data from an external CSV file.
csvHeaders: BooleanThe first row of the CSV file is the headers
csvDelimiter: StringCSV 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 below:

Array
JSON
CSV
Static Table
XLSX

Create a spreadsheet from a JavaScript array

<html>
<script src="https://jspreadsheet.com/v8/jspreadsheet.js"></script>
<script src="https://jsuites.net/v4/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v8/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://jsuites.net/v4/jsuites.css" type="text/css" />

<div id='spreadsheet'></div>

<script>
data = [
    ['Mazda', 2001, 2000],
    ['Pegeout', 2010, 5000],
    ['Honda Fit', 2009, 3000],
    ['Honda CRV', 2010, 6000],
];

jspreadsheet(document.getElementById('spreadsheet'), {
    data: data,
    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/v8/jspreadsheet.js"></script>
<script src="https://jsuites.net/v4/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v8/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://jsuites.net/v4/jsuites.css" type="text/css" />

<div id='spreadsheet'></div>

<script>
jspreadsheet(document.getElementById('spreadsheet'), {
    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/v8/jspreadsheet.js"></script>
<script src="https://jsuites.net/v4/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v8/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://jsuites.net/v4/jsuites.css" type="text/css" />

<div id='spreadsheet'></div>

<script>
jspreadsheet(document.getElementById('spreadsheet'), {
    csv: 'demo.csv',
    csvHeaders: true,
    columns: [
        { width:300 },
        { width:80 },
        { width:100 }
    ]
});
</script>
</html>

Create a spreadsheet from a existing HTML table element

<html>
<script src="https://jspreadsheet.com/v8/jspreadsheet.js"></script>
<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://jspreadsheet.com/v8/jspreadsheet.css" type="text/css" />

<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(document.getElementById('spreadsheet')); 
</script>
</html>

The XLSX parser is a premium JSS extension and it is not part in the native distribution. You need to activate the extension in your profile.

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
<html>
<script src="https://jspreadsheet.com/v8/jspreadsheet.js"></script>
<script src="https://jsuites.net/v4/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v8/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://jsuites.net/v4/jsuites.css" type="text/css" />

<script src="https://jspreadsheet.com/v8/plugins/parser.js"></script>

<script>
// Bind the XSLX parser to your JSS distribution
jspreadsheet.parser = parser;

// Create spreadsheet from the XLSX
jspreadsheet.parser({
    url: '/tests/Samples/sample.xlsx',
    license: 'YjQzMzdlOTRiOGY3ZTQ0ZDQ4ZTI1YWU3MDFjMDI0ZWJmOTNjODA1NWFiZTRiNDJhNmRiYTJlZjkwODQ3N2IwMWRmNWRjYWUwZDViM2VhMmI3NzVjOTcwMzVlN2ZhODI1Y2EyMmE3NDI0ZmE0ZjVmNGQ2MWEzN2M3MTA4MThhMDUsZXlKdVlXMWxJam9pY0dGMWJDNW9iMlJsYkNJc0ltUmhkR1VpT2pFMk16SXdPVEkwTURBc0ltUnZiV0ZwYmlJNld5SnFjMlpwWkdSc1pTNXVaWFFpTENKcVpYaGpaV3d1Ym1WMElpd2lZMjlrWlhOaGJtUmliM2d1YVc4aUxDSnFjMmhsYkd3dWJtVjBJaXdpTVROemQyMHVZM05pTG1Gd2NDSXNJbXh2WTJGc2FHOXpkQ0pkTENKd2JHRnVJam9pTXlKOQ==',
    onload: function(config) {
        // Create the spreadsheet
        jspreadsheet(document.getElementById('spreadsheet'), config);
    },
}); 
</script>