Open and edit an existing Excel workbook
XlsxDocument opens a workbook somebody else made, tells you what is in its sheets, lets you change cells, and saves it back with everything else as it was read. Charts, pivot tables, tables, comments and macros survive, because nothing is rebuilt.
dotnet add package DocWright.Formats.Xlsx
It reads .xlsx and .xlsm written by Excel or LibreOffice. This is a different type from the XlsxWorkbook you build and write yourself, and the two do not convert into each other: a workbook loaded into the writer's model would lose everything that model has no member for.
Read sheets and cells
using DocWright.Formats.Xlsx;
XlsxDocument book;
using (FileStream input = File.OpenRead("sales-book.xlsx"))
{
book = XlsxDocument.Open(input); // read whole here; the stream is not needed again
}
foreach (XlsxDocumentSheet each in book.Sheets)
{
Console.WriteLine($"sheet {each.Name}: {each.Kind}, {each.Visibility}");
}
XlsxDocumentSheet sales = book.FindSheet("Sales")!;
XlsxDocumentCell total = sales.GetCell("E2"); // or GetCell(2, 5): row, then column
Console.WriteLine($"E2 formula: {total.Formula}");
Console.WriteLine($"E2 value: {total.Value.NumberValue} (the result stored in the file)");
// Cells streams the sheet once, in order, holding only the cell being read.
decimal sum = 0m;
foreach (XlsxDocumentCell cell in sales.Cells)
{
if (cell.Column == 3 && cell.Value.Kind == XlsxCellValueKind.Number)
{
sum += cell.Value.NumberValue;
}
}
Console.WriteLine($"column C sums to {sum}");
sheet Pivot: Worksheet, Visible
sheet Sales: Worksheet, Visible
E2 formula: SUM(SalesTable[Amount])
E2 value: 855 (the result stored in the file)
column C sums to 855
parts before: 28, after: 28
xl/charts/chart1.xml: kept
xl/pivotTables/pivotTable1.xml: kept
xl/tables/table1.xml: kept
xl/comments1.xml: kept
A10 holds a Number: 45306, shown through yyyy\-mm\-dd
IsDate: True, date: 2024-01-15
D2: B2*C2, D4: B4*C4
B2 is now 10; D2 still reads 3.75
parts that differ from the original: [Content_Types].xml, xl/workbook.xml, xl/_rels/workbook.xml.rels, xl/worksheets/sheet1.xml, xl/styles.xml, xl/calcChain.xml (removed)
column A is 10.42578125 wide, row 12 is 30 points
Data=Visible, Other Sheet=Visible, Deep=VeryHidden, Notes=Visible
GetCellreturns a blank cell where the sheet stores nothing. Its first call on a sheet reads the sheet once and keeps an index of its cells.Cellsholds nothing but the cell being read, so it is the way to go through a large sheet once. It returns only the cells the sheet stores.Dimensionis the smallest range that holds every stored cell, measured from the cells rather than taken from what the file says about itself.
What a cell tells you
| Member | What it is |
|---|---|
Value |
An XlsxCellValue: Text, Number, Boolean, Error or Blank. A number is the decimal the file stores, digit for digit. |
Formula |
The formula without its =, or null. |
IsDate, ToDateTime() |
Whether the number format shows the value as a date or time, and the date it stands for. |
NumberFormat |
The format code as stored (null for General). Excel's built-in formats are given as their codes. |
Style |
An XlsxCellStyle: font, fill, borders, alignment, with theme and palette colours resolved to the RGB Excel shows. |
Reference, Row, Column |
Where the cell is. |
Important
Formulas are not calculated. For a formula cell, Value is the result stored in the file from the last time Excel calculated it. DocWright reads the formula's text and never evaluates it.
Dates and filled formulas
A date in Excel is a number whose format displays it as a date. ToDateTime() converts it in the workbook's own date system (Uses1904DateSystem), including the 29 February 1900 that Excel counts. Don't convert the serial yourself.
A formula filled down a column is stored once in the file. Each cell gives it as Excel shows it in that cell, with its references moved; an array formula is given by every cell of its range.
using DocWright.Formats.Xlsx;
XlsxDocument prices;
using (FileStream input = File.OpenRead("price-list.xlsx"))
{
prices = XlsxDocument.Open(input);
}
XlsxDocumentSheet data = prices.Sheets[0];
XlsxDocumentCell day = data.GetCell("A10");
Console.WriteLine($"A10 holds a {day.Value.Kind}: {day.Value.NumberValue}, shown through {day.NumberFormat}");
Console.WriteLine($"IsDate: {day.IsDate}, date: {day.ToDateTime():yyyy-MM-dd}");
// A formula filled down a column is stored once; each cell gives it as Excel shows it there.
Console.WriteLine($"D2: {data.GetCell("D2").Formula}, D4: {data.GetCell("D4").Formula}");
Save it back, with nothing lost
Save writes each part of the package with the bytes it was read with, in the order it was read. The sample workbook holds a table, a chart, a pivot table and a comment, none of which DocWright models:
using System.IO.Compression;
using DocWright.Formats.Xlsx;
using (FileStream output = File.Create("sales-book-copy.xlsx"))
{
book.Save(output);
}
// Every part of the package is still there, whether or not DocWright knows what it is.
using ZipArchive before = ZipFile.OpenRead("sales-book.xlsx");
using ZipArchive after = ZipFile.OpenRead("sales-book-copy.xlsx");
Console.WriteLine($"parts before: {before.Entries.Count}, after: {after.Entries.Count}");
foreach (string part in new[] { "xl/charts/chart1.xml", "xl/pivotTables/pivotTable1.xml", "xl/tables/table1.xml", "xl/comments1.xml" })
{
Console.WriteLine($" {part}: {(after.GetEntry(part) is null ? "missing" : "kept")}");
}
Download the saved copy and open it in Excel: sales-book-copy.xlsx.
Only the ZIP container around the parts is written afresh, and deterministically: saving twice gives the same bytes.
Change cells
The same sheet object changes cells in place. Each method takes a one-based row and column, or a reference such as "B4", and the cell need not exist.
using DocWright.Formats.Xlsx;
data.SetValue("B2", XlsxCellValue.Number(10m)); // the cell keeps its format
data.SetValue("A2", XlsxCellValue.Text("Banana"));
data.SetFormula("H1", "=SUM(B2:B6)"); // a cell that did not exist
data.SetStyle("C2", data.GetCell("C2").Style.With(bold: true, numberFormat: "0.000"));
data.Clear("C9"); // emptied, kept for its format
data.Remove("A12"); // gone
// Nothing is calculated: D2 is =B2*C2 and still holds the result the file had.
Console.WriteLine($"B2 is now {data.GetCell("B2").Value.NumberValue}; D2 still reads {data.GetCell("D2").Value.NumberValue}");
using (FileStream output = File.Create("price-list-filled.xlsx"))
{
prices.Save(output); // the workbook asks Excel to recalculate when it opens
}
Download the result and open it in Excel: price-list-filled.xlsx. D2 shows 12.5 there, because Excel recalculates on opening.
| Method | Does |
|---|---|
SetValue |
Sets the value. A formula the cell had goes; its format stays. |
SetFormula |
Sets the formula, with or without the leading =, as Excel stores it: English function names, commas between arguments. |
SetStyle |
Sets the look and keeps the value. Start from sheet.GetCell(r, c).Style.With(...) to change one thing. |
Clear |
Empties the cell and keeps it for its format. |
Remove |
Takes the cell out altogether. Nothing moves. |
Important
Nothing is calculated. After SetValue, a formula that depends on the cell still reads the result the file had, in memory and in the saved file. The saved workbook asks Excel for a full recalculation, so Excel shows the right numbers as soon as it opens it. A formula you set has no value until then.
The saved file differs from the original only where the edit needs it to: the edited sheet (its own XML, with the edited cells replaced and every other character as it was), the styles when you used a look the workbook had no format for, and the workbook's calculation chain, which is removed because Excel rejects a stale one. Every other part is byte for byte what it was.
Two kinds of cell cannot have their content changed, because Excel would ignore or reject it, and SetValue throws UnsupportedFeatureException: a cell of an array formula entered over several cells, and a table's header or totals cell. Their look can still be set, and a table's body cells are ordinary cells.
Widths, heights, merged ranges and sheets
using DocWright.Formats.Xlsx;
Console.WriteLine($"column A is {data.GetColumnWidth(1)} wide, row 12 is {data.GetRowHeight(12)} points");
data.SetColumnWidth(2, 30m); // the file's unit: about 30 characters
data.SetRowHeight(2, 40m); // points
data.Merge(new XlsxCellRange(14, 1, 15, 3)); // A14:C15
XlsxDocumentSheet notes = prices.AddSheet("Notes"); // an ordinary sheet at once
notes.SetValue("A1", XlsxCellValue.Text("Prepared by the import job"));
prices.Sheets[1].Visibility = XlsxSheetVisibility.Visible;
Console.WriteLine(string.Join(", ", prices.Sheets.Select(s => $"{s.Name}={s.Visibility}")));
using (FileStream output = File.Create("price-list-shaped.xlsx"))
{
prices.Save(output);
}
Download the result: price-list-shaped.xlsx.
| Member | Notes |
|---|---|
GetColumnWidth, SetColumnWidth |
The unit the file stores: characters of the default font plus padding, 0 to 255. Excel's own figure is slightly smaller (a stored 30 shows as 29.29). null when the sheet states no width; 0 hides the column. |
GetRowHeight, SetRowHeight |
Points, 0 to 409.5. null when Excel sizes the row itself; 0 hides it. |
MergedRanges, Merge, Unmerge |
A merge covers more than one cell and may not overlap another merge or a table: Excel refuses a file with either, so Merge throws. The cells keep their values. |
FrozenRows, FrozenColumns |
Read only. |
Visibility |
Settable. The last visible sheet cannot be hidden. Hiding the sheet the workbook opens on moves the opening tab to the first visible sheet; otherwise Excel shows it anyway. |
XlsxDocument.AddSheet(name) |
An empty worksheet at the end. The name must follow Excel's rules and not be another sheet's, ignoring case. |
None of these asks Excel to recalculate: only a change to a cell's value or formula does.
Find and replace, and placeholders
A workbook is searched the way a Word document is, with the same method names, on one sheet or on the whole workbook:
| Method | Does |
|---|---|
FindText, FindRegex, FindWildcards |
Return the matches: sheet, cell, position in the cell's text, capture groups. Wildcards are Excel's: ?, *, ~. |
ReplaceText, ReplaceRegex |
Replace, and return what changed and what was skipped, with the reason: a formula's result, a table's header, a cell of an array formula. |
FindPlaceholderNames, ReplacePlaceholders |
List and fill {{Name}} markers from a dictionary. A marker that is the whole cell takes the type of its value. |
XlsxFindOptions has the document's options (MatchCase, default true; WholeWord; MaxMatches; RegexTimeout) and three more: WholeCell, SearchIn (values or formulas) and CellFilter. Text cells are searched; numbers are not.
The tutorial fills an invoice template step by step.
Untrusted workbooks
Open treats the file as hostile. XlsxReadSettings.Limits is a ResourceLimits with its defaults on (input size, number of entries, compression ratio, size of a part), DTDs are prohibited, and nothing outside the package is ever fetched.
| Input | Result |
|---|---|
| Not a ZIP, a damaged package, malformed XML, a cell outside Excel's grid | InvalidDocumentException |
A Word or PowerPoint file, .xlsb, .xls |
InvalidDocumentException, naming what it is |
| An encrypted workbook | DocumentEncryptedException |
| Past a limit | ResourceLimitExceededException; LimitName names the limit |
| A cell pointing at a shared string, format or shared formula that does not exist | Read as blank, the default format or no formula, with DXP1005; StrictMode = true refuses instead |
A sheet is read when you ask for its cells, so a malformed sheet throws from Cells, GetCell or Dimension, not from Open. The whole package is held in memory once opened.
Tip
The tutorial Generate, export and import Excel files imports a workbook row by row, by header name, with validation.
Not supported
Calculating formulas, inserting or deleting rows and columns, renaming, deleting or moving a sheet, setting frozen panes, .xls and .xlsb, and decrypting a protected workbook. Charts, pivot tables, comments and the like are kept on save but are not readable as objects.