Table of Contents

Class Worksheet

Namespace
FileFormat.Cells
Assembly
FileFormat.Cells.dll

Represents a worksheet within an Excel file, providing methods to manipulate its content.

public sealed class Worksheet
Inheritance
Worksheet
Inherited Members

Fields

DefaultColumnWidth 

public const double DefaultColumnWidth = 8.43

Field Value

double

DefaultRowHeight 

public const double DefaultRowHeight = 15

Field Value

double

Properties

Cells

Gets the CellIndexer for the worksheet. This property provides indexed access to the cells of the worksheet.

public CellIndexer Cells { get; }

Property Value

CellIndexer

The CellIndexer for the worksheet.

FreezePanesColumn

Gets the column index where the pane is frozen in the worksheet. Returns 0 if no columns are frozen.

public int FreezePanesColumn { get; }

Property Value

int

Remarks

This property retrieves the value of HorizontalSplit from the Pane element in the SheetView.

FreezePanesRow

Gets the row index where the pane is frozen in the worksheet. Returns 0 if no rows are frozen.

public int FreezePanesRow { get; }

Property Value

int

Remarks

This property retrieves the value of VerticalSplit from the Pane element in the SheetView.

Name

Gets or sets the name of the worksheet. This property performs several checks to ensure the integrity and validity of the worksheet and workbook parts.

public string Name { get; set; }

Property Value

string

The name of the worksheet.

Exceptions

InvalidOperationException

Thrown if the WorksheetPart is null, if the WorkbookPart is not found as a parent, if the ID of the part is null or empty, or if no sheet is found with the specified ID.

ArgumentException

Thrown when attempting to set the name with a null or empty value.

Methods

AddComment(string, Comment)

Adds or updates a comment in a specified cell within the worksheet. If the cell already has a comment, it updates the existing comment text. If there is no comment, it creates a new one.

public void AddComment(string cellReference, Comment comment)

Parameters

cellReference string

The cell reference where the comment should be added, e.g., "A1".

comment Comment

The comment object containing the author and the text of the comment.

Remarks

This method ensures that the worksheet comments part exists before adding or updating a comment. It also manages the authors list to ensure that each author is only added once and reuses the existing author index if available. Usage of this method requires that the workbook and worksheet are properly initialized and that the worksheet part is correctly associated.

AddDropdownListValidation(string, string[])

Adds a dropdown list validation to a specified cell. The dropdown list contains the options provided.

public void AddDropdownListValidation(string cellReference, string[] options)

Parameters

cellReference string

The cell reference in A1 notation where the dropdown should be added.

options string[]

An array of string values that will appear as options in the dropdown list.

Remarks

This method creates a data validation rule that restricts input to the cell to the provided list of options.

Exceptions

ArgumentException

Thrown when cellReference is null or invalid, or if options is empty or null.

AddImage(Image, int, int, int, int)

Adds an image to the worksheet.

public void AddImage(Image image, int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex)

Parameters

image Image

The image to be added.

startRowIndex int

The starting row index.

startColumnIndex int

The starting column index.

endRowIndex int

The ending row index.

endColumnIndex int

The ending column index.

ApplyValidation(string, ValidationRule) 

public void ApplyValidation(string cellReference, ValidationRule rule)

Parameters

cellReference string
rule ValidationRule

AutoFitColumn(string)

Auto-fits the specified column to match its content width by setting the BestFit and CustomWidth attributes.

public void AutoFitColumn(string columnName)

Parameters

columnName string

The column name (e.g., "A", "B") to auto-fit.

CopyRange(Range, string) 

public void CopyRange(Range sourceRange, string targetStartCellReference)

Parameters

sourceRange Range
targetStartCellReference string

ExtractImages()

Extracts images from the worksheet.

public List<Image> ExtractImages()

Returns

List<Image>

A list of images present in the worksheet.

FreezePane(int, int)

Freezes the specified rows and/or columns of the worksheet.

public void FreezePane(int rowsToFreeze, int columnsToFreeze)

Parameters

rowsToFreeze int

The number of rows to freeze. Set to 0 if no row freezing is needed.

columnsToFreeze int

The number of columns to freeze. Set to 0 if no column freezing is needed.

GetCell(string)

Retrieves a cell based on its reference.

public Cell GetCell(string cellReference)

Parameters

cellReference string

The cell reference in A1 notation.

Returns

Cell

The cell at the specified reference.

GetColumnCount()

Retrieves the total number of columns in the worksheet.

public int GetColumnCount()

Returns

int

The total number of columns in the worksheet. Returns 0 if the worksheet or sheet data is null.

Remarks

This method calculates and returns the total number of columns present in the worksheet. It does this by analyzing the cell references in each row to determine the unique column indices in use. If the worksheet or sheet data is null, indicating an improperly initialized or corrupted worksheet, the method returns 0. This is useful for dynamically determining the size of the worksheet and iterating through its columns.

Exceptions

InvalidOperationException

Thrown when the Worksheet or WorksheetPart is null.

GetColumnHeading(string)

Gets the column heading for a specified cell in the worksheet.

public string? GetColumnHeading(string cellName)

Parameters

cellName string

The name of the cell (e.g., "A1").

Returns

string

The text of the column heading, or null if the column does not exist.

Exceptions

ArgumentException

Thrown when the cellName is null or empty.

InvalidOperationException

Thrown when the WorkbookPart is not found, the sheet is not found, the column name is invalid, no header cell is found, or the SharedStringTablePart is missing.

IndexOutOfRangeException

Thrown when the shared string index is out of range.

GetColumnWidth(uint)

Retrieves the width of the specified column in the worksheet. If the width of the column has been explicitly set, it returns that value; otherwise, it returns the default column width.

public double GetColumnWidth(uint columnIndex)

Parameters

columnIndex uint

The 1-based index of the column for which the width is to be retrieved.

Returns

double

The width of the specified column. If the column's width is explicitly set, that value is returned; otherwise, the default column width is returned.

Exceptions

ArgumentOutOfRangeException

Thrown if the columnIndex is less than 1.

InvalidOperationException

Thrown if the worksheet part or the worksheet is null.

GetHiddenColumns() 

public List<uint> GetHiddenColumns()

Returns

List<uint>

GetHiddenRows() 

public List<uint> GetHiddenRows()

Returns

List<uint>

GetRange(string, string)

Retrieves a range of cells specified by the start and end cell references in A1 notation.

public Range GetRange(string startCellReference, string endCellReference)

Parameters

startCellReference string

The start cell reference in A1 notation.

endCellReference string

The end cell reference in A1 notation.

Returns

Range

A Range object representing the specified range of cells.

GetRange(uint, uint, uint, uint)

Retrieves a range of cells specified by the start and end row and column indices.

public Range GetRange(uint startRowIndex, uint startColumnIndex, uint endRowIndex, uint endColumnIndex)

Parameters

startRowIndex uint

The starting row index of the range.

startColumnIndex uint

The starting column index of the range.

endRowIndex uint

The ending row index of the range.

endColumnIndex uint

The ending column index of the range.

Returns

Range

A Range object representing the specified range of cells.

GetRowCount()

Retrieves the total number of rows in the worksheet.

public int GetRowCount()

Returns

int

The total number of rows in the worksheet. Returns 0 if the worksheet or sheet data is null.

Remarks

This method calculates and returns the total number of rows present in the worksheet. It does this by counting the number of Row elements within the SheetData. If the worksheet or sheet data is null, indicating an improperly initialized or corrupted worksheet, the method returns 0. This is useful for dynamically determining the size of the worksheet and iterating through its rows.

Exceptions

InvalidOperationException

Thrown when the Worksheet or WorksheetPart is null.

GetRowHeight(uint)

Retrieves the height of the specified row in the worksheet. If the height of the row has been explicitly set, it returns that value; otherwise, it returns the default row height.

public double GetRowHeight(uint rowIndex)

Parameters

rowIndex uint

The 1-based index of the row for which the height is to be retrieved.

Returns

double

The height of the specified row. If the row's height is explicitly set, that value is returned; otherwise, the default row height is returned.

Exceptions

ArgumentOutOfRangeException

Thrown if the rowIndex is less than 1.

InvalidOperationException

Thrown if the worksheet part or the worksheet is null.

GetSheetIndex()

Retrieves the index of the current worksheet within the workbook. This method locates the worksheet within the workbook's collection of sheets and returns its index. Note that the SheetId property of a worksheet is different from its index in the workbook's sheet collection.

public int GetSheetIndex()

Returns

int

The index of the sheet within the workbook. This is not the same as the SheetId.

Exceptions

InvalidOperationException

Thrown if no WorkbookPart is found, or if the worksheet is not found in the workbook.

GetValidationRule(string)

Retrieves the validation rule applied to a specific cell in the worksheet.

public ValidationRule GetValidationRule(string cellReference)

Parameters

cellReference string

The reference of the cell for which to retrieve the validation rule, e.g., "A1".

Returns

ValidationRule

The validation rule applied to the specified cell if one exists; otherwise, null.

Remarks

This method searches for a data validation rule that applies to the specified cell. It iterates through all the data validation rules present in the worksheet. If a rule is found that includes the cell reference, the method constructs and returns a corresponding ValidationRule object. If no such rule is found, the method returns null. This is useful for dynamically determining validation criteria or rules applied to specific cells.

Exceptions

InvalidOperationException

Thrown when the Worksheet part is not loaded or is null.

HideColumn(string)

Hides a specific column in the worksheet.

public void HideColumn(string columnName)

Parameters

columnName string

The letter of the column to hide. Cannot be null or whitespace.

Remarks

This method hides a single column in the worksheet, specified by the column name. If the column does not already exist in the worksheet's column collection, it is created with the Hidden property set to true. If the column already exists, the Hidden property is set to true, effectively hiding the column. The method ensures that the specified column is hidden, whether it was previously defined or not.

Exceptions

ArgumentNullException

Thrown when columnName is null or whitespace, indicating that the column name cannot be null or empty.

InvalidOperationException

Thrown when the Worksheet or WorksheetPart is null.

HideRow(uint)

Hides a specific row in the worksheet.

public void HideRow(uint rowIndex)

Parameters

rowIndex uint

The one-based index of the row to hide. Must be greater than 0.

Remarks

This method hides a single row specified by the rowIndex. If the row does not exist in the worksheet, it is created and then hidden. This ensures that the specified row is effectively hidden regardless of its initial existence. The method checks the existence of the row and sets the Hidden property accordingly.

Exceptions

ArgumentOutOfRangeException

Thrown when rowIndex is 0, as the row index must be greater than 0.

InvalidOperationException

Thrown when the Worksheet or WorksheetPart is null, or if SheetData is null.

HideRows(uint, uint)

Hides a range of rows in the worksheet.

public void HideRows(uint startRowIndex, uint numberOfRows)

Parameters

startRowIndex uint

The one-based index of the first row to hide. Must be greater than 0.

numberOfRows uint

The number of rows to hide, starting from the startRowIndex. Must be greater than 0.

Remarks

This method hides rows in a consecutive range starting from startRowIndex and spanning numberOfRows. If a row within the specified range does not exist, it is created and then hidden, ensuring that the entire specified range is effectively hidden. The method iterates through each row in the specified range and sets or creates the Hidden property as true.

Exceptions

ArgumentOutOfRangeException

Thrown when startRowIndex or numberOfRows is 0.

InvalidOperationException

Thrown when Worksheet or WorksheetPart is null, or if SheetData is null.

InsertColumn(string)

Inserts a new column to the right of a specified starting column.

public void InsertColumn(string startColumn)

Parameters

startColumn string

The letter of the starting column for insertion. Cannot be null or whitespace.

Remarks

This method inserts a new column to the right of the specified starting column in the worksheet. It shifts existing columns to the right to make space for the new column. All cell references in each row to the right of the starting column are adjusted accordingly to maintain data integrity. This is useful for dynamically adding columns to the worksheet without overwriting existing data.

Exceptions

ArgumentException

Thrown when startColumn is null or whitespace, indicating that the starting column name cannot be null or empty.

InsertColumns(string, int)

Inserts a specified number of new columns to the right of a specified starting column.

public void InsertColumns(string startColumn, int numberOfColumns)

Parameters

startColumn string

The letter of the starting column for insertion. Cannot be null or whitespace.

numberOfColumns int

The number of columns to insert. Must be greater than 0.

Remarks

This method inserts a specified number of new columns to the right of the specified starting column in the worksheet. It shifts existing columns to the right to make space for the new columns. All cell references in each row to the right of the starting column are adjusted accordingly to maintain data integrity. This is useful for dynamically adding columns to the worksheet without overwriting existing data.

Exceptions

ArgumentException

Thrown when startColumn is null or whitespace, indicating that the starting column name cannot be null or empty.

ArgumentOutOfRangeException

Thrown when numberOfColumns is less than or equal to 0, indicating that the number of columns to insert must be greater than 0.

InvalidOperationException

Thrown when the Worksheet, WorksheetPart, or SheetData is null.

InsertRow(uint)

Inserts a new row into the worksheet at the specified row index.

public void InsertRow(uint rowIndex)

Parameters

rowIndex uint

The one-based index at which to insert the new row. Existing rows starting from this index will be shifted down.

Remarks

The method will shift down all existing rows and their cells starting from the specified row index. Each cell's reference in the shifted rows will also be updated to reflect the new row index. If a row already exists at the specified index, it will be shifted down along with subsequent rows.

Exceptions

ArgumentOutOfRangeException

Thrown when the provided row index is 0, as row indices in Excel are 1-based.

InvalidOperationException

Thrown when the Worksheet or WorksheetPart is null, or if the SheetData is not available in the Worksheet.

InsertRows(uint, uint)

Inserts a specified number of new rows into the worksheet starting at a given row index.

public void InsertRows(uint startRowIndex, uint numberOfRows)

Parameters

startRowIndex uint

The one-based index of the row from which new rows should start being inserted. Must be greater than 0.

numberOfRows uint

The number of rows to insert. Must be greater than 0.

Remarks

This method inserts a number of new rows into the worksheet starting at the specified startRowIndex. Existing rows starting from this index are shifted downwards to make space for the new rows. This includes adjusting the row indices and references of existing cells to maintain data integrity. The method is useful in scenarios where rows need to be dynamically added to the worksheet without overwriting existing data.

Exceptions

ArgumentOutOfRangeException

Thrown when either startRowIndex or numberOfRows is 0, as both values must be greater than 0.

InvalidOperationException

Thrown when the Worksheet, WorksheetPart, or SheetData is null.

IsColumnHidden(string)

Checks if a specific column in the worksheet is hidden.

public bool IsColumnHidden(string columnName)

Parameters

columnName string

The letter of the column to check for hidden status. Cannot be null or whitespace.

Returns

bool

true if the specified column is hidden; otherwise, false. Returns false if the worksheet or sheet data is null, or if the column doesn't exist.

Remarks

This method determines whether a specified column in the worksheet is hidden. It checks the existence of the column in the worksheet's Columns collection and its Hidden property. If the worksheet or Columns collection is null, or if the column doesn't exist, the method returns false, indicating that the column is not hidden. This is useful for checking the visibility status of columns in the worksheet.

Exceptions

ArgumentNullException

Thrown when columnName is null or whitespace, indicating that the column name cannot be null or empty.

InvalidOperationException

Thrown when the Worksheet or WorksheetPart is null.

IsProtected()

Determines whether the worksheet is protected. This method checks for the presence of a SheetProtection element within the worksheet to ascertain its protection status.

public bool IsProtected()

Returns

bool

A boolean value indicating whether the worksheet is protected. Returns true if the worksheet is protected, otherwise false.

Remarks

A worksheet is considered protected if there is at least one SheetProtection element present in its elements.

IsRowHidden(uint)

Checks if a specific row in the worksheet is hidden.

public bool IsRowHidden(uint rowIndex)

Parameters

rowIndex uint

The one-based index of the row to check for hidden status.

Returns

bool

true if the specified row is hidden; otherwise, false. Returns false if the worksheet or sheet data is null, or if the row doesn't exist.

Remarks

This method determines whether a specified row in the worksheet is hidden. It checks the existence of the row and its Hidden property. If the worksheet or sheet data is null, or if the row doesn't exist, the method returns false, indicating that the row is not hidden. This is useful for checking the visibility status of rows in the worksheet.

Exceptions

InvalidOperationException

Thrown when the Worksheet or WorksheetPart is null.

MergeCells(string, string)

Merges a range of cells specified by the start and end cell references in A1 notation. This method creates a merged cell area that spans from the start cell to the end cell.

public void MergeCells(string startCellReference, string endCellReference)

Parameters

startCellReference string

The start cell reference in A1 notation for the merge range.

endCellReference string

The end cell reference in A1 notation for the merge range.

Remarks

If a MergeCells element already exists in the worksheet, this method appends a new merge cell reference to it. If no MergeCells element exists, it creates a new one and then appends the merge cell reference. After defining the merge cell range, it saves the changes to the WorksheetPart.

Exceptions

ArgumentException

Thrown when either startCellReference or endCellReference is null, empty, or invalid.

ProtectSheet(string) 

public void ProtectSheet(string password)

Parameters

password string

SetColumnWidth(string, double)

Sets the width of the specified column in the worksheet.

public void SetColumnWidth(string columnName, double width)

Parameters

columnName string

The name of the column (e.g., "A", "B", "C") for which the width is to be set.

width double

The width to set for the specified column.

Exceptions

ArgumentNullException

Thrown if the columnName is null or empty.

ArgumentOutOfRangeException

Thrown if the width is less than 0 or if the columnName is invalid or represents a column index out of range.

InvalidOperationException

Thrown if the worksheet part or the worksheet is null.

SetRowHeight(uint, double)

Sets the height of the specified row in the worksheet.

public void SetRowHeight(uint rowIndex, double height)

Parameters

rowIndex uint

The 1-based index of the row for which the height is to be set.

height double

The height to set for the specified row.

Exceptions

ArgumentOutOfRangeException

Thrown if the rowIndex is less than 1 or if the height is less than 0.

InvalidOperationException

Thrown if the worksheet part or the worksheet is null.

UnhideColumn(string)

Unhides a specific column in the worksheet.

public void UnhideColumn(string columnName)

Parameters

columnName string

The letter of the column to unhide. Cannot be null or whitespace.

Remarks

This method unhides a single column specified by the column name. If the column does not exist in the worksheet's column collection, it is added with the Hidden property set to false. If the column exists, the Hidden property is set to false. This ensures that the specified column is effectively unhidden regardless of its initial state. The method checks for the existence of the column and adjusts the Hidden property accordingly.

Exceptions

ArgumentNullException

Thrown when columnName is null or whitespace, as the column name cannot be null or empty.

InvalidOperationException

Thrown when the Worksheet or WorksheetPart is null.

UnhideColumns(string, int)

Unhides a range of columns in the worksheet.

public void UnhideColumns(string startColumn, int numberOfColumns)

Parameters

startColumn string

The letter of the starting column to unhide. Cannot be null or empty.

numberOfColumns int

The number of columns to unhide, starting from the startColumn. Must be greater than 0.

Remarks

This method unhides a specified range of columns in the worksheet. It calculates the column indices based on the starting column letter and the number of columns to unhide. If the columns within the specified range are not currently hidden or do not exist, no action is taken for those columns. The method only modifies columns that are defined and hidden.

Exceptions

ArgumentException

Thrown when startColumn is null or whitespace.

ArgumentOutOfRangeException

Thrown when numberOfColumns is less than or equal to 0, as the number of columns to unhide must be greater than 0.

InvalidOperationException

Thrown when the Worksheet or WorksheetPart is null.

UnhideRow(uint)

Unhides a single row in the worksheet.

public void UnhideRow(uint rowIndex)

Parameters

rowIndex uint

The one-based index of the row to unhide.

Remarks

This method unhides the row at the specified rowIndex. It is a convenience method that internally calls UnhideRows(uint, uint) with the numberOfRows parameter set to 1. If the row at the specified index does not exist or is already visible, the method leaves it unaffected.

Exceptions

ArgumentOutOfRangeException

Thrown when the rowIndex is 0, as Excel row indices are 1-based.

UnhideRows(uint, uint)

Unhides a specified range of rows in the worksheet.

public void UnhideRows(uint startRowIndex, uint numberOfRows)

Parameters

startRowIndex uint

The one-based index of the first row to unhide.

numberOfRows uint

The number of rows to unhide, starting from the startRowIndex.

Remarks

This method unhides rows in a consecutive range starting from startRowIndex and covering numberOfRows. If any row within the specified range does not exist or is already visible, the method leaves it unaffected. The method iterates through each row in the specified range and sets its Hidden property to false.

Exceptions

ArgumentOutOfRangeException

Thrown when the startRowIndex is 0 (since Excel row indices are 1-based) or when numberOfRows is 0 (as at least one row must be specified to unhide).

InvalidOperationException

Thrown when either the Worksheet or WorksheetPart is null, indicating that the worksheet has not been properly initialized, or when SheetData is null, indicating that the worksheet does not contain any rows.

UnprotectSheet()

Removes protection from the worksheet, if it is currently protected. This method checks for the presence of a SheetProtection element and removes it to unprotect the sheet.

public void UnprotectSheet()

Remarks

If the worksheet is protected (indicated by the presence of a SheetProtection element), this method removes the protection. After altering the protection status, it saves the changes to the worksheet. If the worksheet is not protected, this method performs no action.

Exceptions

InvalidOperationException

Thrown if an attempt is made to remove protection but no SheetProtection element is found. This should not normally occur, as the method first checks if the sheet is protected.