Creating and Reading Excel Files: Formatted Reports with ClosedXML, Large Exports and the EPPlus License

Version note: The code in this post was built and run with .NET 10, ClosedXML 0.105.1, DocumentFormat.OpenXml 3.5.1 and SpreadCheetah 1.29.0. The EPPlus license details were checked against its official page on 15 November 2026.

Guess First

The back office of Bookstore, the online bookstore used as the shared example across this site's series, wants October's sales as an Excel file. Three questions:

First: how long does it take to produce the 100,000-row report with ClosedXML? On our first try the answer was 123 seconds. Who is to blame: ClosedXML, or a single line of the report?

Second: the Open XML SDK's OpenXmlWriter writes rows one by one in SAX style, that is, without building a DOM. Does memory stay flat at 100,000, 300,000 and 1 million rows?

Third: a book title comes from customer input and starts with =HYPERLINK("https://attacker.example.com","Dune"). Does ClosedXML write it to the file as a formula?

The answers: in the first case the culprits were two AdjustToContents() calls; removing them brought the time down to 3.9 seconds. In the second, memory does not stay flat: 56, 292 and 583 MB. In the third, the value is stored as text, not as a formula. The details follow.

Workbooks, Cell Types and Formats with ClosedXML

ClosedXML is an MIT-licensed library built on top of the Open XML SDK: it keeps the workbook in memory as an object model, you give the cells values, and SaveAs writes the file. The monthly report renderer is the Excel implementation of the IDocumentRenderer<MonthlySalesReport> from the earlier posts:

public Task RenderAsync(MonthlySalesReport model, Stream output, CancellationToken cancellationToken = default)
{
    using XLWorkbook workbook = new();
    IXLWorksheet sales = workbook.Worksheets.Add("Sales");
    // ... header row
    int row = 2;
    foreach (SalesRow item in model.Rows)
    {
        cancellationToken.ThrowIfCancellationRequested();
        sales.Cell(row, 1).Value = item.OrderId;
        sales.Cell(row, 2).Value = item.PlacedAtUtc;               // a real date, not a string
        SetText(sales.Cell(row, 3), item.CustomerEmail);
        sales.Cell(row, 4).Value = item.Isbn;                      // text: 13 digits must not become 9.78044E+12
        SetText(sales.Cell(row, 5), item.Title);
        sales.Cell(row, 6).Value = item.Quantity;
        sales.Cell(row, 7).Value = item.UnitPrice;
        sales.Cell(row, 8).FormulaA1 = $"F{row}*G{row}";
        row++;
    }

    IXLTable table = sales.Range(1, 1, row - 1, headers.Length).CreateTable("SalesTable");
    table.ShowTotalsRow = true;
    table.Field("Quantity").TotalsRowFunction = XLTotalsRowFunction.Sum;
    table.Field("Total").TotalsRowFunction = XLTotalsRowFunction.Sum;

    sales.Column(2).Style.DateFormat.Format = "yyyy-mm-dd hh:mm";
    sales.Column(4).Style.NumberFormat.Format = "@";
    sales.Columns(7, 8).Style.NumberFormat.Format = MoneyFormat;
    sales.SheetView.FreezeRows(1);
    // ... fixed column widths and the Summary sheet
    workbook.SaveAs(output);
    return Task.CompletedTask;
}

Whatever type you give a cell is the type written to the file. If you turn a date into a string with ToString(), Excel cannot sort it, filter it or use it in a formula; written as a DateTime, it is stored as an Excel date serial number and only the format decides how it looks. An ISBN is the opposite and must be text: a 13-digit number falls into scientific notation in Excel, and codes with a leading zero lose it. The money format is #,##0.00 "₺"; the decimal and thousands separators are decided by the Excel settings of whoever opens the file.

CreateTable turns the range into an Excel table: autofilter buttons on the headers, banded rows and a totals row. FreezeRows(1) keeps the header in place while scrolling. We read the file back with the same library and looked at the first data row:

A2  Number   1791                   format='' formula=''
B2  DateTime 10/1/2026 12:15:00 AM  format='yyyy-mm-dd hh:mm' formula=''
D2  Text     9780441172719          format='@' formula=''
G2  Number   450                    format='#,##0.00 "₺"' formula=''
H2  Blank    1350                   format='#,##0.00 "₺"' formula='F2*G2'

The type of H2 is Blank because the formula's result is not stored in the file; Excel calculates it when it opens the file, and ClosedXML finds 1350 with its own calculation engine when the value is read. The summary sheet has a SUMIFS formula for each book. The first version used table references (SalesTable[Total]) in the formula; ClosedXML's calculation engine evaluated those as 0, so we switched to plain ranges (Sales!$H$2:$H$2001) to be able to verify the file in tests. The summary we read back and the total calculated in code match:

Title                  Copies      Revenue
Dune                      630       283500
Emma                      600       144000
Middlemarch               665       212800
Nineteen Eighty-Four      686       178360
Pride and Prejudice       681       149820
The Hobbit                728       276640
rows=2000 sum(Total)=1245120.00

Sending Excel from the API: Content-Disposition and a Turkish File Name

The 2,000-row report takes a few hundred KB in memory; producing and sending it inside the request is fine:

// Excel straight into the response: ClosedXML writes synchronously, so render into memory first
app.MapGet("/api/reports/monthly-sales/{month}/excel", async (string month, IDocumentRenderer<MonthlySalesReport> renderer, CancellationToken ct) =>
{
    MonthlySalesReport report = BookstoreSampleData.MonthlySales(DateOnly.ParseExact(month + "-01", "yyyy-MM-dd"), rowCount: 2_000);
    MemoryStream buffer = new();
    await renderer.RenderAsync(report, buffer, ct);
    buffer.Position = 0;
    return Results.File(buffer, renderer.Format.ContentType, $"Aylık Satış Raporu {month}.xlsx");
});

HTTP/1.1 200 OK
Content-Length: 104746
Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Content-Disposition: attachment; filename="Ayl_k Sat__ Raporu 2026-10.xlsx"; filename*=UTF-8''Ayl%C4%B1k%20Sat%C4%B1%C5%9F%20Raporu%202026-10.xlsx

HTTP headers expect ASCII. That is why ASP.NET Core writes two parameters: filename, with the Turkish characters replaced by _ for old clients, and filename*, percent-encoded UTF-8 for browsers that support RFC 6266. Current browsers use filename*, and the file downloads as "Aylık Satış Raporu 2026-10.xlsx". If you write the header yourself, use the FileNameStar property of ContentDispositionHeaderValue; do not put the file name into the header by concatenating strings.

Why the MemoryStream? We also tried an endpoint that writes the workbook straight to Response.Body:

fail: Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware[1]
      An unhandled exception has occurred while executing the request.
      System.InvalidOperationException: Synchronous operations are disallowed. Call WriteAsync or set AllowSynchronousIO to true instead.
         at Microsoft.AspNetCore.Server.Kestrel.Core.Internal.Http.HttpResponseStream.Write(Byte[] buffer, Int32 offset, Int32 count)
         at ClosedXML.Excel.XLWorkbook.CopyStream(Stream input, Stream output)
         at ClosedXML.Excel.XLWorkbook.SaveAs(Stream stream, SaveOptions options)

Kestrel switches off synchronous I/O by default, while ClosedXML writes synchronously. Turning it on with AllowSynchronousIO = true means blocking a thread pool thread while it waits on the network. For a small file, render into memory and send it asynchronously; for a large one, a job and storage as in the next section is the right way. The Open XML SDK also wants a readable stream; when we gave it a FileStream that could be written but not read, we got The stream was not opened for reading. Response.Body meets neither requirement.

Reading Excel: a Fillable Institutional Order Template

Institutional customers like preparing bulk orders in Excel. We produce a template for Nevin's school: the ISBN, unit and quantity cells are open, the title and price formulas are locked, and the units and the catalog sit on a hidden "Lists" sheet:

IXLRange isbn = order.Range(FirstInputRow, 1, last, 1);
isbn.Style.NumberFormat.Format = "@";
isbn.CreateDataValidation().TextLength.EqualTo(13);

IXLRange unit = order.Range(FirstInputRow, 3, last, 3);
unit.CreateDataValidation().List(lists.Range(1, 1, units.Count, 1));

IXLRange quantity = order.Range(FirstInputRow, 4, last, 4);
quantity.CreateDataValidation().WholeNumber.Between(1, 10_000);

for (int r = FirstInputRow; r <= last; r++)
{
    // Looked up from the hidden list: the customer cannot change titles or prices
    order.Cell(r, 2).FormulaA1 = $"IF(A{r}=\"\",\"\",IFERROR(VLOOKUP(A{r},Lists!$C:$E,2,FALSE),\"Unknown ISBN\"))";
    // ... price and line total
}

order.Range(FirstInputRow, 1, last, 1).Style.Protection.SetLocked(false);
order.Range(FirstInputRow, 3, last, 4).Style.Protection.SetLocked(false);
order.Protect().AllowElement(XLSheetProtectionElements.FormatColumns);

Sheet protection is not a security measure but guidance: it stops the user from deleting a formula by accident. Data validation, too, only checks values entered in the Excel UI. Instead of filling the file by hand in Excel, we filled it with the same ClosedXML code, with two mistakes a customer might make: we wrote one ISBN as a number and typed one quantity as the text "20 copies". Validation stopped neither. Reading on the server side:

// Typed as a number in a General cell, an ISBN arrives as a double
string isbn = isbnCell.Value.Type switch
{
    XLDataType.Number => isbnCell.Value.GetNumber().ToString("0", System.Globalization.CultureInfo.InvariantCulture),
    _ => isbnCell.GetString().Trim(),
};

if (!row.Cell(4).TryGetValue(out int quantity) || quantity < 1)
{
    problems.Add($"Row {row.RowNumber()}: quantity '{row.Cell(4).GetString()}' is not a whole number");
    continue;
}

row 6: 9780441172719 x25 for Science Department
row 7: 9780547928227 x30 for Library
Row 8: quantity '20 copies' is not a whole number
B6='Dune' E6=450 F6=11250 protected=True A6 locked=False B6 locked=True

The ISBN in row seven was a number in the file and became text when read. Row eight landed in the problem list. We stop Excel reading here, at the API level. Row-by-row validation, returning errors to the user as a report and the full bulk import are the subject of the bulk import post in the Solution Patterns series.

Memory in Large Exports

We produced the same report with 10,000 and 100,000 rows, in separate processes and in a Release build. The table shows the managed heap peak we measured by sampling (relative to the starting point), the total allocation and the time:

The first row is the answer to the guessing question. The report code had two AdjustToContents() calls. We had limited the one on the Sales sheet to 500 rows, yet its time still grew fast with the row count. The one on the Summary sheet measured formula cells; to find the width of a formula cell you first have to calculate its value, and each SUMIFS scans 100,000 rows. Replacing both with fixed column widths brought the time down from 123 seconds to 3.9 seconds. Do not use AdjustToContents on large reports, or use it only on a small range without formulas.

ClosedXML keeps every cell as an object, so memory grows with the row count. OpenXmlWriter builds no DOM and uses seven times less memory at 100,000 rows:

// Rows are written with the Open XML SDK's SAX-style OpenXmlWriter, no DOM is built
using SpreadsheetDocument document = SpreadsheetDocument.Create(output, SpreadsheetDocumentType.Workbook);
// ... workbook part and styles
using (OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart))
{
    writer.WriteStartElement(new Worksheet());
    writer.WriteStartElement(new SheetData());
    foreach (SalesRow row in rows)
    {
        writer.WriteStartElement(new Row());
        WriteNumber(writer, row.OrderId);
        WriteNumber(writer, row.PlacedAtUtc.ToOADate(), DateStyle);
        WriteText(writer, row.CustomerEmail);
        // ...
        writer.WriteEndElement();
    }

    writer.WriteEndElement();
    writer.WriteEndElement();
}

But at 1 million rows the peak climbs to 583 MB, so memory is not flat. We passed an IEnumerable that produces rows one at a time and repeated the measurement with a smaller GC gen0 budget, and the result did not change. The reason lies in System.IO.Packaging: the worksheet part is kept in memory until it is written to the zip. Issue 1951 in the Open XML SDK repository reports the same behaviour with .NET 8 and SDK 3.3; a maintainer reproduced it on a .NET 10 preview and linked it to zip issue 1544 in dotnet/runtime, and issue 1951 is still open.

If you need flat memory, you need a library that compresses rows and writes them straight to the stream. MIT-licensed SpreadCheetah stayed at 8 MB for 1 million rows, as the table shows. The price is flexibility: you write rows in order, you cannot go back to a cell you have written, and its feature set is not as broad as ClosedXML's. Even so, you cannot attach it directly to Response.Body: when we tried it with an async-only stream, it called a synchronous Write while closing the zip archive. For a large export the path from the first post applies: produce it in a job, write it to IFileStore, and email the link. Streaming the rows from the database with IAsyncEnumerable instead of loading 100,000 rows into a List<SalesRow> at once is part of the same calculation; in the measurements in the table, the data is included in the baseline.

Formula Injection: the Excel Version of CSV Injection

In the CSV post of the I/O in .NET series we saw that a cell starting with = can be run as a formula when the CSV is opened. In XLSX things are a little different, because each cell's type is written in the file. When we give ClosedXML =HYPERLINK(...) through Value, the XML in the file is:

A1 DataType=Text HasFormula=False
<x:row r="1" spans="1:1"><x:c r="A1" s="0" t="s"><x:v>0</x:v></x:c></x:row><x:row r="2" spans="1:1"><x:c r="A2" s="1" t="s"><x:v>0</x:v></x:c></x:row>
<x:xf numFmtId="0" fontId="0" fillId="0" borderId="0" quotePrefix="1" applyNumberFormat="1" applyFill="1" applyBorder="0" applyAlignment="1" applyProtection="1"><x:protection locked="1" hidden="0" />

t="s" says the cell is a shared string, that is, text; there is no formula (<x:f>). The risk comes back from three places. First, putting user input into FormulaA1 through string interpolation; put only cell addresses you generated yourself into formulas such as the template's VLOOKUP. Second, the file being exported to CSV later. Third, a user opening and editing the cell. For the last two we give the quotePrefix style to text that starts with =, +, -, @, a tab or a carriage return:

// Stored as text either way; quotePrefix also tells Excel to keep it text if a user edits the cell
public static void SetText(IXLCell cell, string value)
{
    cell.Value = value;
    if (value.Length > 0 && value[0] is '=' or '+' or '-' or '@' or '\t' or '\r')
    {
        cell.Style.IncludeQuotePrefix = true;
    }
}

quotePrefix is the file's equivalent of typing ' before a value in Excel, and the apostrophe is not added to the cell content. That is why cell A2 above uses a separate style (s="1").

The EPPlus License Model and When to Choose It

EPPlus is one of the first libraries that come to mind for Excel in .NET, and its feature list is broader than ClosedXML's. According to its official site it supports all Excel 2019 chart types, pivot tables, a formula engine that can calculate 478 Excel functions, encryption and digital signatures, VBA projects, and HTML/JSON export. EPPlus 9 brings PDF export as a preview.

Its license, as we saw in the first post, is PolyForm Noncommercial 1.0.0: personal, hobby and noncommercial organisation use is free, and use in a commercial setting requires a commercial license. Commercial licenses are sold per developer, either as a subscription or as a perpetual license with a fixed update period. In EPPlus 8, if you do not set the license in code, in configuration or in the EPPlusLicense environment variable, you get a LicenseNotSetException on first use. For personal use, the SetNonCommercialPersonal call writes license information into the package's Comment and Tag fields and adds a license file to the package.

For a commercial application like Bookstore, choosing EPPlus means buying a license for each developer. If your reports need charts or pivot tables, if you have to calculate Excel formula results on the server, or if you have to encrypt files, that cost may be reasonable. For formatted tables, formulas and validation like our report and template, ClosedXML is enough. For very large exports both keep a DOM, so a streaming writer such as SpreadCheetah is needed as well. The license decision also hits the nuget-license check from the first post; adding EPPlus requires a deliberate override.

One note: ClosedXML itself is MIT, but its transitive dependencies include SixLabors.Fonts, and the allowed range is 1.0.0 to 3.0.0. In our project NuGet picked the lowest version, the Apache-2.0 licensed 1.0.0. If another package pulls it to 2.x, the license becomes the Six Labors Split License. Even though the Split License counts transitive use under Apache 2.0, it is good for the license check in CI to catch this.

In Production

Excel's limit is 1,048,576 rows per sheet. Either split an export that approaches a million rows across several sheets or send it to CSV; nobody is going to read those rows by eye in Excel.

Move large exports to a job and make them cancellable. ClosedXML's SaveAs does not accept cancellation; our loop calls ThrowIfCancellationRequested per row, but the save phase runs to the end.

Write numbers, dates and currencies independently of culture. Give the cell a decimal and a DateTime and leave the display to the number format. Excel sees an amount written with ToString("N2") as text and cannot add it up.

An uploaded Excel file is also an upload. For size limits, content type checks and the zip bomb risk, see the file upload post in the ASP.NET Core Web API series and the Kestrel limits post in the ASP.NET Core Security series. ClosedXML loads the whole file into memory when it opens it; a 50 MB order template should be a reason to reject.

Control who can download the report file. The monthly sales report contains customer email addresses; a URL that anyone who knows the link can download is a data leak.

Common Pitfalls

Writing dates as strings. It looks the same, but Excel cannot sort, filter or group it by month.

Writing an ISBN, phone number or order code as a number. Leading zeros disappear and 13 digits fall into scientific notation.

Loading 100,000 rows into memory at once and producing them with ClosedXML inside the request. In our measurement that is a 368 MB heap and 4 seconds; five requests at the same time strain the server.

Calling AdjustToContents on a large sheet. In our report it made the run more than 30 times longer.

Putting user input into a formula, or not marking text that starts like a formula. When the file is converted to CSV or the cell is edited, the text can turn into a formula.

A Small Experiment

★☆☆ In MonthlySalesExcelRenderer, change the ISBN line to sales.Cell(row, 4).Value = long.Parse(item.Isbn);, produce the file, and look at the type and format of D2 with ExcelLab's report command. Then open the file in Excel or LibreOffice and check how the column looks.

★★☆ Run ExcelLab's large command with 300,000 rows for the three methods and add them to the table. Whose heap peak grows in proportion to the row count, and whose stays flat?

★★★ Extend InstitutionalOrderTemplate.Read to compare the rows it reads with the catalog and report unknown ISBNs, the 20-copy minimum and the same ISBN repeated on two rows. Write an endpoint that colours the faulty cells red and sends the file back to the customer.

Excel is done. In the next post we move to PDF: we produce Bookstore's order invoice with QuestPDF, embed a font for Turkish characters and look at how the Community License is set in code.