Update, 14 October: the library is now published as **SheetStream.NET* (dotnet add package SheetStream.NET); the repository moved to github.com/P4Software/SheetStream.NET. Version 0.4.0 added editing of small workbooks, pivot tables, cached formula results, async reading, Native AOT support and a Roslyn analyzer; the million-row benchmark is in the repo.*
Most .NET Excel libraries build the whole workbook in memory and save it at the end. That's convenient, and it's why a 100,000-row export can allocate hundreds of megabytes to over a gigabyte. Here's how we built SheetStream to write the same file with about 27 MB allocated, and what that design costs.
An .xlsx is a zip of XML
Open any .xlsx with a zip tool and you'll find xl/worksheets/sheet1.xml, xl/styles.xml, xl/sharedStrings.xml and a few relationship files. The worksheet is a <sheetData> element full of <row> and <c> (cell) elements.
So writing a sheet is just writing XML into a zip entry, and System.IO.Compression plus XmlWriter can do that as a stream:
var entry = archive.CreateEntry("xl/worksheets/sheet1.xml");
using var writer = XmlWriter.Create(new BufferedStream(entry.Open(), 64 * 1024), settings);
// <worksheet><sheetData><row r="1"><c r="A1" t="s"><v>0</v></c>...
The catch: Excel's schema is strict about order
CT_Worksheet is an XML Schema sequence. Column widths (<cols>) must come before <sheetData>, and merged cells, conditional formatting, validations, hyperlinks and tables come after it, in a fixed order. Get the order wrong and Excel shows "We found a problem with some content".
For a streaming writer this means:
- Layout before data. Column widths and frozen panes have to be known before the first row. SheetStream throws a clear exception if you set them late.
-
Everything else at the end. Features that live after
sheetDataare collected while you write and emitted in schema order when the sheet is sealed.
Shared strings and styles live in other parts
Cells don't contain text; they point into sharedStrings.xml. Styles are indexes into styles.xml, which itself has five interlocking tables (number formats, fonts, fills, borders, cell formats). Both parts are written last, after every sheet, so the writer keeps two small in-memory dictionaries and nothing else.
AutoFit without font metrics or memory
AutoFit needs to see every value before writing <cols>, which comes first. Our trick: when AutoFit is on, rows spill to a temporary file while their widths are measured, then the header is written and the rows are copied back in 64 KB chunks. Memory stays flat; disk use roughly equals the uncompressed sheet.
Filling templates without a DOM
Designers want to build the report in Excel. SheetStream streams the template's XML around your rows, continues the template's style and string numbering, stretches tables and conditional formats over the new rows, and drops calcChain.xml (a stale calculation chain is a classic repair trigger) so Excel recalculates on open.
Results
100,000 rows × 10 formatted columns, BenchmarkDotNet, .NET 8:
| Library | Time | Allocated |
|---|---|---|
| SheetStream (fastest compression) | 534 ms | 27 MB |
| MiniExcel | 596 ms | 343 MB |
| EPPlus 4.5.3.3 | 2,287 ms | 390 MB |
| ClosedXML | 3,103 ms | 1,104 MB |
With default (best) compression SheetStream takes 1,013 ms, slower than MiniExcel, in exchange for smaller files. The suite is in the repo if you want to check our work.
What streaming costs
No formula engine, no pivot tables, and no "open any workbook, change a cell, save". Rows go in ascending order, one sheet at a time. If you need random access on a small sheet, there's an opt-in Cells[row, col] mode that buffers just that sheet.
SheetStream.NET is MIT licensed: https://github.com/P4Software/SheetStream.NET
dotnet add package SheetStream.NET
Top comments (0)