All Projects → LesterLyu → fast-formula-parser

LesterLyu / fast-formula-parser

Licence: MIT license
Parse and evaluate MS Excel formula in javascript.

Programming Languages

javascript
184084 projects - #8 most used programming language

Projects that are alternatives of or similar to fast-formula-parser

Reogrid
Fast and powerful .NET spreadsheet component, support data format, freeze, outline, formula calculation, chart, script execution and etc. Compatible with Excel 2007 (.xlsx) format and working on .NET 3.5 (or client profile), WPF and Android platform.
Stars: ✭ 532 (+56.01%)
Mutual labels:  formula, excel, spreadsheet
Calx.js
jQuery Calx - a jQuery plugin for creating formula-based calculation form
Stars: ✭ 190 (-44.28%)
Mutual labels:  formula, excel, spreadsheet
spreadsheet
TypeScript/javascript spreadsheet parser, with formulas.
Stars: ✭ 40 (-88.27%)
Mutual labels:  formula, excel, spreadsheet
Formula Parser
Javascript Library parsing Excel Formulas and more
Stars: ✭ 544 (+59.53%)
Mutual labels:  formula, excel, spreadsheet
Excelize
Golang library for reading and writing Microsoft Excel™ (XLSX) files.
Stars: ✭ 10,286 (+2916.42%)
Mutual labels:  formula, excel, spreadsheet
Luckysheet
Luckysheet is an online spreadsheet like excel that is powerful, simple to configure, and completely open source.
Stars: ✭ 9,772 (+2765.69%)
Mutual labels:  formula, excel, spreadsheet
spreadcheetah
SpreadCheetah is a high-performance .NET library for generating spreadsheet (Microsoft Excel XLSX) files.
Stars: ✭ 107 (-68.62%)
Mutual labels:  excel, spreadsheet
xlsx-reader
xlsx-reader is a PHP library for fast and efficient reading of XLSX spreadsheet files. Its focus is on reading the data contained within XLSX files, disregarding all document styling beyond that which is strictly necessary for data type recognition. It is built to be usable for very big XLSX files in the magnitude of multiple GBs.
Stars: ✭ 40 (-88.27%)
Mutual labels:  excel, spreadsheet
BulkPDF
BulkPDF is a free and easy to use open source software, which allows to automatically fill an existing PDF form with differen values. Only a spreadsheet (Microsoft Excel 2007/2010/2013, LibreOffice or OpenOffice Calc) with the desired values is required.
Stars: ✭ 94 (-72.43%)
Mutual labels:  excel, spreadsheet
fxl
fxl is a Clojure spreadsheet library
Stars: ✭ 117 (-65.69%)
Mutual labels:  excel, spreadsheet
xltpl
A python module to generate xls/x files from a xls/x template.
Stars: ✭ 46 (-86.51%)
Mutual labels:  excel, spreadsheet
Dexiom.EPPlusExporter
A very simple, yet incredibly powerfull library to generate Excel documents out of objects, arrays, lists, collections, etc.
Stars: ✭ 19 (-94.43%)
Mutual labels:  excel, spreadsheet
react-datasheet-grid
An Airtable-like / Excel-like component to create beautiful spreadsheets.
Stars: ✭ 227 (-33.43%)
Mutual labels:  excel, spreadsheet
easy-excel
🚀 快速读写Excel文件,简单高效
Stars: ✭ 118 (-65.4%)
Mutual labels:  excel, spreadsheet
Hyperformula
A complete, open-source Excel-like calculation engine written in TypeScript. Includes 380+ built-in functions. Maintained by the Handsontable team⚡
Stars: ✭ 210 (-38.42%)
Mutual labels:  formula, spreadsheet
BakingSheet
Easy datasheet management for C# and Unity. Supports Excel, Google Sheet, JSON and CSV format.
Stars: ✭ 144 (-57.77%)
Mutual labels:  excel, spreadsheet
ToolGood.Algorithm
Support four arithmetic operations, Excel formulas, and support custom parameters. 支持四则运算、Excel公式语法,并支持自定义参数。
Stars: ✭ 77 (-77.42%)
Mutual labels:  formula, excel
xlsx reader
A production-ready XLSX file reader for Elixir.
Stars: ✭ 46 (-86.51%)
Mutual labels:  excel, spreadsheet
spreadsheet
Yii2 extension for export to Excel
Stars: ✭ 79 (-76.83%)
Mutual labels:  excel, spreadsheet
Excel2Object
excel convert to .NET Object | Excel与.NET 对象进行转换,支持公式、多Sheet等功能
Stars: ✭ 35 (-89.74%)
Mutual labels:  formula, excel

GitHub npm (tag) npm Coverage Status Build Status

A Fast Excel Formula Parser & Evaluator

A fast and reliable excel formula parser in javascript. Using LL(1) parser.

Demo

Documentation

Grammar Diagram

Supports 280 Formulas

ABS, ACOS, ACOSH, ACOT, ACOTH, ADDRESS, AND, ARABIC, AREAS, ASC, ASIN, ASINH, ATAN, ATAN2, ATANH, AVEDEV, AVERAGE, AVERAGEA, AVERAGEIF, BAHTTEXT, BASE, BESSELI, BESSELJ, BESSELK, BESSELY, BETA.DIST, BETA.INV, BIN2DEC, BIN2HEX, BIN2OCT, BINOM.DIST, BINOM.DIST.RANGE, BINOM.INV, BITAND, BITLSHIFT, BITOR,
BITRSHIFT, BITXOR, CEILING, CEILING.MATH, CEILING.PRECISE, CHAR, CHISQ.DIST, CHISQ.DIST.RT, CHISQ.INV, CHISQ.INV.RT, CHISQ.TEST, CLEAN, CODE, COLUMN, COLUMNS, COMBIN, COMBINA, COMPLEX, CONCAT, CONCATENATE, CONFIDENCE.NORM, CONFIDENCE.T, CORREL, COS, COSH, COT, COTH, COUNT, COUNTIF, COVARIANCE.P,
COVARIANCE.S, CSC, CSCH, DATE, DATEDIF, DATEVALUE, DAY, DAYS, DAYS360, DBCS, DEC2BIN, DEC2HEX, DEC2OCT, DECIMAL, DEGREES, DELTA, DEVSQ, DOLLAR, EDATE, ENCODEURL, EOMONTH, ERF, ERFC, ERROR.TYPE, EVEN, EXACT, EXP, EXPON.DIST, F.DIST, F.DIST.RT, F.INV, F.INV.RT, F.TEST, FACT, FACTDOUBLE, FALSE, FIND, FINDB,
FISHER, FISHERINV, FIXED, FLOOR, FLOOR.MATH, FLOOR.PRECISE, FORECAST, FORECAST.LINEAR, FREQUENCY, GAMMA, GAMMA.DIST, GAMMA.INV, GAMMALN, GAMMALN.PRECISE, GAUSS, GCD, GEOMEAN, GESTEP, GROWTH, HARMEAN, HEX2BIN, HEX2DEC, HEX2OCT, HLOOKUP, HOUR, HYPGEOM.DIST, IF, IFERROR, IFNA, IFS, IMABS, IMAGINARY, IMARGUMENT,
IMCONJUGATE, IMCOS, IMCOSH, IMCOT, IMCSC, IMCSCH, IMDIV, IMEXP, IMLN, IMLOG10, IMLOG2, IMPOWER, IMPRODUCT, IMREAL, IMSEC, IMSECH, IMSIN, IMSINH, IMSQRT, IMSUB, IMSUM, IMTAN, INDEX, INT, INTERCEPT, ISBLANK, ISERR, ISERROR, ISEVEN, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISO.CEILING, ISOWEEKNUM, ISREF, ISTEXT,
KURT, LCM, LEFT, LEFTB, LN, LOG, LOG10, LOGNORM.DIST, LOGNORM.INV, LOWER, MDETERM, MID, MIDB, MINUTE, MMULT, MOD, MONTH, MROUND, MULTINOMIAL, MUNIT, N, NA, NEGBINOM.DIST, NETWORKDAYS, NETWORKDAYS.INTL, NORM.DIST, NORM.INV, NORM.S.DIST, NORM.S.INV, NOT, NOW, NUMBERVALUE, OCT2BIN, OCT2DEC, OCT2HEX, ODD, OR,
PHI, PI, POISSON.DIST, POWER, PRODUCT, PROPER, QUOTIENT, RADIANS, RAND, RANDBETWEEN, REPLACE, REPLACEB, REPT, RIGHT, RIGHTB, ROMAN, ROUND, ROUNDDOWN, ROUNDUP, ROW, ROWS, SEARCH, SEARCHB, SEC, SECH, SECOND, SERIESSUM, SIGN, SIN, SINH, SQRT, SQRTPI, STANDARDIZE, SUM, SUMIF, SUMPRODUCT, SUMSQ, SUMX2MY2, 
SUMX2PY2, SUMXMY2, T, T.DIST, T.DIST.2T, T.DIST.RT, T.INV, T.INV.2T, TAN, TANH, TEXT, TIME, TIMEVALUE, TODAY, TRANSPOSE, TRIM, TRUE, TRUNC, TYPE, UNICHAR, UNICODE, UPPER, VLOOKUP, WEBSERVICE, WEEKDAY, WEEKNUM, WEIBULL.DIST, WORKDAY, WORKDAY.INTL, XOR, YEAR, YEARFRAC

Size: 291KB Minified, 81KB Gzipped+Minified

Background

Inspired by XLParser and the paper "A Grammar for Spreadsheet Formulas Evaluated on Two Large Datasets" by Efthimia Aivaloglou, David Hoepelman and Felienne Hermans.

Note: The grammar in my implementation is different from theirs. My implementation gets rid of ambiguities to boost the performance.

What is not supported:

  • External reference
    • Anything with [ and ]
  • Ambiguous old styles
    • Sheet name contains :, e.g. SUM('1003:1856'!D6)
    • Sheet name with space that is not quoted, e.g. I am a sheet!A1
  • SUM(Sheet2:Sheet3!A1:C3)
  • You tell me

Performance

  • The expected performance is at least 3x faster than the optimized formula-parser.

Dependency

  • Chevrotain , thanks to this great parser building toolkit.

Examples

  • Install

    npm i fast-formula-parser
    # or using yarn
    yarn add fast-formula-parser
  • Import

    const FormulaParser = require('fast-formula-parser');
    const {FormulaHelpers, Types, FormulaError, MAX_ROW, MAX_COLUMN} = FormulaParser;
    // or
    import FormulaParser, {FormulaHelpers, Types, FormulaError, MAX_ROW, MAX_COLUMN} from 'fast-formula-parser';

    UMD minified build is also provides:

    <script src="/node_modules/fast-formula-parser/build/parser.min.js"> </script> 
  • Basic Usage

    const data = [
      // A  B  C
        [1, 2, 3], // row 1
        [4, 5, 6]  // row 2
    ];
    
    const parser = new FormulaParser({
    
        // External functions, this will override internal functions with same name
        functions: {
            CHAR: (number) => {
                number = FormulaHelpers.accept(number, Types.NUMBER);
                if (number > 255 || number < 1)
                    throw FormulaError.VALUE;
                return String.fromCharCode(number);
            }
        },
    
        // Variable used in formulas (defined name)
        // Should only return range reference or cell reference
        onVariable: (name, sheetName) => {
            // If it is a range reference (A1:B2)
            return {
                sheet: 'sheet name',
                from: {
                    row: 1,
                    col: 1,
                },
                to: {
                    row: 2,
                    col: 2,
                }
            };
            // If it is a cell reference (A1)
            return {
                sheet: 'sheet name',
                row: 1,
                col: 1
            }
        },
    
        // retrieve cell value
        onCell: ({sheet, row, col}) => {
            // using 1-based index
            // return the cell value, see possible types in next section.
            return data[row - 1][col - 1];
        },
    
        // retrieve range values
        onRange: (ref) => {
            // using 1-based index
            // Be careful when ref.to.col is MAX_COLUMN or ref.to.row is MAX_ROW, this will result in
            // unnecessary loops in this approach.
            const arr = [];
            for (let row = ref.from.row; row <= ref.to.row; row++) {
                const innerArr = [];
                if (data[row - 1]) {
                    for (let col = ref.from.col; col <= ref.to.col; col++) {
                        innerArr.push(data[row - 1][col - 1]);
                    }
                }
                arr.push(innerArr);
            }
            return arr;
        }
    });
    
    // position is required for evaluating certain formulas, e.g. ROW()
    const position = {row: 1, col: 1, sheet: 'Sheet1'};
    
    // parse the formula, the position of where the formula is located is required
    // for some functions.
    console.log(parser.parse('SUM(A:C)', position));
    // print 21
    
    // you can specify if the return value can be an array, this is helpful when dealing
    // with an array formula
    console.log(parser.parse('MMULT({1,5;2,3},{1,2;2,3})', position, true));
    // print [ [ 11, 17 ], [ 8, 13 ] ]
  • Custom Async functions

    Remember to use await parser.parseAsync(...) instead of parser.parse(...)

    const position = {row: 1, col: 1, sheet: 'Sheet1'};
    const parser = new FormulaParser({
        onCell: ref => {
            return 1;
        },
        functions: {
            DEMO_FUNC: async () => {
                return [[1,2,3],[4,5,6]];
            }
        },
    });
    console.log(await parser.parseAsync('A1 + IMPORT_CSV())', position));
    // print 2
    console.log(await parser.parseAsync('SUM(DEMO_FUNC(), 1))', position));
    // print 22
  • Custom function requires parser context (e.g. location of the formula)

    const position = {row: 1, col: 1, sheet: 'Sheet1'};
    const parser = new FormulaParser({
        functionsNeedContext: {
            // the first argument is the context
            // the followings are the arguments passed to the function
            ROW_PLUS_COL: (context, ...args) => {
                 return context.position.row + context.position.col;
            }
        },
    });
    console.log(await parser.parseAsync('SUM(ROW_PLUS_COL(), 1)', position));
    // print 3
  • Parse Formula Dependency

    This is helpful for building dependency graph/tree.

    import {DepParser} from 'fast-formula-parser';
    const depParser = new DepParser({
        // onVariable is the only thing you need provide if the formula contains variables
        onVariable: variable => {
            return 'VAR1' === variable ? {from: {row: 1, col: 1}, to: {row: 2, col: 2}} : {row: 1, col: 1};
        }
    });
    
    // position of the formula should be provided
    const position = {row: 1, col: 1, sheet: 'Sheet1'};
    
    // Return an array of references (range reference or cell reference)
    // This gives [{row: 1, col: 1, sheet: 'Sheet1'}]
    depParser.parse('A1+1', position);
    
    // This gives [{sheet: 'Sheet1', from: {row: 1, col: 1}, to: {row: 3, col: 3}}]
    depParser.parse('A1:C3', position);
    
    // This gives [{from: {row: 1, col: 1}, to: {row: 2, col: 2}}]
    depParser.parse('VAR1 + 1', position);
    
    // Complex formula
    depParser.parse('IF(MONTH($K$1)<>MONTH($K$1-(WEEKDAY($K$1,1)-(start_day-1))-IF((WEEKDAY($K$1,1)-(start_day-1))<=0,7,0)+(ROW(O5)-ROW($K$3))*7+(COLUMN(O5)-COLUMN($K$3)+1)),"",$K$1-(WEEKDAY($K$1,1)-(start_day-1))-IF((WEEKDAY($K$1,1)-(start_day-1))<=0,7,0)+(ROW(O5)-ROW($K$3))*7+(COLUMN(O5)-COLUMN($K$3)+1))', position);
    // This gives the following result
    const result = [
        {
            "col": 11,
            "row": 1,
            "sheet": "Sheet1",
        },
        {
            "col": 1,
            "row": 1,
            "sheet": "Sheet1",
        },
        {
            "col": 15,
            "row": 5,
            "sheet": "Sheet1",
        },
        {
            "col": 11,
            "row": 3,
            "sheet": "Sheet1",
        },
    ];

Formula data types in JavaScript

The following data types are used in excel formulas and these are the only valid data types a formula or a function can return.

  • Number (date uses number): 1234
  • String: 'some string'
  • Boolean: true, false
  • Array: [[1, 2, true, 'str']]
  • Range Reference: (1-based index)
    const ref = {
        sheet: String,
        from: {
            row: Number,
            col: Number,
        },
        to: {
            row: Number,
            col: Number,
        },
    }
  • Cell Reference: (1-based index)
    const ref = {
        sheet: String,
        row: Number,
        col: Number,
    }
  • Union (e.g. (A1:C3, E1:G6))
  • FormulaError
    • FormulaError.DIV0: #DIV/0!
    • FormulaError.NA: #N/A
    • FormulaError.NAME: #NAME?
    • FormulaError.NULL: #NULL!
    • FormulaError.NUM: #NUM!
    • FormulaError.REF: #REF!
    • FormulaError.VALUE: #VALUE!

Types Definition

Comming soon

Error handling

  • Lexing/Parsing Error

    Error location is available at error.details.errorLocation

    try {
        parser.parse('SUM(1))', position);
    } catch (e) {
        console.log(e);
        // #ERROR!:
        // SUM(1))
        //       ^
        // Error at position 1:7
        // Redundant input, expecting EOF but found: )
    
        expect(e).to.be.instanceof(FormulaError);
        expect(e.details.errorLocation.line).to.eq(1);
        expect(e.details.errorLocation.column).to.eq(7);
        expect(e.name).to.eq('#ERROR!');
        expect(e.details.name).to.eq('NotAllInputParsedException');
    }
  • Error from internal/external functions or unexpected error from the parser

    The error will be wrapped into FormulaError. The exact error is in error.details.

    const parser = new FormulaParser({
        functions: {
            BAD_FN: () => {
                throw new SyntaxError();
            }
        }
    });
    
    try {
        parser.parse('SUM(1))', position);
    } catch (e) {
        expect(e).to.be.instanceof(FormulaError);
        expect(e.name).to.eq('#ERROR!');
        expect(e.details.name).to.eq('SyntaxError');
    }

Thanks

  • JetBrains
Note that the project description data, including the texts, logos, images, and/or trademarks, for each open source project belongs to its rightful owner. If you wish to add or remove any projects, please contact us at [email protected].