Footers in Jspreadsheet allow you to display information or calculations at the bottom of a data grid. This section provides instructions for implementing footers during initialization or runtime using programming techniques.
Documentation
Methods
The following methods provide programmatic interaction with the footers of the Jspreadsheet data grid.
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
The following events allow you to intercept user interactions with the Jspreadsheet data grid.
Event
Description
onchangefooter
This method is invoked when the footers undergo modification in the Jspreadsheet data grid.
onchangefooter(worksheet: Object, newValue: String, oldValue: String) : null
onchangefootervalue
This method is called when the values of the footers in the Jspreadsheet data grid are modified.
onchangefootervalue(worksheet: Object, x: Number, y: Number, newValue: String) : null
onrenderfootercell
This method is called when a footer cell is rendered.
onrenderfootercell(worksheet: Object, x: Number, y: Number, newValue: String, td: HTMLElement) : null
Initial Settings
The following property is available during the initialization of the Jspreadsheet 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.
Programmatic updates
How to change the formulas in the footers after the initialization.
JavaScript example
<html>
<script src="https://jspreadsheet.com/v10/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/v10/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('YzY3NDE0NTQxZTBlY2FiOGJlZDA2ZGZmZWQ1M2FkYzJmMGViMzAwMTdjMzE2MDgwZDgwM2EzMjg2OWJjMTg0MDc3NDA4NjdjN2FiYmMwNjc3ZGM3NjkxNTA4M2U3Zjc3MDRlYzM0NjE3NWI4NzBkYzU4YjRiNjU3OTBkZDRkMmYsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UY3dNak0zTkRNeE1pd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklsMHNJbVJsYlc4aU9uUnlkV1Y5');
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 && x > 0) {
// Applying a mask to the footer value
td.innerText = jSuites.mask.render(value, { mask: '$ #,##0.00'}, true);
}
}
});
</script>
</html>
React example
import React, { useRef } from "react";
import { Spreadsheet, Worksheet } from "@jspreadsheet/react";
import jSuites from "jsuites";
import formula from "@jspreadsheet/formula";
// License
jspreadsheet.setLicense('YzY3NDE0NTQxZTBlY2FiOGJlZDA2ZGZmZWQ1M2FkYzJmMGViMzAwMTdjMzE2MDgwZDgwM2EzMjg2OWJjMTg0MDc3NDA4NjdjN2FiYmMwNjc3ZGM3NjkxNTA4M2U3Zjc3MDRlYzM0NjE3NWI4NzBkYzU4YjRiNjU3OTBkZDRkMmYsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UY3dNak0zTkRNeE1pd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Load the formula pro extension
jspreadsheet.setExtensions({ formula });
// Create a new data grid
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// Data
const 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"],
];
// Columns
const columns = [
{ width:'400px' }
];
// Data grid cell definitions
const footers = [
[
'Total',
'=SUM(B:B)',
'=SUM(C:C)',
]
];
// Event
const onrenderfootercell = (worksheet, x, y, value, td) => {
if (value && x > 0) {
// Applying a mask to the footer value
td.innerText = jSuites.mask.render(value, { mask: '$ #,##0.00'}, true);
}
}
// Update
const update = () => {
spreadsheet.current[0].setFooterValue(0,0,'Avarage');
spreadsheet.current[0].setFooterValue(1,0,'=AVERAGE(B:B)');
spreadsheet.current[0].setFooterValue(2,0,'=AVERAGE(C:C)');
}
// Render data grid component
return (
<Spreadsheet ref={spreadsheet} onrenderfootercell={onrenderfootercell}>
<Worksheet data={data} columns={columns} footers={footers} />
</Spreadsheet>
<input type='button' value='Change to AVERAGE' onClick={()=>update()}'>
);
}
import { Component, ViewChild, ElementRef } from "@angular/core";
import * as jspreadsheet from "jspreadsheet";
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('YzY3NDE0NTQxZTBlY2FiOGJlZDA2ZGZmZWQ1M2FkYzJmMGViMzAwMTdjMzE2MDgwZDgwM2EzMjg2OWJjMTg0MDc3NDA4NjdjN2FiYmMwNjc3ZGM3NjkxNTA4M2U3Zjc3MDRlYzM0NjE3NWI4NzBkYzU4YjRiNjU3OTBkZDRkMmYsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UY3dNak0zTkRNeE1pd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Create component
@Component({
selector: "app-root",
template: `<div #spreadsheet></div>
<button type="button" (click)="toogle">Update</button>`;
})
export class AppComponent {
@ViewChild("spreadsheet") spreadsheet: ElementRef;
// Worksheets
worksheets: jspreadsheet.worksheetInstance[];
// Create a new data grid
ngOnInit() {
// Create spreadsheet
this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
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 && x > 0) {
// Applying a mask to the footer value
td.innerText = jSuites.mask.render(value, { mask: '$ #,##0.00'}, true);
}
}
});
}
update() {
this.worksheets[0].setFooterValue(0,0,'Avarage');
this.worksheets[0].setFooterValue(1,0,'=AVERAGE(B:B)');
this.worksheets[0].setFooterValue(2,0,'=AVERAGE(C:C)');
}
}
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 jsfiddle
JavaScript example
<html>
<script src="https://jspreadsheet.com/v10/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/v10/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('YzY3NDE0NTQxZTBlY2FiOGJlZDA2ZGZmZWQ1M2FkYzJmMGViMzAwMTdjMzE2MDgwZDgwM2EzMjg2OWJjMTg0MDc3NDA4NjdjN2FiYmMwNjc3ZGM3NjkxNTA4M2U3Zjc3MDRlYzM0NjE3NWI4NzBkYzU4YjRiNjU3OTBkZDRkMmYsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UY3dNak0zTkRNeE1pd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklsMHNJbVJsYlc4aU9uUnlkV1Y5');
// 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>
React example
import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula";
// License
jspreadsheet.setLicense('YzY3NDE0NTQxZTBlY2FiOGJlZDA2ZGZmZWQ1M2FkYzJmMGViMzAwMTdjMzE2MDgwZDgwM2EzMjg2OWJjMTg0MDc3NDA4NjdjN2FiYmMwNjc3ZGM3NjkxNTA4M2U3Zjc3MDRlYzM0NjE3NWI4NzBkYzU4YjRiNjU3OTBkZDRkMmYsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UY3dNak0zTkRNeE1pd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Load the formula pro extension
jspreadsheet.setExtensions({ formula });
// Create a new data grid
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// Update footers from the last data grid
const onafterchanges = function () {
s3[0].setFooter();
}
const s1 = [{
worksheetName: 'JSS1',
data: [
[43, 64],
[51, 46],
],
footers: [
['=SUM(A:A)']
],
}]
const s2 = [{
worksheetName: 'JSS2',
data: [
[34, 53],
[51, 35],
],
footers: [
['=SUM(A:A)']
],
}]
const s3 = [{
worksheetName: 'JSS3',
data: [
[34, 53],
[51, 35],
],
footers: [
['=SUM(JSS1!A:A,JSS2!A:A,JSS3!A:A)']
],
}]
// Render data grid component
return (
<Spreadsheet worksheets={s1} onafterchanges={onafterchanges} />
<Spreadsheet worksheets={s2} onafterchanges={onafterchanges} />
<Spreadsheet worksheets={s3} onafterchanges={onafterchanges} />
);
}