Documentation
Back to the Documentation

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.
MethodDescription
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.
EventDescription
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.
PropertyDescription
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('ZWY1MTMxOGE4YzcwMGE3NmIwNzhlZGZmZGZiY2VmNzI4Mzc1ZDA2NjAwYjRjYWVlZDQ0MDQ4MmRiODA2OTQwOWM5ZmRlNGU2YThhYmRiMzM2M2YzM2ViZTBlZjU2MTNjODUwODA1OGVmMmU5MGI4Y2EwY2M0ZjUyZWRhM2UwNmYsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOalE1T0RRMU1Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09');

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 jsfiddle








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="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('ZWY1MTMxOGE4YzcwMGE3NmIwNzhlZGZmZGZiY2VmNzI4Mzc1ZDA2NjAwYjRjYWVlZDQ0MDQ4MmRiODA2OTQwOWM5ZmRlNGU2YThhYmRiMzM2M2YzM2ViZTBlZjU2MTNjODUwODA1OGVmMmU5MGI4Y2EwY2M0ZjUyZWRhM2UwNmYsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOalE1T0RRMU1Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09');

// 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>