fast-formula-parser
Advanced tools
A fast and reliable excel formula parser in javascript. Using LL(1) parser.
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
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.
[ and ]:, e.g. SUM('1003:1856'!D6)I am a sheet!A1SUM(Sheet2:Sheet3!A1:C3)Install
npm i fast-formula-parser
# or using yarn
yarn add fast-formula-parser
Import
const FormulaParser = require('fast-formula-parser');
const {FormulaHelpers, Types, FormulaError, MAX_ROW, MAX_COLUMN} = FormulaParser;
// or
import FormulaParser, {FormulaHelpers, Types, FormulaError, MAX_ROW, MAX_COLUMN} from 'fast-formula-parser';
UMD minified build is also provides:
<script src="/node_modules/fast-formula-parser/build/parser.min.js"> </script>
Basic Usage
const data = [
// A B C
[1, 2, 3], // row 1
[4, 5, 6] // row 2
];
const parser = new FormulaParser({
// External functions, this will override internal functions with same name
functions: {
CHAR: (number) => {
number = FormulaHelpers.accept(number, Types.NUMBER);
if (number > 255 || number < 1)
throw FormulaError.VALUE;
return String.fromCharCode(number);
}
},
// Variable used in formulas (defined name)
// Should only return range reference or cell reference
onVariable: (name, sheetName) => {
// If it is a range reference (A1:B2)
return {
sheet: 'sheet name',
from: {
row: 1,
col: 1,
},
to: {
row: 2,
col: 2,
}
};
// If it is a cell reference (A1)
return {
sheet: 'sheet name',
row: 1,
col: 1
}
},
// retrieve cell value
onCell: ({sheet, row, col}) => {
// using 1-based index
// return the cell value, see possible types in next section.
return data[row - 1][col - 1];
},
// retrieve range values
onRange: (ref) => {
// 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 = ref.from.row; row <= ref.to.row; row++) {
const innerArr = [];
if (data[row - 1]) {
for (let col = ref.from.col; col <= ref.to.col; col++) {
innerArr.push(data[row - 1][col - 1]);
}
}
arr.push(innerArr);
}
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.log(parser.parse('SUM(A:C)', position));
// print 21
// you can specify if the return value can be an array, this is helpful when dealing
// with an array formula
console.log(parser.parse('MMULT({1,5;2,3},{1,2;2,3})', position, true));
// 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 = new FormulaParser({
onCell: ref => {
return 1;
},
functions: {
DEMO_FUNC: async () => {
return [[1,2,3],[4,5,6]];
}
},
});
console.log(await parser.parseAsync('A1 + IMPORT_CSV())', position));
// print 2
console.log(await parser.parseAsync('SUM(DEMO_FUNC(), 1))', position));
// print 22
Custom function requires parser context (e.g. location of the formula)
const position = {row: 1, col: 1, sheet: 'Sheet1'};
const parser = new FormulaParser({
functionsNeedContext: {
// the first argument is the context
// the followings are the arguments passed to the function
ROW_PLUS_COL: (context, ...args) => {
return context.position.row + context.position.col;
}
},
});
console.log(await parser.parseAsync('SUM(ROW_PLUS_COL(), 1)', position));
// print 3
Parse Formula Dependency
This is helpful for building
dependency graph/tree.
import {DepParser} from 'fast-formula-parser';
const depParser = new DepParser({
// onVariable is the only thing you need provide if the formula contains variables
onVariable: variable => {
return 'VAR1' === variable ? {from: {row: 1, col: 1}, to: {row: 2, col: 2}} : {row: 1, col: 1};
}
});
// position of the formula should be provided
const 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.parse('A1+1', position);
// This gives [{sheet: 'Sheet1', from: {row: 1, col: 1}, to: {row: 3, col: 3}}]
depParser.parse('A1:C3', position);
// This gives [{from: {row: 1, col: 1}, to: {row: 2, col: 2}}]
depParser.parse('VAR1 + 1', position);
// Complex formula
depParser.parse('IF(MONTH($K$1)<>MONTH($K$1-(WEEKDAY($K$1,1)-(start_day-1))-IF((WEEKDAY($K$1,1)-(start_day-1))<=0,7,0)+(ROW(O5)-ROW($K$3))*7+(COLUMN(O5)-COLUMN($K$3)+1)),"",$K$1-(WEEKDAY($K$1,1)-(start_day-1))-IF((WEEKDAY($K$1,1)-(start_day-1))<=0,7,0)+(ROW(O5)-ROW($K$3))*7+(COLUMN(O5)-COLUMN($K$3)+1))', position);
// This gives the following result
const 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",
},
];
The following data types are used in excel formulas and these are the only valid data types a formula or a function can return.
1234'some string'true, false[[1, 2, true, 'str']]const ref = {
sheet: String,
from: {
row: Number,
col: Number,
},
to: {
row: Number,
col: Number,
},
}
const ref = {
sheet: String,
row: Number,
col: Number,
}
FormulaError.DIV0: #DIV/0!FormulaError.NA: #N/AFormulaError.NAME: #NAME?FormulaError.NULL: #NULL!FormulaError.NUM: #NUM!FormulaError.REF: #REF!FormulaError.VALUE: #VALUE!Comming soon
Error location is available at
error.details.errorLocation
try {
parser.parse('SUM(1))', position);
} catch (e) {
console.log(e);
// #ERROR!:
// SUM(1))
// ^
// Error at position 1:7
// Redundant input, expecting EOF but found: )
expect(e).to.be.instanceof(FormulaError);
expect(e.details.errorLocation.line).to.eq(1);
expect(e.details.errorLocation.column).to.eq(7);
expect(e.name).to.eq('#ERROR!');
expect(e.details.name).to.eq('NotAllInputParsedException');
}
The error will be wrapped into
FormulaError. The exact error is inerror.details.
const parser = new FormulaParser({
functions: {
BAD_FN: () => {
throw new SyntaxError();
}
}
});
try {
parser.parse('SUM(1))', position);
} catch (e) {
expect(e).to.be.instanceof(FormulaError);
expect(e.name).to.eq('#ERROR!');
expect(e.details.name).to.eq('SyntaxError');
}
FAQs
fast excel formula parser
We found that fast-formula-parser demonstrated a not healthy version release cadence and project activity because the last version was released a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
Research
The Socket Research Team breaks down a malicious wrapper package that uses obfuscation to harvest credentials and exfiltrate sensitive data.
Research
Security News
Attackers used a malicious npm package typosquatting a popular ESLint plugin to steal sensitive data, execute commands, and exploit developer systems.
Security News
The Ultralytics' PyPI Package was compromised four times in one weekend through GitHub Actions cache poisoning and failure to rotate previously compromised API tokens.