GitHub - ChasLui/ts-formula-parser: Parse and evaluate MS Excel formula in TypeScript.

A Fast Excel Formula Parser & Evaluator

English | 中文简体

A fast and reliable excel formula parser in TypeScript/JavaScript with full ESM support. Using LL(1) parser.

🔥 Key Features

✅ ESM First: Native ES Module support with TypeScript definitions
⚡ High Performance: 3x faster than other formula parsers
🧮 283+ Excel Functions: Comprehensive Excel function support
🏗️ Multiple Build Formats: ESM, CJS, UMD, IIFE with minified versions
🔒 Type Safe: Full TypeScript support with detailed type definitions
📦 Zero Config: Works out-of-the-box with modern Node.js (>=22.0.0)

Demo

Documentation

Grammar Diagram

Supports 283+ 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, UPPER, TRUE, TRUNC, TYPE, UNICHAR, UNICODE, VLOOKUP, WEBSERVICE, WEEKDAY, WEEKNUM, WEIBULL.DIST, WORKDAY, WORKDAY.INTL, XOR, YEAR, YEARFRAC

Bundle Sizes

Format	Uncompressed	Minified	Gzipped+Minified
ESM	228KB	108KB	~30KB
CJS	230KB	121KB	~32KB
UMD	258KB	109KB	~30KB
IIFE	258KB	108KB	~30KB

Requirements

Node.js: >=22.0.0
Package Manager: npm, yarn, or pnpm (recommended)
Module System: ESM (ES Modules) - CommonJS also supported via build outputs

Testing Framework

This project uses Vitest as the testing framework:

Main test files located in test/ directory
Formula-specific tests located in test/formulas/
Uses @vitest/coverage-v8 for coverage reporting
Test data stored in JSON files (e.g., test/formulas2.json)
All test files are written in TypeScript for better type safety

Key testing commands:

# Run all tests
pnpm test

# Run formula-specific tests
pnpm test:f

# Run tests in watch mode
pnpm test:watch

# Generate coverage report
pnpm run coverage

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 ]
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
SUM(Sheet2:Sheet3!A1:C3)
You tell me

Performance

3x faster than the optimized formula-parser
LL(1) parsing for optimal performance
Optimized bundle sizes across all build formats:
- ESM: 228KB / 108KB minified
- CJS: 230KB / 121KB minified
- UMD: 258KB / 109KB minified
- IIFE: 258KB / 108KB minified

Dependency

Chevrotain , thanks to this great parser building toolkit.

Build Outputs

The package provides multiple build formats for different use cases:

Format	File	Size	Use Case
ESM	`build/index.mjs`	228KB	Modern Node.js/bundlers
CJS	`build/index.cjs`	230KB	Legacy Node.js
UMD	`build/index.umd.min.js`	109KB	Browser globals
IIFE	`build/index.iife.min.js`	108KB	Direct browser use
ESM Browser	`build/index.esm.min.js`	108KB	Modern browsers

Examples

Install

# Using pnpm (recommended)
pnpm add ts-formula-parser

# Using npm
npm install ts-formula-parser

# Using yarn
yarn add ts-formula-parser

Import (ESM - Recommended)

// Default import with named exports
import FormulaParser, {
  FormulaHelpers,
  DepParser,
  SSF,
  FormulaError,
  MAX_ROW,
  MAX_COLUMN,
} from "ts-formula-parser";

// Or named imports only
import { 
  FormulaParser, 
  FormulaHelpers, 
  FormulaError 
} from "ts-formula-parser";

Import (CommonJS - Legacy)

const FormulaParser = require("ts-formula-parser");
const { FormulaHelpers, FormulaError, MAX_ROW, MAX_COLUMN } = FormulaParser;

Browser Usage

<!-- UMD build -->
<script src="/node_modules/ts-formula-parser/build/index.umd.min.js"></script>

<!-- ESM build -->
<script type="module">
  import FormulaParser from '/node_modules/ts-formula-parser/build/index.mjs';
</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, '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 of parser.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(parser.parse("SUM(ROW_PLUS_COL(), 1)", position));
// print 3

Development

This project uses pnpm for package management, TypeScript for type safety, Vitest for testing, and unbuild for creating multiple output formats.

# Install dependencies
pnpm install

# Run tests (using Vitest)
pnpm test

# Run tests in watch mode
pnpm test:watch

# Build all formats
pnpm build

# Development build (stub mode)
pnpm build:dev

# Type checking
pnpm typecheck

# Lint code
pnpm lint

# Generate documentation
pnpm run docs

# Coverage report
pnpm run coverage

# Performance test
pnpm run perf

# CI pipeline (typecheck + test + build)
pnpm run ci

Parse Formula Dependency

This is helpful for building dependency graph/tree.

import { DepParser } from "ts-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",
  },
];

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: String,
    from: {
        row: Number,
        col: Number,
    },
    to: {
        row: Number,
        col: Number,
    },
}

Cell Reference: (1-based index)

const ref = {
    sheet: String,
    row: Number,
    col: 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!

TypeScript Support

Full TypeScript support with comprehensive type definitions

import FormulaParser, { 
  FormulaHelpers, 
  FormulaError, 
  MAX_ROW, 
  MAX_COLUMN,
  DepParser,
  SSF 
} from "ts-formula-parser";

// All types are properly defined
const parser = new FormulaParser({
  onCell: ({ sheet, row, col }) => {
    // TypeScript knows the exact shape of this object
    return data[row - 1][col - 1];
  },
  
  functions: {
    CUSTOM_FUNC: (arg1: number, arg2: string): number => {
      // Custom functions with proper typing
      return FormulaHelpers.accept(arg1, 'number') as number;
    }
  }
});

// Parse with position context
const result = parser.parse('SUM(A1:C3)', {
  sheet: 'Sheet1',
  row: 1, 
  col: 1
});

Error handling

Lexing/Parsing Error

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

Error from internal/external functions or unexpected error from the parser

The error will be wrapped into FormulaError. The exact error is in error.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");
}

Migration from fast-formula-parser

If you're migrating from fast-formula-parser, here are the key changes:

Package name: fast-formula-parser → ts-formula-parser
ESM First: Update your imports to use ES modules
Node.js version: Requires Node.js >=22.0.0
TypeScript: Full type definitions included
Bundle paths: Updated build output paths

// Old (fast-formula-parser)
const FormulaParser = require('fast-formula-parser');

// New (ts-formula-parser)
import FormulaParser from 'ts-formula-parser';

Thanks

Forked from the LesterLyu/fast-formula-parser repo
Built with Chevrotain parser toolkit
Uses unbuild for modern build system

Name		Name	Last commit message	Last commit date
Latest commit History 357 Commits
.github/workflows		.github/workflows
docs		docs
examples		examples
formulas		formulas
grammar		grammar
logos		logos
scripts		scripts
ssf		ssf
test		test
types		types
.eslintrc.json		.eslintrc.json
.gitignore		.gitignore
.jsdoc.json		.jsdoc.json
.npmignore		.npmignore
CODE_OF_CONDUCT.md		CODE_OF_CONDUCT.md
LICENSE		LICENSE
README-zh-CN.md		README-zh-CN.md
README.md		README.md
build.config.ts		build.config.ts
index.d.ts		index.d.ts
index.ts		index.ts
package.json		package.json
pnpm-lock.yaml		pnpm-lock.yaml
tsconfig.json		tsconfig.json
vitest.config.js		vitest.config.js

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Repository files navigation

A Fast Excel Formula Parser & Evaluator

🔥 Key Features

Demo

Documentation

Grammar Diagram

Supports 283+ Formulas

Bundle Sizes

Requirements

Testing Framework

Background

What is not supported

Performance

Dependency

Build Outputs

Examples

Development

Formula data types in JavaScript

TypeScript Support

Error handling

Migration from fast-formula-parser

Thanks

About

Uh oh!

Releases 1

Packages

Languages

License

ChasLui/ts-formula-parser

Folders and files

Latest commit

History

Repository files navigation

A Fast Excel Formula Parser & Evaluator

🔥 Key Features

Demo

Documentation

Grammar Diagram

Supports 283+ Formulas

Bundle Sizes

Requirements

Testing Framework

Background

What is not supported

Performance

Dependency

Build Outputs

Examples

Development

Formula data types in JavaScript

TypeScript Support

Error handling

Migration from fast-formula-parser

Thanks

About

Topics

Resources

License

Code of conduct

Uh oh!

Stars

Watchers

Forks

Releases 1

Packages 0

Languages

Packages