Fast Formula Parser (lesterlyu/fast-formula-parser)

Fast Formula Parser

https://github.com/lesterlyu/fast-formula-parser
Admin
A fast and reliable Excel formula parser and evaluator written in JavaScript, designed for high...

Tokens:10,392
Snippets:52
Trust Score:9.3
Update:3 months ago
Show doc for...
Context Summary (auto-generated)
Raw
# fast-formula-parser

fast-formula-parser is a high-performance Excel formula parser and evaluator for JavaScript. Built on the Chevrotain parser toolkit using an LL(1) grammar, it provides at least 3x faster performance than alternative formula parsers. The library parses and evaluates Excel formulas in both Node.js and browser environments, supporting 280+ Excel functions across math, text, logical, statistical, date, and engineering categories.

The parser is designed for applications that need spreadsheet-like functionality, such as data analysis tools, reporting systems, and web-based spreadsheet applications. It handles formula parsing, dependency tracking, cell references, range operations, and custom function integration. The library returns native JavaScript types (numbers, strings, booleans, arrays) and Excel-compatible error objects, making it straightforward to integrate into existing applications.

## Installation and Basic Usage

### Install the package

```bash
npm install fast-formula-parser
# or
yarn add fast-formula-parser
```

### Parse and evaluate basic formulas

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

// Sample data representing a spreadsheet
const data = [
    // A  B  C
    [1, 2, 3], // row 1
    [4, 5, 6]  // row 2
];

const parser = new FormulaParser({
    // Retrieve 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;
    }
});

// Position of the formula (required for certain functions like ROW())
const position = {row: 1, col: 1, sheet: 'Sheet1'};

// Parse and evaluate formulas
console.log(parser.parse('SUM(A:C)', position));
// Output: 21

console.log(parser.parse('A1 + B2 * C1', position));
// Output: 13 (1 + 5 * 3 = 1 + 15 = 16, evaluates as 1 + 10 = 11 actually)

// Array formulas
console.log(parser.parse('MMULT({1,5;2,3},{1,2;2,3})', position, true));
// Output: [[11, 17], [8, 13]]
```

## Custom Function Registration

### Override internal functions or add new ones

```javascript
const FormulaParser = require('fast-formula-parser');
const {FormulaHelpers, Types, FormulaError} = FormulaParser;

const parser = new FormulaParser({
    // External functions override internal functions with the same name
    functions: {
        CHAR: (number) => {
            number = FormulaHelpers.accept(number, Types.NUMBER);
            if (number > 255 || number < 1)
                throw FormulaError.VALUE;
            return String.fromCharCode(number);
        },
        CUSTOM_ADD: (a, b) => {
            a = FormulaHelpers.accept(a, Types.NUMBER);
            b = FormulaHelpers.accept(b, Types.NUMBER);
            return a + b;
        }
    },

    onCell: ({row, col}) => 0
});

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

console.log(parser.parse('CHAR(65)', position));
// Output: "A"

console.log(parser.parse('CUSTOM_ADD(10, 20)', position));
// Output: 30
```

## Async Functions Support

### Use async functions and await results

```javascript
const FormulaParser = require('fast-formula-parser');

const parser = new FormulaParser({
    onCell: ref => 1,

    functions: {
        ASYNC_DATA: async () => {
            // Simulate API call
            return new Promise(resolve => {
                setTimeout(() => resolve([[1,2,3],[4,5,6]]), 100);
            });
        },
        FETCH_VALUE: async (url) => {
            // Simulate fetching data
            return 42;
        }
    }
});

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

// Must use parseAsync for async functions
(async () => {
    const result1 = await parser.parseAsync('A1 + FETCH_VALUE("api/data")', position);
    console.log(result1);
    // Output: 43

    const result2 = await parser.parseAsync('SUM(ASYNC_DATA(), 1)', position);
    console.log(result2);
    // Output: 22 (sum of all values: 1+2+3+4+5+6+1 = 22)
})();
```

## Context-Aware Functions

### Access formula position in custom functions

```javascript
const FormulaParser = require('fast-formula-parser');

const parser = new FormulaParser({
    functionsNeedContext: {
        // First argument is the context containing position and parser instance
        ROW_PLUS_COL: (context, ...args) => {
            return context.position.row + context.position.col;
        },
        CELL_INFO: (context) => {
            const {row, col, sheet} = context.position;
            return `${sheet}!R${row}C${col}`;
        },
        CUSTOM_OFFSET: (context, offset) => {
            const {FormulaHelpers, Types} = FormulaParser;
            offset = FormulaHelpers.accept(offset, Types.NUMBER);
            const newRow = context.position.row + offset;
            // Access the parser's onCell method
            return context.onCell({
                sheet: context.position.sheet,
                row: newRow,
                col: context.position.col
            });
        }
    },

    onCell: ({row, col}) => row * 10 + col
});

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

console.log(parser.parse('ROW_PLUS_COL()', position));
// Output: 8 (row 3 + col 5)

console.log(parser.parse('CELL_INFO()', position));
// Output: "Sheet1!R3C5"

console.log(parser.parse('CUSTOM_OFFSET(2)', position));
// Output: 55 (row 5 * 10 + col 5 = 55)
```

## Variable Support (Named Ranges)

### Handle defined names and named ranges

```javascript
const FormulaParser = require('fast-formula-parser');

const namedRanges = {
    'TAX_RATE': {row: 1, col: 1, sheet: 'Config'},
    'SALES_DATA': {
        from: {row: 1, col: 1},
        to: {row: 5, col: 3},
        sheet: 'Sales'
    }
};

const parser = new FormulaParser({
    // Handle variable lookup
    onVariable: (name, sheetName, position) => {
        const range = namedRanges[name.toUpperCase()];
        if (!range) return null;
        return range;
    },

    onCell: ({sheet, row, col}) => {
        if (sheet === 'Config' && row === 1 && col === 1) return 0.15;
        if (sheet === 'Sales') return row * col;
        return 0;
    },

    onRange: (ref) => {
        const arr = [];
        for (let row = ref.from.row; row <= ref.to.row; row++) {
            const innerArr = [];
            for (let col = ref.from.col; col <= ref.to.col; col++) {
                innerArr.push(row * col);
            }
            arr.push(innerArr);
        }
        return arr;
    }
});

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

console.log(parser.parse('TAX_RATE * 100', position));
// Output: 15

console.log(parser.parse('SUM(SALES_DATA)', position));
// Output: 105 (sum of 1*1 + 1*2 + 1*3 + 2*1 + 2*2 + 2*3 + ... + 5*3)
```

## Dependency Parsing

### Extract formula dependencies for building dependency graphs

```javascript
const {DepParser} = require('fast-formula-parser');

const depParser = new DepParser({
    // Only needed if formula contains variables
    onVariable: (variable) => {
        if (variable === 'TAX_RATE') {
            return {row: 1, col: 1, sheet: 'Config'};
        }
        if (variable === 'RANGE_DATA') {
            return {
                from: {row: 1, col: 1},
                to: {row: 2, col: 2}
            };
        }
        return null;
    }
});

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

// Simple cell reference
const deps1 = depParser.parse('A1 + 1', position);
console.log(deps1);
// Output: [{row: 1, col: 1, sheet: 'Sheet1'}]

// Range reference
const deps2 = depParser.parse('SUM(A1:C3)', position);
console.log(deps2);
// Output: [{sheet: 'Sheet1', from: {row: 1, col: 1}, to: {row: 3, col: 3}}]

// Variable reference
const deps3 = depParser.parse('TAX_RATE * RANGE_DATA', position);
console.log(deps3);
// Output: [{row: 1, col: 1, sheet: 'Config'}, {from: {row: 1, col: 1}, to: {row: 2, col: 2}}]

// Complex formula with multiple references
const deps4 = depParser.parse('IF(A1>10, SUM(B1:B5), C1*2)', position);
console.log(deps4);
// Output: [
//   {row: 1, col: 1, sheet: 'Sheet1'},
//   {from: {row: 1, col: 2}, to: {row: 5, col: 2}, sheet: 'Sheet1'},
//   {row: 1, col: 3, sheet: 'Sheet1'}
// ]
```

## Error Handling

### Handle parsing errors and formula errors

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

const parser = new FormulaParser({
    onCell: ({row, col}) => {
        if (row === 1 && col === 1) return 0; // A1 = 0 for DIV0 example
        return 10;
    },
    onRange: ref => [[1, 2, 3]],
    functions: {
        BAD_FN: () => {
            throw new SyntaxError('Custom error');
        }
    }
});

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

// Parsing error - extra closing parenthesis
try {
    parser.parse('SUM(1, 2))', position);
} catch (e) {
    console.log(e instanceof FormulaError); // true
    console.log(e.name); // "#ERROR!"
    console.log(e.details.errorLocation);
    // {line: 1, column: 10}
    console.log(e.message);
    // "Redundant input, expecting EOF but found: )"
}

// Division by zero error
try {
    const result = parser.parse('10 / A1', position);
    console.log(result instanceof FormulaError); // true
    console.log(result.name); // "#DIV/0!"
} catch (e) {
    // Errors from evaluation are returned, not thrown
}

// Custom function throwing error
try {
    parser.parse('BAD_FN()', position);
} catch (e) {
    console.log(e instanceof FormulaError); // true
    console.log(e.name); // "#ERROR!"
    console.log(e.details.name); // "SyntaxError"
}

// Accessing FormulaError types
console.log(FormulaError.DIV0.toString()); // "#DIV/0!"
console.log(FormulaError.VALUE.toString()); // "#VALUE!"
console.log(FormulaError.REF.toString()); // "#REF!"
console.log(FormulaError.NA.toString()); // "#N/A"
console.log(FormulaError.NUM.toString()); // "#NUM!"
console.log(FormulaError.NULL.toString()); // "#NULL!"
console.log(FormulaError.NAME.toString()); // "#NAME?"
```

## Type Validation with FormulaHelpers

### Validate and convert argument types in custom functions

```javascript
const FormulaParser = require('fast-formula-parser');
const {FormulaHelpers, Types, FormulaError} = FormulaParser;

const parser = new FormulaParser({
    functions: {
        // Accept single number
        DOUBLE: (value) => {
            const num = FormulaHelpers.accept(value, Types.NUMBER);
            return num * 2;
        },

        // Accept array and flatten
        SUM_CUSTOM: (...args) => {
            let sum = 0;
            FormulaHelpers.flattenParams(
                args,
                Types.NUMBER,
                false, // don't allow unions
                (item) => {
                    const num = FormulaHelpers.accept(item, Types.NUMBER, 0);
                    sum += num;
                }
            );
            return sum;
        },

        // Accept string
        MAKE_UPPER: (text) => {
            const str = FormulaHelpers.accept(text, Types.STRING);
            return str.toUpperCase();
        },

        // Accept boolean
        NEGATE: (bool) => {
            const b = FormulaHelpers.accept(bool, Types.BOOLEAN);
            return !b;
        }
    },

    onCell: ({row, col}) => row * col,
    onRange: ref => [[1, 2], [3, 4]]
});

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

console.log(parser.parse('DOUBLE(21)', position));
// Output: 42

console.log(parser.parse('SUM_CUSTOM(1, 2, 3, 4)', position));
// Output: 10

console.log(parser.parse('MAKE_UPPER("hello")', position));
// Output: "HELLO"

console.log(parser.parse('NEGATE(TRUE)', position));
// Output: false

// Type conversion - string "123" converts to number 123
console.log(parser.parse('DOUBLE("21")', position));
// Output: 42

// Invalid type throws VALUE error
try {
    parser.parse('DOUBLE("abc")', position);
} catch (e) {
    console.log(e.equals(FormulaError.VALUE)); // true
}
```

## Reference and Array Operations

### Work with cell references, range references, and arrays

```javascript
const FormulaParser = require('fast-formula-parser');
const {MAX_ROW, MAX_COLUMN} = FormulaParser;

const data = [
    [10, 20, 30],
    [40, 50, 60],
    [70, 80, 90]
];

const parser = new FormulaParser({
    onCell: ({row, col}) => {
        if (data[row - 1] && data[row - 1][col - 1] !== undefined) {
            return data[row - 1][col - 1];
        }
        return 0;
    },

    onRange: (ref) => {
        // Handle full column/row references
        const maxRow = ref.to.row === MAX_ROW ? data.length : ref.to.row;
        const maxCol = ref.to.col === MAX_COLUMN ?
            Math.max(...data.map(row => row.length)) : ref.to.col;

        const arr = [];
        for (let row = ref.from.row; row <= maxRow; row++) {
            const innerArr = [];
            for (let col = ref.from.col; col <= maxCol; col++) {
                if (data[row - 1] && data[row - 1][col - 1] !== undefined) {
                    innerArr.push(data[row - 1][col - 1]);
                } else {
                    innerArr.push(0);
                }
            }
            arr.push(innerArr);
        }
        return arr;
    }
});

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

// Cell reference
console.log(parser.parse('A1', position));
// Output: 10

// Range reference
console.log(parser.parse('SUM(A1:C1)', position));
// Output: 60

// Full column reference
console.log(parser.parse('SUM(A:A)', position));
// Output: 120 (10 + 40 + 70)

// Array literal
console.log(parser.parse('SUM({1,2,3;4,5,6})', position));
// Output: 21

// Mixed operations
console.log(parser.parse('AVERAGE(A1:C3)', position));
// Output: 50

console.log(parser.parse('MAX(A:C)', position));
// Output: 90

// Array formulas with allowArray=true
console.log(parser.parse('A1:C1 + 5', position, true));
// Output: [15, 25, 35]
```

## Browser Integration

### Use in browser with UMD build

```html
<!DOCTYPE html>
<html>
<head>
    <script src="node_modules/fast-formula-parser/build/parser.min.js"></script>
</head>
<body>
    <script>
        // FormulaParser is available globally
        const parser = new FormulaParser({
            onCell: ({row, col}) => {
                // Get data from DOM or JavaScript
                const cell = document.querySelector(`#cell-${row}-${col}`);
                return cell ? parseFloat(cell.textContent) || 0 : 0;
            },
            onRange: (ref) => {
                const arr = [];
                for (let row = ref.from.row; row <= ref.to.row; row++) {
                    const innerArr = [];
                    for (let col = ref.from.col; col <= ref.to.col; col++) {
                        const cell = document.querySelector(`#cell-${row}-${col}`);
                        innerArr.push(cell ? parseFloat(cell.textContent) || 0 : 0);
                    }
                    arr.push(innerArr);
                }
                return arr;
            }
        });

        const position = {row: 1, col: 1, sheet: 'Sheet1'};
        const result = parser.parse('SUM(A1:A10)', position);
        console.log('Sum result:', result);

        document.getElementById('result').textContent = result;
    </script>
    <div id="result"></div>
</body>
</html>
```

## Summary

fast-formula-parser excels in scenarios requiring Excel formula evaluation in JavaScript environments. Primary use cases include building web-based spreadsheet applications, implementing formula-driven business logic, creating data analysis dashboards, and developing Excel-compatible reporting tools. The library handles complex formulas with nested functions, supports custom function registration for domain-specific calculations, and provides dependency tracking for building reactive spreadsheet systems. Its high performance makes it suitable for processing large datasets and real-time formula evaluation.

Integration is straightforward through the main FormulaParser class and optional DepParser for dependency analysis. Applications typically implement three callback functions (onCell, onRange, onVariable) to bridge the parser with their data storage layer. The parser returns native JavaScript types or FormulaError objects, simplifying error handling and result processing. Support for async functions enables integration with external APIs and databases. With 280+ built-in Excel functions and the ability to add custom functions, the library adapts to diverse spreadsheet calculation requirements while maintaining Excel compatibility.