Skip to content

A toolset for parsing excel-like formulars and calculating with custom functions.

License

Notifications You must be signed in to change notification settings

taggon/tiny-formula

Repository files navigation

Tiny Formula

Npm package version Node.js CI Coverage Status MIT license

A lightweight toolset for parsing excel-like formulas and calculating with custom functions.

No dependencies.

Installation

Using npm:

$ npm i --save tiny-formula

Or using yarn:

$ yarn add tiny-formula

API

parse()

The parse() function takes a formula string and returns a tree of expressions.

parse(formula: string): Expression

An Expression is a plain object containing a type property and other properties depending on the expression type. Note that parse() does NOT check if the cells exist or not. It only syntactically parses the formula to create a tree.

Be careful that all cell names and function names should be uppercased. Lowercased letters are not allowed for the names.

In most cases, you may want to use calc() which calculates the formula instead of parse().

calc()

The calc() function takes a formula string, a set of data and a list of functions and returns the result. The optional second parameter should be a two-dimensional array of data. Every function name should be uppercased.

calc(
    formula: string,
    data: any[][],
    functions: {[string]: Function}
): any

A cell range is always transformed to a two-dimensional array of data whether there's a data array or not.

Exceptions

While parsing, or calculating the formula, parse() and calc() may throw exceptions if something went wrong. The followings are the exceptions thrown by those functions:

Type Description
UnexpectedTokenError Unexpected token found.
CellNotFoundError The referred cell doesn't exist in the given dataset.
UndefinedFunctionError Undefined function is called.

Examples

Parsing a formula string into a tree

import { parse } from 'tiny-formula';

parse('A1+B2');
/**
result:
{
    type: 'group',
    items: [
        {
            type: 'cell',
            name: 'A1',
        },
        {
            type: 'operator',
            op: '+',
        },
        {
            type: 'cell',
            name: 'B2',
        },
    ],
}
**/

parse('FN(TRUE)');
/**
{
    type: 'func',
    items: [
        {
            type: 'literal',
            value: true
        }
    ]
}
*/

parse('FN(,TRUE)'); // throws an UnexpectedToken exception

Calculation

import { calc } from 'tiny-formula';

calc('A1+C1', [[10, 20, 30]]); // 40
calc('"Hello ," & "world!"'); // Hello, world!
calc('D1*10', [[10, 20, 30]]); // throws a CellNotFoundError exception
calc('SQRT(A1)', [[9]], { SQRT: Math.sqrt }); // 3
calc('UNDEFINED()'); // throws a UndefinedFunctionError exception

License

Tiny Formula is released under MIT license.

About

A toolset for parsing excel-like formulars and calculating with custom functions.

Resources

License

Stars

Watchers

Forks

Packages

No packages published