Documentationkeyboard_arrow_rightFooters

Data Grid Footers

Footers allow you to display information, summaries, or calculations at the bottom of a data grid. This section provides instructions for implementing footers during initialization or runtime using programmatic techniques.

Documentation

Methods

Methods providing 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
getFooterValue Get the current footer cell value from the coordinates x, y.
getFooterValue(x: Number, y: Number) : void
setFooterValue Set the value of a footer cell. In v12, accepts array of values.
setFooterValue(records: FooterRecord[]) : void		 
interface FooterRecord {
    value: string | number | boolean | undefined;
    x: number;
    y: number;
}

Events

Events allowing you to intercept user interactions with the Jspreadsheet data grid.

Event Description
onchangefooter Triggered when the footers undergo modification in the Jspreadsheet data grid.
onchangefooter(worksheet: Object, newValue: String, oldValue: String) : null
onchangefootervalue Triggered when the values of the footers in the Jspreadsheet data grid are modified.
onchangefootervalue(worksheet: Object, records: FooterRecord[]) : null
onrenderfootercell Triggered when a footer cell is rendered.
onrenderfootercell(worksheet: Object, footer: object) : null

Initial Settings

Properties available during the initialization of the Jspreadsheet online spreadsheet.

Property Description
footers?: string[] Footer definitions

Examples

Basic footer with mask

The following example shows how to implement footers with basic formulas and formatting.

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

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />

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

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NGM4OGQ5OTYwYjVjMzI1NjgxYjRlYTg3MjJhM2U4NDIzY2ZhZTc5ZTQ3MmFhYWM1NTgzZmNkNWQ0OWEyMTExOWQ4Y2FmNmQwYjVkNGNjNzFhZGJkZjU0MzM1NTViNGY1YTNhYjI5NTdjNTA3YjI4MjMxM2QxMDBiMTJkZWQyZDAsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZek9ERXdOREl4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Create the spreadsheet
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, footer) {
        if (footer.v && footer.x > 0) {
            // Applying a mask to the footer value
            footer.element.textContent = jSuites.mask.render(footer.v, { mask: '$ #,##0.00'}, true);
        }
    }
});
</script>
</html>

import React, {useRef} from "react";
import {Spreadsheet, Worksheet, jspreadsheet} from "@jspreadsheet/react";
import jSuites from "jsuites";
import formula from "@jspreadsheet/formula";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// License
jspreadsheet.setLicense('NGM4OGQ5OTYwYjVjMzI1NjgxYjRlYTg3MjJhM2U4NDIzY2ZhZTc5ZTQ3MmFhYWM1NTgzZmNkNWQ0OWEyMTExOWQ4Y2FmNmQwYjVkNGNjNzFhZGJkZjU0MzM1NTViNGY1YTNhYjI5NTdjNTA3YjI4MjMxM2QxMDBiMTJkZWQyZDAsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZek9ERXdOREl4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// 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],
        ['Apples', 5, 4.00],
        ['Carrots', 5, 1.00],
        ['Oranges', 6, 2.00],
    ];
    // Columns
    const columns = [
        {width: '400px'}
    ];
    // Data grid cell definitions
    const footers = [
        [
            'Total',
            '=SUM(B:B)',
            '=SUM(C:C)',
        ]
    ];
    // Event
    const onrenderfootercell = (worksheet, footer) => {
        if (footer.v && footer.x > 0) {
            // Applying a mask to the footer value
            footer.element.textContent = jSuites.mask.render(footer.v, {mask: '$ #,##0.00'}, true);
        }
    }
    // Render data grid component
    return (
        <Spreadsheet ref={spreadsheet} onrenderfootercell={onrenderfootercell}>
            <Worksheet data={data} columns={columns} footers={footers}/>
        </Spreadsheet>
    );
}

<template>
  <Spreadsheet ref="spreadsheet" :onrenderfootercell="onrenderfootercell">
    <Worksheet :data="data" :columns="columns" :footers="footers" />
  </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from '@jspreadsheet/vue';
import formula from '@jspreadsheet/formula';
import jSuites from 'jsuites';

import 'jsuites/dist/jsuites.css';
import 'jspreadsheet/dist/jspreadsheet.css';

// License
jspreadsheet.setLicense('NGM4OGQ5OTYwYjVjMzI1NjgxYjRlYTg3MjJhM2U4NDIzY2ZhZTc5ZTQ3MmFhYWM1NTgzZmNkNWQ0OWEyMTExOWQ4Y2FmNmQwYjVkNGNjNzFhZGJkZjU0MzM1NTViNGY1YTNhYjI5NTdjNTA3YjI4MjMxM2QxMDBiMTJkZWQyZDAsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZek9ERXdOREl4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Load the formula pro extension
jspreadsheet.setExtensions({ formula });

export default {
  components: {
    Spreadsheet,
    Worksheet,
  },
  methods: {
    // Event
    onrenderfootercell(worksheet, footer) {
      if (footer.v && footer.x > 0) {
        // Applying a mask to the footer value
        footer.element.textContent = jSuites.mask.render(footer.v, { mask: '$ #,##0.00' }, true);
      }
    },
  },
  data() {
    // Data
    const data = [
      ['Cheese', 10, 6.0],
      ['Apples', 5, 4.0],
      ['Carrots', 5, 1.0],
      ['Oranges', 6, 2.0],
    ];
    // Columns
    const columns = [{ width: '400px' }];
    // Data grid cell definitions
    const footers = [['Total', '=SUM(B:B)', '=SUM(C:C)']];

    return {
      data,
      columns,
      footers,
    };
  },
};
</script>

import { Component, ViewChild, ElementRef, AfterViewInit } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import formula from '@jspreadsheet/formula';
import jSuites from 'jsuites';
import 'jsuites/dist/jsuites.css';
import 'jspreadsheet/dist/jspreadsheet.css';

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NGM4OGQ5OTYwYjVjMzI1NjgxYjRlYTg3MjJhM2U4NDIzY2ZhZTc5ZTQ3MmFhYWM1NTgzZmNkNWQ0OWEyMTExOWQ4Y2FmNmQwYjVkNGNjNzFhZGJkZjU0MzM1NTViNGY1YTNhYjI5NTdjNTA3YjI4MjMxM2QxMDBiMTJkZWQyZDAsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZek9ERXdOREl4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Load the formula pro extension
jspreadsheet.setExtensions({ formula });

// Create component
@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`
})
export class AppComponent implements AfterViewInit {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];
    // Create a new data grid
    ngAfterViewInit() {
        // 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, footer) {
                if (footer.v && footer.x > 0) {
                    // Applying a mask to the footer value
                    footer.element.textContent = jSuites.mask.render(footer.v, { mask: '$ #,##0.00'}, true);
                }
            }
        });
    }
}

Programmatic updates

Change the formulas in the footers after initialization.

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

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />

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

<p><input type='button' value='Change to AVERAGE' id="btn1"></p>

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NGM4OGQ5OTYwYjVjMzI1NjgxYjRlYTg3MjJhM2U4NDIzY2ZhZTc5ZTQ3MmFhYWM1NTgzZmNkNWQ0OWEyMTExOWQ4Y2FmNmQwYjVkNGNjNzFhZGJkZjU0MzM1NTViNGY1YTNhYjI5NTdjNTA3YjI4MjMxM2QxMDBiMTJkZWQyZDAsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZek9ERXdOREl4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Create the spreadsheet
let 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, footer) {
        if (footer.v && footer.x > 0) {
            // Applying a mask to the footer value
            footer.element.textContent = jSuites.mask.render(footer.v, { mask: '$ #,##0.00'}, true);
        }
    }
});

let update = function() {
    worksheets[0].setFooterValue([
        { x: 0, y: 0, value: 'Average' },
        { x: 1, y: 0, value: '=AVERAGE(B:B)' },
        { x: 2, y: 0, value: '=AVERAGE(C:C)' }
    ]);
}

document.getElementById("btn1").onclick = update
</script>
</html>

import React, {useRef} from "react";
import {Spreadsheet, Worksheet, jspreadsheet} from "@jspreadsheet/react";
import jSuites from "jsuites";
import formula from "@jspreadsheet/formula";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// License
jspreadsheet.setLicense('NGM4OGQ5OTYwYjVjMzI1NjgxYjRlYTg3MjJhM2U4NDIzY2ZhZTc5ZTQ3MmFhYWM1NTgzZmNkNWQ0OWEyMTExOWQ4Y2FmNmQwYjVkNGNjNzFhZGJkZjU0MzM1NTViNGY1YTNhYjI5NTdjNTA3YjI4MjMxM2QxMDBiMTJkZWQyZDAsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZek9ERXdOREl4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// 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],
        ['Apples', 5, 4.00],
        ['Carrots', 5, 1.00],
        ['Oranges', 6, 2.00],
    ];
    // Columns
    const columns = [
        {width: '400px'}
    ];
    // Data grid cell definitions
    const footers = [
        [
            'Total',
            '=SUM(B:B)',
            '=SUM(C:C)',
        ]
    ];
    // Event
    const onrenderfootercell = (worksheet, footer) => {
        if (footer.v && footer.x > 0) {
            // Applying a mask to the footer value
            footer.element.textContent = jSuites.mask.render(footer.v, {mask: '$ #,##0.00'}, true);
        }
    }
    // Update
    const update = () => {
        spreadsheet.current[0].setFooterValue([
            { x: 0, y: 0, value: 'Average' },
            { x: 1, y: 0, value: '=AVERAGE(B:B)' },
            { x: 2, y: 0, value: '=AVERAGE(C:C)' }
        ]);
    }
    // Render data grid component
    return (<>
        <Spreadsheet ref={spreadsheet} onrenderfootercell={onrenderfootercell}>
            <Worksheet data={data} columns={columns} footers={footers}/>
        </Spreadsheet>
        <button type="button" onClick={update}>Change to AVERAGE</button>
    </>);
}

<template>
  <Spreadsheet ref="spreadsheet" :onrenderfootercell="onrenderfootercell">
    <Worksheet :data="data" :columns="columns" :footers="footers" />
  </Spreadsheet>
  <button type="button" @click="update">Change to AVERAGE</button>
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from '@jspreadsheet/vue';
import formula from '@jspreadsheet/formula';
import jSuites from 'jsuites';

import 'jsuites/dist/jsuites.css';
import 'jspreadsheet/dist/jspreadsheet.css';

// License
jspreadsheet.setLicense('NGM4OGQ5OTYwYjVjMzI1NjgxYjRlYTg3MjJhM2U4NDIzY2ZhZTc5ZTQ3MmFhYWM1NTgzZmNkNWQ0OWEyMTExOWQ4Y2FmNmQwYjVkNGNjNzFhZGJkZjU0MzM1NTViNGY1YTNhYjI5NTdjNTA3YjI4MjMxM2QxMDBiMTJkZWQyZDAsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZek9ERXdOREl4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Load the formula pro extension
jspreadsheet.setExtensions({ formula });

export default {
  components: {
    Spreadsheet,
    Worksheet,
  },
  methods: {
    update() {
      this.$refs.spreadsheet.current[0].setFooterValue([
        { x: 0, y: 0, value: 'Average' },
        { x: 1, y: 0, value: '=AVERAGE(B:B)' },
        { x: 2, y: 0, value: '=AVERAGE(C:C)' }
      ]);
    },
    // Event
    onrenderfootercell(worksheet, footer) {
      if (footer.v && footer.x > 0) {
        // Applying a mask to the footer value
        footer.element.textContent = jSuites.mask.render(footer.v, { mask: '$ #,##0.00' }, true);
      }
    },
  },
  data() {
    // Data
    const data = [
      ['Cheese', 10, 6.0],
      ['Apples', 5, 4.0],
      ['Carrots', 5, 1.0],
      ['Oranges', 6, 2.0],
    ];
    // Columns
    const columns = [{ width: '400px' }];
    // Data grid cell definitions
    const footers = [['Total', '=SUM(B:B)', '=SUM(C:C)']];

    return {
      data,
      columns,
      footers,
    };
  },
};
</script>

import { Component, ViewChild, ElementRef, AfterViewInit } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import formula from '@jspreadsheet/formula';
import jSuites from 'jsuites';
import 'jsuites/dist/jsuites.css';
import 'jspreadsheet/dist/jspreadsheet.css';

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NGM4OGQ5OTYwYjVjMzI1NjgxYjRlYTg3MjJhM2U4NDIzY2ZhZTc5ZTQ3MmFhYWM1NTgzZmNkNWQ0OWEyMTExOWQ4Y2FmNmQwYjVkNGNjNzFhZGJkZjU0MzM1NTViNGY1YTNhYjI5NTdjNTA3YjI4MjMxM2QxMDBiMTJkZWQyZDAsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZek9ERXdOREl4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Load the formula pro extension
jspreadsheet.setExtensions({ formula });

// Create component
@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>
        <button type="button" (click)="update()">Change to AVERAGE</button>`
})
export class AppComponent implements AfterViewInit {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];
    // Create a new data grid
    ngAfterViewInit() {
        // 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, footer) {
                if (footer.v && footer.x > 0) {
                    // Applying a mask to the footer value
                    footer.element.textContent = jSuites.mask.render(footer.v, { mask: '$ #,##0.00'}, true);
                }
            }
        });
    }
    update() {
        this.worksheets[0].setFooterValue([
            { x: 0, y: 0, value: 'Average' },
            { x: 1, y: 0, value: '=AVERAGE(B:B)' },
            { x: 2, y: 0, value: '=AVERAGE(C:C)' }
        ]);
    }
}

Cross-footer Calculations

Footers are used to hold static information, but the following example creates a complete cross-footer calculation with real-time updates using onafterchanges. See cross-spreadsheet footer calculations on jsfiddle.

<html>
<script src="https://jspreadsheet.com/v12/jspreadsheet.js"></script>
<script src="https://jsuites.net/v6/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v6/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v12/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('NGM4OGQ5OTYwYjVjMzI1NjgxYjRlYTg3MjJhM2U4NDIzY2ZhZTc5ZTQ3MmFhYWM1NTgzZmNkNWQ0OWEyMTExOWQ4Y2FmNmQwYjVkNGNjNzFhZGJkZjU0MzM1NTViNGY1YTNhYjI5NTdjNTA3YjI4MjMxM2QxMDBiMTJkZWQyZDAsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZek9ERXdOREl4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Load the formula pro extension
jspreadsheet.setExtensions({ formula });

// Create the spreadsheet
let s1 = jspreadsheet(document.getElementById('spreadsheet1'), {
    worksheets: [{
        worksheetName: 'JSS1',
        data: [
            [43, 64],
            [51, 46],
        ],
        footers: [
            ['=SUM(A:A)']
        ],
    }],
});

// Create the spreadsheet
let s2 = jspreadsheet(document.getElementById('spreadsheet2'), {
    worksheets: [{
        worksheetName: 'JSS2',
        data: [
            [34, 53],
            [51, 35],
        ],
        footers: [
            ['=SUM(A:A)']
        ],
    }],
});

// Create the spreadsheet
let s3 = jspreadsheet(document.getElementById('spreadsheet3'), {
    worksheets: [{
        worksheetName: 'JSS3',
        data: [
            [34, 53],
            [51, 35],
        ],
        footers: [
            ['=SUM(JSS1!A:A,JSS2!A:A,JSS3!A:A)']
        ],
    }],
});
</script>
</html>

import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// License
jspreadsheet.setLicense('NGM4OGQ5OTYwYjVjMzI1NjgxYjRlYTg3MjJhM2U4NDIzY2ZhZTc5ZTQ3MmFhYWM1NTgzZmNkNWQ0OWEyMTExOWQ4Y2FmNmQwYjVkNGNjNzFhZGJkZjU0MzM1NTViNGY1YTNhYjI5NTdjNTA3YjI4MjMxM2QxMDBiMTJkZWQyZDAsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZek9ERXdOREl4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Load the formula pro extension
jspreadsheet.setExtensions({ formula });

// Create a new data grid
export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet1 = useRef();
    const spreadsheet2 = useRef();
    const spreadsheet3 = useRef();

    // Update footers from the last data grid
    const onafterchanges = function () {
        if (spreadsheet3.current) {
            spreadsheet3.current[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 (
        <p>
            <Spreadsheet ref={spreadsheet1} worksheets={s1} onafterchanges={onafterchanges} />
            <Spreadsheet ref={spreadsheet2} worksheets={s2} onafterchanges={onafterchanges} />
            <Spreadsheet ref={spreadsheet3} worksheets={s3} onafterchanges={onafterchanges} />
        </p>
    );
}

<template>
    <Spreadsheet ref="spreadsheet1" :worksheets="s1" :onafterchanges="onafterchanges" />
    <Spreadsheet ref="spreadsheet2" :worksheets="s2" :onafterchanges="onafterchanges" />
    <Spreadsheet ref="spreadsheet3" :worksheets="s3" :onafterchanges="onafterchanges" />
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import formula from "@jspreadsheet/formula";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// License
jspreadsheet.setLicense('NGM4OGQ5OTYwYjVjMzI1NjgxYjRlYTg3MjJhM2U4NDIzY2ZhZTc5ZTQ3MmFhYWM1NTgzZmNkNWQ0OWEyMTExOWQ4Y2FmNmQwYjVkNGNjNzFhZGJkZjU0MzM1NTViNGY1YTNhYjI5NTdjNTA3YjI4MjMxM2QxMDBiMTJkZWQyZDAsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZek9ERXdOREl4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Load the formula pro extension
jspreadsheet.setExtensions({ formula });

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    methods: {
        // Update footers from the last data grid
        onafterchanges() {
            if (this.$refs.spreadsheet3) {
                this.$refs.spreadsheet3.current[0].setFooter();
            }
        }
    },
    data() {
        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)']
            ],
        }]

        return {
            s1,
            s2,
            s3,
        };
    }
}
</script>

import { Component, ViewChild, ElementRef, AfterViewInit } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import formula from "@jspreadsheet/formula";
import "jspreadsheet/dist/jspreadsheet.css";
import "jsuites/dist/jsuites.css";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NGM4OGQ5OTYwYjVjMzI1NjgxYjRlYTg3MjJhM2U4NDIzY2ZhZTc5ZTQ3MmFhYWM1NTgzZmNkNWQ0OWEyMTExOWQ4Y2FmNmQwYjVkNGNjNzFhZGJkZjU0MzM1NTViNGY1YTNhYjI5NTdjNTA3YjI4MjMxM2QxMDBiMTJkZWQyZDAsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZek9ERXdOREl4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Load the formula pro extension
jspreadsheet.setExtensions({ formula });

// Create component
@Component({
    standalone: true,
    selector: "app-root",
    template: `
        <div #spreadsheet1></div>
        <div #spreadsheet2></div>
        <div #spreadsheet3></div>
    `
})
export class AppComponent implements AfterViewInit {
    @ViewChild("spreadsheet1") spreadsheet1: ElementRef;
    @ViewChild("spreadsheet2") spreadsheet2: ElementRef;
    @ViewChild("spreadsheet3") spreadsheet3: ElementRef;
    // Worksheets
    s1: jspreadsheet.worksheetInstance[];
    s2: jspreadsheet.worksheetInstance[];
    s3: jspreadsheet.worksheetInstance[];
    // Create a new data grid
    ngAfterViewInit() {
        // Create the spreadsheet
        this.s1 = jspreadsheet(this.spreadsheet1.nativeElement, {
            worksheets: [{
                worksheetName: 'JSS1',
                data: [
                    [43, 64],
                    [51, 46],
                ],
                footers: [
                    ['=SUM(A:A)']
                ],
            }],
            onafterchanges: () => {
                this.s3[0].setFooter();
            }
        });

        // Create the spreadsheet
        this.s2 = jspreadsheet(this.spreadsheet2.nativeElement, {
            worksheets: [{
                worksheetName: 'JSS2',
                data: [
                    [34, 53],
                    [51, 35],
                ],
                footers: [
                    ['=SUM(A:A)']
                ],
            }],
            onafterchanges: () => {
                this.s3[0].setFooter();
            }
        });

        // Create the spreadsheet
        this.s3 = jspreadsheet(this.spreadsheet3.nativeElement, {
            worksheets: [{
                worksheetName: 'JSS3',
                data: [
                    [34, 53],
                    [51, 35],
                ],
                footers: [
                    ['=SUM(JSS1!A:A,JSS2!A:A,JSS3!A:A)']
                ],
            }],
            onafterchanges: () => {
                this.s3[0].setFooter();
            }
        });
    }
}

What are the differences from Version 11

  • Footer is now automatically updated when formulas are executed.
  • refreshFooter method is dropped
  • Method signature updated: setFooterValue now receives an array of values
  • Event signature updated for onupdatefootervalue
  • Event signature updated for onrenderfootercell

Edit this page on GitHub