close
Documentation
Persistence
Jspreadsheet is a frontend tool but has methods, events, and other features to help with the backend data persistence. This section will cover the following points:- Posting data to a remote server
- Dealing with record IDs synchronization and sequences
- Backend persistence in PHP example
- Plugins for persistence
Features
Sequence
Jspreadsheet has an internal sequence for each worksheet. That is automatically incremented and allocated to a row when no record id is defined. It is possible to assign the sequence value to the row throughworksheet.setRowId(row, id)
.
Documentation
Methods
Method | Description |
---|---|
getNextSequence | Get the next sequence number.getNextSequence() : void |
getRowId | Get the row ID from a row number given.getRowId(rowNumber: Number) : void |
setRowId | Set the row ID from a row number given.setRowId(rowNumber: Number, rowId: Number) : void |
getRowById | Get the data from a row, or the row object by id.getRowById(rowNumber: Number, element: Boolean) : any |
save | Internal method to post a request to the server. The callback is executed when the backend returns a JSONsave(url: String, data: Any, token: String, callback: Function) : void |
Events
Theonbeforesave
event can be used to intercept, change or cancel the user action.Method | Description |
---|---|
onbeforesave | Executed before a server update request.onbeforesave(spreadsheet: Object, worksheet: Object, data: Object) : Object It will return false to cancel the event or the replacement for the original data. Data is the information about the event that requires server persistence. |
onsave | It will bring information about the server update request.onsave(spreadsheet: Object, worksheet: Object, data: Object, result, Object) : void |
Settings
Property | Description |
---|---|
On the spreadsheet configuration level | |
server: string | URL for the server persistence requests. A global URL for all worksheets. |
On the worksheet configuration level | |
persistence: string | URL for the server persistence requests. One URL for each worksheet. |
columns.primaryKey: boolean | The primaryKey defines which column will be considered the ID for the records. |
rows.id: number | Define the ID for the row |
Posting data to a remote server
When the persistence directive is active, an HTTP request happens on each spreadsheet update.Source code
<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'), { worksheets: [{ url: '/jspreadsheet/books.json', columns: [ { type: 'autonumber', width: '50px', title: 'Code', name: 'id', readOnly: true, primaryKey: true }, { type: 'image', width: '80px', title: 'Image', name: 'thumbnailUrl', }, { type: 'text', width: '200px', title: 'Title', name: 'title' }, { type: 'text', width: '55px', title: 'Pages', name: 'pageCount' }, { type: 'calendar', width: '90px', title: 'Published', name: 'publishedDate' }, { type: 'text', width: '200px', title: 'Author', name: 'authors' }, { type: 'dropdown', width: '180px', title: 'Categories', name: 'categories', source: ['Internet','Web Development', 'Java', 'Mobile', 'Open Source'], multiple: true, render: 'tag' }, ], allowComments:true, search: true, persistence: '/jspreadsheet/save', }], updateTable: function(instance, cell, col, row, val, label, cellName) { if (col == 1) { if (! val) { cell.innerHTML = '<img src="https://images-na.ssl-images-amazon.com/images/I/21%2Bwfxx2lyL._SX319_BO1,204,203,200_.jpg" style="width:60px;">'; } } } }); </script> </html>
Record ID synchronization and sequences
The following example implements a few concepts:- How to load the data with hidden IDs for the user;
- Using a custom column to create an icon to perform any action using the record id;
- Get the record id from a remote server when a new row is added.
NOTE: the example shows one new line, but you can interact to get more ids if more than one row is added.
Source code
<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-2"></div> <script> var action = function() { var methods = {}; methods.createCell = function(cell, value, x, y, instance, options) { var input = document.createElement('i'); input.className = 'material-icons'; input.style.cursor = 'pointer'; input.style.fontSize = '22px'; input.innerHTML = "search"; input.onclick = function() { var id = instance.getRowId(y); // Do some action alert(id); } cell.appendChild(input); // Readonly cell.classList.add('readonly'); } return methods; }(); jspreadsheet(document.getElementById('spreadsheet-2'), { worksheets: [{ data: [ { id:1, data:['Google', '5', ''] }, { id:2, data:['Bind', '4', ''] }, { id:3, data:['Yahoo', '1', ''] }, { id:4, data:['Duckduckgo', '5', ''] }, ], columns: [ { type: 'text', width:'400px' }, { type: 'rating', width:'100px' }, { type: action, width:'100px' }, ], persistence: '/jspreadsheet/save', }], oninsertrow: function(a,b,c,d,e) { // Inserted before? var rowNumber = e == false ? b + 1 : b; // Go in remotely get the id and return to the cell jSuites.ajax({ url: '/jspreadsheet/id', method: 'GET', dataType: 'json', success: function(result) { // The new id is alert('The new row has id: ' + result); // set row id a.setRowId(rowNumber, result); } }) } }); </script> </html>