Products

Documentation keyboard_arrow_right Calculations

Cross-Spreadsheet Calculations

Execute Excel-like formulas across multiple spreadsheet instances with automatic dependency management and calculation optimization.

Documentation

Calculation State

Control when formulas execute to prevent errors when referencing data from spreadsheets that haven't loaded yet. Use jspreadsheet.calculations(false) to pause calculations, then jspreadsheet.calculations(true) to execute all queued formulas.

jspreadsheet.calculations(state: boolean);

Calculation Namespaces

Prevent worksheet name conflicts by organizing spreadsheets into namespaces.

jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [{
        minDimensions: [10,10]
    }],
    namespace: '3b0fe10b-e48d-4d47-9a4f-5ebf59d52c58',
});

Examples

Cross-Spreadsheet Calculations Example

Create formulas that reference data across multiple spreadsheet instances using worksheet names (e.g., Products!D1:D8).

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

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

<div id="spreadsheet1"></div><br><br>
<div id="spreadsheet2"></div>

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

// Stop calculations to ensure all data grids are ready and loaded
jspreadsheet.calculations(false);

// Create spreadsheets with cross-references
jspreadsheet(document.getElementById('spreadsheet1'), {
    worksheets: [{
        data: [
            [ 'Crayons Crayola only (No Rose Art)', 2, 5.13, '=B1*C1' ],
            [ 'Colored Pencils Crayola only', 2, 4.41, '=B2*C2' ],
            [ 'Expo Dry-erase Markers Wide', 4, 3.00, '=B3*C3' ],
            [ 'Index Cards Unlined', 3, 6.00, '=B4*C4' ],
            [ 'Tissues', 10, 1.90, '=B5*C5' ],
            [ 'Ziploc Sandwich-size Bags', 5, 1.00, '=B6*C6' ],
            [ 'Thin Markers Crayola only', 2, 3.00, '=B7*C7' ],
            [ 'Highlighter', 4, 1.20, '=B8*C8' ],
            [ 'Total', '=SUM(B1:B8)', '=ROUND(SUM(C1:C8), 2)', '=SUM(D1:D8)' ],
        ],
        columns: [
            { type: 'text', title:'Product', width:'300' },
            { type: 'number', title:'Qtd', width:'80' },
            { type: 'number', title:'Price', width:'100' },
            { type: 'number', title:'Total', width:'100' },
        ],
        worksheetName: 'Products',
        columnSorting: false,
    }],
});

jspreadsheet(document.getElementById('spreadsheet2'), {
    worksheets: [{
        data: [
            [ 'Price', '=SUM(Products!D1:D8)'],
            [ 'Discount', 0.1],
            [ 'Total', '=B1*(1-B2)'],
        ],
        columns: [
            { type: 'text', title:'Summary', width:'300' },
            { type: 'number', title:'Total', width:'200' },
        ],
        cells: { B2: { type:'percent' } },
        columnSorting: false,
    }],
});

// Enable the data grid calculations
jspreadsheet.calculations(true);
</script>
</html>

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


// Set the license
jspreadsheet.setLicense('NWM2NWYxMmFkYjRhY2Y3ZTMxNzM0OWNjNGRlOTNhZDc1N2IyNWU3YmFmMmMxOGYxMDRlYjg1MjI5NWZkZGYzYmVjNjRmN2Y4YTNmMjk2ZGIyZDRhOTgyYTA5ODdmNjk2MzM3MThjNjg2Nzk3YzY5ODg0MGE0MGUyOGExMmIwM2EsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNRGczTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

jspreadsheet.setExtensions({formula})

// Stop calculations to ensure all data grids are ready and loaded
jspreadsheet.calculations(false);

// Create the component
export default function App() {

    const productsRef = useRef();
    const summaryRef = useRef();

    // Spreadsheet products
    const products = {
        worksheets: [{
            data: [
                ['Crayons Crayola only (No Rose Art)', 2, 5.13, '=B1*C1'],
                ['Colored Pencils Crayola only', 2, 4.41, '=B2*C2'],
                ['Expo Dry-erase Markers Wide', 4, 3.00, '=B3*C3'],
                ['Index Cards Unlined', 3, 6.00, '=B4*C4'],
                ['Tissues', 10, 1.90, '=B5*C5'],
                ['Ziploc Sandwich-size Bags', 5, 1.00, '=B6*C6'],
                ['Thin Markers Crayola only', 2, 3.00, '=B7*C7'],
                ['Highlighter', 4, 1.20, '=B8*C8'],
                ['Total', '=SUM(B1:B8)', '=ROUND(SUM(C1:C8), 2)', '=SUM(D1:D8)'],
            ],
            columns: [
                {type: 'text', title: 'Product', width: '300'},
                {type: 'number', title: 'Qtd', width: '80'},
                {type: 'number', title: 'Price', width: '100'},
                {type: 'number', title: 'Total', width: '100'},
            ],
            worksheetName: 'Products',
            columnSorting: false,
        }],
    };
    // Spreadsheet summary
    const summary = {
        worksheets: [{
            data: [
                ['Price', '=SUM(Products!D1:D8)'],
                ['Discount', 0.1],
                ['Total', '=B1*(1-B2)'],
            ],
            columns: [
                {type: 'text', title: 'Summary', width: '300'},
                {type: 'number', title: 'Total', width: '200'},
            ],
            cells: {B2: {type: 'percent'}},
            columnSorting: false,
        }],
    }

    useEffect(() => {
        // Enable the data grid calculations after components mount
        jspreadsheet.calculations(true);
    }, [])

    // Create spreadsheets
    return (<>
            <Spreadsheet ref={productsRef} worksheets={products.worksheets}/>
            <Spreadsheet ref={summaryRef} worksheets={summary.worksheets}/>
    </>);
}

<template>
    <Spreadsheet :worksheets="summary" />
    <Spreadsheet :worksheets="products" />
</template>

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

// Set the license
jspreadsheet.setLicense('NWM2NWYxMmFkYjRhY2Y3ZTMxNzM0OWNjNGRlOTNhZDc1N2IyNWU3YmFmMmMxOGYxMDRlYjg1MjI5NWZkZGYzYmVjNjRmN2Y4YTNmMjk2ZGIyZDRhOTgyYTA5ODdmNjk2MzM3MThjNjg2Nzk3YzY5ODg0MGE0MGUyOGExMmIwM2EsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNRGczTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

jspreadsheet.setExtensions({ formula })

// Stop calculations to ensure all data grids are ready and loaded
jspreadsheet.calculations(false);

// Create components
export default {
    components: {
        Spreadsheet,
    },
    mounted() {
        // Enable the data grid calculations
        jspreadsheet.calculations(true);
    },
    data() {
        // Spreadsheet products
        const products = [
            {
                data: [
                    ["Crayons Crayola only (No Rose Art)", 2, 5.13, "=B1*C1"],
                    ["Colored Pencils Crayola only", 2, 4.41, "=B2*C2"],
                    ["Expo Dry-erase Markers Wide", 4, 3.0, "=B3*C3"],
                    ["Index Cards Unlined", 3, 6.0, "=B4*C4"],
                    ["Tissues", 10, 1.9, "=B5*C5"],
                    ["Ziploc Sandwich-size Bags", 5, 1.0, "=B6*C6"],
                    ["Thin Markers Crayola only", 2, 3.0, "=B7*C7"],
                    ["Highlighter", 4, 1.2, "=B8*C8"],
                    ["Total", "=SUM(B1:B8)", "=ROUND(SUM(C1:C8), 2)", "=SUM(D1:D8)"],
                ],
                columns: [{
                        type: "text",
                        title: "Product",
                        width: "300"
                    },
                    {
                        type: "number",
                        title: "Qtd",
                        width: "80"
                    },
                    {
                        type: "number",
                        title: "Price",
                        width: "100"
                    },
                    {
                        type: "number",
                        title: "Total",
                        width: "100"
                    },
                ],
                worksheetName: "Products",
                columnSorting: false,
            }
        ];

        // Spreadsheet summary
        const summary = [
            {
                data: [
                    ["Price", "=SUM(Products!D1:D8)"],
                    ["Discount", 0.1],
                    ["Total", "=B1*(1-B2)"],
                ],
                columns: [{
                        type: "text",
                        title: "Summary",
                        width: "300"
                    },
                    {
                        type: "number",
                        title: "Total",
                        width: "200"
                    },
                ],
                cells: {
                    B2: {
                        type: "percent"
                    }
                },
                columnSorting: false,
            }
        ];

        return {
            products,
            summary,
        };
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import formula from "@jspreadsheet/formula-pro";
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('NWM2NWYxMmFkYjRhY2Y3ZTMxNzM0OWNjNGRlOTNhZDc1N2IyNWU3YmFmMmMxOGYxMDRlYjg1MjI5NWZkZGYzYmVjNjRmN2Y4YTNmMjk2ZGIyZDRhOTgyYTA5ODdmNjk2MzM3MThjNjg2Nzk3YzY5ODg0MGE0MGUyOGExMmIwM2EsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNRGczTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

jspreadsheet.setExtensions({ formula })

// Create the data grid component
@Component({
    standalone: true,
    selector: "app-root",
    template: `
        <div #summary></div>
        <div #products></div>
    `,
})
export class AppComponent {
    @ViewChild("summary") summary: ElementRef;
    @ViewChild("products") products: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];
    // Create a new data grid
    ngAfterViewInit() {
        // Stop calculations to ensure all data grids are ready and loaded
        jspreadsheet.calculations(false);

        // Create summary spreadsheet
        jspreadsheet(this.summary.nativeElement, {
            worksheets: [{
                data: [
                    [ 'Crayons Crayola only (No Rose Art)', 2, 5.13, '=B1*C1' ],
                    [ 'Colored Pencils Crayola only', 2, 4.41, '=B2*C2' ],
                    [ 'Expo Dry-erase Markers Wide', 4, 3.00, '=B3*C3' ],
                    [ 'Index Cards Unlined', 3, 6.00, '=B4*C4' ],
                    [ 'Tissues', 10, 1.90, '=B5*C5' ],
                    [ 'Ziploc Sandwich-size Bags', 5, 1.00, '=B6*C6' ],
                    [ 'Thin Markers Crayola only', 2, 3.00, '=B7*C7' ],
                    [ 'Highlighter', 4, 1.20, '=B8*C8' ],
                    [ 'Total', '=SUM(B1:B8)', '=ROUND(SUM(C1:C8), 2)', '=SUM(D1:D8)' ],
                ],
                columns: [
                    { type: 'text', title:'Product', width: 300 },
                    { type: 'number', title:'Qtd', width: 80 },
                    { type: 'number', title:'Price', width: 100 },
                    { type: 'number', title:'Total', width: 100 },
                ],
                worksheetName: 'Products',
                columnSorting: false,
            }],
        });

        // Create products spreadsheet
        jspreadsheet(this.products.nativeElement, {
            worksheets: [{
                data: [
                    [ 'Price', '=SUM(Products!D1:D8)'],
                    [ 'Discount', 0.1],
                    [ 'Total', '=B1*(1-B2)'],
                ],
                columns: [
                    { type: 'text', title:'Summary', width: 300 },
                    { type: 'number', title:'Total', width: 200 },
                ],
                cells: { B2: { type:'percent' } },
                columnSorting: false,
            }],
        });

        // Enable the data grid calculations
        jspreadsheet.calculations(true);
    }
}

Edit this page on GitHub