SharpCell

Worksheet

public sealed class Worksheet

Namespace SharpCell · package SharpCell

A sheet of a Workbook. Reading a cell that was never set costs nothing: a Cell is a handle, and storage is allocated only when a value or formula is set.

Properties

Cells

public IEnumerable<Cell> Cells { get; }

The cells of the sheet that hold something (a constant, a formula or a value spilled into them), row by row and left to right. Values and formulas may be changed while enumerating; adding or removing a cell makes the next step throw InvalidOperationException.

ColumnPageBreaks

public IReadOnlyList<int> ColumnPageBreaks { get; }

The columns that start a new printed page, in order.

ConditionalFormats

public IReadOnlyList<(IReadOnlyList<CellRange>, ConditionalFormat)> ConditionalFormats { get; }

The conditional formats in priority order, highest first, each with its ranges. The formulas of a rule are relative to the first cell of its first range.

DefaultColumnWidth

public double? DefaultColumnWidth { get; set; }

The width of columns without one of their own, as .xlsx files store it: in characters of the widest digit of the workbook's default font, padding included. Excel's standard 8.43 characters of Calibri 11 are 9.140625 here. Null when not given; Excel then uses 8 characters.

Exceptions

DefaultRowHeight

public double? DefaultRowHeight { get; set; }

The height of rows without one of their own, in points. Null when not given; Excel then fits it to the default font, 15 points for Calibri 11.

Exceptions

FilterMode

public bool FilterMode { get; set; }

Whether the sheet has a filter with criteria, like Worksheet.FilterMode in Excel. Excel then treats every hidden row of the sheet as filtered out, so SUBTOTAL with codes 1–11 skips them as well; without a filter it counts them. AGGREGATE does not look at it.

FrozenColumns

public int FrozenColumns { get; set; }

How many columns on the left stay in place while the rest scrolls; 0 for none.

Exceptions

FrozenRows

public int FrozenRows { get; set; }

How many rows at the top stay in place while the rest scrolls, as Excel's Freeze Panes keeps them; 0 for none.

Exceptions

MergedAreas

public IReadOnlyList<CellRange> MergedAreas { get; }

The merged areas, in the order they were merged.

Name

public string Name { get; }

The sheet name.

Notes

public IReadOnlyList<(Cell, Note)> Notes { get; }

The cells with a note, row by row and left to right, with their notes.

PageMargins

public PageMargins PageMargins { get; set; }

The margins of the printed pages; PageMargins.Normal unless set otherwise.

Exceptions

PageOrientation

public PageOrientation PageOrientation { get; set; }

How the sheet is turned on the printed page; PageOrientation.Portrait unless set otherwise.

Exceptions

PrintArea

public string? PrintArea { get; set; }

The cells that print, as Excel's Print Area sets them: one range such as A1:F40, or several separated by commas, each printed on pages of its own. Null prints every cell that holds something, which is also what setting null gives. Read without $ signs; written with or without them. The ranges move with their cells when rows, columns or cells are inserted or deleted, as the defined name Excel keeps them in does.

Exceptions

PrintTitleColumns

public string? PrintTitleColumns { get; set; }

The columns printed at the left of every page: A:A or A:B. Null for none. They move with their columns when columns are inserted or deleted.

Exceptions

PrintTitleRows

public string? PrintTitleRows { get; set; }

The rows printed at the top of every page, as Excel's Print Titles sets them: 1:1 or 1:3. Null for none. They move with their rows when rows are inserted or deleted.

Exceptions

Protection

public SheetProtection? Protection { get; }

What users may still do on the sheet when it is protected; null when it is not protected.

RowPageBreaks

public IReadOnlyList<int> RowPageBreaks { get; }

The rows that start a new printed page, as Excel's Insert Page Break puts them, in order.

ShowGridlines

public bool ShowGridlines { get; set; }

Whether Excel draws gridlines between the sheet's cells; true unless turned off.

UsedRange

public CellRange? UsedRange { get; }

The smallest range holding every non-empty cell of the sheet; null when the sheet is empty.

Validations

public IReadOnlyList<(CellRange, DataValidation)> Validations { get; }

The data validation rules, one entry per range: a rule over several ranges has an entry for each, in order. The formulas of a rule are relative to the first cell of its first range.

Workbook

public Workbook Workbook { get; }

The workbook the sheet belongs to.

this[int, int]

public Cell this[int row, int column] { get; }

A cell by 1-based row and column.

this[string]

public Cell this[string address] { get; }

A cell by A1 address such as B3 (no $, no range).

Methods

AddColumnPageBreak(int)

public void AddColumnPageBreak(int column)

Starts a new printed page at a column: a page break left of it.

Parameters

Exceptions

AddConditionalFormat(string, ConditionalFormat)

public void AddConditionalFormat(string range, ConditionalFormat format)

Adds a conditional format to a range, below the rules the sheet has: where several apply to a cell, those added earlier win. Formulas are relative to the range's top-left cell; for several ranges, to the first one's.

Parameters

Exceptions

AddRowPageBreak(int)

public void AddRowPageBreak(int row)

Starts a new printed page at a row: a page break above it. Breaks move with their rows.

Parameters

Exceptions

AddTable(string, string, bool, bool)

public Table AddTable(string name, string range, bool hasHeaderRow = true, bool hasTotalsRow = false)

Makes a range a table that formulas can refer to by name, as in Sales[Units]. Column names come from the header row as it is now: a cell's value as text (a formula's too), ColumnN for an empty cell or an error (N counts from the table's first column), and a number added to a repeated name (Units2). Without a header row the columns are Column1, Column2 and so on. Changing a header cell later does not rename its column.

Parameters

Exceptions

AddValidation(string, DataValidation)

public void AddValidation(string range, DataValidation validation)

Adds a data validation rule to a range. Cells of the range leave the rules they had, as in Excel, where a cell has one rule. The formulas are relative to the range's top-left cell; for several ranges, to the first one's.

Parameters

Exceptions

ClearConditionalFormats()

public void ClearConditionalFormats()

Removes every conditional format of the sheet, as Excel's Clear Rules from Entire Sheet does.

ClearConditionalFormats(string)

public void ClearConditionalFormats(string range)

Takes the cells of a range out of every conditional format, as Excel's Clear Rules from Selected Cells does; a rule left without cells is removed.

Parameters

Exceptions

ClearValidation(string)

public void ClearValidation(string range)

Takes the cells of a range out of every data validation rule; a rule left without cells is removed. A rule that loses its first cell keeps its formulas relative to its new first cell, so every remaining cell is checked as before.

Parameters

Exceptions

ColumnWidth(int)

public double? ColumnWidth(int column)

A column's own width, in the units of Worksheet.DefaultColumnWidth; null when it has none.

Exceptions

DeleteColumns(int, int)

public void DeleteColumns(int column, int count = 1)

Deletes columns; see Worksheet.DeleteRows.

Parameters

Exceptions

DeleteRows(int, int)

public void DeleteRows(int row, int count = 1)

Deletes rows, as Excel's Delete Sheet Rows does: cells below move up. A reference to a deleted cell becomes #REF!, a range that loses some of its rows shrinks, and a table whose rows are all deleted is removed. Every formula is calculated again at the next Workbook.Recalculate.

Parameters

Exceptions

InsertColumns(int, int)

public void InsertColumns(int column, int count = 1)

Inserts empty columns before column; see Worksheet.InsertRows.

Parameters

Exceptions

InsertRows(int, int)

public void InsertRows(int row, int count = 1)

Inserts empty rows before row, as Excel's Insert Sheet Rows does. Cells, styles, row heights, hidden rows, merged cells, tables and data validation below move down, and every reference to them follows: in formulas of every sheet, in defined names and in data validation. A range that spans the new rows grows; a table, merged area or validated range they are inserted into grows. Every formula is calculated again at the next Workbook.Recalculate.

Parameters

Exceptions

IsColumnHidden(int)

public bool IsColumnHidden(int column)

Whether a column is hidden.

Exceptions

IsRowHidden(int)

public bool IsRowHidden(int row)

Whether a row is hidden.

Exceptions

LoadDataTable(string, DataTable, bool)

public CellRange LoadDataTable(string topLeft, DataTable table, bool headers = true)

Writes the rows of a DataTable starting at a cell, optionally under a header row of its column names, and returns the range written. Nothing is written unless every value could be converted. DBNull is an empty cell; other values are converted as Worksheet.LoadRows<T> does.

Parameters

Returns: The range written, header row included.

Exceptions

LoadRows(string, IEnumerable, (string, Func<T, object>)[])

public CellRange LoadRows<T>(string topLeft, IEnumerable<T> rows, params (string Header, Func<T, object?> Value)[] columns)

Writes a header row and then one row per item of a collection, starting at a cell, and returns the range written (the header row included). The collection is enumerated once and nothing is written unless every value could be converted. Columns are lambdas, so no reflection is needed.

Values are converted like this:

Number cells keep the format they had; date and time cells take a format.

Parameters

Returns: The range written, header row included.

Exceptions

Merge(string)

public void Merge(string address)

Merges a range of at least two cells into one, as Excel shows it: the top-left cell covers the range. The values of the other cells are kept; Excel's Merge command would clear them.

Exceptions

Protect(string, SheetProtection)

public void Protect(string? password = null, SheetProtection? allow = null)

Protects the sheet as Excel's Protect Sheet does: Excel then refuses changes to locked cells and allows only the actions allow names. SharpCell itself does not enforce protection: code may still change the sheet, as Excel's object model may with the password. Protecting a protected sheet replaces its protection and password.

Parameters

Range(string)

public CellRange Range(string address)

A range of this sheet by A1 address: A1:C10, B2, whole columns A:B or whole rows 2:3.

Exceptions

RemoveColumnPageBreak(int)

public void RemoveColumnPageBreak(int column)

Removes the page break left of a column, if there is one.

Parameters

RemoveRowPageBreak(int)

public void RemoveRowPageBreak(int row)

Removes the page break above a row, if there is one.

Parameters

RowHeight(int)

public double? RowHeight(int row)

A row's own height in points; null when it has none.

Exceptions

SetColumnHidden(int, bool)

public void SetColumnHidden(int column, bool hidden)

Hides or shows a column. Unlike hidden rows, hidden columns change no result, as in Excel.

Exceptions

SetColumnWidth(int, double?)

public void SetColumnWidth(int column, double? width)

Gives a column a width of its own, in the units of Worksheet.DefaultColumnWidth; null takes it away.

Exceptions

SetRowHeight(int, double?)

public void SetRowHeight(int row, double? height)

Gives a row a height of its own in points; null takes it away.

Exceptions

SetRowHidden(int, bool)

public void SetRowHidden(int row, bool hidden)

Hides or shows a row. SUBTOTAL with codes 101–111 and AGGREGATE with options 1, 3, 5 and 7 skip hidden rows; see Worksheet.FilterMode for SUBTOTAL 1–11. Like any change, it shows at the next Workbook.Recalculate.

Exceptions

ToString()

public override string ToString()

The sheet name.

Unmerge(string)

public void Unmerge(string address)

Undoes a merge; the address must be exactly a merged area.

Exceptions

Unprotect()

public void Unprotect()

Removes the sheet's protection and its password, as Excel's Unprotect Sheet does once the password is given.