Formula Pro

Formula Pro is a JavaScript plugin for parsing and executing spreadsheet formula strings on the browser or using NodeJS. It handles ranges, variables, worksheets and a great number of formulas available in other spreadsheet software such as Excel or Google Sheets.

Parse excel-like formulas using JavaScript

For testing, you can use the following form, or use the browser’s console.

Type here your formula

Variables should be define as an object

Results

290

What are the main features?

Formula Pro is a plugin that can parse Excel-like formulas into JavaScript, offering users a wide range of capabilities. Some of its key features include:

More than 400 Excel formulas available in JavaScript;
Operates in a private scope;
Utilization of a custom shunting yard parser, eliminating the need for eval or Function constructs;
A bespoke approach to addressing JavaScript's precision limitations;
Capability to perform date operations;
Supports cross-worksheet calculations;
Supports defined names and external variable definitions;
Supports matrix calculations;
Offers international customizations, such as a translate method name feature;
Supports @ as a single operator;
Formula supports column name in calculations.

Documentation

Methods

Method	Description
define	Define external variables. `define(variables: object) => void`
setFormula	Append new custom formulas `setFormula(formulas: object) => void`
reset	Destroy external variables definitions `reset() => void`

Settings

Property	Description
adjustPrecision: boolean	Automatically adjust JavaScript precision issues. Default: false
license: string \| object	License information
cache: boolean	Enable cache. Default: true
onbeforeformula: (expression: string) : string	Intercept a formula before execution.

NOTE: When adjustPrecision is enabled, the results will be rounded to a maximum of ten decimal places.

Installation

Browser

The basic standalone usage.

<html>
<script src="https://cdn.jsdelivr.net/npm/jsuites/dist/jsuites.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>
<script>
// Activate the precision adjust
formula.adjustPrecision = true;
// Define the license just once
formula.license({
    clientId: 'your-client-id',
    licenseKey: 'your-key',
});
</script>
</html>

NodeJS

Using formula pro on your backend.

npm install @jspreadsheet/formula-pro

// Import formula pro
import formula from '@jspreadsheet/formula-pro';
// Define the license just once
formula.license({
    clientId: 'your-client-id',
    licenseKey: 'your-key',
});
// Calculate
formula('A1*2', { A1: 10 }); // Result 20

License

This extension requires a custom license. It uses formulas from formulajs and multiple contributors.

Features

JavaScript precision

JSS Formula Premium's adjustPrecision flag assists with rounding issues in JavaScript by employing an adaptive toFixed method that adjusts dynamically based on the number of decimals required.

// Activate the precision adjust
formula.adjustPrecision = true;

formula('37.02 + 2.56');
// Without adjustPrecision: 39.580000000000005
// With adjustPrecision: 39.58

formula('185.32 - 84.78');
// Without adjustPrecision: 100.53999999999999
// With adjustPrecision: 100.54

formula('25.92 * 3.33');
// Without adjustPrecision: 86.31360000000001
// With adjustPrecision: 86.3136

formula('9.15 / 6');
// Without adjustPrecision: 1.5250000000000001
// With adjustPrecision: 1.525

External variables

You can define variables to be used in your calculations as below.

// Define a number
formula.define({ number: 1000 });
// You can also define a number with single quotes
formula.define({ number: '2000' });
// Define a string you must define with the double quotes.
formula.define({ hello: '"Dealing with strings"' });

Usage example

The formula method receives the expression and the variables that will support the calculations.

/**
 * @param {string} expression - the formula to be calculated
 * @param {object} variables - the variables and values necessary to parse the expression
 * @param {number=} x - a optional coordinate reference
 * @param {number=} y - a optional coordinate reference
*/
formula(expression: String, variables: Object, [x: Number], [y: Number]) : string|array

Internationalization

Translate the formulas name to portuguese, for example:

<html>
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>
<script>
// Activate the precision adjust
formula.adjustPrecision = true;

// Formula parser
formula.onbeforeformula = function(expression) {
    return expression.replace(/\./g, '').replace(/\,/g, '.')
}

// Translate the formula names
let translate = {
    NOW: "AGORA",
    RAND: "ALEATÓRIO",
    RANDBETWEEN: "ALEATÓRIOENTRE",
    YEAR: "ANO",
    AREAS: "ÁREAS",
    ROUND: "ARRED",
    FLOOR: "ARREDMULTB",
    TRIM: "ARRUMAR",
    ASINH: "ASENH",
    ASIN: "ASEN",
    AVERAGEIF: "MÉDIASE",
    DB: "BD",
    DCOUNT: "BDCONTAR",
    DCOUNTA: "BDCONTARA",
    // (...)
    FV: "VF",
    FVSCHEDULE: "VFPLANO",
    PV: "VP",
    NPV: "VPL",
    XIRR: "XTIR",
    XNPV: "XVPL",
}

let keys = Object.keys(translate)
keys.forEach(function(v){
    formula[translate[v]] = formula[v];
});

// By default, method arguments are separated by comma. But you can change that using:
formula.divisor = ';';

// Run the formula
formula("SOMA(1;2;3)"); // result = 6
</script>
</html>

Examples

You can run the following tests in the browser's console when initialized as above.

Range operations

formula('A1:B1*2', { A1: 2, B1: 4 });
// Returns Array[ 4, 8 ]

formula('SUM(A1:B1*2)', { A1: 1, B1: 'A2+B2', A2: 3, B2: 4 })
// Returns 16

formula('SUM(A1:A6)', { A1: 2, A2: 4, A3: 5, A4: 1, A5: 5, A6: 1 });
// Returns 18

formula('AVERAGE(CALCULATION*10)', { CALCULATION: 'A1:A3', A1: 1, A2: 2, A3: 3 })
// Returns 20

formula('SUM(B:B)', { B1: 1, B2: 1, B3: 14 });
// Returns 16

Conditional calculations

formula('IF(B1=0,0,B9/B1)', { B1:0, B9: 3 });
// Returns zero when B1 is zero

formula('IF(true, CALCULATION, 10)', { CALCULATION: 'A1:A3', A1: 1, A2: 2, A3: 3 })
// Returns [[1], [2], [3]]

formula('IF(C16+C15!=0,C13+C14,false)', { C16: 0, C15: 0, C14: 3, C13: 12 })
// Returns false

Comparisons

formula('1*2<1^4');
// Returns false

formula('(1==1)<>(2>2)')
// Returns true

Date calculations

formula('NOW()+1');
// Today + one day

formula('DATE(2021,1,1) > DATE(2021,2,1)');
// false

Cross worksheets

formula('SHEET1!A1*A1', { 'SHEET1!A1': 2, 'A1': 3 });
// Returns 6

formula('SUM(SHEET3!B1:B3)', { 'SHEET3!B1': 3, 'SHEET3!B2': 3, 'SHEET3!B3': 4 });
// Returns 10

formula('SUM(B:B)', { 'B1': 1, 'B2': 2, 'B3': 4 });
// Returns 7

Worksheet operations

formula('SHEET1!A1*10', { SHEET1: [[1,2,3],[4,5,6]] });
// Returns 1 * 10

formula('SHEET1!B1*SHEET2!B1', { SHEET1: [[1,2,3],[4,5,6]], SHEET2: [[10,20,30]] });
// Returns 2 * 20

Limitations

A few of known Formula Pro limitations:

Limited loop detection within conditional functions.

Available formulas

You can find a list of the available formulas here.

Show only implemented formulas

RSQ Available on Formulas Pro

Returns the square of the Pearson product moment correlation coefficient through data points in known_y's and known_x's.

@param known_y's: An array or range of dependent data points.
@param known_x's: An array or range of independent data points.

RSQ(known_y's, known_x's)

RSQ([2,3,9,1,8], [6,5,11,7,5])

DVARP Available on Formulas Pro

The DVARP function calculates the variance of a population.

@param database: The range of cells that makes up the database.
@param field: The column label that contains the numbers for which you want the variance.
@param criteria: The range of cells that contains the conditions you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column.

DVARP(database, field, criteria)

DVARP(A2:C10, 'Sales', A1:C1)

QUARTILE.INC Available on Formulas Pro

Calculates the inclusive quartile of a dataset, which is a value that separates the lowest 25% from the highest 75% of values. This function uses a slightly different calculation than the QUARTILE function.

@param array: The array or range of data for which to determine the quartile.
@param quart: The quartile to return. 1 returns the minimum value, 2 returns the value at the first quartile, 3 returns the value at the median (second quartile), and 4 returns the value at the third quartile.

QUARTILE.INC(array, quart)

QUARTILE.INC(A1:A10,2) 
// returns the value at the first quartile (the value separating the lowest 25% of values from the highest 75%) for the range A1:A10 using the inclusive method

QUARTILE.INC(B2:B20,3) 
// returns the value at the median (the second quartile) for the range B2:B20 using the inclusive method

DAYS360 Available on Formulas Pro

Calculates the number of days between two dates using a 360-day year.

@param start_date: The start date of the time period.
@param end_date: The end date of the time period.
@param [method]: Optional. A flag that specifies the method to use for calculating the number of days. If omitted or zero, the US method is used. If non-zero, the European method is used.

DAYS360(start_date, end_date, [method])

DAYS360("2012-02-02","2012-03-30") 
// returns 58

OR Available on Formulas Pro

Returns true if any argument is true, and false otherwise.

@param logical: The first condition to be evaluated.
@param logicalN: Optional. An optional condition to be evaluated.

OR(logical1, [logical2], ...)

OR(1=1,2=3) 
// returns true

OR(1=2,2=3,3=4) 
// returns false

OR(A1="Yes",A2="Yes",A3="Yes") 
// returns true if at least one of the specified cells contains the value "Yes"

Integrating with JSS spreadsheet

Basic integration

How to integrate JSS spreadsheet and the formula-pro plugin.

Sheet1

Sheet2

	A	B	C	D	E
1	20
2
3
4
5

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

// Add-on for JSpreadsheet
jspreadsheet.setExtensions({ formula });

// Create the spreadsheets
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [
        { minDimensions: [5, 5], data: [['=SUM(10, 10)']] },
        { minDimensions: [5, 5] },
    ],
});
</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)
jspreadsheet.setLicense('ZmZhZDQ5YzNjYTk3NGUyNTUxNDZiNjEzNjExNmZmZjY4ZTgzNTY5MDQzMDcxMjQ0OGIzMWE5YzM4NWE0ZjA5NDc2NWU1NmFlOTlkZmFkOTQ2YzRkMmYzNWU0ODg3YzNkNDBhODhiZmY3ZmQzYjkwZGU3YzNjZTVjZTcwNDgzNmYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpRMU16ZzVPVGs1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');

// Add-on for JSpreadsheet
jspreadsheet.setExtensions({ formula });

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

    // Render component
    return (
        <Spreadsheet ref={spreadsheet}>
            <Worksheet minDimensions={[5, 5]} data={[['=SUM(10, 10)']]} />
            <Worksheet minDimensions={[5, 5]}/>
        </Spreadsheet>
    );
}

import { ref } from 'vue';
import { Spreadsheet, Worksheet, jspreadsheet } 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)
jspreadsheet.setLicense('ZmZhZDQ5YzNjYTk3NGUyNTUxNDZiNjEzNjExNmZmZjY4ZTgzNTY5MDQzMDcxMjQ0OGIzMWE5YzM4NWE0ZjA5NDc2NWU1NmFlOTlkZmFkOTQ2YzRkMmYzNWU0ODg3YzNkNDBhODhiZmY3ZmQzYjkwZGU3YzNjZTVjZTcwNDgzNmYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpRMU16ZzVPVGs1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');

// Add-on for JSpreadsheet
jspreadsheet.setExtensions({ formula });

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    template: `<Spreadsheet ref="spreadsheet">
        <Worksheet :minDimensions="[10,10]" :data="[['=SUM(10, 10)']]" />
        <Worksheet :minDimensions="[10,10]" />
    </Spreadsheet>`,
}

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

// Add-on for JSpreadsheet
jspreadsheet.setExtensions({ formula });

@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, {
            worksheets: [
                { minDimensions: [5, 5], data: [['=SUM(10, 10)']] },
                { minDimensions: [5, 5] },
            ],
        });
    }
}

External Variables Example

How to use external variable in my formulas.

	A	B	C
1	100	200	Dealing with strings

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

// Define custom variables to be use in the calculations
formula.define({ QTY: 10, CALC: 20, HELLO: '"Dealing with strings"' })

// Add-on for Jspreadsheet
jspreadsheet.setExtensions({ formula });

// Create the spreadsheets
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [
        { data: [['=QTY*10', '=CALC*10', '=HELLO']] },
    ],
});
</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";

// License
jspreadsheet.setLicense('ZmZhZDQ5YzNjYTk3NGUyNTUxNDZiNjEzNjExNmZmZjY4ZTgzNTY5MDQzMDcxMjQ0OGIzMWE5YzM4NWE0ZjA5NDc2NWU1NmFlOTlkZmFkOTQ2YzRkMmYzNWU0ODg3YzNkNDBhODhiZmY3ZmQzYjkwZGU3YzNjZTVjZTcwNDgzNmYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpRMU16ZzVPVGs1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');

// Define custom variables to be use in the calculations
formula.define({ QTY: 10, CALC: 20, HELLO: '"Dealing with strings"' });

// Extensions
jspreadsheet.setExtensions({ formula });

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();
    // Data
    const data = [['=QTY*10', '=CALC*10', '=HELLO']];

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

<template>
    <Spreadsheet ref="spreadsheet">
        <Worksheet :data="data" :minDimensions="[10,10]" />
    </Spreadsheet>
</template>

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

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

// License
jspreadsheet.setLicense('ZmZhZDQ5YzNjYTk3NGUyNTUxNDZiNjEzNjExNmZmZjY4ZTgzNTY5MDQzMDcxMjQ0OGIzMWE5YzM4NWE0ZjA5NDc2NWU1NmFlOTlkZmFkOTQ2YzRkMmYzNWU0ODg3YzNkNDBhODhiZmY3ZmQzYjkwZGU3YzNjZTVjZTcwNDgzNmYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpRMU16ZzVPVGs1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');

// Define custom variables to be use in the calculations
formula.define({ QTY: 10, CALC: 20, HELLO: '"Dealing with strings"' });

// Extensions
jspreadsheet.setExtensions({ formula });


export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Data
        const data = [['=QTY*10', '=CALC*10', '=HELLO']];

        return {
            data,
        };
    }
}
</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('ZmZhZDQ5YzNjYTk3NGUyNTUxNDZiNjEzNjExNmZmZjY4ZTgzNTY5MDQzMDcxMjQ0OGIzMWE5YzM4NWE0ZjA5NDc2NWU1NmFlOTlkZmFkOTQ2YzRkMmYzNWU0ODg3YzNkNDBhODhiZmY3ZmQzYjkwZGU3YzNjZTVjZTcwNDgzNmYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpRMU16ZzVPVGs1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');

// Define custom variables to be use in the calculations
formula.define({ QTY: 10, CALC: 20, HELLO: '"Dealing with strings"' });

// Extensions
jspreadsheet.setExtensions({ formula });

@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, {
            worksheets: [
                { data: [['=QTY*10', '=CALC*10', '=HELLO']] },
            ],
        });
    }
}

More examples

Edit this page on GitHub