ProductsFormulas

Spreadsheet advance formulas

Formula Pro

ENTERPRISE

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

Formula Pro v7 requires Jspreadsheet v13. Use Formula Pro v6 with Jspreadsheet v12 and Formula Pro v5 with v11.

What's new with Version 7

  • New references: the spill range A1#, unions (A1:B2,D6), the intersection A1:B5 B3:C6, 3D references Sheet1:Sheet3!A1 and trim references A1.:.E10
  • Functions as values: =BYROW(A1:B2,SUM), and results that can be called in place, =LAMBDA(X,LAMBDA(Y,X+Y))(1)(2)
  • More functions: GROUPBY, PIVOTBY, VSTACK, TOCOL, TOROW, WRAPROWS, WRAPCOLS, TRIMRANGE, IMAGE, REDUCE, PERCENTOF, the FORECAST.ETS family, byte text functions, more financial functions, WEBSERVICE and FILTERXML
  • Excel fidelity: every function validates its argument count, errors follow the argument order, empty and omitted arguments are told apart, and text limits apply per function
  • Engine API: formula.locale, formula.loopError, formula.errors.blocked, a function catalog in formulas.json and complete type definitions

Parse Excel-like formulas using JavaScript

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

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 500 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.

What's new in Formula Pro 7

Breaking changes

  • Requires Jspreadsheet v13: cells are read through the v13 record accessors.
  • formula.debug defaults to false; set it to true to log evaluation errors to the console.
  • Every function declares its accepted argument count. A wrong number of arguments returns #N/A; variadic functions take up to 255 arguments.
  • Matrix arguments passed from JavaScript must be two-dimensional.
  • IMAGE returns an image entity: ISTEXT is FALSE, TYPE is 128, and String(value) still yields the source URL.
  • REGEXEXTRACT and REGEXREPLACE follow the Excel signature (occurrence, return_mode, case_sensitivity); REGEXMATCH keeps the Sheets behaviour.
  • The v11 spill bookkeeping, the Update module and the UpdateLoop export were removed.

New syntax

Syntax Description
=A1# The whole spill range of an anchor cell; composes with : and arithmetic
=SUM((A1:B2,D6)) Union of areas; AREAS counts them and overlapping cells count twice, as in Excel
=SUM(Sheet1:Sheet3!A1) 3D reference over every worksheet between the endpoints, in tab order
=A1:B5 B3:C6 Intersection: the cells common to both references
A1.:E10, A1:.E10, A1.:.E10 Trim references: leading, trailing or both blank rows and columns removed, as TRIMRANGE
=BYROW(A1:B2,SUM) Bare function names as values for BYROW, BYCOL, MAP, REDUCE, SCAN, MAKEARRAY, GROUPBY and PIVOTBY
=CHOOSE(2,LAMBDA(1),LAMBDA(X,X*3))(5) A function returned by an expression can be called in place; lambdas capture their scope

Functions added

  • Dynamic arrays: TAKE, VSTACK, TOCOL, TOROW, WRAPROWS, WRAPCOLS, TRIMRANGE, GROUPBY, PIVOTBY, IMAGE
  • Text: REGEXTEST, ASC, BAHTTEXT, DBCS, JIS, PHONETIC, FINDB, LEFTB, LENB, MIDB, REPLACEB, RIGHTB, SEARCHB
  • Financial: VDB, ACCRINTM, DURATION, MDURATION, RECEIVED, ODDFPRICE, ODDFYIELD, ODDLPRICE, ODDLYIELD
  • Statistical: FORECAST.ETS, FORECAST.ETS.CONFINT, FORECAST.ETS.SEASONALITY, FORECAST.ETS.STAT
  • Web: WEBSERVICE, FILTERXML
  • Logical and math: REDUCE, PERCENTOF
  • Compatibility: PERCENTRANK, WEIBULL

No function was removed.

API additions

Property Description
formula.locale Set by Jspreadsheet from the worksheet international configuration; drives locale-aware date parsing and the byte semantics of the DBCS functions
formula.loopError The #LOOP! error instance returned on circular references
formula.errors.blocked The #BLOCKED! error, returned by IMAGE when a source cannot be loaded
formulas.json Ships with the package: every function with description, syntax, parameters, examples and category

WEBSERVICE resolves through the Jspreadsheet cell cache and the cell shows the value once the request completes; without a worksheet it returns a Promise. The type definitions cover the object-form call, the scope custom functions receive, adjustPrecisionGranular, horizontalArraySeparator, errors, loopError and locale.

Behaviour changes

  • When several arguments are invalid, the first problematic cell in argument order decides the error.
  • A lambda or an invalid value inside a computed array fails only its own cells, not the whole formula.
  • A union used as a cell value returns #VALUE!; a direct 3D reference used as a value returns #REF!; function values inside spilled arrays render as #CALC!.
  • COUNT, AGGREGATE(2) and SUBTOTAL(2/102) skip error values. MAX, MIN, MAXA and MINA ignore booleans and numeric text in referenced cells. SORT and SORTBY return 0 on empty input.
  • BETADIST, CHIDIST, HYPGEOMDIST, LOGNORMDIST, NEGBINOMDIST, NORMSDIST, TTEST, FTEST and ZTEST use their original signatures instead of aliasing the dotted functions.
  • TEXTJOIN flattens matrices cell by cell; ARRAYTOTEXT and VALUETOTEXT use ; as row separator in strict mode.
  • An omitted argument counts as 0; an empty referenced cell is skipped. The 32767-character text limit applies per function.
  • WEEKDAY, ISOWEEKNUM and NETWORKDAYS handle serials in the 1900 range like Excel.

Documentation

Methods

Method Description
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
caching: boolean Enable cache. Default: true
onbeforeformula: Function Intercept a formula before execution.
debug: boolean Log evaluation errors to the console. Default: false
locale: string Locale for date parsing and byte semantics; set by Jspreadsheet

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

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 known Formula Pro limitations:

  • Limited loop detection within conditional functions.
  • LINEST, LOGEST, TREND and GROWTH do not support multi-variable regression.
  • Chained comparisons are right-associative: =1<2<3 returns TRUE where Excel returns FALSE.
  • Scientific notation and trailing-dot number literals are not parsed: =1E2+1 returns #ERROR!.
  • LAMBDA optional parameters [y] are not supported.
  • WEBSERVICE does not accept array or range arguments, and spaces in table column names are not supported.
  • Cube, RTD, GETPIVOTDATA, STOCKHISTORY, EUROCONVERT, CALL and REGISTER.ID are listed in the catalog but not implemented.
  • The Excel formula limits (8192 characters, 64 nesting levels) are not enforced.

Available formulas

You can find a list of the available formulas here.

Integrating with JSS spreadsheet

Basic integration

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

<html>
<script src="https://jsuites.net/v6/jsuites.js"></script>
<script src="https://cdn.jsdelivr.net/npm/jspreadsheet@13/dist/index.min.js"></script>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jspreadsheet@13/dist/jspreadsheet.min.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" />

<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>

<div id='spreadsheet'></div>

<script>
// You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox.
// The license is valid for one day, after which the spreadsheet will become read-only.
// For a longer trial period, you can create a free account and generate a demo license with an extended expiration date.
jspreadsheet.setLicense('Y2JiNGM3NjNkYjRiZDVhNzY0NzA0ODNiNzFiODg0ZTlhNjY4YmI5MDJlMTczNWJkNTM4NGMwMzZmNmYyOTI1ZWRhMGU2YjBkNzNkODgyZWE2YzFjYTM0OWZiYmMxMWFjZjkzMjFkNWZiZDY2MThkYmEzNWY3ZTdiMmNmMWIxNTUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreU1EUTVOVE0wTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

// 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";

// You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox.
// The license is valid for one day, after which the spreadsheet will become read-only.
// For a longer trial period, you can create a free account and generate a demo license with an extended expiration date.
jspreadsheet.setLicense('Y2JiNGM3NjNkYjRiZDVhNzY0NzA0ODNiNzFiODg0ZTlhNjY4YmI5MDJlMTczNWJkNTM4NGMwMzZmNmYyOTI1ZWRhMGU2YjBkNzNkODgyZWE2YzFjYTM0OWZiYmMxMWFjZjkzMjFkNWZiZDY2MThkYmEzNWY3ZTdiMmNmMWIxNTUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreU1EUTVOVE0wTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

// 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>
    );
}

<template>
  <Spreadsheet
    ref="spreadsheet"
    :license="license"
    :extensions="extensions"
    toolbar="true"
  >
    <Worksheet :minDimensions="[10, 10]" :data="[['=SUM(10, 10)']]" />
    <Worksheet :minDimensions="[10, 10]" />
  </Spreadsheet>
</template>

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

import '@lemonadejs/studio';
import '@lemonadejs/studio/dist/style.css';
import 'jspreadsheet/dist/jspreadsheet.css';
import 'jsuites/dist/jsuites.css';

// You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox.
// The license is valid for one day, after which the spreadsheet will become read-only.
// For a longer trial period, you can create a free account and generate a demo license with an extended expiration date.
jspreadsheet.setLicense(
  'OWJiZDQ3MTYzYTUzZTNkOTExZTdhY2M3NWUxNmJlNDNkYjdiNzA4Y2E2YzYxYjdjNDg1ODllYmU1YjRkZjgzNDAwYjcyZGQ0NjE3ZTY1NmVlMzc3MDI4NWVmYmM2MjM0M2NmNGRjNTczMmRlZGY4MGY3MWVlNTBlMzM1MDE1MzgsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVek16RTJNek16TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5'
);

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

export default {
  components: {
    Spreadsheet,
    Worksheet,
  },
};
</script>

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

// You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox.
// The license is valid for one day, after which the spreadsheet will become read-only.
// For a longer trial period, you can create a free account and generate a demo license with an extended expiration date.
jspreadsheet.setLicense('Y2JiNGM3NjNkYjRiZDVhNzY0NzA0ODNiNzFiODg0ZTlhNjY4YmI5MDJlMTczNWJkNTM4NGMwMzZmNmYyOTI1ZWRhMGU2YjBkNzNkODgyZWE2YzFjYTM0OWZiYmMxMWFjZjkzMjFkNWZiZDY2MThkYmEzNWY3ZTdiMmNmMWIxNTUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreU1EUTVOVE0wTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

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

More examples