DOCSEARCHbyAlgolia
Submit Docs

fast-formula-parser

repository·master·Indexed 19 days ago

https://github.com/lesterlyu/fast-formula-parser

A high-performance Excel formula parser and evaluator for JavaScript. It supports synchronous and asynchronous evaluation, custom function registration, and dependency analysis via DepParser. The library is compatible with Node.js and the browser, and is currently transitioning into the @sheetxl/formulas package.

Tokens
2.9K
Snippets
9
Records
11
Agent score
18%

What's inside fast-formula-parser

  1. Formula data types in JavaScript

    master

    Formulas and functions in fast-formula-parser can only return the following data types:

    • Number: 1234 (Dates are also represented as numbers)
    • String: 'some string'
    • Boolean: true, false
    • Array: [[1, 2, true, 'str']]
    • Range Reference (1-based index):
      {
    sheet: String,
    from: { row: Number, col: Number },
    to: { row: Number, col: Number }
}
    • Cell Reference (1-based index):
      {
    sheet: String,
    row: Number,
    col: Number
}
    • Union: A collection of references (e.g., A1:C3, E1:G6)
    • FormulaError: An error object (e.g., #DIV/0!)

    Supported FormulaError constants:

    • FormulaError.DIV0: #DIV/0!
    • FormulaError.NA: #N/A
    • FormulaError.NAME: #NAME?
    • FormulaError.NULL: #NULL!
    • FormulaError.NUM: #NUM!
    • FormulaError.REF: #REF!
    • FormulaError.VALUE: #VALUE!

  2. Import fast-formula-parser

    master

    The library supports both CommonJS (require) and ESM (import) syntax. For browser environments, a UMD minified build is available via a <script> tag.

    // CommonJS
const FormulaParser = require('fast-formula-parser');
const {FormulaHelpers, Types, FormulaError, MAX_ROW, MAX_COLUMN} = FormulaParser;

// ESM
import FormulaParser, {FormulaHelpers, Types, FormulaError, MAX_ROW, MAX_COLUMN} from 'fast-formula-parser';
    <script src="/node_modules/fast-formula-parser/build/parser.min.js"> </script>

  4. Basic usage of FormulaParser

    master

    To evaluate Excel formulas, instantiate FormulaParser with configuration callbacks that define how to access data and handle variables. You must provide a position object (containing row, col, and sheet) when calling .parse() to support functions like ROW() or COLUMN().

    const data = [
  [1, 2, 3], // row 1
  [4, 5, 6]  // row 2
];

const parser = new FormulaParser({
    // Override or add external functions
    functions: {
        CHAR: (number) => {
            number = FormulaHelpers.accept(number, Types.NUMBER);
            if (number > 255 || number < 1)
                throw FormulaError.VALUE;
            return String.fromCharCode(number);
        }
    },

    // Handle defined names/variables
    onVariable: (name, sheetName) => {
        // Return a cell reference
        return { sheet: 'sheet name', row: 1, col: 1 };
    },

    // Retrieve a single cell value (1-based index)
    onCell: ({sheet, row, col}) => {
        return data[row - 1][col - 1];
    },

    // Retrieve range values (1-based index)
    onRange: (ref) => {
        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;
    }
});

const position = {row: 1, col: 1, sheet: 'Sheet1'};

// Parse a standard formula
console.log(parser.parse('SUM(A:C)', position));

// Parse an array formula (returns an array)
console.log(parser.parse('MMULT({1,5;2,3},{1,2;2,3})', position, true));

  5. Migrate to @sheetxl/formulas

    master

    The original fast-formula-parser is being integrated into the SheetXL ecosystem. For the most up-to-date capabilities, including 100 additional formulas and improved TypeScript support, it is recommended to migrate to the new package @sheetxl/formulas.

    Key benefits of the new package include:

    • Support for more formulas (original set + 100 new ones).
    • Strong typing via TypeScript.
    • Compatibility with both Node.js and the browser.
    • High performance (up to 10x faster than Excel for most operations).
    • Integration with @sheetxl/sdk.

    Note: All formulas remain under the MIT License.

  6. Handle parsing and function errors

    master

    Errors can occur during lexing/parsing or within custom functions. All errors are wrapped in a FormulaError object.

    Lexing/Parsing Errors

    If the formula is syntactically incorrect, the error object contains details.errorLocation (line and column) and details.name (the specific exception type).

    Function Errors

    If a custom function throws an error (e.g., a SyntaxError), it will be caught and wrapped in a FormulaError where error.name is #ERROR! and error.details.name contains the original error name.

    try {
    parser.parse('SUM(1))', position);
} catch (e) {
    // e.details.errorLocation.line
    // e.details.errorLocation.column
    // e.name -> '#ERROR!'
    // e.details.name -> 'NotAllInputParsedException'
}

  7. Use custom async functions

    master

    If your custom functions need to perform asynchronous operations (e.g., fetching data), use await parser.parseAsync(...) instead of the synchronous .parse() method.

    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

  8. Use functions that require parser context

    master

    To create a function that needs access to the formula's location (like ROW() or COLUMN()), register it under functionsNeedContext. The first argument passed to these functions is the context object, which contains the position.

    const position = {row: 1, col: 1, sheet: 'Sheet1'};
const parser = new FormulaParser({
    functionsNeedContext: {
        // context is the first argument
        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

  9. Parse formula dependencies with DepParser

    master

    Use DepParser to build a dependency graph or tree. It identifies which cells or variables a formula relies on. You must provide an onVariable callback if the formula contains named variables.

    import {DepParser} from 'fast-formula-parser';

const depParser = new DepParser({
    onVariable: variable => {
        return 'VAR1' === variable ? {from: {row: 1, col: 1}, to: {row: 2, col: 2}} : {row: 1, col: 1};
    }
});

const position = {row: 1, col: 1, sheet: 'Sheet1'};

// Returns cell reference: [{row: 1, col: 1, sheet: 'Sheet1'}]
depParser.parse('A1+1', position);

// Returns range reference: [{sheet: 'Sheet1', from: {row: 1, col: 1}, to: {row: 3, col: 3}}]
depParser.parse('A1:C3', position);

// Returns variable reference: [{from: {row: 1, col: 1}, to: {row: 2, col: 2}}]
depParser.parse('VAR1 + 1', position);

  10. Use FormulaParser to parse and evaluate spreadsheet formulas

    master

    The FormulaParser class is the primary entrypoint for the library. It is used to parse spreadsheet formulas and evaluate them. The exported FormulaParser object also acts as a namespace for several utility classes and constants including SSF (Spreadsheet Syntax Functions), DepParser (Dependency Parser), and FormulaError.

    const { FormulaParser } = require('fast-formula-parser');

const parser = new FormulaParser();
// Use parser to evaluate formulas...

  11. Reference FormulaParser constants and utilities

    master

    The FormulaParser export includes the following constants and utility classes:

    • MAX_ROW: The maximum number of rows supported (1048576).
    • MAX_COLUMN: The maximum number of columns supported (16384).
    • SSF: Spreadsheet Syntax Functions utility.
    • DepParser: Dependency Parser for analyzing formula dependencies.
    • FormulaError: Error class for formula-related issues.
    • Various helper functions imported from ./formulas/helpers.
    const FormulaParser = require('fast-formula-parser');

console.log(FormulaParser.MAX_ROW);
console.log(FormulaParser.MAX_COLUMN);
// Access SSF, DepParser, or FormulaError directly from FormulaParser