Class Worksheet

Namespace

SheetStream

Assembly

SheetStream.dll

A worksheet being streamed into the package. Rows are written forward-only through Row() / Row(int); layout settings that the schema places before the sheet data (column widths, frozen panes, tab colour) must be declared before the first row, unless AutoFitColumns(double, double) or random-access Cells are used, which defer the header.

public sealed class Worksheet

Inheritance

object

Worksheet

Inherited Members

object.Equals(object)

object.Equals(object, object)

object.GetHashCode()

object.GetType()

object.ReferenceEquals(object, object)

object.ToString()

Remarks

The worksheet XML is produced in three phases: a header (sheetPr, sheetViews, sheetFormatPr, cols, sheetData start); the rows; and a trailer (protection, filters, merges, conditional formats, validations, hyperlinks, print settings, drawings, tables) written when the sheet is sealed by adding the next sheet or saving the package. Parts that live outside the sheet (tables, drawings, charts, comments, pivot tables, the sheet's relationships) are written right after it. Not thread-safe.

Properties

Cells

Random-access cells, EPPlus style: sheet.Cells[3, 2].Value = 42, in any order. Using it puts the sheet in random-access mode: cells are kept in memory and written in order when the sheet is saved, so memory grows with the cell count. Use Row() streaming for large exports; a sheet uses one mode or the other. A sheet opened with Worksheets.Edit starts in this mode with every existing cell loaded.

public CellCollection Cells { get; }

Property Value

CellCollection

CurrentRow

Index of the last row started, or 0 if none.

public int CurrentRow { get; }

Property Value

int

Index

1-based position in the workbook, also used as the sheet's part number.

public int Index { get; }

Property Value

int

IsSealed

True once the sheet has been closed; no further rows can be written.

public bool IsSealed { get; }

Property Value

bool

IsTemplateSheet

True when this sheet fills a sheet of a template workbook.

public bool IsTemplateSheet { get; }

Property Value

bool

Name

Tab name.

public string Name { get; }

Property Value

string

PageSetup

Print settings (orientation, margins, header and footer, print titles...).

public PageSetup PageSetup { get; }

Property Value

PageSetup

ReplacedTemplateRows

Number of template rows holding values or formulas that were replaced because a row with the same number was written (formatted but empty rows do not count). Lets callers detect a template footer that the data overran.

public int ReplacedTemplateRows { get; }

Property Value

int

RightToLeft

Lays the sheet out right to left (Arabic, Hebrew). Must be set before the first row.

public bool RightToLeft { get; set; }

Property Value

bool

ShowGridLines

Show the cell gridlines on screen. Default true. Must be set before the first row.

public bool ShowGridLines { get; set; }

Property Value

bool

TabColor

Tab colour as hex ("#C00000"). Must be set before the first row; it lives in the sheet header.

public string? TabColor { get; set; }

Property Value

string

Visibility

Tab visibility. Can be changed until the package is saved. At least one sheet must stay visible.

public SheetVisibility Visibility { get; set; }

Property Value

SheetVisibility

Workbook

The owning workbook.

public Workbook Workbook { get; }

Property Value

Workbook

Zoom

Zoom percentage (10 to 400). Must be set before the first row.

public int? Zoom { get; set; }

Property Value

int?

Methods

AddChart(ChartType, string)

Adds a chart that fills the cells of placement (e.g. "E2:L20"). Add series that reference data on any sheet; Excel draws the chart from the cells when the file opens.

public ExcelChart AddChart(ChartType type, string placement)

Parameters

type ChartType

placement string

Returns

ExcelChart

AddComment(int, int, string, string)

Attaches a comment (Excel note) to a cell. Notes appear when the mouse hovers over the cell's red corner mark.

public Worksheet AddComment(int row, int column, string text, string author = "SheetStream")

Parameters

row int

column int

text string

author string

Returns

Worksheet

AddConditionalFormatting(int, int, int, int)

Adds conditional formatting over a rectangle given by row and column numbers.

public ConditionalFormatting AddConditionalFormatting(int fromRow, int fromColumn, int toRow, int toColumn)

Parameters

fromRow int

fromColumn int

toRow int

toColumn int

Returns

ConditionalFormatting

AddConditionalFormatting(string)

Adds conditional formatting rules over an A1 range such as "C2:C101".

public ConditionalFormatting AddConditionalFormatting(string range)

Parameters

range string

Returns

ConditionalFormatting

AddDataValidation(int, int, int, int)

Adds a data validation over a rectangle given by row and column numbers.

public DataValidation AddDataValidation(int fromRow, int fromColumn, int toRow, int toColumn)

Parameters

fromRow int

fromColumn int

toRow int

toColumn int

Returns

DataValidation

AddDataValidation(string)

Adds a data validation (drop-down list, number / date / length bounds, or custom formula) over an A1 range such as "C2:C500". Pick the rule on the returned object.

public DataValidation AddDataValidation(string range)

Parameters

range string

Returns

DataValidation

AddHyperlink(int, int, string, string?)

Attaches a web, mail or file hyperlink to a cell that is (or will be) written separately. To write a link cell in one call use WriteHyperlink(string, string, CellStyle?, string?).

public Worksheet AddHyperlink(int row, int column, string url, string? tooltip = null)

Parameters

row int

column int

url string

tooltip string

Returns

Worksheet

AddInternalLink(int, int, string, string?)

Attaches a link to a place in this workbook, e.g. "'Summary'!A1" or a defined name.

public Worksheet AddInternalLink(int row, int column, string location, string? tooltip = null)

Parameters

row int

column int

location string

tooltip string

Returns

Worksheet

AddPicture(byte[], int, int, int?, int?, string?)

Places a PNG, JPEG or GIF image with its top-left corner in the given cell. Without a size the image keeps its pixel size; with only one dimension the other follows the aspect ratio.

public ExcelPicture AddPicture(byte[] image, int row, int column, int? widthPixels = null, int? heightPixels = null, string? name = null)

Parameters

image byte[]

row int

column int

widthPixels int?

heightPixels int?

name string

Returns

ExcelPicture

AddPicture(string, int, int, int?, int?)

Places an image read from a file. See AddPicture(byte[], int, int, int?, int?, string?).

public ExcelPicture AddPicture(string path, int row, int column, int? widthPixels = null, int? heightPixels = null)

Parameters

path string

row int

column int

widthPixels int?

heightPixels int?

Returns

ExcelPicture

AddPivotTable(string, ExcelTable, int, int)

Adds a pivot table whose source is an Excel table written earlier (or on this sheet). The field names come from the table's columns, and Excel re-reads the whole table on every refresh, so the pivot follows the table as it grows.

public PivotTable AddPivotTable(string name, ExcelTable source, int row, int column)

Parameters

name string

Pivot table name, unique within the workbook.

source ExcelTable

The table to summarise, from AddTable(string, string, IReadOnlyList<string>?) or TableName.

row int

1-based row of the pivot's top-left cell on this sheet.

column int

1-based column of the pivot's top-left cell on this sheet.

Returns

PivotTable

AddPivotTable(string, string, string, int, int, IReadOnlyList<string>)

Adds a pivot table to this sheet with its top-left cell at (row, column), summarising sourceRange (header row included) on sheet sourceSheetName. Excel computes the pivot when the file opens; see PivotTable for the design and its limits.

public PivotTable AddPivotTable(string name, string sourceSheetName, string sourceRange, int row, int column, IReadOnlyList<string> headers)

Parameters

name string

Pivot table name, unique within the workbook (e.g. "SalesByRegion").

sourceSheetName string

Sheet holding the data. It must already exist in this workbook (it may be this sheet).

sourceRange string

A1 range of the data including its header row, e.g. "A1:F500".

row int

1-based row of the pivot's top-left cell on this sheet.

column int

1-based column of the pivot's top-left cell on this sheet.

headers IReadOnlyList<string>

The header texts of the source columns, in order. The source sheet has already been streamed out, so SheetStream cannot read them back; Headers returns them for data written with a LoadFrom* call, and the AddPivotTable(string, ExcelTable, int, int) overload takes them from the table.

Returns

PivotTable

Examples

var loaded = data.LoadFromCollection(orders);
var pivot = summary.AddPivotTable("ByRegion", "Data", loaded.Range, 1, 1, loaded.Headers);
pivot.AddRowField("Region").AddDataField("Amount");

AddTable(string, int, int, int, int, IReadOnlyList<string>?)

Adds a table over a rectangle given by row and column numbers.

public ExcelTable AddTable(string name, int fromRow, int fromColumn, int toRow, int toColumn, IReadOnlyList<string>? columnNames = null)

Parameters

name string

fromRow int

fromColumn int

toRow int

toColumn int

columnNames IReadOnlyList<string>

Returns

ExcelTable

AddTable(string, string, IReadOnlyList<string>?)

Turns an A1 range (header row included) into an Excel table. Add it before writing the header row so the header texts can be captured, or pass columnNames.

public ExcelTable AddTable(string name, string range, IReadOnlyList<string>? columnNames = null)

Parameters

name string

range string

columnNames IReadOnlyList<string>

Returns

ExcelTable

Examples

var table = sheet.AddTable("Sales", "A1:C101");
table.StyleName = "TableStyleMedium9";
sheet.Row(1).WriteCell("ID").WriteCell("Product").WriteCell("Revenue");

AutoFilter(int, int, int)

Adds filter drop-downs to a header row spanning firstColumn to lastColumn. Excel extends the filter over the contiguous data below it. Can be called at any time before the sheet is sealed. Only one filter per sheet, and not on a range covered by a table (tables carry their own filter).

public Worksheet AutoFilter(int headerRow, int firstColumn, int lastColumn)

Parameters

headerRow int

firstColumn int

lastColumn int

Returns

Worksheet

AutoFitColumns(double, double)

Sizes columns to their content when the sheet is saved, the way Excel's own AutoFit does. Call before the first row. Each cell is measured as it will display (formatted numbers, dates, booleans, the widest line of wrapped text) with a built-in Calibri 11 glyph table calibrated against Excel, bold and larger fonts included; Excel's cell padding is added and the result is clamped to [minWidth, maxWidth]. Columns given an explicit width keep it.

public Worksheet AutoFitColumns(double minWidth = 6, double maxWidth = 80)

Parameters

minWidth double

maxWidth double

Returns

Worksheet

Remarks

The table covers ASCII and Latin-1; other scripts count one digit width per character (two for CJK). For another default font or non-Latin text, supply real metrics with AutoFitColumns(Func<string, bool, double>, double, double) or TextMeasurer.

Column widths sit before the data in the file, so this mode writes the rows to a temporary file and copies them in at save time. Memory stays flat; disk use roughly equals the uncompressed sheet size.

AutoFitColumns(Func<string, bool, double>, double, double)

Sizes columns to their content using the application's own text measurer, for workbooks whose default font is not Calibri 11 or whose text the built-in table cannot measure (Cyrillic, Arabic, Thai...).

public Worksheet AutoFitColumns(Func<string, bool, double> measure, double minWidth = 6, double maxWidth = 80)

Parameters

measure Func<string, bool, double>

(text, bold) => width in Excel's column unit: the width of one digit of the default font, so "0000000000" must return 10. Measure at the default size; SheetStream scales the result for larger fonts and adds Excel's cell padding. Called once per cell, so it should be cheap.

minWidth double

Smallest width a measured column gets.

maxWidth double

Largest width a measured column gets (Excel allows up to 255).

Returns

Worksheet

Examples

using var paint = new SKPaint { Typeface = SKTypeface.FromFamilyName("Calibri"), TextSize = 11 * 96f / 72 };
var digit = paint.MeasureText("0");
sheet.AutoFitColumns((text, bold) => paint.MeasureText(text) / digit);

CacheFormulaResults(bool)

Makes WriteFormula(string, CellStyle?) also write the formula's result as the cell's cached value whenever the library can compute it, so viewers that do not calculate (file previews, Quick Look, many mobile and web viewers, ExcelReader) show a number instead of a blank. Must be enabled before the first row is written.

public Worksheet CacheFormulaResults(bool enabled = true)

Parameters

enabled bool

Returns

Worksheet

Examples

sheet.CacheFormulaResults();
for (var i = 0; i < 100; i++) sheet.Row().WriteCell(i).WriteCell(i * 1.5);
sheet.Row().WriteCell("Total").WriteFormula("SUM(B1:B100)"); // Written with its value, 7425.

Remarks

This is not a formula engine. Supported: numbers, references to cells of this sheet that were already written (rows above, or cells to the left on the same row), rectangular ranges, + - * /, unary minus, a % suffix, parentheses, and SUM, AVERAGE, COUNT, COUNTA, MIN, MAX, ROUND and ABS. A formula that uses anything else, references a row not yet written or another sheet, or would produce an error (such as a division by zero) is written exactly as before: without a cached value, and Excel computes it on open. A wrong value is never written. A formula that does get a value counts as a numeric cell for later formulas, so a grand total over subtotals works.

Cost: the sheet remembers every value written, about 8 bytes per cell of every column that holds numbers, which is why this is opt-in. Values written through WriteCell, LoadFrom* and Cells[...] are all seen. Rows copied from a template (Worksheets.Open) are not: a formula referencing a template row gets no cached value.

Column(int)

Returns the settings of a 1-based column. Must be called before the first row is written (or any time before saving when AutoFit or random-access cells are used).

public ColumnSettings Column(int index)

Parameters

index int

Returns

ColumnSettings

Examples

sheet.Column(1).SetWidth(15);

Columns(int, int)

Returns a handle that applies settings to every column from from to to inclusive. Adjacent columns with identical settings are written as a single <col min max> range, so configuring thousands of columns costs a handful of bytes.

public ColumnRangeSettings Columns(int from, int to)

Parameters

from int

to int

Returns

ColumnRangeSettings

Examples

sheet.Columns(1, 2000).SetWidth(12);

FreezePanes(int, int)

Freezes the top rows rows and the leftmost columns columns so they stay visible while scrolling. Must be called before the first row is written.

public Worksheet FreezePanes(int rows, int columns = 0)

Parameters

rows int

columns int

Returns

Worksheet

LoadFromArrays(IEnumerable<object?[]>, LoadOptions?)

Writes rows given as arrays, the direct replacement for EPPlus' Cells.LoadFromArrays. With PrintHeaders (the default) the first array is the header row and gets HeaderStyle; every later array is a data row. Rows may be shorter than the header (missing cells stay empty); longer rows have their extra values dropped, because a table or filter needs a fixed column count. Values are written like WriteCell(object?, CellStyle?).

public LoadResult LoadFromArrays(IEnumerable<object?[]> rows, LoadOptions? options = null)

Parameters

rows IEnumerable<object[]>

options LoadOptions

Returns

LoadResult

Examples

sheet.LoadFromArrays(new[] { new object?[] { "Sku", "Qty" }, new object?[] { "A-1", 3 } }, new LoadOptions { TableName = "Stock" });

LoadFromAsyncEnumerable<T>(IAsyncEnumerable<T>, ISheetRowMapper<T>, LoadOptions?, CancellationToken)

Writes an IAsyncEnumerable<T> through an explicit ISheetRowMapper<T>, so no reflection is involved: the overload to use in trimmed or Native AOT applications.

public Task<LoadResult> LoadFromAsyncEnumerable<T>(IAsyncEnumerable<T> items, ISheetRowMapper<T> mapper, LoadOptions? options = null, CancellationToken cancellationToken = default)

Parameters

items IAsyncEnumerable<T>

mapper ISheetRowMapper<T>

options LoadOptions

cancellationToken CancellationToken

Returns

Task<LoadResult>

Type Parameters

T

LoadFromAsyncEnumerable<T>(IAsyncEnumerable<T>, LoadOptions?, CancellationToken)

Writes an IAsyncEnumerable<T> as rows, awaiting each item, so an EF Core query (AsAsyncEnumerable()) or a paged API streams straight into the sheet without ever being materialised. Columns, headers and options work exactly like LoadFromCollection<T>(IEnumerable<T>, LoadOptions?), including the use of a mapper registered in SheetMappers before falling back to reflection.

public Task<LoadResult> LoadFromAsyncEnumerable<T>(IAsyncEnumerable<T> items, LoadOptions? options = null, CancellationToken cancellationToken = default)

Parameters

items IAsyncEnumerable<T>

options LoadOptions

cancellationToken CancellationToken

Returns

Task<LoadResult>

Type Parameters

T

Examples

await sheet.LoadFromAsyncEnumerable(db.Orders.AsNoTracking().AsAsyncEnumerable(), new LoadOptions { TableName = "Orders" }, ct);

Remarks

Only available on .NET 8 and later; netstandard2.0 has no built-in IAsyncEnumerable.

LoadFromCollection<T>(IEnumerable<T>, ISheetRowMapper<T>, LoadOptions?)

Writes a collection as rows through an explicit ISheetRowMapper<T> (for example Order.SheetMapper generated for a [SheetRow] type). No reflection is involved, so this overload is safe for trimmed and Native AOT applications and never boxes a cell value.

public LoadResult LoadFromCollection<T>(IEnumerable<T> items, ISheetRowMapper<T> mapper, LoadOptions? options = null)

Parameters

items IEnumerable<T>

mapper ISheetRowMapper<T>

options LoadOptions

Returns

LoadResult

Type Parameters

T

LoadFromCollection<T>(IEnumerable<T>, LoadOptions?)

Writes a collection as rows, one column per public property, starting on the next row. Header text comes from SheetColumnAttribute, DisplayNameAttribute or the property name; IDictionary<string, object> items (ExpandoObject, Dapper rows) use the first item's keys. Values stream one row at a time, so any size of enumerable is fine.

public LoadResult LoadFromCollection<T>(IEnumerable<T> items, LoadOptions? options = null)

Parameters

items IEnumerable<T>

options LoadOptions

Returns

LoadResult

Type Parameters

T

Examples

sheet.LoadFromCollection(orders, new LoadOptions { HeaderStyle = header, TableName = "Orders" });

Remarks

A mapper registered in SheetMappers (generated for [SheetRow] types) is used when present; otherwise the columns are discovered through reflection, which needs the public properties and fields of T to survive trimming.

LoadFromDataReader(IDataReader, LoadOptions?)

Writes everything an IDataReader returns, streaming row by row; the natural fit for exporting a large SQL query without materialising it.

public LoadResult LoadFromDataReader(IDataReader reader, LoadOptions? options = null)

Parameters

reader IDataReader

options LoadOptions

Returns

LoadResult

LoadFromDataTable(DataTable, LoadOptions?)

Writes a DataTable as rows, with column captions as headers.

public LoadResult LoadFromDataTable(DataTable table, LoadOptions? options = null)

Parameters

table DataTable

options LoadOptions

Returns

LoadResult

MergeCells(int, int, int, int)

Merges the rectangle from (fromRow, fromColumn) to (toRow, toColumn). Only the top-left cell's value is shown. Can be called at any time before the sheet is sealed; overlapping merges are not checked.

public Worksheet MergeCells(int fromRow, int fromColumn, int toRow, int toColumn)

Parameters

fromRow int

fromColumn int

toRow int

toColumn int

Returns

Worksheet

MergeCells(string)

Merges an A1 range such as "A1:D1".

public Worksheet MergeCells(string range)

Parameters

range string

Returns

Worksheet

Protect(string?, Action<SheetProtectionOptions>?)

Protects the sheet so locked cells cannot be edited. Cells whose style sets Protection.Locked = false stay editable. The password is stored as a salted SHA-512 hash. Sheet protection deters accidental edits; it is not encryption.

public Worksheet Protect(string? password = null, Action<SheetProtectionOptions>? configure = null)

Parameters

password string

configure Action<SheetProtectionOptions>

Returns

Worksheet

Row()

Starts the next row (current row + 1) and returns the writer for its cells.

public RowWriter Row()

Returns

RowWriter

Row(int)

Returns the writer for row index. Returns the open row if it is the current one, starts a new row if the index is beyond it (gaps are allowed and simply left empty), and throws if the row has already been passed, because the sheet is streamed forward-only.

public RowWriter Row(int index)

Parameters

index int

Returns

RowWriter

Row(int, RowOptions?)

Starts row index with full row options (style, height, hidden, outline level).

public RowWriter Row(int index, RowOptions? options)

Parameters

index int

options RowOptions

Returns

RowWriter

Row(int, CellStyle?, double?)

Starts row index with a default style and/or custom height. The style applies to cells written without their own style and to the row's empty cells.

public RowWriter Row(int index, CellStyle? style, double? height = null)

Parameters

index int

1-based row index; must be the current row or later.

style CellStyle

Default style for the row, or null.

height double?

Row height in points, or null for Excel's default.

Returns

RowWriter

TemplateStyle(string)

Returns the style of a cell in the template (for example the formatted first data row), so new rows can look exactly like the designer intended. Returns the default style when the cell has none.

public CellStyle TemplateStyle(string cell)

Parameters

cell string

Returns

CellStyle

Examples

var money = sheet.TemplateStyle("D5");