Worksheets

One of the most important changes from v8 is the worksheet management. Now there is a spreadsheet component which acts as the container for each new worksheet, and brings several exclusive methods, events and properties to manage the worksheets in a more efficient way.


Documentation

Initial Settings

Configure the behavior of the spreadsheet and the worksheet management using the worksheet related settings below.

PropertyDescription
Spreadsheet properties
tabs: boolean|objectShow tabs and allow the user to create new worksheets. Default: false. It can be used with extended tabs options.
allowDeleteWorksheet: booleanAdd a delete worksheet option to the contextMenu. Default: true
allowRenameWorksheet: booleanAdd a rename worksheet option to the contextMenu. Default: true
allowMoveWorksheet: booleanAllow worksheet drag and drop options. Default: true
Worksheet properties
worksheetId: stringWorksheet identification. Default: randomNumber
worksheetName: stringWorksheet title. Default: string + integer


Methods

Available methods to interact programmatically with the worksheets.
PropertyDescription
createWorksheet(object) : voidAdd a new worksheet to the online spreadsheet based on a given configuration.
createWorksheet(Object configuration) : void
deleteWorksheet(number) : voidDelete worksheet by position.
deleteWorksheet(Integer worksheetNumber) : boid
getWorksheet(mixed) : worksheetPositionGet the worksheet position by the worksheet object or by the worksheetId.
getWorksheet(Object worksheetInstace | String worksheetId) : number
openWorksheet(number) : voidOpen the worksheet with position: worksheetNumber (starts on zero)
openWorksheet(Number worksheetPosition) : void
renameWorksheet(number, string)Change a worksheet title.
renameWorksheet(Number worksheetNumber, String worksheetNewTitle) : void
moveWorksheet(number, number)Update the worksheet position.
moveWorksheet(Number fromPosition, Number toPosition) : void


Available events

Available events related to the worksheets.
EventDescription
onopenworksheetonopenworksheet(Object worksheet, Number worksheetNumber) : void
onbeforecreateworksheetBefore create a new worksheet, it is possible to overwrite the configuration for the new worksheet or cancel the operation (return false).
onbeforecreateworksheet(Object options, Number worksheetNumber) : mixed
oncreateworksheetoncreateworksheet(Object worksheetInstance, Object worksheetOptions, Number worksheetNumber) : void
onrenameworksheetonrenameworksheet(Object worksheetInstance, Number worksheetNumber, String newValue, String oldValue) : void;
ondeleteworksheetondeleteworksheet(Object oldWorksheetInstance, Number oldWorksheetNumber) : void
onmoveworksheetonmoveworksheet(Object worksheetInstance, Number newPosition, Number oldPosition) : void


Examples

Add new worksheet button

Allow users to add new worksheets to an existing spreadsheet.



Source code

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

<div id="spreadsheet"></div>

<script>
var data = [
    ["1","DIVINELY UNINSPIRED TO A HELLISH EXTENT","LEWIS CAPALDI"],
    ["2","NO 6 COLLABORATIONS PROJECT","ED SHEERAN"],
    ["3","THE GREATEST SHOWMAN","MOTION PICTURE CAST RECORDING"],
    ["4","WHEN WE ALL FALL ASLEEP WHERE DO WE GO","BILLIE EILISH"]
];

jspreadsheet(document.getElementById('spreadsheet'), {
    // Allow create a new tab button
    tabs: true,
    // Intercept the new worksheet and define the options for the new worksheet
    onbeforecreateworksheet: function() {
        var options = {
            minDimensions: [5,5],
        }
        return options;
    },
    // Run an event as soon the new worksheet is created
    onopenworksheet: function(element, instance, worksheetNumber) {
        console.log(element, instance, worksheetNumber);
    },
    // Initial worksheet
    worksheets: [
        {
            data: data,
            columns: [
                { type: 'autonumber', title: 'Id' },
                { type: 'text', width: '350px', title: 'Title' },
                { type: 'text', width: '250px', title: 'Artist' },
             ]
        }
    ],
    license: 'MWEzMTE4MGFkNWY5YzQzNjE4NjZiNmE1NThhM2M0Yjc1NmUyNGM2N2YzZjU2NDQ5ZjM1MGFiYWNmOTFkNTkwODFiYmYwNDE1YjhhM2ViNGUyMzM2YjYzY2Q4NTcyMWE4MGQ4YjVjNjI2NWY4NWYyMTBjMWU5M2ZmNTU4OGI1MDQsZXlKdVlXMWxJam9pY0dGMWJDNW9iMlJsYkNJc0ltUmhkR1VpT2pFMk5UZzVOakk0TURBc0ltUnZiV0ZwYmlJNld5SnFjM0J5WldGa2MyaGxaWFF1WTI5dElpd2lZM05pTG1Gd2NDSXNJbXB6YUdWc2JDNXVaWFFpTENKc2IyTmhiR2h2YzNRaVhTd2ljR3hoYmlJNklqSWlMQ0p6WTI5d1pTSTZXeUoyTnlJc0luWTRJaXdpY0dGeWMyVnlJaXdpYzJobFpYUnpJaXdpWm05eWJYTWlMQ0p5Wlc1a1pYSWlMQ0ptYjNKdGRXeGhJbDE5'
});
</script>

<input type='button' value='Create a new tab' onclick="create()">
</html>


Programatic operations on worksheets

Create a new worksheet programatically.





Source code

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

<div id="spreadsheet-2"></div>

<input type="button" value="Create a new worksheet"
    onclick="spreadsheet.createWorksheet({ minDimensions: [5,5] })">

<script>
var spreadsheet = jspreadsheet(document.getElementById('spreadsheet-2'), {
    worksheets: [
        {
            minDimensions: [5,5],
            defaultColWidth: '100px',
            worksheetName: 'Example2',
        }
    ],
    license: 'MWEzMTE4MGFkNWY5YzQzNjE4NjZiNmE1NThhM2M0Yjc1NmUyNGM2N2YzZjU2NDQ5ZjM1MGFiYWNmOTFkNTkwODFiYmYwNDE1YjhhM2ViNGUyMzM2YjYzY2Q4NTcyMWE4MGQ4YjVjNjI2NWY4NWYyMTBjMWU5M2ZmNTU4OGI1MDQsZXlKdVlXMWxJam9pY0dGMWJDNW9iMlJsYkNJc0ltUmhkR1VpT2pFMk5UZzVOakk0TURBc0ltUnZiV0ZwYmlJNld5SnFjM0J5WldGa2MyaGxaWFF1WTI5dElpd2lZM05pTG1Gd2NDSXNJbXB6YUdWc2JDNXVaWFFpTENKc2IyTmhiR2h2YzNRaVhTd2ljR3hoYmlJNklqSWlMQ0p6WTI5d1pTSTZXeUoyTnlJc0luWTRJaXdpY0dGeWMyVnlJaXdpYzJobFpYUnpJaXdpWm05eWJYTWlMQ0p5Wlc1a1pYSWlMQ0ptYjNKdGRXeGhJbDE5',
});
</script>
</html>


Formulas on worksheets Pro feature

It is possible on the Pro distribution to execute formulas with variables from any other existing worksheet. The syntax follow the same standard from other spreadsheet software such as Google spreadsheet or Excel, using the exclamation mark, for instance "Products!A2*10".



Source code

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


<div id="spreadsheet-3"></div>

<script>
var data1 = [
    ['Cheese', 10, 6.00, "=B1*C1"],
    ['Apples', 5, 4.00, "=B2*C2"],
    ['Carrots', 5, 1.00, "=B3*C3"],
    ['Oranges', 6, 2.00, "=B4*C4"],
];

var data2 = [
    ['20%', "=Products!D1"],
    ['20%', "=Products!D2"],
    ['20%', "=Products!D3"],
    ['20%', "=Products!D4"],
];

jspreadsheet(document.getElementById('spreadsheet-3'), {
    onload: function() {
        console.log('Ready');
    },
    worksheets: [
        {
            data: data1,
            minDimensions: [5,5],
            defaultColWidth: '100px',
            worksheetName: 'Products',
        },
        {
            data: data2,
            minDimensions: [5,5],
            defaultColWidth: '100px',
            worksheetName: 'Profitability',
        }
    ],
    license: 'MWEzMTE4MGFkNWY5YzQzNjE4NjZiNmE1NThhM2M0Yjc1NmUyNGM2N2YzZjU2NDQ5ZjM1MGFiYWNmOTFkNTkwODFiYmYwNDE1YjhhM2ViNGUyMzM2YjYzY2Q4NTcyMWE4MGQ4YjVjNjI2NWY4NWYyMTBjMWU5M2ZmNTU4OGI1MDQsZXlKdVlXMWxJam9pY0dGMWJDNW9iMlJsYkNJc0ltUmhkR1VpT2pFMk5UZzVOakk0TURBc0ltUnZiV0ZwYmlJNld5SnFjM0J5WldGa2MyaGxaWFF1WTI5dElpd2lZM05pTG1Gd2NDSXNJbXB6YUdWc2JDNXVaWFFpTENKc2IyTmhiR2h2YzNRaVhTd2ljR3hoYmlJNklqSWlMQ0p6WTI5d1pTSTZXeUoyTnlJc0luWTRJaXdpY0dGeWMyVnlJaXdpYzJobFpYUnpJaXdpWm05eWJYTWlMQ0p5Wlc1a1pYSWlMQ0ptYjNKdGRXeGhJbDE5',
});
</script>
</html>