Tutorial: Generate, export, import and fill Excel files
Four complete examples: build a formatted workbook from nothing, export a list of your own objects with a small reusable helper, import a workbook a person filled in, checking every cell before trusting it, and fill the {{placeholders}} of a template somebody designed in Excel. No Excel installation and no other dependency.
dotnet add package DocWright.Formats.Xlsx
You will learn to:
- generate a workbook with a merged title, styles, number formats, frozen rows and print titles
- export any list to a worksheet with one line per column, as typed cells that sum and sort in Excel
- import rows by header name, read dates and formula results, and report bad cells instead of importing them
- fill
{{placeholders}}in a template so that numbers stay numbers and its formulas keep working - pick the right type:
XlsxWorkbookfor a workbook you build,XlsxDocumentfor one that already exists
| You want to | Use | Example |
|---|---|---|
| Build a new workbook from your data | XlsxWorkbook + XlsxWriter |
Generate, Export |
| Read a workbook somebody gives you | XlsxDocument.Open |
Import |
Fill a template's {{placeholders}}, or find and replace text |
XlsxDocument.ReplacePlaceholders, ReplaceText |
Fill a template |
| Change particular cells of an existing workbook and keep its charts, formulas and formatting | XlsxDocument and its sheets |
Open and edit an existing workbook |
Export a report (.rdl) to Excel |
ReportXlsxExporter |
Run an SSRS report |
1. Generate a workbook
A workbook is sheets, a sheet is rows, a row is cells. Each cell has a typed value and, optionally, a style. Styles are plain values: build one, reuse it on as many cells as you like, and the file stores each distinct style once.
using DocWright.Core.Primitives;
using DocWright.Formats.Xlsx;
var navy = ColorRgb.FromRgb(0x1E, 0x3A, 0x8A);
XlsxCellStyle title = XlsxCellStyle.Default.With(fontSizePoints: 16m, bold: true, fontColor: navy);
XlsxCellStyle heading = XlsxCellStyle.Default.With(bold: true, bottomBorder: new XlsxBorder(XlsxBorderStyle.Medium, navy));
XlsxCellStyle money = XlsxCellStyle.Default.With(numberFormat: "#,##0.00");
XlsxCellStyle percent = XlsxCellStyle.Default.With(numberFormat: "0.0%");
XlsxCellStyle total = money.With(bold: true, topBorder: new XlsxBorder(XlsxBorderStyle.Thin, navy));
var summary = new XlsxWorksheet("Summary");
summary.Columns.Add(new XlsxColumn(1, 1, 22m)); // column A, width in characters
summary.Columns.Add(new XlsxColumn(2, 4, 14m)); // columns B to D
// A title across the table, in a taller row.
var titleRow = new XlsxRow(1) { HeightPoints = 28m };
titleRow.Cells.Add(new XlsxCell(1, XlsxCellValue.Text("Revenue by region, Q1 2026"), title));
summary.Rows.Add(titleRow);
summary.MergedRanges.Add(new XlsxCellRange(1, 1, 1, 4));
var headings = new XlsxRow(3);
string[] names = ["Region", "Revenue", "Target", "Of target"];
for (int c = 0; c < names.Length; c++)
{
headings.Cells.Add(new XlsxCell(c + 1, XlsxCellValue.Text(names[c]), heading));
}
summary.Rows.Add(headings);
(string Region, decimal Revenue, decimal Target)[] regions =
[
("North", 412_900.50m, 400_000m),
("South", 268_114.00m, 300_000m),
("East", 351_260.75m, 325_000m),
];
int r = 4;
foreach ((string region, decimal revenue, decimal target) in regions)
{
var row = new XlsxRow(r++);
row.Cells.Add(new XlsxCell(1, XlsxCellValue.Text(region)));
row.Cells.Add(new XlsxCell(2, XlsxCellValue.Number(revenue), money)); // numbers, so they sum in Excel
row.Cells.Add(new XlsxCell(3, XlsxCellValue.Number(target), money));
row.Cells.Add(new XlsxCell(4, XlsxCellValue.Number(revenue / target), percent));
summary.Rows.Add(row);
}
// The model has no formulas, so a total is computed here and written as a value.
var totals = new XlsxRow(r);
totals.Cells.Add(new XlsxCell(1, XlsxCellValue.Text("Total"), total.With(numberFormat: string.Empty)));
totals.Cells.Add(new XlsxCell(2, XlsxCellValue.Number(regions.Sum(x => x.Revenue)), total));
totals.Cells.Add(new XlsxCell(3, XlsxCellValue.Number(regions.Sum(x => x.Target)), total));
summary.Rows.Add(totals);
summary.FrozenRows = 3; // title and headings stay while scrolling
summary.PageSetup.Landscape = true;
summary.PageSetup.PrintTitleRows = 3; // and repeat on every printed page
var notes = new XlsxWorksheet("Notes");
notes.Columns.Add(new XlsxColumn(1, 1, 60m));
var note = new XlsxRow(1);
note.Cells.Add(new XlsxCell(1, XlsxCellValue.Text("Figures exclude tax. Targets were set in January."), XlsxCellStyle.Default.With(wrapText: true)));
notes.Rows.Add(note);
var book = new XlsxWorkbook();
book.Worksheets.Add(summary);
book.Worksheets.Add(notes);
using (FileStream output = File.Create("revenue-q1.xlsx"))
{
XlsxWriter.Write(book, output);
}
revenue-q1.xlsx: 2 sheets, 6 rows on Summary, 3,317 bytes
Download the result: revenue-q1.xlsx.
Things to notice:
- Values are typed.
XlsxCellValue.Number(412900.50m)with the format#,##0.00shows412,900.50and still sums. The same text typed as a string would show the same and sum to zero. - Totals are computed in your code. A workbook you build has no formulas. To keep live formulas, start from a template instead and fill it in.
- Widths are in characters, heights in points.
- The output is deterministic. The same workbook always produces the same bytes: no timestamps, no generated ids.
2. Export your data
Most exports are the same shape: a header row, then one row per item. This helper is all of it, and it is yours to change:
using DocWright.Core;
using DocWright.Core.Primitives;
using DocWright.Formats.Xlsx;
/// <summary>One column of an export: its heading, its width, how a row gives its value, and how the value looks.</summary>
internal sealed class ExcelColumn<T>(string header, decimal width, Func<T, XlsxCellValue> value, XlsxCellStyle? style = null)
{
public string Header { get; } = header;
public decimal Width { get; } = width;
public Func<T, XlsxCellValue> Value { get; } = value;
public XlsxCellStyle? Style { get; } = style;
}
internal static class ExcelExporter
{
private static readonly XlsxCellStyle Heading = XlsxCellStyle.Default.With(
bold: true,
fontColor: ColorRgb.FromRgb(255, 255, 255),
backgroundColor: ColorRgb.FromRgb(0x1E, 0x3A, 0x8A));
/// <summary>Turns any list into a worksheet: a styled header row, one row per item, a frozen header and a filter.</summary>
public static XlsxWorksheet ToWorksheet<T>(string name, IEnumerable<T> items, IReadOnlyList<ExcelColumn<T>> columns)
{
// Sheet names have rules (31 characters, no : \ / ? * [ ]); Normalize makes any text legal.
var sheet = new XlsxWorksheet(XlsxSheetName.Normalize(name, "Sheet1"));
var header = new XlsxRow(1);
for (int c = 0; c < columns.Count; c++)
{
sheet.Columns.Add(new XlsxColumn(c + 1, c + 1, columns[c].Width));
header.Cells.Add(new XlsxCell(c + 1, XlsxCellValue.Text(columns[c].Header), Heading));
}
sheet.Rows.Add(header);
int next = 2;
foreach (T item in items)
{
var row = new XlsxRow(next++);
for (int c = 0; c < columns.Count; c++)
{
row.Cells.Add(new XlsxCell(c + 1, columns[c].Value(item), columns[c].Style));
}
sheet.Rows.Add(row);
}
sheet.FrozenRows = 1;
if (next > 2)
{
sheet.AutoFilter = new XlsxCellRange(1, 1, next - 1, columns.Count);
}
return sheet;
}
}
With it, an export is one line per column:
using DocWright.Formats.Xlsx;
// Your data, whatever it is: rows from a database, a query result, an API response.
List<SalesOrder> orders =
[
new(1001, "Contoso Ltd", new DateTime(2026, 1, 14), 1_250.00m, Paid: true),
new(1002, "Fabrikam & Sons", new DateTime(2026, 1, 20), 86.40m, Paid: false),
new(1003, "Northwind <Traders>", new DateTime(2026, 2, 3), 12_990.95m, Paid: true),
new(1004, "Adventure Works", new DateTime(2026, 2, 3, 16, 30, 0), 430.00m, Paid: false),
];
XlsxCellStyle date = XlsxCellStyle.Default.With(numberFormat: "yyyy-mm-dd");
XlsxCellStyle money = XlsxCellStyle.Default.With(numberFormat: "#,##0.00");
// One line per column: heading, width, value, format. The value is typed, never pre-formatted text.
XlsxWorksheet sheet = ExcelExporter.ToWorksheet("Orders", orders,
[
new ExcelColumn<SalesOrder>("Order", 10m, o => XlsxCellValue.Number((decimal)o.Number)),
new ExcelColumn<SalesOrder>("Customer", 28m, o => XlsxCellValue.Text(o.Customer)),
new ExcelColumn<SalesOrder>("Placed", 12m, o => XlsxCellValue.Date(o.Placed), date),
new ExcelColumn<SalesOrder>("Amount", 14m, o => XlsxCellValue.Number(o.Amount), money),
new ExcelColumn<SalesOrder>("Paid", 8m, o => XlsxCellValue.Boolean(o.Paid)),
]);
var book = new XlsxWorkbook();
book.Worksheets.Add(sheet);
// To a file here; to an HTTP response body or a blob, write to that stream instead.
using (FileStream output = File.Create("orders.xlsx"))
{
XlsxWriter.Write(book, output, new XlsxWriteSettings { MaxOutputBytes = 50 * 1024 * 1024 });
}
orders.xlsx: 4 orders, 2,685 bytes
C4 reads back as Number, IsDate True, 2026-02-03
Download the result: orders.xlsx.
- Dates:
XlsxCellValue.Datestores the date as Excel does. Give the column a date number format, or Excel shows the serial number. - Text needs no escaping.
Northwind <Traders>andFabrikam & Sonsare written as they are. - From a web API: write to the response stream instead of a file, with the content type
application/vnd.openxmlformats-officedocument.spreadsheetml.sheet. MaxOutputBytescaps the file when its size is decided by data you do not control.
3. Import a workbook
XlsxDocument.Open reads a workbook written by Excel or LibreOffice. Here it reads a price list: a header row, then one line per item, with a Total column that is a formula.
using DocWright.Core;
using DocWright.Formats.Xlsx;
XlsxDocument book;
using (FileStream input = File.OpenRead("price-list.xlsx"))
{
// An upload is untrusted. Limits are on by default; this tightens the size.
book = XlsxDocument.Open(input, new XlsxReadSettings { Limits = new ResourceLimits { MaxInputBytes = 10 * 1024 * 1024 } });
}
XlsxDocumentSheet sheet = book.FindSheet("Data") ?? book.Sheets[0];
XlsxCellRange used = sheet.Dimension!.Value;
// The header row says which column holds what, so the import does not depend on column order.
var columnOf = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
for (int c = used.FirstColumn; c <= used.LastColumn; c++)
{
XlsxCellValue heading = sheet.GetCell(1, c).Value;
if (heading.Kind == XlsxCellValueKind.Text)
{
columnOf[heading.TextValue.Trim()] = c;
}
}
var lines = new List<PriceLine>();
var problems = new List<string>();
for (int r = 2; r <= used.LastRow; r++)
{
XlsxDocumentCell item = sheet.GetCell(r, columnOf["Item"]);
if (item.Value.Kind == XlsxCellValueKind.Blank)
{
break; // the list ends at the first row with no item
}
XlsxDocumentCell quantity = sheet.GetCell(r, columnOf["Qty"]);
XlsxDocumentCell price = sheet.GetCell(r, columnOf["Price"]);
XlsxDocumentCell total = sheet.GetCell(r, columnOf["Total"]); // a formula: its stored result is read
XlsxDocumentCell note = sheet.GetCell(r, columnOf["Note"]);
// A cell is whatever the person typed, so check its kind before trusting it.
if (quantity.Value.Kind != XlsxCellValueKind.Number || price.Value.Kind != XlsxCellValueKind.Number)
{
problems.Add($"{quantity.Reference}: expected numbers for quantity and price");
continue;
}
if (note.Value.Kind == XlsxCellValueKind.Error)
{
problems.Add($"{note.Reference}: the formula ={note.Formula} gives {note.Value.TextValue}");
}
lines.Add(new PriceLine(
item.Value.TextValue,
(int)quantity.Value.NumberValue,
price.Value.NumberValue,
Math.Round(total.Value.NumberValue, 2)));
}
foreach (PriceLine line in lines)
{
Console.WriteLine($"{line.Item,-8} {line.Quantity,3} x {line.Price,5:0.00} = {line.Total,6:0.00}");
}
foreach (string problem in problems)
{
Console.WriteLine("problem: " + problem);
}
Apple 3 x 1.25 = 3.75
Pear 6 x 1.35 = 8.10
Apple 9 x 1.45 = 13.05
Quince 12 x 1.55 = 18.60
Fig 15 x 1.65 = 24.75
problem: E4: the formula =1/0 gives #DIV/0!
problem: E5: the formula =NA() gives #N/A
The workbook imported here: price-list.xlsx.
- Find columns by their heading, not their position. People reorder columns.
- Check a cell's
Kindbefore you use it. A cell holds whatever was typed: text where you expect a number, an error where a formula failed. - A formula cell gives its stored result.
Totalis=B2*C2in the file;Valueis the number Excel calculated when the file was last saved, andFormulais the text. DocWright does not calculate. - Dates are numbers with a date format: check
cell.IsDateand callcell.ToDateTime(). - Large sheets:
sheet.Cellsstreams the sheet once without holding it;GetCellbuilds an index on first use. - Uploads are untrusted input. Limits are on by default, and a damaged or hostile file throws
InvalidDocumentExceptionorResourceLimitExceededException(bothDocWrightException)..xls,.xlsband password-protected workbooks are not read.
4. Fill a template
The template is an ordinary workbook made in Excel: formatting, formulas, and {{Name}} typed where a value goes. Filling it is the same call as for a Word template.
Download the template: invoice-template.xlsx. Cells B6, B7 and C8 hold only a marker; the Total column is formulas.
using DocWright.Formats.Xlsx;
XlsxDocument book;
using (FileStream input = File.OpenRead("invoice-template.xlsx"))
{
book = XlsxDocument.Open(input);
}
// What the template asks for.
Console.WriteLine("placeholders: " + string.Join(", ", book.FindPlaceholderNames()));
XlsxPlaceholderResult filled = book.ReplacePlaceholders(new Dictionary<string, object?>
{
["Number"] = "INV-0042",
["Customer"] = "Contoso Ltd",
["Date"] = new DateTime(2026, 3, 1), // a cell of its own: becomes a date
["Days"] = 4, // a cell of its own: becomes a number the formulas use
["Workshops"] = 2,
["Travel"] = 310.50m,
["Author"] = "Ada Lovelace",
});
Console.WriteLine($"filled {filled.ReplacedCount}; no value for: {(filled.UnresolvedNames.Count == 0 ? "(nothing)" : string.Join(", ", filled.UnresolvedNames))}");
// Ordinary find and replace works the same way as in a Word document.
XlsxReplaceResult final = book.ReplaceRegex(@" ?\(Draft\)|\. Draft until approved\.", string.Empty);
Console.WriteLine($"'draft' removed in {final.ReplacedCount} place(s)");
XlsxDocumentSheet invoice = book.Sheets[0];
Console.WriteLine($"A1: {invoice.GetCell("A1").Value.TextValue}");
Console.WriteLine($"B6: {invoice.GetCell("B6").Value.Kind} {invoice.GetCell("B6").Value.NumberValue}, D6 is still ={invoice.GetCell("D6").Formula}");
Console.WriteLine($"A3: {invoice.GetCell("A3").ToDateTime():yyyy-MM-dd}, shown through {invoice.GetCell("A3").NumberFormat}");
using (FileStream output = File.Create("invoice-0042.xlsx"))
{
book.Save(output); // Excel calculates the totals when it opens the file
}
placeholders: Author, Customer, Date, Days, Number, Travel, Workshops
filled 7; no value for: (nothing)
'draft' removed in 2 place(s)
A1: Invoice INV-0042
B6: Number 4, D6 is still =B6*C6
A3: 2026-03-01, shown through d mmm yyyy
Download the result and open it in Excel: invoice-0042.xlsx. The totals are calculated there (4 × 850 + 2 × 1,200 + 310.50 = 6,110.50).
- A marker that is the whole cell takes the type of its value.
{{Days}}filled with4is a number, so=B6*C6works.Prepared by {{ Author }}is text with the value written in. - A date in a cell of its own becomes a date. The cell keeps the template's date format; if it had none it gets
yyyy-mm-dd. - Check
UnresolvedNames. A marker with no value is left as it is by default;XlsxPlaceholderOptions.Unresolvedcan clear it or throw. - Formulas are not looked at and not calculated. The saved workbook asks Excel to recalculate when it opens.
- Find and replace (
FindText,FindRegex,FindWildcards,ReplaceText,ReplaceRegex) take the same options as for a Word document. A replace never throws for a cell it cannot change (a formula's result, a table's header): it lists it inSkippedwith the reason.
Where next
- Open and edit an existing workbook: change cells, widths, merged ranges and sheets of a workbook, and save it with its charts, pivot tables and macros intact.
- Write Excel files: everything the writer supports.