ironcalc

I didn't write this Excel formula engine. Here is why I trust it anyway --- title: I didn't write this Excel formula engine. Here is why I trust it anyway published: true tags: dotnet, csharp, opensource, ai canonical_url: medium.com cover_image: dev-to-uploads.s3.us-east-2.amazonaws.com --- SharpCell.Grid running in the browser. Try it with your own .xlsx: live demo Disclosure: SharpCell's code was written by Claude Code, with me directing it. This article was drafted with Claude too, and edited by me. Which is kind of the point of the article. Originally published on Medium. As we know, sooner or later every .NET project gets an .xlsx with formulas in it. A pricing model from the finance team, a template the customer fills in, a file that is, honestly, the real specification of the business logic. Reading the cells is easy. Calculating them is where the fun starts: change one input, and every formula that depends on it has to be recalculated, and the result has to be what Excel shows. Otherwise the numbers in your app and in the user's Excel quietly drift apart. The usual options: - Run real Excel on the server through Interop - Rewrite the formulas in C# by hand - Use a free library: ClosedXML (MIT) and NPOI both evaluate formulas - Buy a commercial one, like EPPlus Drawbacks of each - Interop needs Excel on the server, and Microsoft itself does not recommend or support server-side Office automation. Next. - Manual rewrite works until the finance team changes the file. They will change the file. - Free libraries may be all you need for simple workbooks. Check their function lists against your files first. I wanted dynamic arrays, LAMBDA, structured references and, most of all, a published measurement of how often the results match Excel. - EPPlus has all of that: 470+ functions, dynamic arrays, LAMBDA. Since version 5, commercial use needs a license, $569 per developer per year for a small team at the time of writing. Fair price for a good product. Still, a formula engine feels like something .NET should have for free, the way it has JSON. So I built one. More precisely, I directed Claude Code to build it. Introducing SharpCell SharpCell is a free, MIT-licensed Excel formula engine for .NET 8 and later. Parser, dependency graph, recalculation, dynamic arrays, LET/LAMBDA, tables with structured references like Sales[Units]. It reads .xlsx files, recalculates them and saves them back. Advantages of SharpCell: - Free, MIT, no license keys - Results measured against real Excel (see below) - 490 functions, including dynamic arrays, LET, LAMBDA, MAP, BYROW, GROUPBY, PIVOTBY - Custom functions in C#, for workbooks that call a VBA function or an add-in - No third-party dependencies, AOT-compatible and trimmable, targets .NET 8 and .NET 10 - For servers: load a template once and Clone() it per request Setting up SharpCell 1. Install the package dotnet add package SharpCell.Xlsx # reads and saves .xlsx; brings SharpCell with it 2. Load the workbook and recalculate it using SharpCell; using SharpCell.Xlsx; // Read the workbook: sheets, values, formulas and any results Excel saved with them. Workbook workbook = XlsxReader.Load(path); Worksheet budget = workbook["Budget"]; // Calculate every formula now instead of relying on saved results. workbook.Recalculate(); 3. Or skip the file and build the workbook in code var workbook = new Workbook(); Worksheet sheet = workbook.AddSheet("Sheet1"); sheet["A1"].Value = 2; sheet["A2"].Formula = "=A1*3"; workbook.Recalculate(); CellValue result = sheet["A2"].Value; // 6 Let's now try more custom scenarios: 1. The workbook calls a function that doesn't exist. The file was built with some add-in or a VBA function, and on the server it's just #NAME?. Write that function in C#: workbook.Functions.Add("VAT", args => { CellValue amount = args.Number(0); // converted as Excel converts: "100" is 100 if (amount.IsError) return amount; // pass #VALUE! and other errors on return amount.AsNumber() * 0.2; }, new FunctionOptions { MinArguments = 1, MaxArguments = 1, ScalarParameters = [0] }); sheet["A1"].Value = 100; sheet["A2"].Value = 250; sheet["B1"].Formula = "=VAT(A1)"; // 20 sheet["C1"].Formula = "=VAT(A1:A2)"; // a scalar parameter given a range: spills 20 and 50 workbook.Recalculate(); Give it a range where it expects one value, and it runs once per element and spills, like a built-in function. It can even go where a LAMBDA is expected: MAP(A1:A9, VAT). 2. Tables. Structured references work in tables from files and from code. SUBTOTAL and AGGREGATE skip hidden rows. 3. Saving. The file is saved with your changes and recalculated values. New files can be created from objects or a DataTable. So how do I trust code I didn't write? Here is the problem with letting an agent build something like this. If the agent writes the code and also writes the tests, it grades its own homework. Its tests encode its own idea of how Excel works, so a wrong idea passes every test. So the rule is: expected values never come from the agent. They come from Excel. - The test corpus is workbooks calculated by Microsoft Excel. Most of them are the test files of IronCalc, an open-source spreadsheet engine written in Rust, licensed MIT or Apache-2.0. Thanks to its authors for publishing them. They are copied unmodified, with the commit and license recorded in the repo. The rest are workbooks calculated in Excel for the web. - Files saved by any app other than Excel are thrown out, because their saved values aren't Excel's. - A conformance tool loads every file, recalculates every formula with SharpCell and compares it with the value Excel saved: numbers within a relative tolerance of 1e-9, errors of the same kind (#N/A is not #VALUE!), text exactly. The agent can change the engine as much as it wants. It can't change what Excel answered. The result: 256 workbooks; of the 254 that contain formulas, 253 match Excel in every cell. In cells it's 80,044 of 80,046, but be careful with that number: half of those cells come from a single ROMAN/ARABIC stress-test file, so the per-workbook number is the honest one. Of the 490 functions, 423 are exercised by at least one Excel-calculated case. !The top of docs/compatibility.md The top of docs/compatibility.md. The full table covers all 490 functions and every test file The one workbook that doesn't fully match is BESSELJ. SharpCell computes the exact value, while Excel's approximation is off by up to about 5E-7: BESSELJ(10,1) is 0.0434722 in Excel and 0.0434727 exactly. It's the one place where you won't get Excel's number. It's also not the only deviation: 23 functions have one documented, for example the REGEX functions use .NET regular expressions, not PCRE2. All of them are listed with reasons in docs/compatibility.md. The same rule applies to bug reports: attach a small .xlsx that shows the problem and the value Excel calculates. Then the fix is checked against Excel's value, not against the agent's opinion. Where it's worse - No benchmarks yet. Comparing load, recalculation and save against EPPlus and ClosedXML is the next item on the roadmap - Links to other workbooks evaluate to #NAME? - Filter criteria are not evaluated: rows stay hidden the way the file left them - No CUBE, RTD or WEBSERVICE - No charts, pivot tables, pictures or shapes created (existing ones survive saving), no macros - No .xls, .xlsb or encrypted files - A Workbook is not thread-safe: one per thread - 0.x, so the API may change between minor versions until 1.0 And it's maintained by one person, me (well, me and Claude), in spare time. Bonus for those who work with agents The docs site has llms.txt and llms-full.txt, plus rules to paste into your agent's instructions, so your agent uses the actual API instead of inventing a plausible one. There is also SharpCell.Grid, a spreadsheet control for Avalonia and WPF on the same engine. You can try it right in the browser. Open your own .xlsx there, it's read on your machine and never uploaded. That's all :) The quickest way to try it is a real file from your own work: load it, call Recalculate(), compare a few cells with Excel. If something doesn't match, that file is exactly the bug report I need. P.S. Links: - GitHub: ifmelate/SharpCell - Docs: ifmelate.github.io - NuGet: SharpCell.Xlsx - Live demo in the browser: SharpCell.Grid demo - Test files: IronCalc If it saved you a license, a star on GitHub helps others find it.