Spreadsheet advance formulas

Formula Pro

JSS Formula Premium is a JavaScript plugin for parsing and executing spreadsheet-like formula strings using NodeJS or your Browser. It handles ranges, variables, JS precision, 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.


{{self.results}}


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;
  • Custom shunting yard parser used, avoiding the use of eval or function;
  • Custom solution for JavaScript precision limitations;
  • Handles operations with dates;
  • 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

Installation


Browser

The basic standalone usage. 
<html>
<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('YjQzMzdlOTRiOGY3ZTQ0ZDQ4ZTI1YWU3MDFjMDI0ZWJmOTNjODA');
</script>
</html>

NodeJS

Using formula pro on your backend. 
// Import formula pro
import formula from '@jspreadsheet/formula-pro';
// Define the license just once
formula.license('YjQzMzdlOTRiOGY3ZTQ0ZDQ4ZTI1YWU3MDFjMDI0ZWJmOTNjODA');
// Calculate
formula('A1*2', { A1: 10 }); // Result 10


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

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


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
var translate = {
    NOW: "AGORA",
    RAND: "ALEATÓRIO",
    RANDBETWEEN: "ALEATÓRIOENTRE",
    YEAR: "ANO",
    AREAS: "ÁREAS",
    ROUND: "ARRED",
    FLOOR: "ARREDMULTB",
    ROUNDDOWN: "ARREDONDAR.PARA.BAIXO",
    ROUNDUP: "ARREDONDAR.PARA.CIMA",
    TRIM: "ARRUMAR",
    ASINH: "ASENH",
    ASIN: "ASEN",
    AVERAGEIF: "MÉDIASE",
    DB: "BD",
    DCOUNT: "BDCONTAR",
    DCOUNTA: "BDCONTARA",
    DDB: "BDD",
    DSTDEVP: "BDDESVPA",
    DSTDEV: "BDEST",
    DGET: "BDEXTRAIR",
    DMAX: "BDMÁX",
    DAVERAGE: "BDMÉDIA",
    DMIN: "BDMÍN",
    DPRODUCT: "BDMULTIPL",
    DSUM: "BDSOMA",
    VDB: "BDV",
    DVAR: "BDVAREST",
    DVARP: "BDVARP",
    BETAINV: "BETA.ACUM.INV",
    BIN2DEC: "BINADEC",
    BIN2HEX: "BINAHEX",
    BIN2OCT: "BINAOCT",
    CHAR: "CARACT",
    CELL: "CÉL",
    CODE: "CÓDIGO",
    COLUMN: "COL",
    COLUMNS: "COLS",
    COMPLEX: "COMPLEXO",
    CONCATENATE: "CONCATENAR",
    COUNTIF: "CONT.SE",
    COUNTIFS: "CONT.SES",
    COUNTA: "CONT.VALORES",
    COUNT: "CONTAR",
    COUNTBLANK: "CONTAR.VAZIO",
    CONVERT: "CONVERTER",
    MATCH: "CORRESP",
    GROWTH: "CRESCIMENTO",
    CRITBINOM: "CRIT.BINOM",
    COUPPCD: "CUPDATAANT",
    COUPNCD: "CUPDATAPRÓX",
    COUPDAYS: "CUPDIAS",
    COUPDAYSBS: "CUPDIASINLIQ",
    COUPDAYSNC: "CUPDIASPRÓX",
    COUPNUM: "CUPNÚM",
    KURT: "CURT",
    DATE: "DATA",
    DATEVALUE: "DATA.VALOR",
    DATEVALUE: "DATA.VALOR",
    EDATE: "DATAM",
    DEC2BIN: "DECABIN",
    DEC2HEX: "DECAHEX",
    DEC2OCT: "DECAOCT",
    FIXED: "DEF.NÚM.DEC",
    GESTEP: "DEGRAU",
    DISC: "DESC",
    OFFSET: "DESLOC",
    AVEDEV: "DESV.MÉDIO",
    STDEV: "DESVPAD",
    STDEVA: "DESVPADA",
    STDEVP: "DESVPADP",
    STDEVPA: "DESVPADPA",
    DEVSQ: "DESVQ",
    DAY: "DIA",
    WEEKDAY: "DIA.DA.SEMANA",
    DAYS360: "DIAS360",
    WORKDAY: "DIATRABALHO",
    NETWORKDAYS: "DIATRABALHOTOTAL",
    RIGHT: "DIREITA",
    NEGBINOMDIST: "DIST.BIN.NEG",
    HYPGEOMDIST: "DIST.HIPERGEOM",
    LOGNORMDIST: "DIST.LOGNORMAL",
    NORMDIST: "DIST.NORM",
    NORMSDIST: "DIST.NORMP",
    CHIDIST: "DIST.QUI",
    BETADIST: "DISTBETA",
    EXPONDIST: "DISTEXPON",
    FDIST: "DISTF",
    GAMMADIST: "DISTGAMA",
    ABSSKEW: "DISTORÇÃO",
    BINOMDIST: "DISTRBINOM",
    TDIST: "DISTT",
    SLN: "DPD",
    DURATION: "DURAÇÃO",
    AND: "E",
    AND: "E",
    ISNA: "É.NÃO.DISP",
    ISNONTEXT: "É.NÃO.TEXTO",
    ISBLANK: "ÉCÉL.VAZIA",
    ISERR: "ÉERRO",
    ISERROR: "ÉERROS",
    EFFECT: "EFETIVA",
    ISODD: "ÉIMPAR",
    ISLOGICAL: "ÉLÓGICO",
    ADDRESS: "ENDEREÇO",
    ISNUMBER: "ÉNÚM",
    STEYX: "EPADYX",
    ISEVEN: "ÉPAR",
    ISPMT: "ÉPGTO",
    ISREF: "ÉREF",
    CHOOSE: "ESCOLHER",
    LEFT: "ESQUERDA",
    ISTEXT: "ÉTEXTO",
    EXACT: "EXATO",
    MID: "EXT.TEXTO",
    FALSO: "FALSO",
    FACTDOUBLE: "FATDUPLO",
    FACT: "FATORIAL",
    EOMONTH: "FIMMÊS",
    PHONETIC: "FONÉTICA",
    YEARFRAC: "FRAÇÃOANO",
    FREQUENCY: "FREQUÊNCIA",
    ERF: "FUNERRO",
    ERFC: "FUNERROCOMPL",
    DEGREES: "GRAUS",
    HEX2BIN: "HEXABIN",
    HEX2DEC: "HEXADEC",
    HEX2OCT: "HEXAOCT",
    HYPERLINK: "HIPERLINK",
    TODAY: "HOJE",
    HOUR: "HORA",
    IMAGINARY: "IMAGINÁRIO",
    IMARGUMENT: "IMARG",
    IMCONJUGATE: "IMCONJ",
    ODD: "ÍMPAR",
    IMPOWER: "IMPOT",
    IMPRODUCT: "IMPROD",
    IMSQRT: "IMRAIZ",
    IMSIN: "IMSENO",
    IMSUM: "IMSOMA",
    IMSUB: "IMSUBTR",
    SLOPE: "INCLINAÇÃO",
    INDEX: "ÍNDICE",
    INDIRECT: "INDIRETO",
    GETPIVOTDATA: "INFODADOSTABELADINÂMICA",
    INFO: "INFORMAÇÃO",
    CONFIDENCE: "INT.CONFIANÇA",
    INTERCEPT: "INTERCEPÇÃO",
    NORMINV: "INV.NORM",
    NORMSINV: "INV.NORMP",
    CHIINV: "INV.QUI",
    FINV: "INVF",
    GAMMAINV: "INVGAMA",
    LOGINV: "INVLOG",
    TINV: "INVT",
    IPMT: "IPGTO",
    ACCRINT: "JUROSACUM",
    ACCRINTM: "JUROSACUMV",
    ROW: "LIN",
    ROWS: "LINS",
    GAMMALN: "LNGAMA",
    FIND: "LOCALIZAR",
    YIELD: "LUCRO",
    YIELDDISC: "LUCRODESC",
    ODDFYIELD: "LUCROPRIMINC",
    ODDLYIELD: "LUCROÚLTINC",
    YIELDMAT: "LUCROVENC",
    LARGE: "MAIOR",
    UPPER: "MAIÚSCULA",
    MROUND: "MARRED",
    MDETERM: "MATRIZ.DETERM",
    MINVERSE: "MATRIZ.INVERSO",
    MMULT: "MATRIZ.MULT",
    MAX: "MÁXIMO",
    MAXA: "MÁXIMOA",
    GCD: "MDC",
    MDURATION: "MDURAÇÃO",
    MEDIAN: "MED",
    AVERAGE: "MÉDIA",
    GEOMEAN: "MÉDIA.GEOMÉTRICA",
    HARMEAN: "MÉDIA.HARMÔNICA",
    TRIMMEAN: "MÉDIA.INTERNA",
    AVERAGEA: "MÉDIAA",
    AVERAGEIF: "MÉDIASE",
    AVERAGEIFS: "MÉDIASES",
    SMALL: "MENOR",
    MONTH: "MÊS",
    MIN: "MÍNIMO",
    MINA: "MÍNIMOA",
    LOWER: "MINÚSCULA",
    MINUTE: "MINUTO",
    LCM: "MMC",
    MODE: "MODO",
    DOLLAR: "MOEDA",
    DOLLARDE: "MOEDADEC",
    DOLLARFR: "MOEDAFRA",
    MIRR: "MTIR",
    REPLACE: "MUDAR",
    PRODUCT: "MULT",
    NOT: "NÃO",
    NA: "NÃO.DISP",
    LEN: "NÚM.CARACT",
    WEEKNUM: "NÚMSEMANA",
    OCT2BIN: "OCTABIN",
    OCT2DEC: "OCTADEC",
    OCT2HEX: "OCTAHEX",
    RANK: "ORDEM",
    PERCENTRANK: "ORDEM.PORCENTUAL",
    TBILLEQ: "OTN",
    TBILLYIELD: "OTNLUCRO",
    TBILLPRICE: "OTNVALOR",
    OR: "OU",
    STANDARDIZE: "PADRONIZAR",
    EVEN: "PAR",
    PERCENTILE: "PERCENTIL",
    PMT: "PGTO",
    CUMPRINC: "PGTOCAPACUM",
    CUMIPMT: "PGTOJURACUM",
    POWER: "POTÊNCIA",
    PPMT: "PPGTO",
    PRICE: "PREÇO",
    PRICEDISC: "PREÇODESC",
    ODDFPRICE: "PREÇOPRIMINC",
    ODDLPRICE: "PREÇOÚLTINC",
    PRICEMAT: "PREÇOVENC",
    FORECAST: "PREVISÃO",
    PROPER: "PRI.MAIÚSCULA",
    LOOKUP: "PROC",
    HLOOKUP: "PROCH",
    SEARCH: "PROCURAR",
    VLOOKUP: "PROCV",
    LINEST: "PROJ.LIN",
    LOGEST: "PROJ.LOG",
    QUARTILE: "QUARTIL",
    QUOTIENT: "QUOCIENTE",
    RADIANS: "RADIANOS",
    SQRT: "RAIZ",
    SQRTPI: "RAIZPI",
    RECEIVED: "RECEBER",
    ROMAN: "ROMANO",
    RSQ: "RQUAD",
    SYD: "SDA",
    IF: "SE",
    IFERROR: "SEERRO",
    SECOND: "SEGUNDO",
    SIN: "SEN",
    SINH: "SENH",
    SIGN: "SINAL",
    SUM: "SOMA",
    SUMSQ: "SOMAQUAD",
    SUMPRODUCT: "SOMARPRODUTO",
    SUMIF: "SOMASE",
    SERIESSUM: "SOMASEQÜÊNCIA",
    SUMIFS: "SOMASES",
    SUMX2MY2: "SOMAX2DY2",
    SUMX2PY2: "SOMAX2SY2",
    SUMXMY2: "SOMAXMY2",
    REPLACE: "SUBSTITUIR",
    RATE: "TAXA",
    INTRATE: "TAXAJUROS",
    TIME: "TEMPO",
    TREND: "TENDÊNCIA",
    CHITEST: "TESTE.QUI",
    FTEST: "TESTEF",
    TTEST: "TESTET",
    ZTEST: "TESTEZ",
    CEILING: "TETO",
    TEXT: "TEXTO",
    TYPE: "TIPO",
    IRR: "TIR",
    CLEAN: "TIRAR",
    TRANSPOSE: "TRANSPOR",
    TRUNC: "TRUNCAR",
    VALUE: "VALOR",
    TIMEVALUE: "VALOR.TEMPO",
    VERDADEIRO: "VERDADEIRO",
    FV: "VF",
    FVSCHEDULE: "VFPLANO",
    PV: "VP",
    NPV: "VPL",
    XIRR: "XTIR",
    XNPV: "XVPL",
}

var 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

Copyright

This extension uses formulas from formulajs and multiple contributors.


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.

JavaScript example

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

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

// Create the spreadsheets
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [
        { minDimensions: [10, 10] },
        { minDimensions: [10, 10] },
    ],
});
</script>
</html>

React example

import React, { useRef } from "react";
import { Spreadsheet, Worksheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";

// License string
const license = 'YTEzZWEwZWU4M2FhYTlmM2I5NDZiNjkzMTBlNmE3YTQzY2VlM2MyNDVmZDRiMzgxYTVlM2IxYjM1ODZiMzg1ZTkwNzQ5NjNiZjdjNzI3YzMxNDBiMmQ0MjcyZTcxM2M0YTRkNTMxNzI0M2Y3Y2QwOTc2MTM0MDBhYTRiMWQ5MzksZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOakl6TlRjM05pd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09';

// Extensions
const extensions = { formula };

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

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

Vue example

import { ref } from 'vue';
import { Spreadsheet, Worksheet } from "@jspreadsheet/vue";
import formula from "@jspreadsheet/formula-pro";

const license = 'YTEzZWEwZWU4M2FhYTlmM2I5NDZiNjkzMTBlNmE3YTQzY2VlM2MyNDVmZDRiMzgxYTVlM2IxYjM1ODZiMzg1ZTkwNzQ5NjNiZjdjNzI3YzMxNDBiMmQ0MjcyZTcxM2M0YTRkNTMxNzI0M2Y3Y2QwOTc2MTM0MDBhYTRiMWQ5MzksZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOakl6TlRjM05pd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09';

// Extensions
const extensions = { formula };

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        const spreadsheet = ref(null);

        return {
            spreadsheet,
            license,
            extensions,
        };
    },
    template: `<Spreadsheet ref="spreadsheet" :license="license" :extensions="extensions">
        <Worksheet :minDimensions="[10,10]" />
        <Worksheet :minDimensions="[10,10]" />
    </Spreadsheet>`,
}

Angular example

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

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

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

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

@Component({
    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: [10, 10] },
                { minDimensions: [10, 10] },
            ],
        });
    }
}


Using external variable in the calculations

How to use external variable in my formulas.

JavaScript example

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

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

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

// Create the spreadsheets
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [
        { data: [['=test*10']] },
    ],
});
</script>
</html>

React example

import React, { useRef } from "react";
import { Spreadsheet, Worksheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";

// License
const license = 'YTEzZWEwZWU4M2FhYTlmM2I5NDZiNjkzMTBlNmE3YTQzY2VlM2MyNDVmZDRiMzgxYTVlM2IxYjM1ODZiMzg1ZTkwNzQ5NjNiZjdjNzI3YzMxNDBiMmQ0MjcyZTcxM2M0YTRkNTMxNzI0M2Y3Y2QwOTc2MTM0MDBhYTRiMWQ5MzksZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOakl6TlRjM05pd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09';

// Extensions
const extensions = { formula };

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

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

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

Vue example

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

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

// License
const license = 'YTEzZWEwZWU4M2FhYTlmM2I5NDZiNjkzMTBlNmE3YTQzY2VlM2MyNDVmZDRiMzgxYTVlM2IxYjM1ODZiMzg1ZTkwNzQ5NjNiZjdjNzI3YzMxNDBiMmQ0MjcyZTcxM2M0YTRkNTMxNzI0M2Y3Y2QwOTc2MTM0MDBhYTRiMWQ5MzksZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOakl6TlRjM05pd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09';

// Extensions
const extensions = { formula };

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

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

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

Angular example

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

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

// Set your JSS license key (The following key only works for one day)
const license = 'YTEzZWEwZWU4M2FhYTlmM2I5NDZiNjkzMTBlNmE3YTQzY2VlM2MyNDVmZDRiMzgxYTVlM2IxYjM1ODZiMzg1ZTkwNzQ5NjNiZjdjNzI3YzMxNDBiMmQ0MjcyZTcxM2M0YTRkNTMxNzI0M2Y3Y2QwOTc2MTM0MDBhYTRiMWQ5MzksZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOakl6TlRjM05pd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09';

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

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

@Component({
    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: [['=test*10']] },
            ],
        });
    }
}


More examples