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 to help to interact 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 would be called when the footers are changed.
onchangefootervalue onchangefootervalue(worksheet: Object, x: Number, y: Number, newValue: String) : null
This method would be called when the footers are changed.


Initial Settings

The following property is available through the initialization of the online spreadsheet.
PropertyDescription
footers: string[]Footers definitions


Examples

Basic footer usage

The following example is a basic footer implementation to SUM all values in each respective columns using a native and a custom formula.

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/v8/jspreadsheet.js"></script>
<script src="https://jsuites.net/v4/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v4/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v8/jspreadsheet.css" type="text/css" />

<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'), {
    data: data,
    minDimensions: [4,10],
    footers: [
        [
            'Total',
            '=SUM(B:B)',
            '=SUM(C:C)',
            // Custom calculation to remove the hidden rows from calculations when filtering
            '=SUMCOL(TABLE(), COLUMN(), true)'
        ]
    ],
    columns: [
        { width:'400px' }
    ]
});
</script>
</html>


Programatically updates

Update the value of one footer cell after the initialization.





Source code

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

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

<input type='button' value='Change to AVERAGE' onclick='update()'>

<script>
var data = [
    ['Cheese', 10, 6.00],
    ['Apples', 5, 4.00],
    ['Carrots', 5, 1.00],
    ['Oranges', 6, 2.00],
];

var update = function() {
    spreadsheet.setFooterValue(0,0,'Avarage');
    spreadsheet.setFooterValue(1,0,'=AVERAGE(B:B)');
    spreadsheet.setFooterValue(2,0,'=AVERAGE(C:C)');
}

var spreadsheet = jspreadsheet(document.getElementById('spreadsheet'), {
    data: data,
    footers: [
        [
            'Total',
            '=SUM(B:B)',
            '=SUM(C:C)',
        ]
    ],
    columns: [
        { width:'400px' }
    ]
});
</script>
</html>