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>