# Worksheet
```csharp
public sealed class Worksheet
```
Namespace `SharpCell` ยท package `SharpCell`
A sheet of a [Workbook](SharpCell.Workbook.md). Reading a cell that was never set costs nothing: a [Cell](SharpCell.Cell.md) is a handle, and storage is allocated only when a value or formula is set.
## Properties
### Cells
```csharp
public IEnumerable| 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
```csharp
public IReadOnlyList ColumnPageBreaks { get; }
```
The columns that start a new printed page, in order.
### ConditionalFormats
```csharp
public IReadOnlyList<(IReadOnlyList, 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
```csharp
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**
- `ArgumentOutOfRangeException`: The width is outside 0 to 255.
### DefaultRowHeight
```csharp
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**
- `ArgumentOutOfRangeException`: The height is outside 0 to 409.
### FilterMode
```csharp
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
```csharp
public int FrozenColumns { get; set; }
```
How many columns on the left stay in place while the rest scrolls; 0 for none.
**Exceptions**
- `ArgumentOutOfRangeException`: The count is negative or leaves no column to scroll.
### FrozenRows
```csharp
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**
- `ArgumentOutOfRangeException`: The count is negative or leaves no row to scroll.
### MergedAreas
```csharp
public IReadOnlyList MergedAreas { get; }
```
The merged areas, in the order they were merged.
### Name
```csharp
public string Name { get; }
```
The sheet name.
### Notes
```csharp
public IReadOnlyList<(Cell, Note)> Notes { get; }
```
The cells with a note, row by row and left to right, with their notes.
### PageMargins
```csharp
public PageMargins PageMargins { get; set; }
```
The margins of the printed pages; [PageMargins.Normal](SharpCell.PageMargins.md#normal) unless set otherwise.
**Exceptions**
- `ArgumentNullException`: The value is null.
### PageOrientation
```csharp
public PageOrientation PageOrientation { get; set; }
```
How the sheet is turned on the printed page; [PageOrientation.Portrait](SharpCell.PageOrientation.md#portrait) unless set otherwise.
**Exceptions**
- `ArgumentOutOfRangeException`: The value is not a defined orientation.
### PrintArea
```csharp
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**
- `ArgumentException`: The text is not A1 ranges of this sheet separated by commas.
### PrintTitleColumns
```csharp
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**
- `ArgumentException`: The text is not a span of whole columns such as `A:B`.
### PrintTitleRows
```csharp
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**
- `ArgumentException`: The text is not a span of whole rows such as `1:2`.
### Protection
```csharp
public SheetProtection? Protection { get; }
```
What users may still do on the sheet when it is protected; null when it is not protected.
### RowPageBreaks
```csharp
public IReadOnlyList RowPageBreaks { get; }
```
The rows that start a new printed page, as Excel's Insert Page Break puts them, in order.
### ShowGridlines
```csharp
public bool ShowGridlines { get; set; }
```
Whether Excel draws gridlines between the sheet's cells; true unless turned off.
### UsedRange
```csharp
public CellRange? UsedRange { get; }
```
The smallest range holding every non-empty cell of the sheet; null when the sheet is empty.
### Validations
```csharp
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
```csharp
public Workbook Workbook { get; }
```
The workbook the sheet belongs to.
### this[int, int]
```csharp
public Cell this[int row, int column] { get; }
```
A cell by 1-based row and column.
### this[string]
```csharp
public Cell this[string address] { get; }
```
A cell by A1 address such as `B3` (no `$`, no range).
## Methods
### AddColumnPageBreak(int)
```csharp
public void AddColumnPageBreak(int column)
```
Starts a new printed page at a column: a page break left of it.
**Parameters**
- `column`: The first column of the new page, from 2 (B).
**Exceptions**
- `ArgumentOutOfRangeException`: The column is not on the sheet or is column A.
### AddConditionalFormat(string, ConditionalFormat)
```csharp
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**
- `range`: The cells, such as `B2:B10`, or several ranges separated by spaces, such as `B2:B10 D2:D10`.
- `format`: The rule.
**Exceptions**
- `ArgumentNullException`: The format is null.
- `ArgumentException`: The range is not an A1 range, or the format is one only read from files (a colour scale, data bars or an icon set).
- `FormulaParseException`: A formula cannot be parsed; nothing is added.
### AddRowPageBreak(int)
```csharp
public void AddRowPageBreak(int row)
```
Starts a new printed page at a row: a page break above it. Breaks move with their rows.
**Parameters**
- `row`: The first row of the new page, from 2.
**Exceptions**
- `ArgumentOutOfRangeException`: The row is not on the sheet or is row 1.
### AddTable(string, string, bool, bool)
```csharp
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**
- `name`: The table name; tables and defined names share one set of names, ignoring case.
- `range`: An A1 range on this sheet, such as `A1:D10`, including the header and totals rows; a single cell such as `A1` is a one-cell table without a header row.
- `hasHeaderRow`: Whether the first row holds the column names.
- `hasTotalsRow`: Whether the last row holds totals.
**Exceptions**
- `ArgumentException`: The name is not valid or is taken, the range is not an area, overlaps another table or has no row for data.
### AddValidation(string, DataValidation)
```csharp
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**
- `range`: The cells, such as `B2:B10`, or several ranges separated by spaces as Excel writes them, such as `B2:B10 D2:D10`.
- `validation`: The rule.
**Exceptions**
- `ArgumentNullException`: The validation is null.
- `ArgumentException`: The range is not an A1 range; or a rule other than [ValidationType.Any](SharpCell.ValidationType.md#any) has no [DataValidation.Formula1](SharpCell.DataValidation.md#formula1); or a comparing rule (whole number, decimal, date, time, text length) with [ValidationOperator.Between](SharpCell.ValidationOperator.md#between) or [ValidationOperator.NotBetween](SharpCell.ValidationOperator.md#notbetween) has no [DataValidation.Formula2](SharpCell.DataValidation.md#formula2).
- `FormulaParseException`: A formula cannot be parsed; nothing is added.
- `NotSupportedException`: The range takes the first cell of a rule read from a file whose formula SharpCell cannot read, so the formula could not be moved to the rule's new first cell.
### ClearConditionalFormats()
```csharp
public void ClearConditionalFormats()
```
Removes every conditional format of the sheet, as Excel's Clear Rules from Entire Sheet does.
### ClearConditionalFormats(string)
```csharp
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**
- `range`: The cells, such as `A1:Z100`, or several ranges separated by spaces.
**Exceptions**
- `ArgumentException`: The range is not an A1 range.
- `NotSupportedException`: The range takes the first cell of a rule read from a file whose formula SharpCell cannot read, so the formula could not be moved to the rule's new first cell.
### ClearValidation(string)
```csharp
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**
- `range`: The cells, such as `A1:Z100`, or several ranges separated by spaces.
**Exceptions**
- `ArgumentException`: The range is not an A1 range.
- `NotSupportedException`: The range takes the first cell of a rule read from a file whose formula SharpCell cannot read, so the formula could not be moved to the rule's new first cell.
### ColumnWidth(int)
```csharp
public double? ColumnWidth(int column)
```
A column's own width, in the units of [Worksheet.DefaultColumnWidth](SharpCell.Worksheet.md#defaultcolumnwidth); null when it has none.
**Exceptions**
- `ArgumentOutOfRangeException`: The column is outside the sheet.
### DeleteColumns(int, int)
```csharp
public void DeleteColumns(int column, int count = 1)
```
Deletes columns; see [Worksheet.DeleteRows](SharpCell.Worksheet.md#deleterows-int-int).
**Parameters**
- `column`: The first column to delete, from 1 (A is 1).
- `count`: How many columns to delete.
**Exceptions**
- `ArgumentOutOfRangeException`: The columns are not all on the sheet.
- `InvalidOperationException`: The columns hold part of an array formula.
- `NotSupportedException`: The columns are some but not all of a table's columns, or a formula on this sheet, or one naming it, cannot be read by SharpCell.
### DeleteRows(int, int)
```csharp
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](SharpCell.Workbook.md#recalculate-cancellationtoken).
**Parameters**
- `row`: The first row to delete, from 1.
- `count`: How many rows to delete.
**Exceptions**
- `ArgumentOutOfRangeException`: The rows are not all on the sheet.
- `InvalidOperationException`: The rows hold part of an array formula, or a table's header or totals row or all its data rows without the whole table.
- `NotSupportedException`: A formula on this sheet, or one naming it, cannot be read by SharpCell.
### InsertColumns(int, int)
```csharp
public void InsertColumns(int column, int count = 1)
```
Inserts empty columns before `column`; see [Worksheet.InsertRows](SharpCell.Worksheet.md#insertrows-int-int).
**Parameters**
- `column`: The column the new columns take the place of, from 1 (A is 1).
- `count`: How many columns to insert.
**Exceptions**
- `ArgumentOutOfRangeException`: The columns would not fit on the sheet.
- `InvalidOperationException`: Content would be pushed past the last column, or the columns would split an array formula.
- `NotSupportedException`: The columns would be inserted inside a table, or a formula on this sheet, or one naming it, cannot be read by SharpCell.
### InsertRows(int, int)
```csharp
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](SharpCell.Workbook.md#recalculate-cancellationtoken).
**Parameters**
- `row`: The row the new rows take the place of, from 1.
- `count`: How many rows to insert.
**Exceptions**
- `ArgumentOutOfRangeException`: The rows would not fit on the sheet.
- `InvalidOperationException`: Content would be pushed past the last row, or the rows would split an array formula (Ctrl+Shift+Enter).
- `NotSupportedException`: A formula on this sheet, or one naming it, cannot be read by SharpCell (such as a link to another workbook), so its references could not follow.
### IsColumnHidden(int)
```csharp
public bool IsColumnHidden(int column)
```
Whether a column is hidden.
**Exceptions**
- `ArgumentOutOfRangeException`: The column is outside the sheet.
### IsRowHidden(int)
```csharp
public bool IsRowHidden(int row)
```
Whether a row is hidden.
**Exceptions**
- `ArgumentOutOfRangeException`: The row is outside the sheet.
### LoadDataTable(string, DataTable, bool)
```csharp
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`](SharpCell.Worksheet.md#loadrows-string-ienumerable-t-string-func-t-object) does.
**Parameters**
- `topLeft`: The cell of the first header (or of the first value without headers), such as `B2`.
- `table`: The table to write; it needs at least one column.
- `headers`: `true` to write the column names as a header row first.
**Returns:** The range written, header row included.
**Exceptions**
- `ArgumentNullException`: An argument is null.
- `ArgumentException`: The table has no columns, the address is not a cell, or a value cannot be stored (NaN, infinity).
- `ArgumentOutOfRangeException`: The data runs past the last row or column, or a date is before the date system starts.
### LoadRows(string, IEnumerable, (string, Func)[])
```csharp
public CellRange LoadRows(string topLeft, IEnumerable rows, params (string Header, Func 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:
- `null` and `DBNull` are empty and clear the cell; a [CellValue](SharpCell.CellValue.md) is stored as it is.
- A `String` or `Char` is text, never a formula: `"=1+1"` stays text.
- A `Boolean` is a logical value.
- Every integer type, `Single`, `Double` and `Decimal` is a number; NaN and infinities are rejected.
- A `DateTime` is a date serial in the workbook's date system, shown in the culture's short date and, with a time of day, hours and minutes. `DateOnly` is a date at midnight; `DateTimeOffset` is the clock time it states.
- A `TimeOnly` is a fraction of a day with a time format; a `TimeSpan` is a number of days shown as `[h]:mm:ss`.
- An enum is its name; anything else is its `IFormattable` text in the workbook's culture, or its `ToString()`.
Number cells keep the format they had; date and time cells take a format.
**Parameters**
- `topLeft`: The cell of the first header, such as `B2`.
- `rows`: The items; one row each.
- `columns`: The header of each column and how to get its value from an item.
**Returns:** The range written, header row included.
**Exceptions**
- `ArgumentNullException`: An argument or a header is null.
- `ArgumentException`: There are no columns, the address is not a cell, or a value cannot be stored (NaN, infinity).
- `ArgumentOutOfRangeException`: The data runs past the last row or column, or a date is before the date system starts.
### Merge(string)
```csharp
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**
- `ArgumentException`: The address is not a range of two cells or more, or it overlaps a merged area.
### Protect(string, SheetProtection)
```csharp
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**
- `password`: The password Excel asks for to unprotect the sheet, or null (or empty) for none. Only its hash is kept, made as Excel makes it (SHA-512, a random salt, 100,000 spins); it cannot be read back.
- `allow`: What users may still do; null for Excel's defaults, selecting cells only.
### Range(string)
```csharp
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**
- `ArgumentException`: The text is not such an address; a sheet name is not allowed.
### RemoveColumnPageBreak(int)
```csharp
public void RemoveColumnPageBreak(int column)
```
Removes the page break left of a column, if there is one.
**Parameters**
- `column`: The column.
### RemoveRowPageBreak(int)
```csharp
public void RemoveRowPageBreak(int row)
```
Removes the page break above a row, if there is one.
**Parameters**
- `row`: The row.
### RowHeight(int)
```csharp
public double? RowHeight(int row)
```
A row's own height in points; null when it has none.
**Exceptions**
- `ArgumentOutOfRangeException`: The row is outside the sheet.
### SetColumnHidden(int, bool)
```csharp
public void SetColumnHidden(int column, bool hidden)
```
Hides or shows a column. Unlike hidden rows, hidden columns change no result, as in Excel.
**Exceptions**
- `ArgumentOutOfRangeException`: The column is outside the sheet.
### SetColumnWidth(int, double?)
```csharp
public void SetColumnWidth(int column, double? width)
```
Gives a column a width of its own, in the units of [Worksheet.DefaultColumnWidth](SharpCell.Worksheet.md#defaultcolumnwidth); null takes it away.
**Exceptions**
- `ArgumentOutOfRangeException`: The column is outside the sheet, or the width outside 0 to 255.
### SetRowHeight(int, double?)
```csharp
public void SetRowHeight(int row, double? height)
```
Gives a row a height of its own in points; null takes it away.
**Exceptions**
- `ArgumentOutOfRangeException`: The row is outside the sheet, or the height outside 0 to 409.
### SetRowHidden(int, bool)
```csharp
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](SharpCell.Worksheet.md#filtermode) for SUBTOTAL 1โ11. Like any change, it shows at the next [Workbook.Recalculate](SharpCell.Workbook.md#recalculate-cancellationtoken).
**Exceptions**
- `ArgumentOutOfRangeException`: The row is outside the sheet.
### ToString()
```csharp
public override string ToString()
```
The sheet name.
### Unmerge(string)
```csharp
public void Unmerge(string address)
```
Undoes a merge; the address must be exactly a merged area.
**Exceptions**
- `ArgumentException`: The address is not a merged area.
### Unprotect()
```csharp
public void Unprotect()
```
Removes the sheet's protection and its password, as Excel's Unprotect Sheet does once the password is given.
|