Products
Star

Jspreadsheet keyboard_arrow_right Documentation keyboard_arrow_right Formulas

Excel-Like Formulas

Jspreadsheet supports Excel-like formulas compatible with popular spreadsheet applications such as Excel and Google Sheets. These features enable advanced calculation capabilities, including:

  • Excel-style Formulas: Customizable formulas resembling Excel.
  • Formula Auto-update: Adjusts on cell actions such as copy and paste.
  • Custom Formulas: Create your custom formulas.

Formula Pro

Jspreadsheet Pro's Formula Pro Extension provides a suite of advanced features designed to enhance spreadsheet functionality, including:

  • Full-stack applications;
  • Secure private scopes;
  • Cross-sheet calculations;
  • complex matrix operations;
  • Advanced operators ('%', '@');
  • Dynamic range calculations (e.g., A:A, 1:1);


More about Formula Pro

Documentation

This section details the settings, methods, and events associated with spreadsheet calculations in Jspreadsheet. All formula names, including custom ones, should be capitalized for security and standardization.

Settings

A summary of configurations related to the use of formulas.

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

Events

All events related to formulas.

Event Description
onbeforeformula?: Function Intercept and modify a formula before execution.
onbeforeformula(worksheet: Object, expression: String, x: Number, y: Number) => String

Methods

All methods related to formulas.

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

Examples

Basic excel-like formulas usage

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

<html>
<script src="https://bossanova.uk/jspreadsheet/v5/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://bossanova.uk/jspreadsheet/v5/jspreadsheet.css" type="text/css" />

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

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

<script>
// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
    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',
            },
        ]
    }]
});
</script>
</html>

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

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

    // 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',
        },
    ]

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

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

<script>
import { ref } from "vue";
import { Spreadsheet, Worksheet } from "@jspreadsheet-ce/vue";

import "jsuites/dist/jsuites.css";
import "jspreadsheet-ce/dist/jspreadsheet.css";

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    setup() {
        // Worksheet data
        const data = ref([
            ['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 = ref([
            { 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',
            },
        ]);

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

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet-ce";

import "jspreadsheet-ce/dist/jspreadsheet.css"
import "jsuites/dist/jsuites.css"

@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];
    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            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',
                    },
                ]
            }]
        });
    }
}

More Information

Explore additional sections related to spreadsheet calculations.

Jspreadsheet CE

Jspreadsheet PRO

Edit this page on GitHub