Click or drag to resize

ExcelWorksheet Class

Excel worksheet is a table with additional properties, identified by a unique name.
Inheritance Hierarchy

Namespace:  GemBox.Spreadsheet
Assembly:  GemBox.Spreadsheet (in GemBox.Spreadsheet.dll) Version:
public sealed class ExcelWorksheet

The ExcelWorksheet type exposes the following members.

Public propertyCode exampleCells
Gets all the cells in the sheet.
Public propertyCharts
Gets the worksheet charts. Not supported in XLS.
Public propertyColumns
Gets collection of all columns (ExcelColumn) in the worksheet.
Public propertyComments
Gets comments in this sheet.
Public propertyConditionalFormatting

Gets the conditional formatting rules applied on cell ranges of this sheet.

Currently supported in XLSX only.

Public propertyDataValidations
Gets the collection that expresses all data validation information for cells in a sheet which have data validation features applied. Supported in XLSX only.
Public propertyDefaultColumnWidth
Gets or sets the default column width in 1/256th of the width of the zero character in default font.
Public propertyDefaultRowHeight
Gets or sets the default row height in twips (1/20th of a point). Not supported in XLS and ODS.
Public propertyCode exampleFilter

Gets or sets the active AutoFilter. Returns if no AutoFilter is active.

AutoFilter is used to store filter settings and to apply filter on a range of cells.

Active AutoFilter is loaded from and saved to XLSX file.

Public propertyHasHeadersFooters
Returns true if worksheet has headers/footers.
Public propertyHasSplitOrFreezePanes
Returns true if worksheet has split or freeze panes.
Public propertyHeadersFooters
Gets or sets headers/footers for the sheet.
Public propertyCode exampleHorizontalPageBreaks
Gets collection of all horizontal page breaks (HorizontalPageBreak) in the worksheet.
Public propertyHyperlinks
Gets hyperlinks in this sheet.
Public propertyCode exampleIgnoredErrors

Gets the ignored errors applied on cell ranges of this sheet.

Currently supported in XLS and XLSX.

Public propertyIndex
Gets worksheet zero-based index.
Public propertyName
Gets or sets worksheet name.
Public propertyCode exampleNamedRanges
Gets NamedRangeCollection containing descriptive names which are used to represent cells, ranges of cells, formulas, or constant values.
Public propertyPanes
Gets or sets the panes in the worksheet.
Public propertyParent
Gets the parent ExcelFile of this ExcelWorksheet instance.
Public propertyCode examplePictures
Gets the worksheet pictures.
Public propertyCode examplePivotTables

Gets the collection of all PivotTables contained in this worksheet.

Currently supported in XLSX only.

Public propertyPrintOptions
Contains MS Excel print and print related options.
Public propertyProtected
Gets or sets the worksheet protection flag.
Public propertyProtectedRanges
Gets the protected ranges. Supported in XLSX only.
Public propertyProtectionSettings
Gets or sets worksheet protection properties. This settings will be used only if Protected is set to ; Otherwise it will be ignored. Supported in XLSX only.
Public propertyRows
Gets collection of all rows (ExcelRow) in the worksheet.
Public propertySelectedRanges

Gets the selected ranges of cells in the worksheet.

Supported only in XLSX.

Public propertyCode exampleSort

Gets or sets the active SortState. Returns if no SortState is active.

SortState is used to store sort settings and to apply sort on a range of cells.

Active SortState is loaded from and saved to XLSX file.

Public propertyTabColor

Gets or sets the worksheet tab color.

Supported only in XLSX and HTML.

Public propertyTables
Gets the collection of all Tables contained in this worksheet.
Public propertyType
Gets worksheet type.
Public propertyCode exampleVerticalPageBreaks
Gets collection of all vertical page breaks (VerticalPageBreak) in the worksheet.
Public propertyViewOptions
Contains MS Excel display and view related options.
Public propertyVisibility
Gets or sets the worksheet visible property.
Public methodCalculate
Calculates formula for every excel cell in this worksheet.
Public methodCalculateMaxUsedColumns
Iterates all rows and finds maximum number of used columns.
Public methodClear
Clears this worksheet.
Public methodCode exampleCreateDataTable
Creates a DataTable and fills it with data from this ExcelWorksheet in a manner specified by create options, see CreateDataTableOptions.
Public methodCode exampleExtractToDataTable
Extracts data to DataTable with specified extract options, see ExtractToDataTableOptions.
Public methodGetUsedCellRange
Calculates cell range that is used in the worksheet.
Public methodInsertDataTable(DataTable)
Inserts a DataTable into the current worksheet.
Public methodInsertDataTable(DataTable, InsertDataTableOptions)
Inserts a DataTable into the current worksheet.

Worksheet in Microsoft Excel has limited size. Number of rows (ExcelRow) is limited to ExcelFile.MaxRows. Number of columns (ExcelColumn) is limited to ExcelFile.MaxColumns. A specific cell (ExcelCell) can be accessed either trough ExcelRow.Cells, ExcelColumn.Cells or ExcelWorksheet.Cells property. Whichever property used, there are two distinct methods of getting a cell reference; using name and using index. For example, full name of cell in top left corner of a worksheet is "A1". Translated to indexes, same cell would be 0,0 (zero row and zero column). If using ExcelRow.Cells or ExcelColumn.Cells to access a specific cell, only partial name or partial index must be used, providing unknown column or row information.

Note that the cells are internally allocated in rows and not in columns. ExcelColumn objects are created only if they have non-standard width or style, or they are accessed directly. So, while ExcelRowCollection.Count shows number of rows occupied with data, ExcelColumnCollection.Count does not say which Column is the last one occupied with data!

If you want to read all data in a sheet, use ExcelRow.AllocatedCells property.

If you want to find last column occupied with data, use CalculateMaxUsedColumns method.


Look at following code for cell referencing examples:

ExcelWorksheet ws = excelFile.Worksheets.ActiveWorksheet;

ws.Cells["B2"].Value = "Cell B2.";
ws.Cells[6, 0].Value = "Cell in row 7 and column A.";

ws.Rows[2].Cells[0].Value = "Cell in row 3 and column A.";
ws.Rows["4"].Cells["B"].Value = "Cell in row 4 and column B.";

ws.Columns[2].Cells[4].Value = "Cell in column C and row 5.";
ws.Columns["AA"].Cells["6"].Value = "Cell in AA column and row 6.";
See Also