Try Live
Add Docs
Rankings
Pricing
Docs
Install
Theme
Install
Docs
Pricing
More...
More...
Try Live
Rankings
Enterprise
Create API Key
Add Docs
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
Context
Skills
Chat
Benchmark
71.3
Suggestions
Latest
Show doc for...
Code
Info
Show Results
Context Summary (auto-generated)
Raw
Copy
Link
# 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.