 | Worksheet Class |
Class representing a worksheet of a workbook
Inheritance HierarchySystemObject
PicoXLSXWorksheet
PicoXLSXWorksheet
Namespace: PicoXLSX
Assembly: PicoXLSX (in PicoXLSX.dll) Version: 3.4.2+f55916d49ef2baa43fc7dc8f5c7d751a77c50baf
SyntaxC#
public class Worksheet
The Worksheet type exposes the following members.
Constructors| Name | Description | |
|---|---|---|
  | Worksheet | |
 | Worksheet | Initializes a new instance of the Worksheet class |
| Worksheet(String) | Initializes a new instance of the Worksheet class |
| Worksheet(String, Int32, Workbook) | Initializes a new instance of the Worksheet class |
Properties| Name | Description | |
|---|---|---|
 | ActivePane |
Gets the active Pane is splitting is applied. The value is nullable. If null, no splitting was defined |
| ActiveStyle | Gets the active Style of the worksheet. If null, no style is defined as active |
| AutoFilterRange | Gets the range of the auto-filter. Wrapped to Nullable to provide null as value. If null, no auto-filter is applied |
| Cells | Gets the cells of the worksheet as dictionary with the cell address as key and the cell object as value |
| Columns | Gets all columns with non-standard properties, like auto filter applied or a special width as dictionary with the zero-based column index as key and the column object as value |
| CurrentCellDirection | Gets or sets the direction when using AddNextCell method |
| DefaultColumnWidth | Gets or sets the default column width |
| DefaultRowHeight | Gets or sets the default Row height |
| FreezeSplitPanes |
Gets the FreezeSplitPanes
Gets whether split panes are frozen. The value is nullable. If null, no freezing is applied. This property also does not apply if PaneSplitAddress is null |
| Hidden |
Gets or sets whether the worksheet is hidden. If true, the worksheet is not listed as tab in the workbook's worksheet selection If the worksheet is not part of a workbook, or the only one in the workbook, an exception will be thrown. If the worksheet is the selected one, and attempted to set hidden, an exception will be thrown. Define another selected worksheet prior to this call, in this case. |
| HiddenRows | Gets the hidden rows as dictionary with the zero-based row number as key and a boolean as value. True indicates hidden, false visible. |
| MergedCells | Gets the merged cells (only references) as dictionary with the cell address as key and the range object as value |
| PaneSplitAddress |
Gets the split address for frozen panes or if pane split was defined in number of columns and / or rows. For vertical splits, only the column component is considered. For horizontal splits, only the row component is considered. The value is nullable. If null, no frozen panes or split by columns / rows are applied to the worksheet. However, splitting can still be applied, if the value is defined in characters. See also: PaneSplitLeftWidth and PaneSplitTopHeight for splitting in characters (without freezing) |
| PaneSplitLeftWidth |
Gets the width of the left, vertical split pane, measured from the left of the window. The value is nullable. If null, no vertical split of the worksheet is applied The value is only applicable to split the worksheet into panes, but not to freeze them. See also: PaneSplitAddress |
| PaneSplitTopHeight |
Gets the height of the upper, horizontal split pane, measured from the top of the window. The value is nullable. If null, no horizontal split of the worksheet is applied. The value is only applicable to split the worksheet into panes, but not to freeze them. See also: PaneSplitAddress |
| PaneSplitTopLeftCell |
Gets the Top Left cell address of the bottom right pane if applicable and splitting is applied. The column is only relevant for vertical split, whereas the row component is only relevant for a horizontal split. The value is nullable. If null, no splitting was defined. |
| RowHeights | Gets defined row heights as dictionary with the zero-based row number as key and the height (float from 0 to 409.5) as value |
| SelectedCellRanges | Gets all ranges of selected cells of this worksheet. An empty list is returned if no cells are selected |
| SelectedCells | Obsolete. Returns either null (if no cells are selected), or the first defined range of selected cells |
| SheetID | Gets or sets the internal ID of the worksheet |
| SheetName | Gets or sets the name of the worksheet |
| SheetProtectionPassword | Gets the password used for sheet protection. See SetSheetProtectionPassword(String) to set the password |
| SheetProtectionPasswordHash | gets the encrypted hash of the password, defined with SheetProtectionPassword. The value will be null, if no password is defined |
| SheetProtectionValues | Gets the list of SheetProtectionValues. These values define the allowed actions if the worksheet is protected |
| ShowGridLines | Gets or sets whether grid lines are visible on the current worksheet. Default is true |
| ShowRowColumnHeaders | Gets or sets whether the column and row headers are visible on the current worksheet. Default is true |
| ShowRuler | Gets or sets whether a ruler is displayed over the column headers. This value only applies if ViewType is set to pageLayout. Default is true |
| UseSheetProtection | Gets or sets whether the worksheet is protected. If true, protection is enabled |
| ViewType | Gets or sets how the current worksheet is displayed in the spreadsheet application (Excel) |
| WorkbookReference | Gets or sets the Reference to the parent Workbook |
| ZoomFactor | Gets or sets the zoom factor of the ViewType of the current worksheet. If AUTO_ZOOM_FACTOR, the zoom factor is set to automatic |
| ZoomFactors | Gets all defined zoom factors per WorksheetSheetViewType of the current worksheet. Use SetZoomFactor(WorksheetSheetViewType, Int32) to define the values |
Methods| Name | Description | |
|---|---|---|
| AddAllowedActionOnSheetProtection | Method to add allowed actions if the worksheet is protected. If one or more values are added, UseSheetProtection will be set to true |
| AddCell(Object, String) | Adds an object to the defined cell address. If the type of the value does not match with one of the supported data types, it will be casted to a String. A prepared object of the type Cell will not be casted but adjusted |
| AddCell(Object, Int32, Int32) | Adds an object to the defined cell address. If the type of the value does not match with one of the supported data types, it will be casted to a String. A prepared object of the type Cell will not be casted but adjusted |
| AddCell(Object, String, Style) | Adds an object to the defined cell address. If the type of the value does not match with one of the supported data types, it will be casted to a String. A prepared object of the type Cell will not be casted but adjusted |
| AddCell(Object, Int32, Int32, Style) | Adds an object to the defined cell address. If the type of the value does not match with one of the supported data types, it will be casted to a String. A prepared object of the type Cell will not be casted but adjusted |
| AddCellFormula(String, String) | Adds a cell formula as string to the defined cell address |
| AddCellFormula(String, Int32, Int32) | Adds a cell formula as string to the defined cell address |
| AddCellFormula(String, String, Style) | Adds a cell formula as string to the defined cell address |
| AddCellFormula(String, Int32, Int32, Style) | Adds a cell formula as string to the defined cell address |
| AddCellRange(IReadOnlyListObject, String) | Adds a list of object values to a defined cell range. If the type of the a particular value does not match with one of the supported data types, it will be casted to a String. Prepared objects of the type Cell will not be casted but adjusted |
| AddCellRange(IReadOnlyListObject, CellAddress, CellAddress) | Adds a list of object values to a defined cell range. If the type of the a particular value does not match with one of the supported data types, it will be casted to a String. Prepared objects of the type Cell will not be casted but adjusted |
| AddCellRange(IReadOnlyListObject, String, Style) | Adds a list of object values to a defined cell range. If the type of the a particular value does not match with one of the supported data types, it will be casted to a String. Prepared objects of the type Cell will not be casted but adjusted |
| AddCellRange(IReadOnlyListObject, CellAddress, CellAddress, Style) | Adds a list of object values to a defined cell range. If the type of the a particular value does not match with one of the supported data types, it will be casted to a String. Prepared objects of the type Cell will not be casted but adjusted |
| AddCellRangeInternalT | Internal function to add a generic list of value to the defined cell range |
| AddHiddenColumn(Int32) | Sets the defined column as hidden |
| AddHiddenColumn(String) | Sets the defined column as hidden |
| AddHiddenRow | Sets the defined row as hidden |
| AddNextCell(Object) | Adds an object to the next cell position. If the type of the value does not match with one of the supported data types, it will be casted to a String. A prepared object of the type Cell will not be casted but adjusted |
| AddNextCell(Object, Style) | Adds an object to the next cell position. If the type of the value does not match with one of the supported data types, it will be casted to a String. A prepared object of the type Cell will not be casted but adjusted |
| AddNextCell(Cell, Boolean, Style) | Method to insert a generic cell to the next cell position |
| AddNextCellFormula(String) | Adds a formula as string to the next cell position |
| AddNextCellFormula(String, Style) | Adds a formula as string to the next cell position |
| AddSelectedCells(CellRange) | Adds a range to the selected cells on this worksheet |
| AddSelectedCells(String) | Adds a range to the selected cells on this worksheet. Null or empty as value will be ignored |
| AddSelectedCells(CellAddress, CellAddress) | Adds a range to the selected cells on this worksheet |
| CastValue | Method to cast a value or align an object of the type Cell to the context of the worksheet |
| CellsByValue | Searches for cells that contain the specified value and returns a list of these cells. |
| ClearActiveStyle | Clears the active style of the worksheet. All later added calls will contain no style unless another active style is set |
| Copy | Creates a (dereferenced) deep copy of this worksheet |
| Equals | Determines whether the specified object is equal to the current object. (Inherited from Object) |
 | Finalize | Allows an object to try to free resources and perform other cleanup operations before it is reclaimed by garbage collection. (Inherited from Object) |
| FirstCellByValue | Searches for the first occurrence of the value. |
| FirstOrDefaultCell | Searches for the first occurrence of the expression. Example: var cell = worksheet.FindCell(c => c.Value?.ToString().Contains("searchValue")); |
| GetBoundaryDataNumber | Gets either the minimum or maximum row or column number, considering only calls with data |
| GetBoundaryNumber | Gets either the minimum or maximum row or column number, considering all available data |
| GetCell(CellAddress) | Gets the cell of the specified address |
| GetCell(Int32, Int32) | Gets the cell of the specified column and row number (zero-based) |
| GetColumn(Int32) | Gets a column as list of cell objects |
| GetColumn(String) | Gets a column as list of cell objects |
| GetCurrentColumnNumber | Gets the current column number (zero based) |
| GetCurrentRowNumber | Gets the current row number (zero based) |
| GetFirstCellAddress | Gets the first existing cell in the current worksheet (bottom right) |
| GetFirstColumnNumber | Gets the first existing column number in the current worksheet (zero-based) |
| GetFirstDataCellAddress | Gets the first existing cell with data in the current worksheet (bottom right) |
| GetFirstDataColumnNumber | Gets the first existing column number with data in the current worksheet (zero-based) |
| GetFirstDataRowNumber | Gets the first existing row number with data in the current worksheet (zero-based) |
| GetFirstRowNumber | Gets the first existing row number in the current worksheet (zero-based) |
| GetHashCode | Serves as the default hash function. (Inherited from Object) |
| GetLastCellAddress | Gets the last existing cell in the current worksheet (bottom right) |
| GetLastColumnNumber | Gets the last existing column number in the current worksheet (zero-based) |
| GetLastDataCellAddress | Gets the last existing cell with data in the current worksheet (bottom right) |
| GetLastDataColumnNumber | Gets the last existing column number with data in the current worksheet (zero-based) |
| GetLastDataRowNumber | Gets the last existing row number with data in the current worksheet (zero-based) |
| GetLastRowNumber | Gets the last existing row number in the current worksheet (zero-based) |
| GetMaxRow | Gets the maximum row coordinate either from cell data, height definitions or hidden rows |
| GetMinRow | Gets the minimum row coordinate either from cell data, height definitions or hidden rows |
| GetRow | Gets a row as list of cell objects |
| GetType | Gets the Type of the current instance. (Inherited from Object) |
| GetUnusedWorksheetName | Determines the next unused worksheet name in the passed workbook |
| GoToNextColumn | Moves the current position to the next column |
| GoToNextColumn(Int32, Boolean) | Moves the current position to the next column with the number of cells to move |
| GoToNextRow | Moves the current position to the next row (use for a new line) |
| GoToNextRow(Int32, Boolean) | Moves the current position to the next row with the number of cells to move (use for a new line) |
| HasCell(CellAddress) | Gets whether the specified address exists in the worksheet. Existing means that a value was stored at the address |
| HasCell(Int32, Int32) | Gets whether the specified address exists in the worksheet. Existing means that a value was stored at the address |
| InsertColumn | Inserts 'count' columns right of the specified 'columnNumber'. Existing cells are moved to the right by the number of new columns. The inserted, new columns inherits the style of the original cell at the defined column number. The inserted cells are empty. The values can be set later |
| InsertRow | Inserts 'count' rows below the specified 'rowNumber'. Existing cells are moved down by the number of new rows. The inserted, new rows inherits the style of the original cell at the defined row number. The inserted cells are empty. The values can be set later |
| MemberwiseClone | Creates a shallow copy of the current Object. (Inherited from Object) |
| MergeCells(CellRange) | Merges the defined cell range |
| MergeCells(String) | Merges the defined cell range |
| MergeCells(CellAddress, CellAddress) | Merges the defined cell range |
| RecalculateAutoFilter | Method to recalculate the auto filter (columns) of this worksheet. This is an internal method. There is no need to use it |
| RecalculateColumns | Method to recalculate the collection of columns of this worksheet. This is an internal method. There is no need to use it |
| RemoveAllowedActionOnSheetProtection | Removes an allowed action on the current worksheet or its cells |
| RemoveAutoFilter | Removes auto filters from the worksheet |
| RemoveCell(String) | Removes a previous inserted cell at the defined address |
| RemoveCell(Int32, Int32) | Removes a previous inserted cell at the defined address |
| RemoveHiddenColumn(Int32) | Sets a previously defined, hidden column as visible again |
| RemoveHiddenColumn(String) | Sets a previously defined, hidden column as visible again |
| RemoveHiddenRow | Sets a previously defined, hidden row as visible again |
| RemoveMergedCells | Removes the defined merged cell range |
| RemoveRowHeight | Removes the defined, non-standard row height |
| RemoveSelectedCells | Removes the cell selection of this worksheet |
| ReplaceCellValue | Replaces all occurrences of 'oldValue' with 'newValue' and returns the number of replacements. |
| ResetColumn | Resets the defined column, if existing. The corresponding instance will be removed from Columns |
| ResetSplit | Resets splitting of the worksheet into panes, as well as their freezing |
| ResolveMergedCells |
Method to resolve all merged cells of the worksheet. Only the value of the very first cell of the locked cells range will be visible. The other values are still present (set to EMPTY) but will not be stored in the worksheet. This is an internal method. There is no need to use it |
| SanitizeWorksheetName | Sanitizes a worksheet name |
| SetActiveStyle | Sets the active style of the worksheet. This style will be assigned to all later added cells |
| SetAutoFilter(String) | Sets the column auto filter within the defined column range |
| SetAutoFilter(Int32, Int32) | Sets the column auto filter within the defined column range |
| SetColumnDefaultStyle(Int32, Style) | Sets the default column style of the passed column number (zero-based) |
| SetColumnDefaultStyle(String, Style) | Sets the default column style of the passed column address |
| SetColumnHiddenState | Sets the defined column as hidden or visible |
| SetColumnWidth(Int32, Single) | Sets the width of the passed column number (zero-based) |
| SetColumnWidth(String, Single) | Sets the width of the passed column address |
| SetCurrentCellAddress(String) | Set the current cell address |
| SetCurrentCellAddress(Int32, Int32) | Set the current cell address |
| SetCurrentColumnNumber | Sets the current column number (zero based) |
| SetCurrentRowNumber | Sets the current row number (zero based) |
| SetHorizontalSplit(Single, CellAddress, WorksheetWorksheetPane) | Sets the horizontal split of the worksheet into two panes. The measurement in characters cannot be used to freeze panes |
| SetHorizontalSplit(Int32, Boolean, CellAddress, WorksheetWorksheetPane) | Sets the horizontal split of the worksheet into two panes. The measurement in rows can be used to split and freeze panes |
| SetRowHeight | Sets the height of the passed row number (zero-based) |
| SetRowHiddenState | Sets the defined row as hidden or visible |
| SetSelectedCells(CellRange) | Obsolete. Sets a single range of selected cells on this worksheet. All existing ranges will be removed |
| SetSelectedCells(String) | Obsolete. Sets a single range of selected cells on this worksheet. All existing ranges will be removed. Null will remove all selected cells |
| SetSelectedCells(CellAddress, CellAddress) | Obsolete. Sets the selected cells on this worksheet |
| SetSheetName(String) | Validates and sets the worksheet name |
| SetSheetName(String, Boolean) | Sets the name of the worksheet |
| SetSheetProtectionPassword | Sets or removes the password for worksheet protection. If set, UseSheetProtection will be also set to true |
| SetSplit(NullableSingle, NullableSingle, CellAddress, WorksheetWorksheetPane) | Sets the horizontal and vertical split of the worksheet into four panes. The measurement in characters cannot be used to freeze panes |
| SetSplit(NullableInt32, NullableInt32, Boolean, CellAddress, WorksheetWorksheetPane) | Sets the horizontal and vertical split of the worksheet into four panes. The measurement in rows and columns can be used to split and freeze panes |
| SetStyle(CellAddress, Style) | Sets the passed style on the passed (singular) cell address. If the cell is already existing, the style will be added or replaced Sets the passed style on the passed (singular) cell address. If the cell is already existing, the style will be added or replaced |
| SetStyle(CellRange, Style) | Sets the passed style on the passed cell range. If cells are already existing, the style will be added or replaced |
| SetStyle(String, Style) | Sets the passed style on the passed address expression. Such an expression may be a single cell or a cell range Sets the passed style on the passed address expression. Such an expression may be a single cell or a cell range Sets the passed style on the passed address expression. Such an expression may be a single cell or a cell range |
| SetStyle(CellAddress, CellAddress, Style) | Sets the passed style on the passed cell range, derived from a start and end address. If cells are already existing, the style will be added or replaced Sets the passed style on the passed cell range, derived from a start and end address. If cells are already existing, the style will be added or replaced |
| SetVerticalSplit(Single, CellAddress, WorksheetWorksheetPane) | Sets the vertical split of the worksheet into two panes. The measurement in characters cannot be used to freeze panes |
| SetVerticalSplit(Int32, Boolean, CellAddress, WorksheetWorksheetPane) | Sets the vertical split of the worksheet into two panes. The measurement in columns can be used to split and freeze panes |
| SetZoomFactor | Sets a zoom factor for a given WorksheetSheetViewType. If AUTO_ZOOM_FACTOR, the zoom factor is set to automatic |
| ToString | Returns a string that represents the current object. (Inherited from Object) |
| WorksheetExists | Checks whether a worksheet with the given name exists |
Fields| Name | Description | |
|---|---|---|
 | AUTO_ZOOM_FACTOR | Automatic zoom factor of a worksheet |
| DEFAULT_COLUMN_WIDTH | Default column width as constant |
| DEFAULT_ROW_HEIGHT | Default row height as constant |
| MAX_COLUMN_NUMBER | Maximum column number (zero-based) as constant |
| MAX_COLUMN_WIDTH | Maximum column width as constant |
| MAX_ROW_HEIGHT | Maximum row height as constant |
| MAX_ROW_NUMBER | Maximum row number (zero-based) as constant |
| MAX_WORKSHEET_NAME_LENGTH | Maximum number of characters a worksheet name can have |
| MAX_ZOOM_FACTOR | Maximum zoom factor of a worksheet |
| MIN_COLUMN_NUMBER | Minimum column number (zero-based) as constant |
| MIN_COLUMN_WIDTH | Minimum column width as constant |
| MIN_ROW_HEIGHT | Minimum row height as constant |
| MIN_ROW_NUMBER | Minimum row number (zero-based) as constant |
| MIN_ZOOM_FACTOR | Minimum zoom factor of a worksheet |
See Also