
Security News
Deno 2.2 Improves Dependency Management and Expands Node.js Compatibility
Deno 2.2 enhances Node.js compatibility, improves dependency management, adds OpenTelemetry support, and expands linting and task automation for developers.
hot-formula-parser
Advanced tools
Library provides a Parser
class that evaluates excel and mathematical formulas.
A recommended way to install Formula Parser is through NPM using the following command:
$ npm install hot-formula-parser --save
Node.js:
var FormulaParser = require('hot-formula-parser').Parser;
var parser = new FormulaParser();
parser.parse('SUM(1, 6, 7)'); // It returns `Object {error: null, result: 14}`
Browser:
<script src="/node_modules/hot-formula-parser/dist/formula-parser.min.js"></script>
<script>
var parser = new formulaParser.Parser();
parser.parse('SUM(1, 6, 7)'); // It returns `Object {error: null, result: 14}`
</script>
It supports:
+
, -
, /
, *
, %
, ^
;AND()
, OR()
, NOT()
, XOR()
;=
, >
, >=
, <
, <=
, <>
;PI()
, E()
, LN10()
, LN2()
, LOG10E()
, LOG2E()
, SQRT1_2()
, SQRT2()
;&
(concatenation eq. parser.parse('-(2&5)');
will return -25
);A1
, $A1
, A$1
, $A$1
;TRUE
, FALSE
, NULL
var parser = new formulaParser.Parser();
Parses and evaluates provided expression. It always returns an object with result
and error
properties. result
property
always keep evaluated value. If error occurs error
property will be set as:
#ERROR!
General error;#DIV/0!
Divide by zero error;#NAME?
Not recognised function name or variable name;#N/A
Indicates that a value is not available to a formula;#NUM!
Occurs when formula encounters an invalid number;#VALUE!
Occurs when one of formula arguments is of the wrong type.parser.parse('(1 + 5 + (5 * 10)) / 10'); // returns `Object {error: null, result: 5.6}`
parser.parse('SUM(MY_VAR)'); // returns `Object {error: "#NAME?", result: null}`
parser.parse('1;;1'); // returns `Object {error: "#ERROR!", result: null}`
Set predefined variable name which can be visible while parsing formula expression.
parser.setVariable('MY_VARIABLE', 5);
parser.setVariable('fooBar', 10);
parser.parse('(1 + MY_VARIABLE + (5 * fooBar)) / fooBar'); // returns `5.6`
Get variable name.
parser.setVariable('fooBar', 10);
parser.getVariable('fooBar'); // returns `10`
Set custom function which can be visible while parsing formula expression.
parser.setFunction('ADD_5', function(params) {
return params[0] + 5;
});
parser.setFunction('GET_LETTER', function(params) {
var string = params[0];
var index = params[1] - 1;
return string.charAt(index);
});
parser.parse('SUM(4, ADD_5(1))'); // returns `10`
parser.parse('GET_LETTER("Some string", 3)'); // returns `m`
Get custom function.
parser.setFunction('ADD_5', function(params) {
return params[0] + 5;
});
parser.getFunction('ADD_5')([1]); // returns `6`
List of all supported formulas function.
require('hot-formula-parser').SUPPORTED_FORMULAS; // An array of formula names
Fired while retrieving variable. If variable was defined earlier using setVariable
you can overwrite it by this hook.
parser.on('callVariable', function(name, done) {
if (name === 'foo') {
done(Math.PI / 2);
}
});
parser.parse('SUM(SIN(foo), COS(foo))'); // returns `1`
Fired while calling function. If function was defined earlier using setFunction
you can overwrite it's result by this hook.
You can also use this to override result of build-in formulas.
parser.on('callFunction', function(name, params, done) {
if (name === 'ADD_5') {
done(params[0] + 5);
}
});
parser.parse('ADD_5(3)'); // returns `8`
Fired while retrieving cell value by its label (eq: B3
, B$3
, B$3
, $B$3
).
parser.on('callCellValue', function(cellCoord, done) {
// using label
if (cellCoord.label === 'B$6') {
done('hello');
}
// or using indexes
if (cellCoord.row.index === 5 && cellCoord.row.isAbsolute && cellCoord.column.index === 1 && !cellCoord.column.isAbsolute) {
done('hello');
}
if (cellCoord.label === 'C6') {
done(0.75);
}
});
parser.parse('B$6'); // returns `"hello"`
parser.parse('B$6&" world"'); // returns `"hello world"`
parser.parse('FISHER(C6)'); // returns `0.9729550745276566`
Fired while retrieving cells range value (eq: A1:B3
, $A1:B$3
, A$1:B$3
, $A$1:$B$3
).
parser.on('callRangeValue', function(startCellCoord, endCellCoord, done) {
var data = [
[1, 2, 3, 4, 5],
[6, 7, 8, 9, 10],
[11, 12, 13, 14, 15],
[16, 17, 18, 19, 20],
];
var fragment = [];
for (var row = startCellCoord.row.index; row <= endCellCoord.row.index; row++) {
var rowData = data[row];
var colFragment = [];
for (var col = startCellCoord.column.index; col <= endCellCoord.column.index; col++) {
colFragment.push(rowData[col]);
}
fragment.push(colFragment);
}
if (fragment) {
done(fragment);
}
});
parser.parse('JOIN(A1:E2)'); // returns `"1,2,3,4,5,6,7,8,9,10"`
parser.parse('COLUMNS(A1:E2)'); // returns `5`
parser.parse('ROWS(A1:E2)'); // returns `2`
parser.parse('COUNT(A1:E2)'); // returns `10`
parser.parse('COUNTIF(A1:E2, ">5")'); // returns `5`
Please see CONTRIBUTING.md.
To see the list of recent changes, see Releases section.
The MIT License (see the LICENSE file for the full text).
You can contact us at hello@handsontable.com.
FAQs
Formula parser
The npm package hot-formula-parser receives a total of 50,251 weekly downloads. As such, hot-formula-parser popularity was classified as popular.
We found that hot-formula-parser demonstrated a not healthy version release cadence and project activity because the last version was released a year ago. It has 2 open source maintainers collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
Deno 2.2 enhances Node.js compatibility, improves dependency management, adds OpenTelemetry support, and expands linting and task automation for developers.
Security News
React's CRA deprecation announcement sparked community criticism over framework recommendations, leading to quick updates acknowledging build tools like Vite as valid alternatives.
Security News
Ransomware payment rates hit an all-time low in 2024 as law enforcement crackdowns, stronger defenses, and shifting policies make attacks riskier and less profitable.