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 --saveNode.js:
varFormulaParser=require('hot-formula-parser').Parser;varparser=newFormulaParser();parser.parse('SUM(1, 6, 7)');// It returns `Object{error: null, result: 14}`Browser:
<scriptsrc="/node_modules/hot-formula-parser/dist/formula-parser.min.js"></script><script>varparser=newformulaParser.Parser();parser.parse('SUM(1, 6, 7)');// It returns `Object{error: null, result: 14}`</script>It supports:
- Any numbers, negative and positive as float or integer;
- Arithmetic operations like
+,-,/,*,%,^; - Logical operations like
AND(),OR(),NOT(),XOR(); - Comparison operations like
=,>,>=,<,<=,<>; - All JavaScript Math constants like
PI(),E(),LN10(),LN2(),LOG10E(),LOG2E(),SQRT1_2(),SQRT2(); - String operations like
&(concatenation eq.parser.parse('-(2&5)');will return-25); - All excel formulas defined in formula.js;
- Relative and absolute cell coordinates like
A1,$A1,A$1,$A$1; - Build-in variables like
TRUE,FALSE,NULL - Custom variables;
- Custom functions/formulas;
- Node and Browser environment.
varparser=newformulaParser.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/AIndicates 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){returnparams[0]+5;});parser.setFunction('GET_LETTER',function(params){varstring=params[0];varindex=params[1]-1;returnstring.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){returnparams[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 namesFired 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 labelif(cellCoord.label==='B$6'){done('hello');}// or using indexesif(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){vardata=[[1,2,3,4,5],[6,7,8,9,10],[11,12,13,14,15],[16,17,18,19,20],];varfragment=[];for(varrow=startCellCoord.row.index;row<=endCellCoord.row.index;row++){varrowData=data[row];varcolFragment=[];for(varcol=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 [email protected].