Examples
Back to Examples

Spreadsheet events

Binding specific events to your JavaScript spreadsheet.




Log:



Source code

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

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

<script>
var changed = function(instance, cell, x, y, value) {
    console.log(cell)
    var cellName = jspreadsheet.helpers.getColumnNameFromCoords(x,y);
    document.getElementById('log').innerText += 'New change on cell ' + cellName + ' to: ' + value + '
';
}

var beforeChange = function(instance, cell, x, y, value) {
    var cellName = jspreadsheet.helpers.getColumnNameFromCoords(x,y);
    document.getElementById('log').innerText += 'The cell ' + cellName + ' will be changed
';
}

var insertedRow = function(instance) {
    document.getElementById('log').innerText += 'Row added
';
}

var insertedColumn = function(instance) {
    document.getElementById('log').innerText += 'Column added
';
}

var deletedRow = function(instance) {
    document.getElementById('log').innerText += 'Row deleted
';
}

var deletedColumn = function(instance) {
    document.getElementById('log').innerText += 'Column deleted
';
}

var sort = function(instance, cellNum, order) {
    var order = (order) ? 'desc' : 'asc';
    document.getElementById('log').innerText += 'The column  ' + cellNum + ' sorted by ' + order + '
';
}

var resizeColumn = function(instance, cell, width) {
    document.getElementById('log').innerText += 'The column  ' + cell + ' resized to width ' + width + ' px
';
}

var resizeRow = function(instance, cell, height) {
    document.getElementById('log').innerText += 'The row  ' + cell + ' resized to height ' + height + ' px
';
}

var selectionActive = function(instance, x1, y1, x2, y2, origin) {
    var cellName1 = jspreadsheet.helpers.getColumnNameFromCoords(x1,y1);
    var cellName2 = jspreadsheet.helpers.getColumnNameFromCoords(x2,y2);
    document.getElementById('log').innerText += 'The selection from ' + cellName1 + ' to ' + cellName2 + '
';
}

var loaded = function(instance) {
    document.getElementById('log').innerText += 'New data is loaded
';
}

var moveRow = function(instance, from, to) {
    document.getElementById('log').innerText += 'The row ' + from + ' was move to the position of ' + to + ' 
';
}

var moveColumn = function(instance, from, to) {
    document.getElementById('log').innerText += 'The col ' + from + ' was move to the position of ' + to + ' 
';
}

var blur = function(instance) {
    document.getElementById('log').innerText += 'The table is blur
';
}

var focus = function(instance) {
    document.getElementById('log').innerText += 'The table is focus
';
}

var update = function (instance, cell, x, y, value) {
    // If the related series does not exists create a new one
    if (! chart.series[y]) {
        // Create a new series row
        var row = [];
        for (i = 1; i < data1[y].length; i++) {
            row.push(parseFloat(data1[y][i]));
        }
        // Append new series to the chart
        chart.addSeries({ name:data1[y][0], data:row });
    } else {
        if (x == 0) {
            // Update legend
            chart.series[y].update({ name:value });
        } else {
            // Update chart data
            chart.series[y].data[x-1].update({ y:parseFloat(value) });
        }
    }
}

var chart = null;

var data1 = [
    ['Mazda', 2001, 2000, '2006-01-01'],
    ['Pegeout', 2010, 5000, '2005-01-01'],
    ['Honda Fit', 2009, 3000, '2004-01-01'],
    ['Honda CRV', 2010, 6000, '2003-01-01'],
];

var data2 = [
    ['Mazda', 2001, 2000, '2006-01-01'],
    ['Pegeout', 2010, 5000, '2005-01-01'],
    ['Honda Fit', 2009, 3000, '2004-01-01'],
    ['Honda CRV', 2010, 6000, '2003-01-01'],
];

var data3 = [
    ['Tokyo', 7.0, 6.9, 9.5, 14.5, 18.2, 21.5, 25.2, 26.5, 23.3, 18.3, 13.9, 9.6],
    ['New York', -0.2, 0.8, 5.7, 11.3, 17.0, 22.0, 24.8, 24.1, 20.1, 14.1, 8.6, 2.5],
    ['Berlin', -0.9, 0.6, 3.5, 8.4, 13.5, 17.0, 18.6, 17.9, 14.3, 9.0, 3.9, 1.0],
    ['London', 3.9, 4.2, 5.7, 8.5, 11.9, 15.2, 17.0, 16.6, 14.2, 10.3, 6.6, 4.8],
];

document.addEventListener('DOMContentLoaded', function() {
    jspreadsheet(document.getElementById('spreadsheet'), {
        data:data2,
        rowResize:true,
        columnDrag:true,
        columns: [
            { type: 'text', width:'200' },
            { type: 'text', width:'100' },
            { type: 'text', width:'100' },
            { type: 'calendar', width:'100' },
        ],
        onchange: changed,
        onbeforechange: beforeChange,
        oninsertrow: insertedRow,
        oninsertcolumn: insertedColumn,
        ondeleterow: deletedRow,
        ondeletecolumn: deletedColumn,
        onselection: selectionActive,
        onsort: sort,
        onresizerow: resizeRow,
        onresizecolumn: resizeColumn,
        onmoverow: moveRow,
        onmovecolumn: moveColumn,
        onload: loaded,
        onblur: blur,
        onfocus: focus,
        license: 'YzEyNTMzMzY2YjI5NmZkN2I0YTA3OTE1NDE0YzY2ZjQ2NDc5N2EwZDI2NDcxNmYwYjc1ODdhOGNmMDFjNzNhNTRkYWVlZTI2NzBkYmVhZGJjMzIwYzQ3MzI5ZDU4MTRkMWFhZDJkZGRhYzk2YzU2NDJkZjMwMzgzNTkxMTAzZGUsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOalE1Tmpnek9Dd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09',
    });

    jspreadsheet(document.getElementById('spreadsheet-2'), {
        data:data3,
        columns: [
            { type: 'text', width:'120px' },
        ],
        defaultColWidth: '60px',
        onchange:update,
        license: 'YzEyNTMzMzY2YjI5NmZkN2I0YTA3OTE1NDE0YzY2ZjQ2NDc5N2EwZDI2NDcxNmYwYjc1ODdhOGNmMDFjNzNhNTRkYWVlZTI2NzBkYmVhZGJjMzIwYzQ3MzI5ZDU4MTRkMWFhZDJkZGRhYzk2YzU2NDJkZjMwMzgzNTkxMTAzZGUsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOalE1Tmpnek9Dd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09',
    });

    chart = Highcharts.chart('container', {
        title: {
            text: 'Monthly Average Temperature',
            x: -20 //center
        },
        subtitle: {
            text: 'Source: WorldClimate.com',
            x: -20
        },
        xAxis: {
            categories: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun',
                'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
        },
        yAxis: {
            title: {
                text: 'Temperature (°C)'
            },
            plotLines: [{
                value: 0,
                width: 1,
                color: '#808080'
            }]
        },
        tooltip: {
            valueSuffix: '°C'
        },
        legend: {
            layout: 'vertical',
            align: 'right',
            verticalAlign: 'middle',
            borderWidth: 0
        },
        series: [{
            name: 'Tokyo',
            data: [7.0, 6.9, 9.5, 14.5, 18.2, 21.5, 25.2, 26.5, 23.3, 18.3, 13.9, 9.6]
        }, {
            name: 'New York',
            data: [-0.2, 0.8, 5.7, 11.3, 17.0, 22.0, 24.8, 24.1, 20.1, 14.1, 8.6, 2.5]
        }, {
            name: 'Berlin',
            data: [-0.9, 0.6, 3.5, 8.4, 13.5, 17.0, 18.6, 17.9, 14.3, 9.0, 3.9, 1.0]
        }, {
            name: 'London',
            data: [3.9, 4.2, 5.7, 8.5, 11.9, 15.2, 17.0, 16.6, 14.2, 10.3, 6.6, 4.8]
        }]
    });
});
</script>
</html>


3

Advanced Example

Update the chart on every change in your spreadsheet, using the onchange handler




Source code

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

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script src="https://code.highcharts.com/highcharts.js"></script>

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

<script>
var data3 = [
    ['Tokyo', 7.0, 6.9, 9.5, 14.5, 18.2, 21.5, 25.2, 26.5, 23.3, 18.3, 13.9, 9.6],
    ['New York', -0.2, 0.8, 5.7, 11.3, 17.0, 22.0, 24.8, 24.1, 20.1, 14.1, 8.6, 2.5],
    ['Berlin', -0.9, 0.6, 3.5, 8.4, 13.5, 17.0, 18.6, 17.9, 14.3, 9.0, 3.9, 1.0],
    ['London', 3.9, 4.2, 5.7, 8.5, 11.9, 15.2, 17.0, 16.6, 14.2, 10.3, 6.6, 4.8],
];

var update = function (instance, cell, x, y, value) {
    // If the related series does not exists create a new one
    if (! chart.series[y]) {
        // Create a new series row
        var row = [];
        for (i = 1; i < data1[y].length; i++) {
            row.push(parseFloat(data1[y][i]));
        }
        // Append new series to the chart
        chart.addSeries({ name:data1[y][0], data:row });
    } else {
        if (x == 0) {
            // Update legend
            chart.series[y].update({ name:value });
        } else {
            // Update chart data
            chart.series[y].data[x-1].update({ y:parseFloat(value) });
        }
    }
}

jspreadsheet(document.getElementById('spreadsheet'), {
    data:data3,
    columns: [
        { type: 'text', width:'200' },
    ],
    onchange:update,,
    license: 'YzEyNTMzMzY2YjI5NmZkN2I0YTA3OTE1NDE0YzY2ZjQ2NDc5N2EwZDI2NDcxNmYwYjc1ODdhOGNmMDFjNzNhNTRkYWVlZTI2NzBkYmVhZGJjMzIwYzQ3MzI5ZDU4MTRkMWFhZDJkZGRhYzk2YzU2NDJkZjMwMzgzNTkxMTAzZGUsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOalE1Tmpnek9Dd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09',
});

var chart = null;

chart = Highcharts.chart('container', {
    title: {
        text: 'Monthly Average Temperature',
        x: -20 //center
    },
    subtitle: {
        text: 'Source: WorldClimate.com',
        x: -20
    },
    xAxis: {
        categories: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun',
            'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
    },
    yAxis: {
        title: {
            text: 'Temperature (°C)'
        },
        plotLines: [{
            value: 0,
            width: 1,
            color: '#808080'
        }]
    },
    tooltip: {
        valueSuffix: '°C'
    },
    legend: {
        layout: 'vertical',
        align: 'right',
        verticalAlign: 'middle',
        borderWidth: 0
    },
    series: [{
        name: 'Tokyo',
        data: [7.0, 6.9, 9.5, 14.5, 18.2, 21.5, 25.2, 26.5, 23.3, 18.3, 13.9, 9.6]
    }, {
        name: 'New York',
        data: [-0.2, 0.8, 5.7, 11.3, 17.0, 22.0, 24.8, 24.1, 20.1, 14.1, 8.6, 2.5]
    }, {
        name: 'Berlin',
        data: [-0.9, 0.6, 3.5, 8.4, 13.5, 17.0, 18.6, 17.9, 14.3, 9.0, 3.9, 1.0]
    }, {
        name: 'London',
        data: [3.9, 4.2, 5.7, 8.5, 11.9, 15.2, 17.0, 16.6, 14.2, 10.3, 6.6, 4.8]
    }]
});
</script>
</html>