Documentation
Back to the docs

Persistence

Jspreadsheet is a frontend tool but brings methods, events, and other features to help the backend data persistence. This section will cover the following points:
  • Post data to a remote server
  • Deal with record IDs syncronization and sequences
  • Backend persistance 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. The new sequence value would be assigned to the row through the worksheet.rows[y].id property.


Documentation

Methods

A few method to help with the data and meta information persistence.
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 JSON
save(url: String, data: Any, token: String, callback: Function) : void

Events

The onbeforesave event can be used to intercept, change or cancel the user action.
Method Description
onbeforesave Execute before a server update request.
onbeforesave(spreadsheet: Object, worksheet: Object, data: Object) : Object
It would 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 consider 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',
    }],
    license: 'MWEzMTE4MGFkNWY5YzQzNjE4NjZiNmE1NThhM2M0Yjc1NmUyNGM2N2YzZjU2NDQ5ZjM1MGFiYWNmOTFkNTkwODFiYmYwNDE1YjhhM2ViNGUyMzM2YjYzY2Q4NTcyMWE4MGQ4YjVjNjI2NWY4NWYyMTBjMWU5M2ZmNTU4OGI1MDQsZXlKdVlXMWxJam9pY0dGMWJDNW9iMlJsYkNJc0ltUmhkR1VpT2pFMk5UZzVOakk0TURBc0ltUnZiV0ZwYmlJNld5SnFjM0J5WldGa2MyaGxaWFF1WTI5dElpd2lZM05pTG1Gd2NDSXNJbXB6YUdWc2JDNXVaWFFpTENKc2IyTmhiR2h2YzNRaVhTd2ljR3hoYmlJNklqSWlMQ0p6WTI5d1pTSTZXeUoyTnlJc0luWTRJaXdpY0dGeWMyVnlJaXdpYzJobFpYUnpJaXdpWm05eWJYTWlMQ0p5Wlc1a1pYSWlMQ0ptYjNKdGRXeGhJbDE5',
    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;">';
            }
        }
    },
    oninsertrow: function(o) {
        setTimeout(function() {
            o.refreshBorders();
        },50);
    }
});
</script>
</html>


Record ID syncronization and sequences

The following example implements a few important concepts:
  • How to load the data with IDS that are hidden to the user;
  • Using a custom column to create a icon to perform any action using the record id;
  • Get the record id from a remote server when you add a new row.


NOTE: the example handles with one new line, but you can interact to get more ids in case more than one rows are added 
<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);
            }
        })
    },
    license: 'NDc4ZDc3NWJiNjIzNzU4MTVmNjA4MjdlYjk1MjlmZDZiYzk4MWM3ZmM2OTdiZDczOWQ2MGRkZjdhNTc4ODExNmMyMDZkZDIwNzExYzJmM2EwOTBiYWUxM2M0NGY1YzNiZmVmZTY1OGY5OWI2ZDg2NDMyNjgwOTFhZGQ2MzVlYmEsZXlKdVlXMWxJam9pY0dGMWJDNW9iMlJsYkNJc0ltUmhkR1VpT2pFMk5EY3pPRGc0TURBc0ltUnZiV0ZwYmlJNld5SnFjMmhsYkd3dWJtVjBJaXdpWTI5a1pYTmhibVJpYjNndVkyOXRJaXdpYW5Od2NtVmhaSE5vWldWMExtTnZiU0lzSW1OellpNWhjSEFpTENKalpIQnVMbWx2SWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXlJbjA9',
});
</script>
</html>