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