Excel-Like Formulas

Jspreadsheet implements spreadsheet-like formulas, ensuring compatibility with major spreadsheet software like Excel or Google Sheets. This section outlines the available formulas, custom formula creation, and other related features.

Highlights

  • Formula Auto-update: Adjusts on cell actions like copy and drag.
  • DOM in Cells: Formulas output DOM objects to cells.
  • Excel-style Formulas: Customizable formulas resembling Excel.
  • Cross-Sheet Calculations: Calculations across different sheets.
  • External Variables: Includes external JavaScript variables.
  • Column Calculations: Performs operations on whole columns.

What's new?

  • Row Range: new row range calculations. For example: =SUM(1:1);
  • Namespaces: calculations scope. More information

Documentation

Important points

  • All formulas and methods must be capital letters.
  • It is possible to queue/release calculations using jspreadsheet.calculations when loading multiple spreadsheets with cross calculations. Read more

Settings

A summary of configurations related to the use of formulas.

Configuration Description
secureFormulas?: boolean Enable formula security. Default: true
editorFormulas?: boolean Enable the formula editor. Default: true
parseFormulas?: boolean Enable formula calculations. Default: true
debugFormulas?: boolean Enable the formula debug notices. Default: true
autoIncrement?: boolean Formula variable increment on cloning or copying. Default: true

Events

All events related to formulas.

Event Description
onbeforeformula?: Function Intercept and parse a formula just before the execution.
onbeforeformula(worksheet: Object, expression: String, x: Number, y: Number) => String
onformulachain?: Function Get the information about the expressions executed from the formula chain.
onformulachain(worksheet: Object, executions: Object) => void

Methods

All methods related to formulas in the JSS context.

Method Description
executeFormula?: Function Execute a formula.
executeFormula(expression: string, x?: number, y?: number, caching?: boolean, basic?: boolean) => String

Available formulas

We are working to bring as many formulas as possible. Meanwhile, you can check for the existing implementation. For security reasons, all references should use capital letters, including the implementation of custom methods.

Formula Feature Roadmap

Ongoing enhancements and the introduction of new methods are part of our commitment to align Jspreadsheet with other software standards, such as Excel and Google Sheets.

Advance

Special formulas

To support calculations, Jspreadsheet has a few special formulas listed below:

Method Example
=TABLE() Return the Jspreadsheet table instance.

Formula Pro Extension

Jspreadsheet offers formulas in two tiers: Basic and Premium. The Basic tier comes standard with all Jspreadsheet distributions. In contrast, the Premium tier, offered as an extension for Enterprise and Premium plans, includes additional advanced features:

  • Matrix Calculations: Complex matrix operations.
  • New Operators: '%' for percentages, '@' for formula specifics.
  • Range Operation: Row/column range operations (A:A, 1:1).
  • Extended Formulas: Access to advanced formulas.
  • Private Scope: Secure, isolated formula execution.
  • Special Properties: 'x', 'y', 'instance' for dynamic formulas.
  • Standalone Compatibility: Formula execution in separate apps.
  • Formula Picker: Easy formula selection and insertion.

Special properties for Formulas Premium

When the Jspreadsheet Pro invokes the method from a valid cell, you will have access to the coordinates of the cell and the instance of the worksheet.

let YOUR_METHOD = function() {
    // Return the coordinates to the cell
    return this.x + ',' + this.y;
}

Custom formulas

It is possible to create custom methods that will be available in the spreadsheet. The custom method should return the appropriate content for the cell. From version 7 on, Jspreadsheet accepts a DOM element as a return of a function. IMPORTANT : All method names should be created using capital letters.

// New custom formula
let COLORIZE = function(v) {
    let d = document.createElement('div');
    d.style.color = v;
    d.innerText = v.toUpperCase();
    return d;
}

// Add to the correct scope
formula.setFormula({ COLORIZE });

// Create spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [{
        data: [
            [ 'red', '=COLORIZE(A1)' ],
            [ 'green', '=COLORIZE(A2)' ],
            [ 'blue', '=COLORIZE(A3)' ],
        ],
        columns: [
            { type: 'text', width:'300' },
            { type: 'text', width:'200' },
        ]
    }]
});

Examples

Basic spreadsheet with formulas.

A basic spreadsheet example using formulas, including currency, percentage and mask.

<html>
<script src="https://jspreadsheet.com/v11/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/v11/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('NTJlOGJkMGQwNGExNTBiNmJlNzUzNTEyNWYwOTg5NDkzYzI2N2Y3NzhiNDI3YWZmNTk4YmE4NDg3ZjQ1YmUwNTU5ZTYwMmQ1YTlkMTBjYzY0M2I5YmY1ZGZmZGM5MDMxODJkOWFlZjQwMDNiMmI4YjgwZTRjOWQxZWY5NTI4MjUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpFME1UQXpORE15TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
    toolbar: true,
    style: ['background-color:orange; font-weight: bold;'],
    worksheets: [{
        data: [
            [ 'Crayons Crayola only (No Rose Art)', 2, 5.01, 0.01, '=B1*C1*(1-D1)' ],
            [ 'Colored Pencils Crayola only', 2, 4.41, 0.02, '=B2*C2*(1-D2)' ],
            [ 'Expo Dry-erase Markers Wide', 4, 3.00, 0.1, '=B3*C3*(1-D3)' ],
            [ 'Index Cards Unlined', 3, 6.00, 0.03, '=B4*C4*(1-D4)' ],
            [ 'Tissues', 10, 1.90, 0.01, '=B5*C5*(1-D5)' ],
            [ 'Ziploc Sandwich-size Bags', 5, 1.00, 0.01, '=B6*C6*(1-D6)' ],
            [ 'Thin Markers Crayola only', 2, 3.00, 0.02, '=B7*C7*(1-D7)' ],
            [ 'Highlighter', 4, 1.20, 0.01, '=B8*C8*(1-D8)' ],
            [ 'Total', '=SUM(B1:B8)', '=ROUND(SUM(C1:C8), 2)', '', '=SUM(E1:E8)' ],
        ],
        columns: [
            { type: 'text', title:'Product', width:'300' },
            { type: 'text', title:'Qtd', width:'80', mask:'#.##0' },
            { type: 'text', title:'Price', width:'100px', mask:'$ #.##0,00'  },
            { type: 'text', title:'Discount', mask:'0.00%' },
            {
                type: 'number',
                title: 'Total',
                width: '100px',
                format: 'US #.##0,00;[Red](#.##0,00)',
            },
        ],
        style: {
            'A9:E9': 0,
        },
        columnSorting:false,
        worksheetName: 'Calculations',
    }]
});
</script>
</html>

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

const license = 'NTJlOGJkMGQwNGExNTBiNmJlNzUzNTEyNWYwOTg5NDkzYzI2N2Y3NzhiNDI3YWZmNTk4YmE4NDg3ZjQ1YmUwNTU5ZTYwMmQ1YTlkMTBjYzY0M2I5YmY1ZGZmZGM5MDMxODJkOWFlZjQwMDNiMmI4YjgwZTRjOWQxZWY5NTI4MjUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpFME1UQXpORE15TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==';

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();

    // Global grid style
    const styles = ['background-color:orange; font-weight: bold;'];

    // Worksheet data
    const data = [
        [ 'Crayons Crayola only (No Rose Art)', 2, 5.01, 0.01, '=B1*C1*(1-D1)' ],
        [ 'Colored Pencils Crayola only', 2, 4.41, 0.02, '=B2*C2*(1-D2)' ],
        [ 'Expo Dry-erase Markers Wide', 4, 3.00, 0.1, '=B3*C3*(1-D3)' ],
        [ 'Index Cards Unlined', 3, 6.00, 0.03, '=B4*C4*(1-D4)' ],
        [ 'Tissues', 10, 1.90, 0.01, '=B5*C5*(1-D5)' ],
        [ 'Ziploc Sandwich-size Bags', 5, 1.00, 0.01, '=B6*C6*(1-D6)' ],
        [ 'Thin Markers Crayola only', 2, 3.00, 0.02, '=B7*C7*(1-D7)' ],
        [ 'Highlighter', 4, 1.20, 0.01, '=B8*C8*(1-D8)' ],
        [ 'Total', '=SUM(B1:B8)', '=ROUND(SUM(C1:C8), 2)', '', '=SUM(E1:E8)' ],
    ]

    // Column definitions
    const columns = [
        { type: 'text', title:'Product', width:'300' },
        { type: 'text', title:'Qtd', width:'80', mask:'#.##0' },
        { type: 'text', title:'Price', width:'100px', mask:'$ #.##0,00'  },
        { type: 'text', title:'Discount', mask:'0.00%' },
        {
            type: 'number',
            title: 'Total',
            width: '100px',
            format: 'US #.##0,00;[Red](#.##0,00)',
        },
    ]

    // Data grid style
    const style = {
        'A9:E9': 0,
    }

    // Render component
    return (
        <Spreadsheet ref={spreadsheet} license={license} style={styles} toolbar>
            <Worksheet data={data} columns={columns} style={style} worksheetName="Calculations" columnSorting />
        </Spreadsheet>
    );
}

<template>
    <Spreadsheet ref="spreadsheet" :license="license" :style="styles">
        <Worksheet :data="data" :columns="columns" :style="style" columnSorting worksheetName="Calculations" />
    </Spreadsheet>
</template>

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

const license = 'NTJlOGJkMGQwNGExNTBiNmJlNzUzNTEyNWYwOTg5NDkzYzI2N2Y3NzhiNDI3YWZmNTk4YmE4NDg3ZjQ1YmUwNTU5ZTYwMmQ1YTlkMTBjYzY0M2I5YmY1ZGZmZGM5MDMxODJkOWFlZjQwMDNiMmI4YjgwZTRjOWQxZWY5NTI4MjUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpFME1UQXpORE15TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==';

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Global grid style
        const styles = ['background-color:orange; font-weight: bold;'];

        // Worksheet data
        const data = [
            [ 'Crayons Crayola only (No Rose Art)', 2, 5.01, 0.01, '=B1*C1*(1-D1)' ],
            [ 'Colored Pencils Crayola only', 2, 4.41, 0.02, '=B2*C2*(1-D2)' ],
            [ 'Expo Dry-erase Markers Wide', 4, 3.00, 0.1, '=B3*C3*(1-D3)' ],
            [ 'Index Cards Unlined', 3, 6.00, 0.03, '=B4*C4*(1-D4)' ],
            [ 'Tissues', 10, 1.90, 0.01, '=B5*C5*(1-D5)' ],
            [ 'Ziploc Sandwich-size Bags', 5, 1.00, 0.01, '=B6*C6*(1-D6)' ],
            [ 'Thin Markers Crayola only', 2, 3.00, 0.02, '=B7*C7*(1-D7)' ],
            [ 'Highlighter', 4, 1.20, 0.01, '=B8*C8*(1-D8)' ],
            [ 'Total', '=SUM(B1:B8)', '=ROUND(SUM(C1:C8), 2)', '', '=SUM(E1:E8)' ],
        ]

        // Column definitions
        const columns = [
            { type: 'text', title:'Product', width:'300' },
            { type: 'text', title:'Qtd', width:'80', mask:'#.##0' },
            { type: 'text', title:'Price', width:'100px', mask:'$ #.##0,00'  },
            { type: 'text', title:'Discount', mask:'0.00%' },
            {
                type: 'number',
                title: 'Total',
                width: '100px',
                format: 'US #.##0,00;[Red](#.##0,00)',
            },
        ]

        // Data grid style
        const style = {
            'A9:E9': 0,
        }

        return {
            styles,
            data,
            columns,
            style,
            license,
        };
    }
}
</script>

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

@Component({
    selector: "app-root",
    template: `<div #spreadsheet></div>`
})
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, {
            toolbar: true,
            style: ['background-color:orange; font-weight: bold;'],
            worksheets: [{
                data: [
                    [ 'Crayons Crayola only (No Rose Art)', 2, 5.01, 0.01, '=B1*C1*(1-D1)' ],
                    [ 'Colored Pencils Crayola only', 2, 4.41, 0.02, '=B2*C2*(1-D2)' ],
                    [ 'Expo Dry-erase Markers Wide', 4, 3.00, 0.1, '=B3*C3*(1-D3)' ],
                    [ 'Index Cards Unlined', 3, 6.00, 0.03, '=B4*C4*(1-D4)' ],
                    [ 'Tissues', 10, 1.90, 0.01, '=B5*C5*(1-D5)' ],
                    [ 'Ziploc Sandwich-size Bags', 5, 1.00, 0.01, '=B6*C6*(1-D6)' ],
                    [ 'Thin Markers Crayola only', 2, 3.00, 0.02, '=B7*C7*(1-D7)' ],
                    [ 'Highlighter', 4, 1.20, 0.01, '=B8*C8*(1-D8)' ],
                    [ 'Total', '=SUM(B1:B8)', '=ROUND(SUM(C1:C8), 2)', '', '=SUM(E1:E8)' ],
                ],
                columns: [
                    { type: 'text', title:'Product', width:'300' },
                    { type: 'text', title:'Qtd', width:'80', mask:'#.##0' },
                    { type: 'text', title:'Price', width:'100px', mask:'$ #.##0,00'  },
                    { type: 'text', title:'Discount', mask:'0.00%' },
                    {
                        type: 'number',
                        title: 'Total',
                        width: '100px',
                        format: 'US #.##0,00;[Red](#.##0,00)',
                    },
                ],
                style: {
                    'A9:E9': 0,
                },
                columnSorting:false,
                worksheetName: 'Calculations',
            }]
        });
    }
}

Cross-Worksheet and Cross-Spreadsheet Formulas

This example demonstrates formula calculations that span across different worksheets and spreadsheets.

<html>
<script src="https://jspreadsheet.com/v11/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/v11/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('NTJlOGJkMGQwNGExNTBiNmJlNzUzNTEyNWYwOTg5NDkzYzI2N2Y3NzhiNDI3YWZmNTk4YmE4NDg3ZjQ1YmUwNTU5ZTYwMmQ1YTlkMTBjYzY0M2I5YmY1ZGZmZGM5MDMxODJkOWFlZjQwMDNiMmI4YjgwZTRjOWQxZWY5NTI4MjUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpFME1UQXpORE15TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [
        {
            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"],
                ['Reference from the spreadsheet above: ', '=Calculations!C9', '', '=Calculations!E9'],
            ],
            worksheetName: 'Example1',
            minDimensions: [5,5],
            defaultColWidth: '50px',
            columns: [{
                width: '300px'
            }],
            cells: {
                B5: { mask:'#.##0' },
                D5: { mask:'#.##0' },
            }
        },
        {
            data: [
                ['20%', "=Example1!D1"],
                ['20%', "=Example1!D2"],
                ['20%', "=Example1!D3"],
                ['20%', "=Example1!D4"],
            ],
            worksheetName: 'Example2',
            minDimensions: [5,5],
        },
    ]
});
</script>
</html>

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

const license = 'NTJlOGJkMGQwNGExNTBiNmJlNzUzNTEyNWYwOTg5NDkzYzI2N2Y3NzhiNDI3YWZmNTk4YmE4NDg3ZjQ1YmUwNTU5ZTYwMmQ1YTlkMTBjYzY0M2I5YmY1ZGZmZGM5MDMxODJkOWFlZjQwMDNiMmI4YjgwZTRjOWQxZWY5NTI4MjUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpFME1UQXpORE15TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==';

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();

    // All worksheets in a single variable
    const worksheets = [
        {
            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"],
                ['Reference from the spreadsheet above: ', '=Calculations!C9', '', '=Calculations!E9'],
            ],
            worksheetName: 'Example1',
            minDimensions: [5,5],
            defaultColWidth: '50px',
            columns: [{
                width: '300px'
            }],
            cells: {
                B5: { mask:'#.##0' },
                D5: { mask:'#.##0' },
            }
        },
        {
            data: [
                ['20%', "=Example1!D1"],
                ['20%', "=Example1!D2"],
                ['20%', "=Example1!D3"],
                ['20%', "=Example1!D4"],
            ],
            worksheetName: 'Example2',
            minDimensions: [5,5],
        },
    ]

    // Render component
    return (
        <Spreadsheet ref={spreadsheet} license={license} worksheets={worksheets} />
    );
}

<template>
    <Spreadsheet ref="spreadsheet" :license="license" :worksheets="worksheets" />
</template>

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

const license = 'NTJlOGJkMGQwNGExNTBiNmJlNzUzNTEyNWYwOTg5NDkzYzI2N2Y3NzhiNDI3YWZmNTk4YmE4NDg3ZjQ1YmUwNTU5ZTYwMmQ1YTlkMTBjYzY0M2I5YmY1ZGZmZGM5MDMxODJkOWFlZjQwMDNiMmI4YjgwZTRjOWQxZWY5NTI4MjUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpFME1UQXpORE15TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==';

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // All worksheets in a single variable
        const worksheets = [
            {
                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"],
                    ['Reference from the spreadsheet above: ', '=Calculations!C9', '', '=Calculations!E9'],
                ],
                worksheetName: 'Example1',
                minDimensions: [5,5],
                defaultColWidth: '50px',
                columns: [{
                    width: '300px'
                }],
                cells: {
                    B5: { mask:'#.##0' },
                    D5: { mask:'#.##0' },
                }
            },
            {
                data: [
                    ['20%', "=Example1!D1"],
                    ['20%', "=Example1!D2"],
                    ['20%', "=Example1!D3"],
                    ['20%', "=Example1!D4"],
                ],
                worksheetName: 'Example2',
                minDimensions: [5,5],
            },
        ]

        return {
            worksheets,
            license,
        };
    }
}
</script>

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

@Component({
    selector: "app-root",
    template: `<div #spreadsheet></div>`
})
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, "=B1*C1"],
                        ['Apples', 5, 4.00, "=B2*C2"],
                        ['Carrots', 5, 1.00, "=B3*C3"],
                        ['Oranges', 6, 2.00, "=B4*C4"],
                        ['Reference from the spreadsheet above: ', '=Calculations!C9', '', '=Calculations!E9'],
                    ],
                    worksheetName: 'Example1',
                    minDimensions: [5,5],
                    defaultColWidth: '50px',
                    columns: [{
                        width: '300px'
                    }],
                    cells: {
                        B5: { mask:'#.##0' },
                        D5: { mask:'#.##0' },
                    }
                },
                {
                    data: [
                        ['20%', "=Example1!D1"],
                        ['20%', "=Example1!D2"],
                        ['20%', "=Example1!D3"],
                        ['20%', "=Example1!D4"],
                    ],
                    worksheetName: 'Example2',
                    minDimensions: [5,5],
                },
            ]
        });
    }
}

Defined names

This feature is only available in with the Formula Pro extension.

<html>
<script src="https://jspreadsheet.com/v11/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/v11/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('NTJlOGJkMGQwNGExNTBiNmJlNzUzNTEyNWYwOTg5NDkzYzI2N2Y3NzhiNDI3YWZmNTk4YmE4NDg3ZjQ1YmUwNTU5ZTYwMmQ1YTlkMTBjYzY0M2I5YmY1ZGZmZGM5MDMxODJkOWFlZjQwMDNiMmI4YjgwZTRjOWQxZWY5NTI4MjUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpFME1UQXpORE15TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [
        {
            data: [
                [ 1, '=SUM(Test*10)' ],
                [ 2, '' ],
                [ 3, '' ],
                [ 4, '' ],
            ],
            minDimensions: [5,5],
            worksheetName: 'Named ranges',
        }
    ],
    // On JSS the defined names must be uppercase
    definedNames: {
        'TEST': 'A1:A4',
    },
});
</script>
</html>

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

const license = 'NTJlOGJkMGQwNGExNTBiNmJlNzUzNTEyNWYwOTg5NDkzYzI2N2Y3NzhiNDI3YWZmNTk4YmE4NDg3ZjQ1YmUwNTU5ZTYwMmQ1YTlkMTBjYzY0M2I5YmY1ZGZmZGM5MDMxODJkOWFlZjQwMDNiMmI4YjgwZTRjOWQxZWY5NTI4MjUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpFME1UQXpORE15TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==';

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();

    // All worksheets in a single variable
    const worksheets = [
        {
            data: [
                [ 1, '=SUM(Test*10)' ],
                [ 2, '' ],
                [ 3, '' ],
                [ 4, '' ],
            ],
            minDimensions: [5,5],
            worksheetName: 'Named ranges',
        }
    ];

    // On JSS the defined names must be uppercase
    const definedNames = {
        TEST: 'A1:A4',
    };

    // Render component
    return (
        <Spreadsheet ref={spreadsheet} license={license} worksheets={worksheets} definedNames={definedNames} />
    );
}

<template>
    <Spreadsheet ref="spreadsheet" :license="license" :worksheets="worksheets" :definedNames="definedNames" />
</template>

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

const license = 'NTJlOGJkMGQwNGExNTBiNmJlNzUzNTEyNWYwOTg5NDkzYzI2N2Y3NzhiNDI3YWZmNTk4YmE4NDg3ZjQ1YmUwNTU5ZTYwMmQ1YTlkMTBjYzY0M2I5YmY1ZGZmZGM5MDMxODJkOWFlZjQwMDNiMmI4YjgwZTRjOWQxZWY5NTI4MjUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpFME1UQXpORE15TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==';

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // All worksheets in a single variable
        const worksheets = [
            {
                data: [
                    [ 1, '=SUM(Test*10)' ],
                    [ 2, '' ],
                    [ 3, '' ],
                    [ 4, '' ],
                ],
                minDimensions: [5,5],
                worksheetName: 'Named ranges',
            }
        ];

        // On JSS the defined names must be uppercase
        const definedNames = {
            TEST: 'A1:A4',
        };

        return {
            worksheets,
            definedNames,
            license,
        };
    }
}
</script>

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

@Component({
    selector: "app-root",
    template: `<div #spreadsheet></div>`
})
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: [
                        [ 1, '=SUM(Test*10)' ],
                        [ 2, '' ],
                        [ 3, '' ],
                        [ 4, '' ],
                    ],
                    minDimensions: [5,5],
                    worksheetName: 'Named ranges',
                }
            ],
            // On JSS the defined names must be uppercase
            definedNames: {
                'TEST': 'A1:A4',
            }
        });
    }
}

Custom methods

All custom methods should be declared in capital letters.

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

// Create a custom javascript method (capital case)
const COLORIZE = function(v) {
    let d = document.createElement('span');
    d.style.color = v;
    d.innerText = v.toUpperCase();
    return d;
}

// Send custom formula to the correct scope
formula.setFormula({ COLORIZE })

// Create spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [{
        data: [
            [ 'red', '=COLORIZE(A1)' ],
            [ 'green', '=COLORIZE(A2)' ],
            [ 'blue', '=COLORIZE(A3)' ],
        ],
        columns: [
            { type: 'text', width:'300' },
            { type: 'text', width:'200' },
        ]
    }]
});
</script>
</html>

import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
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)
const license = 'NTJlOGJkMGQwNGExNTBiNmJlNzUzNTEyNWYwOTg5NDkzYzI2N2Y3NzhiNDI3YWZmNTk4YmE4NDg3ZjQ1YmUwNTU5ZTYwMmQ1YTlkMTBjYzY0M2I5YmY1ZGZmZGM5MDMxODJkOWFlZjQwMDNiMmI4YjgwZTRjOWQxZWY5NTI4MjUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpFME1UQXpORE15TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==';

// Create a custom javascript method (capital case)
const COLORIZE = function(v) {
    let d = document.createElement('span');
    d.style.color = v;
    d.innerText = v.toUpperCase();
    return d;
}

// Send custom formula to the correct scope
formula.setFormula({ COLORIZE })

// Extensions
const extensions = { formula };

// Create the component
export default function App() {
    // Array with all the data grids
    const spreadsheet = useRef();
    // Data
    const data = [
        [ 'red', '=COLORIZE(A1)' ],
        [ 'green', '=COLORIZE(A2)' ],
        [ 'blue', '=COLORIZE(A3)' ],
    ]
    const columns = [
        { type: 'text', width:'300px' },
        { type: 'text', width:'200px' },
    ]

    // Render data grid component
    return (
        <Spreadsheet ref={spreadsheet} license={license} extensions={extensions}>
            <Worksheet data={data} columns={columns} />
        </Spreadsheet>
    );
}

<template>
    <Spreadsheet ref="spreadsheet" :license="license" :extensions="extensions">
        <Worksheet :data="data" :columns="columns" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet } from "@jspreadsheet/vue";
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)
const license = 'NTJlOGJkMGQwNGExNTBiNmJlNzUzNTEyNWYwOTg5NDkzYzI2N2Y3NzhiNDI3YWZmNTk4YmE4NDg3ZjQ1YmUwNTU5ZTYwMmQ1YTlkMTBjYzY0M2I5YmY1ZGZmZGM5MDMxODJkOWFlZjQwMDNiMmI4YjgwZTRjOWQxZWY5NTI4MjUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpFME1UQXpORE15TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==';

// Create a custom javascript method (capital case)
const COLORIZE = function(v) {
    let d = document.createElement('span');
    d.style.color = v;
    d.innerText = v.toUpperCase();
    return d;
}

// Send custom formula to the correct scope
formula.setFormula({ COLORIZE })

// Extensions
const extensions = { formula };

// Create data grid component
export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Data
        const data = [
            [ 'red', '=COLORIZE(A1)' ],
            [ 'green', '=COLORIZE(A2)' ],
            [ 'blue', '=COLORIZE(A3)' ],
        ]
        const columns = [
            { type: 'text', width:'300px' },
            { type: 'text', width:'200px' },
        ]

        return {
            extensions,
            data,
            columns,
            license,
        };
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import * as jspreadsheet from "jspreadsheet";
import * as formula from "@jspreadsheet/formula-pro";

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

// Create a custom javascript method (capital case)
const COLORIZE = (v) => {
    let d = document.createElement('span');
    d.style.color = v;
    d.innerText = v.toUpperCase();
    return d;
}

// Send custom formula to the correct scope
formula.setFormula({ COLORIZE })

// Create the data grid component
@Component({
    selector: "app-root",
    template: `<div #spreadsheet></div>`;
})
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: [
                    [ 'red', '=COLORIZE(A1)' ],
                    [ 'green', '=COLORIZE(A2)' ],
                    [ 'blue', '=COLORIZE(A3)' ],
                ],
                columns: [
                    { type: 'text', width:'300' },
                    { type: 'text', width:'200' },
                ]
            }]
        });
    }
}

Using Columns Names in Your Formulas

You can use custom column name in your calculations.

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

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>

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

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

// Set the extensions
jspreadsheet.setExtensions({ formula });

// Create spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [{
        data: [
            [ 10, '=@Quantity*10' ],
            [ 20, '=@Quantity*10' ],
            [ 30, '=@Quantity*10' ],
        ],
        columns: [
            { type: 'text', width:'300', title: 'Quantity' },
            { type: 'text', width:'200', title: 'Total' },
        ]
    }],
    columnNamesInFormulas: true,
});
</script>
</html>

import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
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)
const license = 'NTJlOGJkMGQwNGExNTBiNmJlNzUzNTEyNWYwOTg5NDkzYzI2N2Y3NzhiNDI3YWZmNTk4YmE4NDg3ZjQ1YmUwNTU5ZTYwMmQ1YTlkMTBjYzY0M2I5YmY1ZGZmZGM5MDMxODJkOWFlZjQwMDNiMmI4YjgwZTRjOWQxZWY5NTI4MjUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpFME1UQXpORE15TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==';

// Extensions
const extensions = { formula };

// Create the component
export default function App() {
    // Array with all the data grids
    const spreadsheet = useRef();

    // Data grid
    const data = [
        [ 10, '=@Quantity*10' ],
        [ 20, '=@Quantity*10' ],
        [ 30, '=@Quantity*10' ],
    ];

    // Grid column definitions
    const columns = [
        { type: 'text', width:'300', title: 'Quantity' },
        { type: 'text', width:'200', title: 'Total' },
    ];

    // Render data grid component
    return (
        <Spreadsheet ref={spreadsheet} license={license} extensions={extensions} columnNamesInFormulas={true}>
            <Worksheet data={data} columns={columns} />
        </Spreadsheet>
    );
}

<template>
    <Spreadsheet ref="spreadsheet" :license="license" :extensions="extensions" : columnNamesInFormulas="true">
        <Worksheet :data="data" :columns="columns" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet } from "@jspreadsheet/vue";
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)
const license = 'NTJlOGJkMGQwNGExNTBiNmJlNzUzNTEyNWYwOTg5NDkzYzI2N2Y3NzhiNDI3YWZmNTk4YmE4NDg3ZjQ1YmUwNTU5ZTYwMmQ1YTlkMTBjYzY0M2I5YmY1ZGZmZGM5MDMxODJkOWFlZjQwMDNiMmI4YjgwZTRjOWQxZWY5NTI4MjUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpFME1UQXpORE15TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==';

// Extensions
const extensions = { formula };

// Create data grid component
export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Data grid
        const data = [
            [ 10, '=@Quantity*10' ],
            [ 20, '=@Quantity*10' ],
            [ 30, '=@Quantity*10' ],
        ];

        // Grid column definitions
        const columns = [
            { type: 'text', width:'300', title: 'Quantity' },
            { type: 'text', width:'200', title: 'Total' },
        ];

        return {
            columnNamesInFormulas: true,
            extensions,
            data,
            columns,
            license,
        };
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import * as jspreadsheet from "jspreadsheet";
import * as formula from "@jspreadsheet/formula-pro";

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

// Set the extensions
jspreadsheet.setExtensions({ formula });

// Create the data grid component
@Component({
    selector: "app-root",
    template: `<div #spreadsheet></div>`;
})
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: [
                    [ 10, '=@Quantity*10' ],
                    [ 20, '=@Quantity*10' ],
                    [ 30, '=@Quantity*10' ],
                ],
                columns: [
                    { type: 'text', width:'300', title: 'Quantity' },
                    { type: 'text', width:'200', title: 'Total' },
                ]
            }],
            columnNamesInFormulas: true,
        });
    }
}

Related content

More about formulas:

Releases notes

Differences from version 9

Deprecated Description
worksheet.formula The internal formula control is now deprecated. The formula chain property is part of the cell object worksheet.records[y][x].chain?: Map
onbeforechangereferences This event is deprecated
onchangereferences This event is deprecated
worksheet.updateFormula() This method is deprecated

Edit this page on GitHub