Spreadsheet footer
The spreadsheet footers help sticky information or calculations at the bottom of a worksheet. Footers can be defined in the initialization or programmatically.Documentation
Methods
There are some methods for interacting with the footers programmatically.Method | Description |
---|---|
getFooter() | Get the current footer settings.getFooter() : Array |
setFooter() | Set the footer settings.setFooter(newValue: Array) : void |
resetFooter() | Reset and remove the footers.resetFooter() : void |
refreshFooter() | Recalculate the values in the footer.refreshFooter() : void |
getFooterValue(number, number) | Get the current footer cell value from the coordinates x, y.getFooterValue(x: Number, y: Number) : void |
setFooterValue(number, number, string) | Set the value of a footer cell to the coordinates x, y.setFooterValue(x: Number, y: Number, value: String) : void |
Events
Events related to the footer property.Event | Description |
---|---|
onchangefooter | onchangefooter(worksheet: Object, newValue: String, oldValue: String) : null This method is called when the footers are changed. |
onchangefootervalue | onchangefootervalue(worksheet: Object, x: Number, y: Number, newValue: String) : null This method is called when the footer values are changed. |
onrenderfootercell | onrenderfootercell(worksheet: Object, x: Number, y: Number, newValue: String, td: HTMLElement) : null This method is called when a footer cell is rendered. |
Initial Settings
The following property is available through the initialization of the online spreadsheet.Property | Description |
---|---|
footers: string[] | Footers definitions |
Examples
Basic footer usage
In the example below the custom SUMCOL formula is implemented to consider only visible rows in the calculations.Source code
<html> <script src="https://jspreadsheet.com/v9/jspreadsheet.js"></script> <script src="https://jsuites.net/v5/jsuites.js"></script> <link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" /> <link rel="stylesheet" href="https://jspreadsheet.com/v9/jspreadsheet.css" type="text/css" /> <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" /> <div id="spreadsheet"></div> <script> var data = [ ['Cheese', 10, 6.00, "=B1*C1"], ['Apples', 5, 4.00, "=B2*C2"], ['Carrots', 5, 1.00, "=B3*C3"], ['Oranges', 6, 2.00, "=B4*C4"], ]; /** * Custom method to SUM all rows of one column specified * @param {object} the worksheet * @param {number} the column number * @param {boolean} consider only thte visible columns */ var SUMCOL = function(instance, columnId, onlyVisible) { var total = 0; var num = 0; for (var j = 0; j < instance.options.data.length; j++) { if (! onlyVisible || ! instance.results || instance.results.indexOf(j) >= 0) { if (num = Number(instance.records[j][columnId-1].element.innerText)) { total += num; } } } return total; } // Send the method to the correct scope formula.setFormula({ SUMCOL }); // Create the spreadsheet jspreadsheet(document.getElementById('spreadsheet'), { worksheets: [{ data: data, minDimensions: [4,10], footers: [ [ 'Total', '=SUMCOL(TABLE(), COLUMN(), true)', '=SUMCOL(TABLE(), COLUMN(), true)', '=SUMCOL(TABLE(), COLUMN(), true)' ] ], columns: [ { width:'400px' } ] }] }); </script> </html>
Programmatic updates
How to change the formulas in the footers after the initialization.Source code
<html> <script src="https://jspreadsheet.com/v9/jspreadsheet.js"></script> <script src="https://jsuites.net/v5/jsuites.js"></script> <link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" /> <link rel="stylesheet" href="https://jspreadsheet.com/v9/jspreadsheet.css" type="text/css" /> <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" /> <div id="spreadsheet"></div> <input type='button' value='Change to AVERAGE' onclick='update()'> <script> // Set your JSS license key (The following key only works for one day) jspreadsheet.setLicense('MmZlMGM2NTIyZDcwOTkzNGRjOGE2OTJmZTU5N2IxNDYyMTRjZTYwYzUxY2MwN2FmZjJhYzZkZGY2MTVkNGJkOTBiZGU5YjkxNjkxZDdjMWZmOGM5ZDA4Y2IzNThjZjJhZjFiMDJkYjE2NDgyNzhkNTdmZTBmYzhhZDM5ZTIzYzIsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOakl6TmpJeE1pd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09'); var update = function() { worksheets[0].setFooterValue(0,0,'Avarage'); worksheets[0].setFooterValue(1,0,'=AVERAGE(B:B)'); worksheets[0].setFooterValue(2,0,'=AVERAGE(C:C)'); } // Create the spreadsheet var worksheets = jspreadsheet(document.getElementById('spreadsheet'), { worksheets: [{ data: [ ['Cheese', 10, 6.00], ['Apples', 5, 4.00], ['Carrots', 5, 1.00], ['Oranges', 6, 2.00], ], footers: [ [ 'Total', '=SUM(B:B)', '=SUM(C:C)', ] ], columns: [ { width:'400px' } ] }], onrenderfootercell: function(worksheet, x, y, value, td) { if (value) { // Applying a mask to the footer value td.innerText = jSuites.mask.render(value, { mask: '$ #,##0.00'}, true); } } }); </script> </html>
Cross footer calculations
Footers are used to hold static information, but the following example creates a complete cross-footer calculations with realtime updates using onafterchanges. See our cross spreadsheet footer calculations on jsfiddleSource code
<html> <script src="https://jspreadsheet.com/v9/jspreadsheet.js"></script> <script src="https://jsuites.net/v5/jsuites.js"></script> <link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" /> <link rel="stylesheet" href="https://jspreadsheet.com/v9/jspreadsheet.css" type="text/css" /> <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" /> <div id="spreadsheet1"></div><br> <div id="spreadsheet2"></div><br> <div id="spreadsheet3"></div><br> <script> // Set your JSS license key (The following key only works for one day) jspreadsheet.setLicense('MmZlMGM2NTIyZDcwOTkzNGRjOGE2OTJmZTU5N2IxNDYyMTRjZTYwYzUxY2MwN2FmZjJhYzZkZGY2MTVkNGJkOTBiZGU5YjkxNjkxZDdjMWZmOGM5ZDA4Y2IzNThjZjJhZjFiMDJkYjE2NDgyNzhkNTdmZTBmYzhhZDM5ZTIzYzIsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOakl6TmpJeE1pd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09'); // Create the spreadsheet var s1 = jspreadsheet(document.getElementById('spreadsheet1'), { worksheets: [{ worksheetName: 'JSS1', data: [ [43, 64], [51, 46], ], footers: [ ['=SUM(A:A)'] ], }], onafterchanges: function () { s3[0].setFooter(); } }); // Create the spreadsheet var s2 = jspreadsheet(document.getElementById('spreadsheet2'), { worksheets: [{ worksheetName: 'JSS2', data: [ [34, 53], [51, 35], ], footers: [ ['=SUM(A:A)'] ], }], onafterchanges: function () { s3[0].setFooter(); } }); // Create the spreadsheet var s3 = jspreadsheet(document.getElementById('spreadsheet3'), { worksheets: [{ worksheetName: 'JSS3', data: [ [34, 53], [51, 35], ], footers: [ ['=SUM(JSS1!A:A,JSS2!A:A,JSS3!A:A)'] ], }], onafterchanges: function () { s3[0].setFooter(); } }); </script> </html>