A Fast Excel Formula Parser & Evaluator
A fast and reliable excel formula parser in javascript. Using LL(1) parser.
Demo
Documentation
Grammar Diagram
Supports 280 Formulas
ABS, ACOS, ACOSH, ACOT, ACOTH, ADDRESS, AND, ARABIC, AREAS, ASC, ASIN, ASINH, ATAN, ATAN2, ATANH, AVEDEV, AVERAGE, AVERAGEA, AVERAGEIF, BAHTTEXT, BASE, BESSELI, BESSELJ, BESSELK, BESSELY, BETA.DIST, BETA.INV, BIN2DEC, BIN2HEX, BIN2OCT, BINOM.DIST, BINOM.DIST.RANGE, BINOM.INV, BITAND, BITLSHIFT, BITOR,
BITRSHIFT, BITXOR, CEILING, CEILING.MATH, CEILING.PRECISE, CHAR, CHISQ.DIST, CHISQ.DIST.RT, CHISQ.INV, CHISQ.INV.RT, CHISQ.TEST, CLEAN, CODE, COLUMN, COLUMNS, COMBIN, COMBINA, COMPLEX, CONCAT, CONCATENATE, CONFIDENCE.NORM, CONFIDENCE.T, CORREL, COS, COSH, COT, COTH, COUNT, COUNTIF, COVARIANCE.P,
COVARIANCE.S, CSC, CSCH, DATE, DATEDIF, DATEVALUE, DAY, DAYS, DAYS360, DBCS, DEC2BIN, DEC2HEX, DEC2OCT, DECIMAL, DEGREES, DELTA, DEVSQ, DOLLAR, EDATE, ENCODEURL, EOMONTH, ERF, ERFC, ERROR.TYPE, EVEN, EXACT, EXP, EXPON.DIST, F.DIST, F.DIST.RT, F.INV, F.INV.RT, F.TEST, FACT, FACTDOUBLE, FALSE, FIND, FINDB,
FISHER, FISHERINV, FIXED, FLOOR, FLOOR.MATH, FLOOR.PRECISE, FORECAST, FORECAST.LINEAR, FREQUENCY, GAMMA, GAMMA.DIST, GAMMA.INV, GAMMALN, GAMMALN.PRECISE, GAUSS, GCD, GEOMEAN, GESTEP, GROWTH, HARMEAN, HEX2BIN, HEX2DEC, HEX2OCT, HLOOKUP, HOUR, HYPGEOM.DIST, IF, IFERROR, IFNA, IFS, IMABS, IMAGINARY, IMARGUMENT,
IMCONJUGATE, IMCOS, IMCOSH, IMCOT, IMCSC, IMCSCH, IMDIV, IMEXP, IMLN, IMLOG10, IMLOG2, IMPOWER, IMPRODUCT, IMREAL, IMSEC, IMSECH, IMSIN, IMSINH, IMSQRT, IMSUB, IMSUM, IMTAN, INDEX, INT, INTERCEPT, ISBLANK, ISERR, ISERROR, ISEVEN, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISO.CEILING, ISOWEEKNUM, ISREF, ISTEXT,
KURT, LCM, LEFT, LEFTB, LN, LOG, LOG10, LOGNORM.DIST, LOGNORM.INV, LOWER, MDETERM, MID, MIDB, MINUTE, MMULT, MOD, MONTH, MROUND, MULTINOMIAL, MUNIT, N, NA, NEGBINOM.DIST, NETWORKDAYS, NETWORKDAYS.INTL, NORM.DIST, NORM.INV, NORM.S.DIST, NORM.S.INV, NOT, NOW, NUMBERVALUE, OCT2BIN, OCT2DEC, OCT2HEX, ODD, OR,
PHI, PI, POISSON.DIST, POWER, PRODUCT, PROPER, QUOTIENT, RADIANS, RAND, RANDBETWEEN, REPLACE, REPLACEB, REPT, RIGHT, RIGHTB, ROMAN, ROUND, ROUNDDOWN, ROUNDUP, ROW, ROWS, SEARCH, SEARCHB, SEC, SECH, SECOND, SERIESSUM, SIGN, SIN, SINH, SQRT, SQRTPI, STANDARDIZE, SUM, SUMIF, SUMPRODUCT, SUMSQ, SUMX2MY2,
SUMX2PY2, SUMXMY2, T, T.DIST, T.DIST.2T, T.DIST.RT, T.INV, T.INV.2T, TAN, TANH, TEXT, TIME, TIMEVALUE, TODAY, TRANSPOSE, TRIM, TRUE, TRUNC, TYPE, UNICHAR, UNICODE, VLOOKUP, WEBSERVICE, WEEKDAY, WEEKNUM, WEIBULL.DIST, WORKDAY, WORKDAY.INTL, XOR, YEAR, YEARFRAC
Size: 291KB Minified, 81KB Gzipped+Minified
Background
Inspired by XLParser and the paper "A Grammar for Spreadsheet Formulas Evaluated on Two Large Datasets" by Efthimia Aivaloglou, David Hoepelman and Felienne Hermans.
Note: The grammar in my implementation is different from theirs. My implementation gets rid of ambiguities to boost the performance.
What is not supported:
- External reference
- Anything with
[
and]
- Anything with
- Ambiguous old styles
- Sheet name contains
:
, e.g.SUM('1003:1856'!D6)
- Sheet name with space that is not quoted, e.g.
I am a sheet!A1
- Sheet name contains
SUM(Sheet2:Sheet3!A1:C3)
- You tell me
Performance
- The expected performance is at least 3x faster than the optimized formula-parser.
Dependency
- Chevrotain , thanks to this great parser building toolkit.
Examples
-
Install
npm i fast-formula-parser# or using yarnyarn add fast-formula-parser -
Import
const FormulaParser = ;const FormulaHelpers Types FormulaError MAX_ROW MAX_COLUMN = FormulaParser;// or;UMD minified build is also provides:
-
Basic Usage
const data =// A B C1 2 3 // row 14 5 6 // row 2;const parser =// External functions, this will override internal functions with same namefunctions:{number = FormulaHelpersacceptnumber TypesNUMBER;if number > 255 || number < 1throw FormulaErrorVALUE;return String;}// Variable used in formulas (defined name)// Should only return range reference or cell reference{// If it is a range reference (A1:B2)returnsheet: 'sheet name'from:row: 1col: 1to:row: 2col: 2;// If it is a cell reference (A1)returnsheet: 'sheet name'row: 1col: 1}// retrieve cell value{// using 1-based index// return the cell value, see possible types in next section.return datarow - 1col - 1;}// retrieve range values{// using 1-based index// Be careful when ref.to.col is MAX_COLUMN or ref.to.row is MAX_ROW, this will result in// unnecessary loops in this approach.const arr = ;for let row = reffromrow; row <= reftorow; row++const innerArr = ;if datarow - 1for let col = reffromcol; col <= reftocol; col++innerArr;arr;return arr;};// position is required for evaluating certain formulas, e.g. ROW()const position = row: 1 col: 1 sheet: 'Sheet1';// parse the formula, the position of where the formula is located is required// for some functions.console;// print 21// you can specify if the return value can be an array, this is helpful when dealing// with an array formulaconsole;// print [ [ 11, 17 ], [ 8, 13 ] ] -
Custom Async functions
Remember to use
await parser.parseAsync(...)
instead ofparser.parse(...)
const position = row: 1 col: 1 sheet: 'Sheet1';const parser ={return 1;}functions:DEMO_FUNC: async {return 123456;};console;// print 2console;// print 22 -
Custom function requires parser context (e.g. location of the formula)
const position = row: 1 col: 1 sheet: 'Sheet1';const parser =functionsNeedContext:// the first argument is the context// the followings are the arguments passed to the function{return contextpositionrow + contextpositioncol;};console;// print 3 -
Parse Formula Dependency
This is helpful for building
dependency graph/tree
.;const depParser =// onVariable is the only thing you need provide if the formula contains variables{return 'VAR1' === variable ? from: row: 1 col: 1 to: row: 2 col: 2 : row: 1 col: 1;};// position of the formula should be providedconst position = row: 1 col: 1 sheet: 'Sheet1';// Return an array of references (range reference or cell reference)// This gives [{row: 1, col: 1, sheet: 'Sheet1'}]depParser;// This gives [{sheet: 'Sheet1', from: {row: 1, col: 1}, to: {row: 3, col: 3}}]depParser;// This gives [{from: {row: 1, col: 1}, to: {row: 2, col: 2}}]depParser;// Complex formuladepParser;// This gives the following resultconst result ="col": 11"row": 1"sheet": "Sheet1""col": 1"row": 1"sheet": "Sheet1""col": 15"row": 5"sheet": "Sheet1""col": 11"row": 3"sheet": "Sheet1";
Formula data types in JavaScript
The following data types are used in excel formulas and these are the only valid data types a formula or a function can return.
- Number (date uses number):
1234
- String:
'some string'
- Boolean:
true
,false
- Array:
[[1, 2, true, 'str']]
- Range Reference: (1-based index)const ref =sheet: Stringfrom:row: Numbercol: Numberto:row: Numbercol: Number
- Cell Reference: (1-based index)const ref =sheet: Stringrow: Numbercol: Number
- Union (e.g. (A1:C3, E1:G6))
- FormulaError
FormulaError.DIV0
:#DIV/0!
FormulaError.NA
:#N/A
FormulaError.NAME
:#NAME?
FormulaError.NULL
:#NULL!
FormulaError.NUM
:#NUM!
FormulaError.REF
:#REF!
FormulaError.VALUE
:#VALUE!
Types Definition
Comming soon
Error handling
- Lexing/Parsing Error
Error location is available at
error.details.errorLocation
tryparser;catch econsole;// #ERROR!:// SUM(1))// ^// Error at position 1:7// Redundant input, expecting EOF but found: )tobe;to;to;to;to; - Error from internal/external functions or unexpected error from the parser
The error will be wrapped into
FormulaError
. The exact error is inerror.details
.const parser =functions:{throw ;};tryparser;catch etobe;to;to;