worksheet_formula #

A standalone formula engine for spreadsheet-like calculations in Dart.

Features #

• Excel/Google Sheets compatible formula parsing
• 400 built-in functions across 14 categories (math, logical, text, statistical, statistical advanced, lookup, date, information, array, financial, engineering, database, lambda/higher-order, web/regex)
• Dynamic array functions (FILTER, SORT, UNIQUE, SEQUENCE, etc.)
• Type-safe formula values with Excel-compatible error handling
• Cell dependency tracking for efficient recalculation
• Custom function registration
• Parse caching for performance
• Zero UI dependencies -- works with any data source

Installation #

dependencies:
  worksheet_formula: ^1.0.0

Quick Start #

import 'package:worksheet_formula/worksheet_formula.dart';

// Create the engine
final engine = FormulaEngine();

// Parse and evaluate
final ast = engine.parse('=1+2*3');
final result = engine.evaluate(ast, myContext);
// result = NumberValue(7)

To connect your data, implement EvaluationContext:

class MyContext implements EvaluationContext {
  final Map<A1, FormulaValue> cells;
  final FunctionRegistry registry;

  MyContext(this.registry, this.cells);

  @override
  A1 get currentCell => 'A1'.a1;
  @override
  String? get currentSheet => null;
  @override
  bool get isCancelled => false;

  @override
  FormulaValue getCellValue(A1 cell) => cells[cell] ?? const EmptyValue();

  @override
  FormulaValue getRangeValues(A1Range range) {
    // Build 2D matrix from your data source
    // ...
  }

  @override
  FormulaFunction? getFunction(String name) => registry.get(name);

  @override
  FormulaValue? getVariable(String name) => null;
}

Then evaluate formulas with cell references:

final context = MyContext(engine.functions, {
  'A1'.a1: NumberValue(10),
  'A2'.a1: NumberValue(20),
});
final result = engine.evaluateString('=SUM(A1:A2)', context);
// result = NumberValue(30)

Built-in Functions #

Math & Trigonometry (50) #

SUM, AVERAGE, MIN, MAX, ABS, ROUND, INT, MOD, SQRT, POWER, SUMPRODUCT, ROUNDUP, ROUNDDOWN, CEILING, FLOOR, SIGN, PRODUCT, RAND, RANDBETWEEN, PI, LN, LOG, LOG10, EXP, SIN, COS, TAN, ASIN, ACOS, ATAN, ATAN2, DEGREES, RADIANS, EVEN, ODD, GCD, LCM, TRUNC, MROUND, QUOTIENT, COMBIN, COMBINA, FACT, FACTDOUBLE, SUMSQ, SUBTOTAL, AGGREGATE, SERIESSUM, SQRTPI, MULTINOMIAL

Logical (11) #

IF, AND, OR, NOT, IFERROR, IFNA, TRUE, FALSE, IFS, SWITCH, XOR

Text (36) #

CONCAT, CONCATENATE, LEFT, RIGHT, MID, LEN, LOWER, UPPER, TRIM, TEXT, FIND, SEARCH, SUBSTITUTE, REPLACE, VALUE, TEXTJOIN, PROPER, EXACT, REPT, CHAR, CODE, CLEAN, DOLLAR, FIXED, T, NUMBERVALUE, UNICHAR, UNICODE, TEXTBEFORE, TEXTAFTER, TEXTSPLIT, ARRAYTOTEXT, VALUETOTEXT, ASC, DBCS, BAHTTEXT

Statistical (35) #

COUNT, COUNTA, COUNTBLANK, COUNTIF, SUMIF, AVERAGEIF, SUMIFS, COUNTIFS, AVERAGEIFS, MEDIAN, MODE.SNGL, MODE, LARGE, SMALL, RANK.EQ, RANK, STDEV.S, STDEV.P, VAR.S, VAR.P, PERCENTILE.INC, PERCENTILE.EXC, PERCENTRANK.INC, PERCENTRANK.EXC, RANK.AVG, FREQUENCY, AVEDEV, AVERAGEA, MAXA, MINA, TRIMMEAN, GEOMEAN, HARMEAN, MAXIFS, MINIFS

Lookup & Reference (18) #

VLOOKUP, INDEX, MATCH, HLOOKUP, LOOKUP, CHOOSE, XMATCH, XLOOKUP, ROW, COLUMN, ROWS, COLUMNS, ADDRESS, INDIRECT, OFFSET, TRANSPOSE, HYPERLINK, AREAS

Date/Time (25) #

DATE, TODAY, NOW, YEAR, MONTH, DAY, DAYS, DATEDIF, DATEVALUE, WEEKDAY, HOUR, MINUTE, SECOND, TIME, EDATE, EOMONTH, TIMEVALUE, WEEKNUM, ISOWEEKNUM, NETWORKDAYS, NETWORKDAYS.INTL, WORKDAY, WORKDAY.INTL, DAYS360, YEARFRAC

Information (15) #

ISBLANK, ISERROR, ISNUMBER, ISTEXT, ISLOGICAL, ISNA, TYPE, ISERR, ISNONTEXT, ISEVEN, ISODD, ISREF, N, NA, ERROR.TYPE

Dynamic Array (21) #

SEQUENCE, RANDARRAY, TOCOL, TOROW, WRAPROWS, WRAPCOLS, CHOOSEROWS, CHOOSECOLS, DROP, TAKE, EXPAND, HSTACK, VSTACK, FILTER, UNIQUE, SORT, SORTBY, MUNIT, MMULT, MDETERM, MINVERSE

Financial (40) #

PMT, FV, PV, NPER, RATE, IPMT, PPMT, CUMIPMT, CUMPRINC, NPV, XNPV, IRR, XIRR, MIRR, FVSCHEDULE, SLN, SYD, DB, DDB, VDB, PRICE, YIELD, DURATION, MDURATION, ACCRINT, DISC, INTRATE, RECEIVED, PRICEDISC, PRICEMAT, TBILLEQ, TBILLPRICE, TBILLYIELD, DOLLARDE, DOLLARFR, EFFECT, NOMINAL, PDURATION, RRI, ISPMT

Advanced Statistical & Probability (70) #

FISHER, FISHERINV, STANDARDIZE, PERMUT, PERMUTATIONA, DEVSQ, KURT, SKEW, SKEW.P, COVARIANCE.P, COVARIANCE.S, CORREL, PEARSON, RSQ, SLOPE, INTERCEPT, STEYX, FORECAST.LINEAR, PROB, MODE.MULT, STDEVA, STDEVPA, VARA, VARPA, GAMMA, GAMMALN, GAMMALN.PRECISE, GAUSS, PHI, NORM.S.DIST, NORM.S.INV, NORM.DIST, NORM.INV, BINOM.DIST, BINOM.INV, BINOM.DIST.RANGE, NEGBINOM.DIST, HYPGEOM.DIST, POISSON.DIST, EXPON.DIST, GAMMA.DIST, GAMMA.INV, BETA.DIST, BETA.INV, CHISQ.DIST, CHISQ.INV, CHISQ.DIST.RT, CHISQ.INV.RT, T.DIST, T.INV, T.DIST.2T, T.INV.2T, T.DIST.RT, F.DIST, F.INV, F.DIST.RT, F.INV.RT, WEIBULL.DIST, LOGNORM.DIST, LOGNORM.INV, CONFIDENCE.NORM, CONFIDENCE.T, Z.TEST, T.TEST, CHISQ.TEST, F.TEST, LINEST, LOGEST, TREND, GROWTH

Engineering (54) #

DELTA, GESTEP, BITAND, BITOR, BITXOR, BITLSHIFT, BITRSHIFT, BIN2DEC, BIN2HEX, BIN2OCT, DEC2BIN, DEC2HEX, DEC2OCT, HEX2BIN, HEX2DEC, HEX2OCT, OCT2BIN, OCT2DEC, OCT2HEX, BASE, DECIMAL, ARABIC, ROMAN, ERF, ERF.PRECISE, ERFC, ERFC.PRECISE, COMPLEX, IMREAL, IMAGINARY, IMABS, IMARGUMENT, IMCONJUGATE, IMSUM, IMSUB, IMPRODUCT, IMDIV, IMPOWER, IMSQRT, IMEXP, IMLN, IMLOG10, IMLOG2, IMSIN, IMCOS, IMTAN, IMSINH, IMCOSH, IMSEC, IMSECH, IMCSC, IMCSCH, IMCOT, CONVERT

Database (12) #

DSUM, DAVERAGE, DCOUNT, DCOUNTA, DMAX, DMIN, DGET, DPRODUCT, DSTDEV, DSTDEVP, DVAR, DVARP

Lambda & Higher-Order (9) #

LAMBDA, LET, MAP, REDUCE, SCAN, MAKEARRAY, BYCOL, BYROW, ISOMITTED

Web & Regex (4) #

ENCODEURL, REGEXMATCH, REGEXEXTRACT, REGEXREPLACE

Custom Functions #

class DiscountFunction extends FormulaFunction {
  @override String get name => 'DISCOUNT';
  @override int get minArgs => 2;
  @override int get maxArgs => 2;

  @override
  FormulaValue call(List<FormulaNode> args, EvaluationContext context) {
    final values = evaluateArgs(args, context);
    final price = values[0].toNumber() ?? 0;
    final rate = values[1].toNumber() ?? 0;
    return FormulaValue.number(price * (1 - rate));
  }
}

engine.registerFunction(DiscountFunction());
engine.evaluateString('=DISCOUNT(100, 0.2)', context);
// result = NumberValue(80)

Dependency Tracking #

Track which cells depend on which, and determine recalculation order:

final graph = DependencyGraph();

// B1 = A1 + 1
graph.updateDependencies('B1'.a1, {'A1'.a1});
// C1 = B1 * 2
graph.updateDependencies('C1'.a1, {'B1'.a1});

// When A1 changes, recalculate in order:
final toRecalc = graph.getCellsToRecalculate('A1'.a1);
// [B1, C1]

// Detect circular references
graph.hasCircularReference('A1'.a1); // false

TEXT Format Codes #

The TEXT function supports Excel-style format codes:

Supported Operators #

Error Types #

All Excel-compatible error types are supported: #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #N/A, #NULL!, #CALC!, #CIRCULAR!

Examples #

Standalone Dart #

example/worksheet_formula_example.dart -- a pure Dart example demonstrating parsing, evaluation, cell references, dependency tracking, custom functions, and conditional logic. No Flutter required.

dart run example/worksheet_formula_example.dart

Flutter + Worksheet Widget #

example/worksheet_integration/ -- a Flutter app integrating worksheet_formula with the worksheet widget. Shows formula cells evaluated live in a spreadsheet grid with dependency tracking, caching, and a custom DISCOUNT function.

cd example/worksheet_integration
flutter run

See the integration README for architecture details.

License #

See LICENSE file.