Formula Pro v7

JavaScript spreadsheet formula parser and execution engine for Jspreadsheet.

Compatibility

• Formula Pro v7: Requires Jspreadsheet Pro v13 or later
• Formula Pro v6: Use with Jspreadsheet Pro v12
• Formula Pro v5: Use with Jspreadsheet Pro v11

Overview

Formula Pro implements a custom shunting yard parser that translates Excel-compatible formula strings into secure JavaScript operations. Every function is validated against desktop Excel, and the engine reproduces Excel argument counting, type coercion, error precedence and dynamic array spilling.

Features

• 525 functions, covering 506 of the Excel function set
• Dynamic arrays and spilling, including the A1# spill range operator
• LAMBDA, LET and function values: =BYROW(A1:B2,SUM), =LAMBDA(X,LAMBDA(Y,X+Y))(1)(2)
• Union (A1:B2,D6), intersection A1:B5 B3:C6 and 3D Sheet1:Sheet3!A1 references
• Trim references A1.:.E10 and TRIMRANGE
• Cross-worksheet calculations
• Range operations (A1:B10, A:A, 1:1)
• Defined names, tables and external variables
• Matrix operations
• @ operator and column name references
• Custom functions with access to the calling cell and worksheet
• Async functions (WEBSERVICE) resolved through the Jspreadsheet cell cache
• Floating-point precision adjustment, per result or per operation
• Formula caching for performance
• International customization: argument and array separators, locale-aware dates
• TypeScript definitions and a machine-readable function catalog (formulas.json)

Installation

npm install @jspreadsheet/formula-pro

import jspreadsheet from 'jspreadsheet';
import formula from '@jspreadsheet/formula-pro';

formula.license('###');

jspreadsheet.setExtensions({ formula });

Configuration

// Argument separator and array column separator
formula.divisor = ';';
formula.horizontalArraySeparator = '\';

// Round floating-point noise on the final result, or on every operation
formula.adjustPrecision = true;
formula.adjustPrecisionGranular = true;

// Log evaluation errors to the console
formula.debug = true;

// Transform a formula before it is evaluated
formula.onbeforeformula = (expression) => expression.toUpperCase();

// Intercept evaluation errors
formula.onerror = (error, expression, variables, x, y, worksheet) => {
    console.log(expression, error.message);
};

Custom Functions

formula.setFormula({
    'MY.DOUBLE': function (value) {
        // this.x, this.y and this.worksheet identify the calling cell
        return value * 2;
    },
});

// Remove a custom function
formula.setFormula({ 'MY.DOUBLE': null });

Arguments arrive already resolved, so references become values. Returning an array spills into the neighbouring cells.

Errors

Results are compared by identity against the instances in formula.errors:

| Key | Value | | --- | --- | | nil | #NULL! | | div0 | #DIV/0! | | value | #VALUE! | | ref | #REF! | | name | #NAME? | | num | #NUM! | | na | #N/A | | error | #ERROR! | | data | #GETTING_DATA | | calc | #CALC! | | spill | #SPILL! | | blocked | #BLOCKED! |

Circular references return formula.loopError (#LOOP!).

Supported Functions

| Category | Functions | | --- | ---: | | Statistical | 128 | | Math and trigonometry | 89 | | Financial | 55 | | Engineering | 54 | | Text | 50 | | Lookup and reference | 39 | | Date and time | 25 | | Logical | 25 | | Compatibility | 24 | | Information | 21 | | Database | 12 | | Web | 3 |

The complete list with syntax, parameters and examples ships in formulas.json.

Not implemented: the CUBE functions, RTD, GETPIVOTDATA, STOCKHISTORY, EUROCONVERT, CALL and REGISTER.ID. These depend on external data connections and are listed in the catalog without the pro flag.

Full documentation: https://jspreadsheet.com/docs/formulas/functions

License

Commercial license required. Visit https://jspreadsheet.com for licensing.

Links

• Product page: https://jspreadsheet.com/products/formulas
• Documentation: https://jspreadsheet.com/docs
• Issues: https://github.com/jspreadsheet/pro/issues