{"id":13298,"library":"hot-formula-parser","title":"Formula Parser for Excel-like Expressions","description":"hot-formula-parser is a JavaScript library providing a `Parser` class capable of evaluating Excel and mathematical formulas. It supports a wide range of features including arithmetic, logical, and comparison operations, JavaScript Math constants, string concatenation, relative and absolute cell coordinates, custom variables, and custom functions. The current stable version is 4.0.0, released in 2023, which focuses on enhancing module compatibility for both CommonJS and ECMAScript environments. While major releases are not frequent, the project maintains an active development pace with dependency updates and feature enhancements. Its key differentiators include comprehensive Excel formula support via `@handsontable/formulajs`, robust error handling, and the flexibility to extend with custom logic, making it suitable for spreadsheet-like applications in both Node.js and browser environments.","status":"active","version":"4.0.0","language":"javascript","source_language":"en","source_url":"https://github.com/handsontable/formula-parser","tags":["javascript","formula","formulas","parser","formula-parser","excel","spreadsheet"],"install":[{"cmd":"npm install hot-formula-parser","lang":"bash","label":"npm"},{"cmd":"yarn add hot-formula-parser","lang":"bash","label":"yarn"},{"cmd":"pnpm add hot-formula-parser","lang":"bash","label":"pnpm"}],"dependencies":[{"reason":"Provides the core Excel-like formula implementations and functions.","package":"@handsontable/formulajs","optional":false}],"imports":[{"note":"Since v4.0.0, the package is compatible with both ESM and CJS. For ESM, use named import. For CJS, `require('hot-formula-parser')` returns an object with `Parser` as a property. Older versions might have required `require('hot-formula-parser').Parser` directly.","wrong":"import FormulaParser from 'hot-formula-parser';\nconst FormulaParser = require('hot-formula-parser');","symbol":"Parser","correct":"import { Parser } from 'hot-formula-parser';"},{"note":"This is the preferred CommonJS import style since v4.0.0 for better compatibility and explicit named export. Attempting `import` in a pure CJS environment will result in errors.","wrong":"import { Parser } from 'hot-formula-parser';","symbol":"Parser (CJS)","correct":"const { Parser } = require('hot-formula-parser');"},{"note":"This is a named export, available for both ESM and CJS modules to list all supported formula functions.","wrong":"import SUPPORTED_FORMULAS from 'hot-formula-parser';","symbol":"SUPPORTED_FORMULAS","correct":"import { SUPPORTED_FORMULAS } from 'hot-formula-parser';"}],"quickstart":{"code":"import { Parser } from 'hot-formula-parser';\n\nconst parser = new Parser();\n\n// Set custom variables\nparser.setVariable('SALES_TAX_RATE', 0.05);\nparser.setVariable('ITEM_PRICE', 100);\n\n// Set a custom function\nparser.setFunction('CALC_TOTAL_WITH_TAX', (params) => {\n  const price = params[0];\n  const taxRate = params[1];\n  return price * (1 + taxRate);\n});\n\n// Parse a formula using variables and the custom function\nconst result1 = parser.parse('ITEM_PRICE * (1 + SALES_TAX_RATE)');\nconsole.log('Result 1:', result1); // Expected: { error: null, result: 105 }\n\nconst result2 = parser.parse('CALC_TOTAL_WITH_TAX(ITEM_PRICE, SALES_TAX_RATE)');\nconsole.log('Result 2:', result2); // Expected: { error: null, result: 105 }\n\n// Example with a built-in Excel function and error handling\nconst sumResult = parser.parse('SUM(10, 20, \"invalid\")');\nconsole.log('Sum with error:', sumResult); // Expected: { error: \"#VALUE!\", result: null }","lang":"typescript","description":"Demonstrates initializing the parser, setting custom variables and functions, and parsing formulas with both custom and built-in logic, including error handling."},"warnings":[{"fix":"Review your formulas and replace usage of the deprecated functions with alternative logic or custom functions where possible. Refer to the `@handsontable/formula.js` documentation for compatible functions.","message":"Several Excel functions including `NUMERAL`, `DOLLAR`, `FIXED`, `TEXT`, and `VALUE` were either removed or marked as not implemented in v3.0.0 due to incompatibility or internal changes in the underlying `@handsontable/formula.js` package.","severity":"breaking","affected_versions":">=3.0.0"},{"fix":"Thoroughly test existing formulas after upgrading to v3.0.0 or later. Consult the `@handsontable/formula.js` release notes for detailed changes.","message":"Version 3.0.0 updated the core `@handsontable/formula.js` package to a new major version. This update may introduce subtle behavior changes or incompatibilities in certain Excel formula evaluations, even for functions not explicitly listed as removed.","severity":"breaking","affected_versions":">=3.0.0"},{"fix":"For ESM, use `import { Parser } from 'hot-formula-parser';`. For CommonJS, `const { Parser } = require('hot-formula-parser');` is recommended. Ensure your build configuration (e.g., Webpack, Rollup) correctly handles module resolution.","message":"Prior to v4.0.0, module import patterns could be inconsistent, particularly for ESM environments. Version 4.0.0 repackaged the library to improve compatibility with both CommonJS and ECMAScript modules, potentially affecting build processes or specific import statements.","severity":"gotcha","affected_versions":">=4.0.0"},{"fix":"No direct fix is required for most users, as this improves security. However, if you have very unusual custom parsing logic that somehow interacted with the library's internal `eval` usage, review its behavior.","message":"The `eval` usage was removed from the source code in version 3.0.1. While this is a security and CSP improvement, environments or custom configurations that might have implicitly relied on `eval` behavior (though unlikely for direct usage) could see changes.","severity":"gotcha","affected_versions":">=3.0.1"}],"env_vars":null,"last_verified":"2026-04-19T00:00:00.000Z","next_check":"2026-07-18T00:00:00.000Z","problems":[{"fix":"Ensure that all custom variables are set using `parser.setVariable(name, value)` and custom functions using `parser.setFunction(name, fn)` before parsing. Check for typos in function or variable names.","cause":"The formula contains a function or variable name that has not been defined or recognized by the parser.","error":"parser.parse('SUM(MY_VAR)'); // returns `Object {error: '#NAME?', result: null}`"},{"fix":"Modify the formula to prevent division by zero, or implement checks in your custom functions to handle zero divisors gracefully.","cause":"The formula attempts to divide a number by zero.","error":"parser.parse('10 / 0'); // returns `Object {error: '#DIV/0!', result: null}`"},{"fix":"Ensure that the arguments passed to functions match their expected types (e.g., numbers for `SUM`). Validate user input or intermediate calculation results before they are fed into the parser.","cause":"One or more arguments provided to a formula function are of the wrong data type.","error":"parser.parse('SUM(1, 2, \"text\")'); // returns `Object {error: '#VALUE!', result: null}`"},{"fix":"Review the formula for syntax errors such as unmatched parentheses, incorrect delimiters, or invalid operator sequences. Use a linter or syntax highlighter for complex formulas if possible.","cause":"A general parsing error occurred, often due to malformed syntax in the formula.","error":"parser.parse('1;;1'); // returns `Object {error: '#ERROR!', result: null}`"}],"ecosystem":"npm","meta_description":null,"install_score":null,"install_tag":null,"quickstart_score":null,"quickstart_tag":null,"pypi_latest":null,"cli_name":"","cli_version":null}