Formula Parser

Formula Parser

Library provides a Parser class that evaluates excel and mathematical formulas.

hot-formula-parser 可以处理Excel和数学公式

Install

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>

Features

支持：任何数字(整数小数正数负数)、四则运算、逻辑运算、比较运算、Math库，自定义变量

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;

API (methods)

var parser = new formulaParser.Parser();

.parse(expression) 计算表达式

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}`

.setVariable(name, value) 设置变量

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`

.getVariable(name) 获取变量

Get variable name.

parser.setVariable('fooBar', 10);

parser.getVariable('fooBar'); // returns `10`

.setFunction(name, fn) 设置函数

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`

.getFunction(name) 获取函数

Get custom function.

parser.setFunction('ADD_5', function(params) {
  return params[0] + 5;
});

parser.getFunction('ADD_5')([1]); // returns `6`

.SUPPORTED_FORMULAS 支持公式

List of all supported formulas function.

列出全部支持的公式的名称数组

require('hot-formula-parser').SUPPORTED_FORMULAS; // An array of formula names

API (hooks)

'callVariable' (name, done)

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`

'callFunction' (name, params, done)

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`

'callCellValue' (cellCoord, done)

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`

'callRangeValue' (startCellCoord, endCellCoord, done)

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`