Danfma.MySheet

🇧🇷 Documentação em português: docs/pt-BR

A fast, in-memory spreadsheet formula engine for .NET — parse Excel-style formulas, evaluate them, and extract data, without a spreadsheet application.

Why MySheet

MySheet does not try to compete with ClosedXML, EPPlus, NPOI or the other established Excel libraries — they are very good at what they do, which is reading and writing Excel files with high fidelity. MySheet solves a narrower problem, in a simpler and faster way:

You keep an Excel workbook on a server as the source of truth for a calculation. You need to load it, re-evaluate its formulas with fresh inputs, and expose or write back the results — repeatedly, with low overhead, and without Excel installed.

For that scenario, MySheet gives you:

• A real formula engine. Formulas are parsed into expression trees and re-evaluated in-process by MySheet itself — no Excel installation, no COM automation, no shelling out.
• A deliberately small API. A Workbook holds Sheets, a Sheet holds Expressions, and Evaluate returns a ComputedValue. That is most of the surface.
• A performance-first design. Evaluating numbers, booleans, blanks and errors allocates nothing (ComputedValue is a value-type union), cell results are memoized, and deep dependency chains run on a dedicated large-stack thread instead of overflowing.

And to be equally honest about scope: MySheet implements 304 built-in functions (plus your own custom ones) out of Excel's ~520, and the Excel interop intentionally skips styles, number formats and other presentation features in its current MVP. If you need full-fidelity spreadsheet manipulation, the libraries above remain the right tools — they also combine well with MySheet (the test suite itself uses ClosedXML as an independent oracle).

Packages

Package	What it does
Danfma.MySheet	The core engine: formula parser, 304 built-in functions, custom functions, allocation-free evaluation, per-cell memoization, MemoryPack serialization.
Danfma.MySheet.Excel	Excel (.xlsx) interop via the OpenXML SDK: load workbooks (formulas become real expression trees), export to .xlsx, and merge computed values into existing templates.

The two packages are released in lockstep and always share the same version.

dotnet add package Danfma.MySheet
dotnet add package Danfma.MySheet.Excel   # only if you need .xlsx interop

Both target .NET 10.

Quick start

using Danfma.MySheet;
using Danfma.MySheet.Expressions;
using Danfma.MySheet.Parsing;

var workbook = new Workbook();
var sheet = workbook.Sheets.Add("Sheet1");

sheet["A1"] = new NumberValue(1);
sheet["A2"] = new NumberValue(2);
sheet["A3"] = ExpressionParser.Parse("=SUM(A1:A2)", sheet);

// Typed, allocation-free result (memoized per cell):
ComputedValue result = workbook.GetCellValue("Sheet1", "A3");

double total = result.ToDouble();            // 3.0 (throws if not a number)

if (result.TryGetError(out Error error))     // e.g. error.Display == "#DIV/0!"
{
    // handle the error
}

object? boxed = result.AsObject();           // 3.0 (double) — for object?-based interop

Excel as the source of truth, end to end:

using Danfma.MySheet;
using Danfma.MySheet.Excel;
using Danfma.MySheet.Expressions;

// Load: formulas come back as real expression trees, re-evaluated by MySheet.
Workbook workbook = ExcelFile.Load("model.xlsx");

// Feed fresh inputs and read the recalculated outputs.
var inputs = workbook["Inputs"];
inputs["B1"] = new NumberValue(1250);
workbook.InvalidateCache();

double projection = workbook.GetCellValue("Results", "B10").ToDouble();

// Produce a report from a pristine template: copy it, then merge the computed
// values into the copy (formatting preserved, formulas replaced by literal values).
File.Copy("template.xlsx", "report.xlsx", overwrite: true);
workbook.MergeIntoExcel("report.xlsx");

Features

• Formula parser (Pratt parser) for the Excel operator set: + - * / ^ %, & (text), comparisons = <> < > <= >= (Excel cross-type ordering), and references : ! , plus grouping ( ).
• 304 built-in functions: logical (IF/IFS/SWITCH/AND/OR/XOR/NOT/IFERROR/IFNA/LET/TRUE/FALSE), aggregation (SUM/AVERAGE/MIN/MAX/COUNT/COUNTA/COUNTBLANK and the AVERAGEA/MAXA/MINA variants), conditional aggregation (COUNTIF(S)/SUMIF(S)/AVERAGEIF(S)/MAXIFS/MINIFS), descriptive statistics (MEDIAN/MODE.SNGL/LARGE/SMALL/RANK.EQ/RANK.AVG, percentiles and quartiles .INC/.EXC, TRIMMEAN, dispersion STDEV.S/.P, VAR.S/.P and their *A variants, AVEDEV/DEVSQ/GEOMEAN/HARMEAN/SKEW(.P)/KURT/STANDARDIZE, bivariate CORREL/PEARSON/COVARIANCE.P/.S/RSQ/SLOPE/INTERCEPT/STEYX/FORECAST.LINEAR, FISHER(INV)/PHI/PERMUT(A)/PROB, plus the legacy Compatibility aliases MODE/STDEV(P)/VAR(P)/RANK/PERCENTILE/PERCENTRANK/QUARTILE/COVAR/FORECAST), SUMPRODUCT/SUMX2MY2/SUMX2PY2/SUMXMY2 and SUBTOTAL (nested subtotals skipped for real), text (UPPER/LOWER/TRIM/LEN/LEFT/MID/RIGHT, search & replace FIND/SEARCH/REPLACE/SUBSTITUTE/TEXTBEFORE/TEXTAFTER plus REGEXTEST/REGEXEXTRACT/REGEXREPLACE, codes CHAR/CODE/UNICHAR/UNICODE/CLEAN, formatting TEXT/FIXED/DOLLAR/VALUE/NUMBERVALUE/VALUETOTEXT, CONCAT/CONCATENATE/TEXTJOIN/REPT/PROPER/EXACT/T), math & trigonometry (SQRT/POWER/EXP/LN/LOG, rounding TRUNC/MROUND/CEILING/FLOOR and variants, MOD/QUOTIENT/PRODUCT, combinatorics FACT/COMBIN/GCD/LCM, the full trig/hyperbolic set, ROMAN/ARABIC/BASE/DECIMAL, the volatile RAND/RANDBETWEEN), info (the IS* family, N/NA/TYPE/ERROR.TYPE/SHEET/SHEETS), lookup & reference (ROW/ROWS/COLUMN/COLUMNS/MATCH/XMATCH/INDEX/CHOOSE/LOOKUP/VLOOKUP/HLOOKUP/XLOOKUP/OFFSET/ADDRESS/AREAS/FORMULATEXT), date & time (dates as Excel serial numbers: DATE/TIME/DATEVALUE/TIMEVALUE, YEAR/MONTH/DAY/HOUR/MINUTE/SECOND, DAYS/DAYS360/EDATE/EOMONTH/WEEKDAY/WEEKNUM/ISOWEEKNUM/DATEDIF/YEARFRAC, working days NETWORKDAYS(.INTL)/WORKDAY(.INTL), and the volatile TODAY/NOW), and financial — the full viable set (PMT/PV/FV/NPER/IPMT/PPMT/NPV/RATE/IRR, depreciation SLN/SYD/DB/DDB/VDB/AMORLINC/AMORDEGRC, rates & value EFFECT/NOMINAL/MIRR/RRI/PDURATION/ISPMT/ CUMIPMT/CUMPRINC/FVSCHEDULE/DOLLARDE/DOLLARFR, dated cash flows XNPV/XIRR, and the bond/T-bill/coupon family PRICE/YIELD/DURATION/MDURATION/ACCRINT(M)/DISC/INTRATE/RECEIVED/PRICE(DISC/MAT)/YIELD(DISC/MAT)/ TBILL*/COUP*/ODD*, day-count bases 0-4 validated against an independent oracle).
• Volatile functions (NOW/TODAY/RAND/RANDBETWEEN): cached per epoch so every NOW()/RAND() in a pass agrees, then refreshed on demand by workbook.Recalculate() (which drops only the cells that touched a volatile — directly or transitively — and re-samples the clock, keeping every stable cell cached). The clock is an injectable TimeProvider (local time, like Excel) and the RNG is seedable (workbook.RandomSeed) for reproducible runs — see Volatile functions.
• References: sheet-qualified (Sheet2!A1, 'My Sheet'!A1:B2), absolute markers ($A$1), reference unions ((A1:A3, C1:C3)), whole-column/row and one-sided open references (A:A, A:C, 1:5, A2:A, A:A10, A1:C — the populated cells within the limits), and case-insensitive sheet names. A reference to a non-existent sheet resolves to #REF! (never throws), so a dangling cross-sheet reference cannot abort a batch; Workbook.TryGetSheet is the non-throwing host lookup.
• Named ranges: workbook-level defined names (workbook.DefineName("Sales", "Data!A1:A3"), case-insensitive) usable anywhere a range is — SUM(Sales), VLOOKUP(x, Table, 2) — with LET shadowing, cycle detection (#REF! instead of overflow), and .xlsx round-trip.
• Custom functions: register host functions by name (workbook.RegisterFunction) — arguments arrive unevaluated (lazy, short-circuit friendly), and calls parse and serialize with the workbook.
• Allocation-free evaluation: expression.Evaluate(workbook) returns a ComputedValue — an opaque value-type union (number / boolean / text / blank / error / reference) that does not box numbers. Extract strictly with TryGetNumber/AsDouble/ToDouble (and TryGetError(out Error)), or get the boxed object? via AsObject() for interop.
• Memoization: per-cell cache (storing ComputedValue inline — no long-lived per-cell box) with explicit invalidation (InvalidateCache); circular references become #REF! instead of a stack overflow.
• MemoryPack serialization of the workbook (Workbook.Save/Load, sync and async), with an opt-in warm start (WorkbookSaveOptions.IncludeComputedValues) that persists the computed cache so a load skips recomputation, and opt-in Brotli compression (WorkbookSaveOptions.Compression) that shrinks large models to well under half their size; the default save stays byte-identical to the model-only format.
• Bulk value reads: GetValueReader(sheet).GetValue(column, row) reads computed values by numeric address — no per-cell id strings or parsing in extraction loops, allocation-free on cache hits (misses still evaluate on demand).
• Excel (.xlsx) interop via Danfma.MySheet.Excel (OpenXML SDK, cross-platform, no Excel installation): ExcelFile.Load, SaveAsExcel (ValuesOnly snapshot or Formulas with cached values), and MergeIntoExcel (inject computed values into an existing file, preserving formatting). All three paths stream the worksheet XML — no OpenXML DOM is ever materialized.
• Shared formulas share one master tree: dragged formulas load through a single parsed, anchored expression tree per group instead of an independent tree per slave cell — 59% less allocation (46% faster) loading a shared-formula-heavy workbook, with an honest fallback to full per-slave expansion for the few shapes it can't safely share (open ranges, unions, chained cross-sheet endpoints); see Excel interop.

Documentation

Guide	What it covers
Getting started	Install, build a workbook, parse and evaluate your first formulas.
Workbook, sheets and expressions	The object model, parsing rules, operators, references, and turning expressions back into formula text.
ComputedValue and errors	The result type: kinds, strict extraction (TryGet*/As*/To*), AsObject(), and the Error struct.
Custom functions	Registering host functions, lazy arguments, implicit return conversions, ranges, and serialization/re-registration.
Excel interop	ExcelFile.Load, SaveAsExcel and FormulaMode, MergeIntoExcel, the template→report recipe, and scope limits.
Serialization	MemoryPack Save/Load, what round-trips, and what must be re-registered.
Performance	Memoization, cache invalidation, RunWithLargeStack, and the allocation-free design (with measured numbers).
Function reference	All 304 built-in functions by category, plus the full Excel coverage table.
Migrating to 2.0	The 2.0 namespace reorganization: type → namespace map, before/after using examples, serialization compatibility.

Excel function coverage

MySheet implements 304 of the ~520 functions in Microsoft's official Excel function catalog. The full per-category coverage table (implemented vs. not yet) lives in the function reference; the authoritative registered list is the Functions map in Danfma.MySheet/Parsing/Parser.cs.

License

MIT — see LICENSE.