Read and write Excel files in C# without Excel

WorkbookEditor reads and writes XLSX files from .NET with no Excel installed. This guide covers values, an unknown upload, formulas and formatting, and where the formatting stops.

byte[] xlsx = WorkbookEditor.Create("Sales", new object?[][]
{
    new object?[] { "Region", "Total" },
    new object?[] { "North",  1200 },
    new object?[] { "South",  950 },
});

string before = WorkbookEditor.ReadCell(xlsx, "Sales", "B2");
byte[] updated = WorkbookEditor.SetCell(xlsx, "Sales", "B2", 1500);
string after = WorkbookEditor.ReadCell(updated, "Sales", "B2");

Numbers stay numbers

WorkbookEditor covers XLSX. No Excel is needed.
Cell values are object?. Numbers are written as numbers and strings as strings, so a column of totals can be summed in Excel.
ReadCell returns the cell's value as text, not as Excel displays it.
A cell holding 1200 under a #,##0.00 number format reads back as 1200, not 1,200.00.

Read a file you were handed

You do not need to know the shape of the workbook in advance.
SheetNames tells you what is in the file, and ReadSheet returns the used range as rows of strings.
Together they are enough to walk an upload you have never seen.

Formulas

A cell holding an XlsxFormula is written as a formula, not as text that starts with =.
Write it without the leading =, as in XlsxFormula.From("SUM(Q1!B2:B4)"). The writer adds the =.
Read back through DocToolkit, the formula gives its computed value.
A reader that only reads cached values sees an empty cell until Excel has opened and saved the file.

Make a sheet look like a report

XlsxFormat.Report is the usual preset: bold header, frozen header and auto-fit.
Format is a separate call that takes a workbook and a sheet name, so it works on a sheet that already exists.
Auto-fit fits the column to its content. Against short values that is narrower than Excel's 8.43-character default.

What it will not do

The formatting vocabulary is closed: six rule conditions, five validation kinds and four highlights. For arbitrary fonts, borders or color scales, use ClosedXML directly.
WithTable and WithAutoFilter cannot target overlapping ranges. Applying both over the same cells throws DocumentConversionException.
WithTable does not make AppendRows keep the table current. The new row lands next to the table, not inside it.

Install

dotnet add package Ank.DocToolkit

Read it in the DocToolkit docs NuGet GitHub

More guides

Everything DocToolkit converts