Class WorkbookEditor

Namespace: DocToolkit

Assembly: DocToolkit.dll

Creates, reads and edits Excel (.xlsx) workbooks. Legacy .xls is not supported.

public static class WorkbookEditor

Inheritance: object

WorkbookEditor

Inherited Members: object.Equals(object)

object.Equals(object, object)

object.GetHashCode()

object.GetType()

object.MemberwiseClone()

object.ReferenceEquals(object, object)

object.ToString()

Methods

AppendRows(byte[], string, IEnumerable<IEnumerable<object?>>)

Appends rows to sheetName, starting immediately after its last used row, and returns the updated workbook. Every other sheet, and all existing formatting, is left as it was.

"Last used row" comes from ClosedXML's LastRowUsed(), which — like LastCellUsed() in ReadSheet(byte[], string) — ignores formatting but counts a cell comment as used even with no value. A comment on an otherwise-blank row far below the real data therefore pushes the append down to start after that row, leaving a gap rather than continuing immediately after the last row a caller would see as holding data.

Cell typing and culture rules are identical to Create(string, IEnumerable<IEnumerable<object?>>). A cell holding an XlsxFormula is written as a formula. An empty rows is a no-op that still returns a valid workbook.

public static byte[] AppendRows(byte[] xlsx, string sheetName, IEnumerable<IEnumerable<object?>> rows)

Parameters

xlsx byte[]
sheetName string
rows IEnumerable<IEnumerable<object>>

Returns

byte[]

Exceptions

ArgumentNullException: xlsx or rows is null.
ArgumentException: xlsx is empty; sheetName is blank, is longer than 31 characters, or contains one of : \ / ? * [ ]; or an element of rows is null.
DocumentConversionException: The sheet was not found, or the package could not be opened or edited.

AppendRowsAsync(Stream, string, IEnumerable<IEnumerable<object?>>, Stream, CancellationToken)

Reads a workbook from source, appends rows to sheetName, and writes the result to destination. See AppendRows(byte[], string, IEnumerable<IEnumerable<object?>>) for the semantics, including exactly what "last used row" means.

source is read to its end and destination is written; neither is disposed, closed or sought.

public static Task AppendRowsAsync(Stream source, string sheetName, IEnumerable<IEnumerable<object?>> rows, Stream destination, CancellationToken ct = default)

Parameters

source Stream: The stream the workbook is read from.
sheetName string: The sheet to append to.
rows IEnumerable<IEnumerable<object>>: The rows to append.
destination Stream: The stream the updated workbook is written to.
ct CancellationToken: Cancels the read, the edit and the write.

Returns

Task

Exceptions

ArgumentNullException: rows is null.
ArgumentException: source is not readable or held no bytes; destination is not writable; sheetName is blank, is longer than 31 characters, or contains one of : \ / ? * [ ]; or an element of rows is null.
OperationCanceledException: ct was cancelled.
DocumentConversionException: The sheet was not found, or the package could not be opened or edited.

AppendRowsAsync(string, string, string, IEnumerable<IEnumerable<object?>>, CancellationToken)

Reads a workbook from inputPath, appends rows to sheetName, and writes the result to outputPath, overwriting any existing file. See AppendRows(byte[], string, IEnumerable<IEnumerable<object?>>) for the semantics, including exactly what "last used row" means.

public static Task AppendRowsAsync(string inputPath, string outputPath, string sheetName, IEnumerable<IEnumerable<object?>> rows, CancellationToken ct = default)

Parameters

inputPath string: The workbook to read.
outputPath string: Where to write the result. Overwritten if it exists.
sheetName string: The sheet to append to.
rows IEnumerable<IEnumerable<object>>: The rows to append.
ct CancellationToken: Cancels the read and the write.

Returns

Task

Exceptions

ArgumentNullException: rows is null.
ArgumentException: inputPath or outputPath is blank; sheetName is blank, is longer than 31 characters, or contains one of : \ / ? * [ ]; or an element of rows is null.
FileNotFoundException: inputPath does not exist.
DirectoryNotFoundException: inputPath's or outputPath's directory does not exist.
OperationCanceledException: ct was cancelled.
DocumentConversionException: The sheet was not found, or the package could not be opened or edited.

Create(IEnumerable<XlsxSheet>)

Builds a workbook from sheets, one worksheet each, in sequence order. Content comes from data rather than a template, so there is no source file to edit.

Cell typing and culture rules are identical to Create(string, IEnumerable<IEnumerable<object?>>). A cell holding an XlsxFormula is written as a formula — see that type for the one limit worth knowing about cached values.

public static byte[] Create(IEnumerable<XlsxSheet> sheets)

Parameters

sheets IEnumerable<XlsxSheet>

Returns

byte[]

Examples

byte[] xlsx = WorkbookEditor.Create(new[]
{
    XlsxSheet.Named("Sales", new[]
    {
        new object?[] { "Region", "Q1" },
        new object?[] { "North", 1200 },
        new object?[] { "Total", XlsxFormula.From("=SUM(B2:B2)") },
    }),
});

Exceptions

ArgumentNullException: sheets is null.
ArgumentException: sheets is empty, contains a null element, or names the same sheet twice.
DocumentConversionException: The workbook could not be built.

Create(string, IEnumerable<IEnumerable<object?>>)

Creates a workbook with one sheet populated from rows.

Every built-in numeric type is written as a number so formulas such as SUM() pick it up; DateTime and DateOnly become dates, TimeOnly and TimeSpan become durations. Anything else is formatted with InvariantCulture, so the same code produces the same spreadsheet on every machine.

public static byte[] Create(string sheetName, IEnumerable<IEnumerable<object?>> rows)

Parameters

sheetName string
rows IEnumerable<IEnumerable<object>>

Returns

byte[]

Exceptions

ArgumentNullException: rows is null.
ArgumentException: sheetName is blank, is longer than 31 characters, or contains one of : \ / ? * [ ]; or a row is null.
DocumentConversionException: The workbook could not be built.

CreateAsync(IEnumerable<XlsxSheet>, Stream, CancellationToken)

Builds a workbook from sheets and writes it to destination. See Create(IEnumerable<XlsxSheet>) for the semantics — this overload applies identical logic.

destination is written, from its current position, and is not disposed, closed or sought.

public static Task CreateAsync(IEnumerable<XlsxSheet> sheets, Stream destination, CancellationToken ct = default)

Parameters

sheets IEnumerable<XlsxSheet>: The sheets to build the workbook from, one worksheet each.
destination Stream: The stream the workbook is written to.
ct CancellationToken: Cancels the build and the write to destination.

Returns

Task

Exceptions

ArgumentNullException: sheets or destination is null.
ArgumentException: sheets is empty, contains a null element, or names the same sheet twice; or destination is not writable.
OperationCanceledException: ct was cancelled.
DocumentConversionException: The workbook could not be built or written.

CreateAsync(string, IEnumerable<IEnumerable<object?>>, Stream, CancellationToken)

Builds a workbook with one sheet populated from rows and writes it to destination. See Create(string, IEnumerable<IEnumerable<object?>>) for the exact typing and culture rules applied to each cell — this overload applies the identical logic, writing to destination instead of returning an array.

destination is written, from its current position, and is not disposed, closed or sought — it belongs to the caller, and may be write-only and forward-only, such as an HTTP response body.

public static Task CreateAsync(string sheetName, IEnumerable<IEnumerable<object?>> rows, Stream destination, CancellationToken ct = default)

Parameters

sheetName string: The name of the sheet to create.
rows IEnumerable<IEnumerable<object>>: The rows to populate it with.
destination Stream: The stream the workbook is written to.
ct CancellationToken: Cancels the build and the write to destination.

Returns

Task

Exceptions

ArgumentNullException: rows or destination is null.
ArgumentException: sheetName is blank, is longer than 31 characters, or contains one of : \ / ? * [ ]; a row is null; or destination is not writable.
OperationCanceledException: ct was cancelled.
DocumentConversionException: The workbook could not be built or written.

CreateToFileAsync(IEnumerable<XlsxSheet>, string, CancellationToken)

Builds a workbook from sheets and writes it to outputPath, overwriting any existing file. See Create(IEnumerable<XlsxSheet>) for the semantics.

public static Task CreateToFileAsync(IEnumerable<XlsxSheet> sheets, string outputPath, CancellationToken ct = default)

Parameters

sheets IEnumerable<XlsxSheet>: The sheets to build the workbook from, one worksheet each.
outputPath string: Where to write the workbook. Overwritten if it exists.
ct CancellationToken: Cancels the write to outputPath.

Returns

Task

Exceptions

ArgumentNullException: sheets or outputPath is null.
ArgumentException: sheets is empty, contains a null element, or names the same sheet twice; or outputPath is blank.
DirectoryNotFoundException: outputPath's directory does not exist.
OperationCanceledException: ct was cancelled.
DocumentConversionException: The workbook could not be built or written.

CreateToFileAsync(string, IEnumerable<IEnumerable<object?>>, string, CancellationToken)

Builds a workbook with one sheet populated from rows and writes it to outputPath. See Create(string, IEnumerable<IEnumerable<object?>>) for the exact typing and culture rules applied to each cell — this overload applies the identical logic, writing to outputPath instead of returning an array.

Named CreateToFileAsync rather than a third CreateAsync overload: sheetName and rows come first, same as CreateAsync(string, IEnumerable<IEnumerable<object?>>, Stream, CancellationToken), but the destination is a string path instead of a Stream — the distinct name keeps which kind of destination a call writes to visible at the call site, rather than resting on the argument type alone.

public static Task CreateToFileAsync(string sheetName, IEnumerable<IEnumerable<object?>> rows, string outputPath, CancellationToken ct = default)

Parameters

sheetName string: The name of the sheet to create.
rows IEnumerable<IEnumerable<object>>: The rows to populate it with.
outputPath string: Where to write the workbook. Overwritten if it exists.
ct CancellationToken: Cancels the write to outputPath.

Returns

Task

Exceptions

ArgumentNullException: outputPath, sheetName or rows is null.
ArgumentException: outputPath is blank; sheetName is blank, is longer than 31 characters, or contains one of : \ / ? * [ ]; or a row is null.
DirectoryNotFoundException: outputPath's directory does not exist.
OperationCanceledException: ct was cancelled.
DocumentConversionException: The workbook could not be built.

ReadCell(byte[], string, string)

Reads a cell as a string. cellRef is an A1-style reference. Text follows the calling thread's CurrentCulture — see ReadSheet(byte[], string) for the full rule.

public static string ReadCell(byte[] xlsx, string sheetName, string cellRef)

Parameters

xlsx byte[]
sheetName string
cellRef string

Returns

string

Exceptions

ArgumentNullException: Any argument is null.
ArgumentException: xlsx is empty, or a name is blank.
DocumentConversionException: The workbook could not be opened, the sheet does not exist, or the reference is not valid.

ReadCellAsync(Stream, string, string, CancellationToken)

Reads a workbook from source and returns a cell as a string. cellRef is an A1-style reference. source is read to its end and is neither disposed, closed nor sought.

public static Task<string> ReadCellAsync(Stream source, string sheetName, string cellRef, CancellationToken ct = default)

Parameters

source Stream
sheetName string
cellRef string
ct CancellationToken

Returns

Task<string>

Exceptions

ArgumentNullException: source is null.
ArgumentException: source is not readable or held no bytes, or a name is blank.
OperationCanceledException: ct was cancelled.
DocumentConversionException: The workbook could not be opened, the sheet does not exist, or the reference is not valid.

ReadCellAsync(string, string, string, CancellationToken)

Reads a workbook from path and returns a cell as a string. cellRef is an A1-style reference. See ReadCell(byte[], string, string) for the culture rule applied to the text.

public static Task<string> ReadCellAsync(string path, string sheetName, string cellRef, CancellationToken ct = default)

Parameters

path string: The workbook to read.
sheetName string: The sheet containing the cell.
cellRef string: An A1-style cell reference, e.g. "B2".
ct CancellationToken: Cancels the read.

Returns

Task<string>: The cell's value as a string.

Exceptions

ArgumentNullException: A path or a name is null.
ArgumentException: path or a name is blank, or the file it names is empty.
FileNotFoundException: path does not exist.
DirectoryNotFoundException: path's directory does not exist.
OperationCanceledException: ct was cancelled.
DocumentConversionException: The workbook could not be opened, the sheet does not exist, or the reference is not valid.

ReadSheet(byte[], string)

Reads a whole sheet as strings, anchored at A1: if the data starts at C3, its first value is at rows[2][2]. Every row is padded to the last used column, so all rows have the same length; blank cells — and entirely blank rows inside the range — come back as empty strings rather than being dropped, which keeps rows[r][c] positionally meaningful.

Values are produced exactly as ReadCell(byte[], string, string) produces them, so the two can never disagree about what a cell says. A formula cell reads back one of two ways, and which one depends on the file rather than on this library:

If the file carries a cached value, as one Excel has saved does, that cached value is returned and the formula is not evaluated. It can therefore be stale — a workbook whose inputs were edited by something that did not recalculate reports the old result.
If it does not — which is what this library writes, see XlsxFormula — ClosedXML evaluates the formula on read, so a cell holding =A1+A2 over 1 and 2 reads back as "3", and one that cannot be evaluated reads back as its Excel error string (#DIV/0!, #NAME?, #REF!) rather than throwing.

Text follows the calling thread's CurrentCulture — the same rule ReadCell(byte[], string, string) uses, and asymmetric with Create(string, IEnumerable<IEnumerable<object?>>), which deliberately writes with InvariantCulture so the same code produces the same file everywhere. A number such as 1234.5 reads back as "1234.5" under an invariant or en-US culture but "1234,5" under de-DE; callers who parse the returned text as a number should account for that, e.g. by parsing with an explicit CultureInfo rather than the default.

public static IReadOnlyList<IReadOnlyList<string>> ReadSheet(byte[] xlsx, string sheetName)

Parameters

xlsx byte[]: The workbook bytes.
sheetName string: The sheet to read.

Returns

IReadOnlyList<IReadOnlyList<string>>: The sheet's used range, anchored at A1 and padded rectangular. Empty only if the sheet holds no values and no cell comments: formatting alone never widens the range, but a comment on an otherwise-blank cell does, because ClosedXML's LastCellUsed() counts it as used.

Examples

// No need to know the workbook's shape in advance.
foreach (string name in WorkbookEditor.SheetNames(xlsx))
{
    IReadOnlyList<IReadOnlyList<string>> grid = WorkbookEditor.ReadSheet(xlsx, name);
    Console.WriteLine($"{name}: {grid.Count} rows x {grid[0].Count} columns");
}

Remarks

The whole range is materialised into memory at once, so its cost is proportional to rows × columns, not to how much of that rectangle actually holds data. To keep one far-flung stray value from exhausting memory, ReadSheet(byte[], string) throws DocumentConversionException rather than allocate when the used range exceeds 2,000,000 cells.

Exceptions

ArgumentNullException: Any argument is null.
ArgumentException: xlsx is empty, or sheetName is blank.
DocumentConversionException: The workbook could not be opened, the sheet does not exist, or the sheet's used range exceeds the 2,000,000-cell limit ReadSheet(byte[], string) will materialise.

ReadSheetAsync(Stream, string, CancellationToken)

Reads a workbook from source and returns a whole sheet as strings. See ReadSheet(byte[], string) for the anchoring, padding, culture and formula rules — this overload applies the identical logic. source is read to its end and is neither disposed, closed nor sought.

public static Task<IReadOnlyList<IReadOnlyList<string>>> ReadSheetAsync(Stream source, string sheetName, CancellationToken ct = default)

Parameters

source Stream: The stream the workbook is read from.
sheetName string: The sheet to read.
ct CancellationToken: Cancels the read.

Returns

Task<IReadOnlyList<IReadOnlyList<string>>>: The sheet's used range, anchored at A1 and padded rectangular. Empty only if the sheet holds no values and no cell comments — see ReadSheet(byte[], string).

Exceptions

ArgumentNullException: source is null.
ArgumentException: source is not readable or held no bytes, or sheetName is blank.
OperationCanceledException: ct was cancelled.
DocumentConversionException: The workbook could not be opened, the sheet does not exist, or the sheet's used range exceeds the 2,000,000-cell limit ReadSheet(byte[], string) will materialise.

ReadSheetAsync(string, string, CancellationToken)

Reads a workbook from path and returns a whole sheet as strings. See ReadSheet(byte[], string) for the anchoring, padding, culture and formula rules — this overload applies the identical logic.

public static Task<IReadOnlyList<IReadOnlyList<string>>> ReadSheetAsync(string path, string sheetName, CancellationToken ct = default)

Parameters

path string: The workbook to read.
sheetName string: The sheet to read.
ct CancellationToken: Cancels the read.

Returns

Task<IReadOnlyList<IReadOnlyList<string>>>: The sheet's used range, anchored at A1 and padded rectangular. Empty only if the sheet holds no values and no cell comments — see ReadSheet(byte[], string).

Exceptions

ArgumentNullException: A path or sheetName is null.
ArgumentException: path or sheetName is blank, or the file at path is empty.
FileNotFoundException: path does not exist.
DirectoryNotFoundException: path's directory does not exist.
OperationCanceledException: ct was cancelled.
DocumentConversionException: The workbook could not be opened, the sheet does not exist, or the sheet's used range exceeds the 2,000,000-cell limit ReadSheet(byte[], string) will materialise.

SetCell(byte[], string, string, object?)

Sets a cell and returns the updated workbook bytes. A cell holding an XlsxFormula is written as a formula instead of a literal value — see that type for the one limit worth knowing about cached values.

public static byte[] SetCell(byte[] xlsx, string sheetName, string cellRef, object? value)

Parameters

xlsx byte[]
sheetName string
cellRef string
value object

Returns

byte[]

Exceptions

ArgumentNullException: Any argument other than value is null.
ArgumentException: xlsx is empty, or a name is blank.
DocumentConversionException: The workbook could not be opened, the sheet does not exist, or the reference is not valid.

SetCellAsync(Stream, string, string, object?, Stream, CancellationToken)

Reads a workbook from source, sets one cell, and writes the result to destination. cellRef is an A1-style reference. A cell holding an XlsxFormula is written as a formula instead of a literal value — see that type for the one limit worth knowing about cached values.

source is read to its end and destination is written; neither is disposed, closed or sought, and neither has to be seekable.

public static Task SetCellAsync(Stream source, string sheetName, string cellRef, object? value, Stream destination, CancellationToken ct = default)

Parameters

source Stream: The stream the workbook is read from.
sheetName string: The sheet containing the cell.
cellRef string: An A1-style cell reference, e.g. "B2".
value object: The value to write. null clears the cell; an XlsxFormula writes a formula.
destination Stream: The stream the updated workbook is written to.
ct CancellationToken: Cancels the read, the edit and the write.

Returns

Task

Exceptions

ArgumentNullException: source or destination is null.
ArgumentException: source is not readable or held no bytes, a name is blank, or destination is not writable.
OperationCanceledException: ct was cancelled.
DocumentConversionException: The workbook could not be opened, the sheet does not exist, or the reference is not valid.

SetCellAsync(string, string, string, string, object?, CancellationToken)

Reads a workbook from inputPath, sets one cell, and writes the result to outputPath. cellRef is an A1-style reference. A cell holding an XlsxFormula is written as a formula instead of a literal value — see that type for the one limit worth knowing about cached values. The two paths may be the same file: the updated bytes are computed in full before outputPath is opened, so a workbook that fails to process — cannot be read, or cannot be edited — leaves outputPath untouched. That guarantee does not extend to a failure during the write itself: a full disk, a cancellation, or the process dying mid-write can still leave a partial file, so in-place editing of an irreplaceable workbook is not crash-safe.

public static Task SetCellAsync(string inputPath, string outputPath, string sheetName, string cellRef, object? value, CancellationToken ct = default)

Parameters

inputPath string: The workbook to read.
outputPath string: Where to write the result. Overwritten if it exists.
sheetName string: The sheet containing the cell.
cellRef string: An A1-style cell reference, e.g. "B2".
value object: The value to write. null clears the cell; an XlsxFormula writes a formula.
ct CancellationToken: Cancels the read and the write.

Returns

Task

Exceptions

ArgumentNullException: A path or a name is null.
ArgumentException: A path or a name is blank, or the file at inputPath is empty.
FileNotFoundException: inputPath does not exist.
DirectoryNotFoundException: inputPath's or outputPath's directory does not exist.
OperationCanceledException: ct was cancelled.
DocumentConversionException: The workbook could not be opened, the sheet does not exist, or the reference is not valid.

SheetNames(byte[])

Lists every sheet in the workbook, in tab order, including hidden sheets — hiding a sheet is a presentation choice, not a privacy boundary, and a caller who cannot see a hidden sheet listed has no way to discover it exists.

public static IReadOnlyList<string> SheetNames(byte[] xlsx)

Parameters

xlsx byte[]: The workbook bytes.

Returns

IReadOnlyList<string>: The sheet names, in tab order.

Exceptions

ArgumentNullException: xlsx is null.
ArgumentException: xlsx is empty.
DocumentConversionException: The workbook could not be opened.

SheetNamesAsync(Stream, CancellationToken)

Reads a workbook from source and lists every sheet in tab order, including hidden sheets. source is read to its end and is neither disposed, closed nor sought.

public static Task<IReadOnlyList<string>> SheetNamesAsync(Stream source, CancellationToken ct = default)

Parameters

source Stream: The stream the workbook is read from.
ct CancellationToken: Cancels the read.

Returns

Task<IReadOnlyList<string>>: The sheet names, in tab order.

Exceptions

ArgumentNullException: source is null.
ArgumentException: source is not readable or held no bytes.
OperationCanceledException: ct was cancelled.
DocumentConversionException: The workbook could not be opened.

SheetNamesAsync(string, CancellationToken)

Reads a workbook from path and lists every sheet in tab order, including hidden sheets. See SheetNames(byte[]) for the full rule.

public static Task<IReadOnlyList<string>> SheetNamesAsync(string path, CancellationToken ct = default)

Parameters

path string: The workbook to read.
ct CancellationToken: Cancels the read.

Returns

Task<IReadOnlyList<string>>: The sheet names, in tab order.

Exceptions

ArgumentNullException: path is null.
ArgumentException: path is blank, or the file it names is empty.
FileNotFoundException: path does not exist.
DirectoryNotFoundException: path's directory does not exist.
OperationCanceledException: ct was cancelled.
DocumentConversionException: The workbook could not be opened.

Table of Contents

Class WorkbookEditor

Methods

AppendRows(byte[], string, IEnumerable<IEnumerable<object?>>)

Parameters

Returns

Exceptions

AppendRowsAsync(Stream, string, IEnumerable<IEnumerable<object?>>, Stream, CancellationToken)

Parameters

Returns

Exceptions

AppendRowsAsync(string, string, string, IEnumerable<IEnumerable<object?>>, CancellationToken)

Parameters

Returns

Exceptions

Create(IEnumerable<XlsxSheet>)

Parameters

Returns

Examples

Exceptions

Create(string, IEnumerable<IEnumerable<object?>>)

Parameters

Returns

Exceptions

CreateAsync(IEnumerable<XlsxSheet>, Stream, CancellationToken)

Parameters

Returns

Exceptions

CreateAsync(string, IEnumerable<IEnumerable<object?>>, Stream, CancellationToken)

Parameters

Returns

Exceptions

CreateToFileAsync(IEnumerable<XlsxSheet>, string, CancellationToken)

Parameters

Returns

Exceptions

CreateToFileAsync(string, IEnumerable<IEnumerable<object?>>, string, CancellationToken)

Parameters

Returns

Exceptions

ReadCell(byte[], string, string)

Parameters

Returns

Exceptions

ReadCellAsync(Stream, string, string, CancellationToken)

Parameters

Returns

Exceptions

ReadCellAsync(string, string, string, CancellationToken)

Parameters

Returns

Exceptions

ReadSheet(byte[], string)

Parameters

Returns

Examples

Remarks

Exceptions

ReadSheetAsync(Stream, string, CancellationToken)

Parameters

Returns

Exceptions

ReadSheetAsync(string, string, CancellationToken)

Parameters

Returns

Exceptions

SetCell(byte[], string, string, object?)

Parameters

Returns

Exceptions

SetCellAsync(Stream, string, string, object?, Stream, CancellationToken)

Parameters

Returns

Exceptions

SetCellAsync(string, string, string, string, object?, CancellationToken)

Parameters

Returns

Exceptions

SheetNames(byte[])

Parameters