ExcelWorksheet Class

Namespace:
GemBox.Spreadsheet
Assembly:
GemBox.Spreadsheet.dll

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

public sealed class ExcelWorksheet
Public NotInheritable Class ExcelWorksheet
Inheritance:
System.Object
ExcelWorksheet
Remarks

Worksheet in Microsoft Excel has limited size. Number of rows (ExcelRow) is limited to GemBox.Spreadsheet.ExcelFile.MaxRows. Number of columns (ExcelColumn) is limited to GemBox.Spreadsheet.ExcelFile.MaxColumns. A specific cell (ExcelCell) can be accessed either trough Cells, Cells or 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 Cells or 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 AllocatedCells property.

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

Properties

BackgroundPictureFormat

Gets the worksheet background picture format.

public ExcelPictureFormat BackgroundPictureFormat { get; }
Public ReadOnly Property BackgroundPictureFormat As ExcelPictureFormat
Property Value
ExcelPictureFormat

The worksheet background picture format.

BackgroundPictureStream

Gets the worksheet background picture stream.

public MemoryStream BackgroundPictureStream { get; }
Public ReadOnly Property BackgroundPictureStream As MemoryStream
Property Value
System.IO.MemoryStream

The worksheet background picture stream.

Cells

Gets all the cells in the sheet.

public CellRange Cells { get; }
Public ReadOnly Property Cells As CellRange
Property Value
CellRange

All the cells in the sheet.

Charts

Gets the worksheet charts. Not supported in XLS.

public ExcelChartCollection Charts { get; }
Public ReadOnly Property Charts As ExcelChartCollection
Property Value
ExcelChartCollection

The worksheet charts.

CodeName

Gets or sets the name of the VbaModule that is bound to this ExcelWorksheet.

public string CodeName { get; set; }
Public Property CodeName As String
Property Value
System.String

Columns

Gets collection of all columns (ExcelColumn) in the worksheet.

public ExcelColumnCollection Columns { get; }
Public ReadOnly Property Columns As ExcelColumnCollection
Property Value
ExcelColumnCollection
Remarks

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 AllocatedCells property.

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

Comments

Gets comments in this sheet.

public ExcelCommentCollection Comments { get; }
Public ReadOnly Property Comments As ExcelCommentCollection
Property Value
ExcelCommentCollection

ConditionalFormatting

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

Currently supported in XLS and XLS only.

public ConditionalFormatRuleCollection ConditionalFormatting { get; }
Public ReadOnly Property ConditionalFormatting As ConditionalFormatRuleCollection
Property Value
ConditionalFormatRuleCollection

The conditional formatting rules applied on cell ranges of this sheet.

DataValidations

Gets the collection that expresses all data validation information for cells in a sheet which have data validation features applied.

public DataValidationCollection DataValidations { get; }
Public ReadOnly Property DataValidations As DataValidationCollection
Property Value
DataValidationCollection

DefaultColumnWidth

Gets or sets the default column width.

public int DefaultColumnWidth { get; set; }
Public Property DefaultColumnWidth As Integer
Property Value
System.Int32

The default column width.

Remarks

Value is a numerator (before a slash) of a fraction in a formula for an Open XML column width.

This value is used as a width for columns which do not have Width property explicitly set.

See Also

DefaultRowHeight

Gets or sets the default row height in twips (1/20th of a point). Not supported in ODS.

public int DefaultRowHeight { get; set; }
Public Property DefaultRowHeight As Integer
Property Value
System.Int32

The default row height in Twip.

Remarks

This value is used as height for rows which don't have Height property explicitly set.

See Also

EmbeddedObjects

Gets the worksheet embedded objects. Supported only in XLSX.

public ExcelEmbeddedObjectCollection EmbeddedObjects { get; }
Public ReadOnly Property EmbeddedObjects As ExcelEmbeddedObjectCollection
Property Value
ExcelEmbeddedObjectCollection

The worksheet embedded objects.

Filter

Gets or sets the active AutoFilter. Returns null 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 AutoFilter Filter { get; set; }
Public Property Filter As AutoFilter
Property Value
AutoFilter

The active AutoFilter or null if no AutoFilter is active.

Remarks

Use Filter() methods to create or get an AutoFilter instance.

Exceptions
System.ArgumentException

Filter range doesn't belong to this worksheet.

FilterMode

Gets a value indicating whether the worksheet is in filter mode.

Supported only in XLSX and XLSB.

public bool FilterMode { get; }
Public ReadOnly Property FilterMode As Boolean
Property Value
System.Boolean

true if the worksheet is in filter mode; otherwise, false.

FormControls

Gets the worksheet form controls.

public ExcelFormControlCollection FormControls { get; }
Public ReadOnly Property FormControls As ExcelFormControlCollection
Property Value
ExcelFormControlCollection

The worksheet form controls.

See Also

GroupShapes

Gets the worksheet group shapes.

public ExcelGroupShapeCollection GroupShapes { get; }
Public ReadOnly Property GroupShapes As ExcelGroupShapeCollection
Property Value
ExcelGroupShapeCollection

The worksheet group shapes.

See Also

HasHeadersFooters

Returns true if worksheet has headers/footers.

public bool HasHeadersFooters { get; }
Public ReadOnly Property HasHeadersFooters As Boolean
Property Value
System.Boolean

HasSplitOrFreezePanes

Returns true if worksheet has split or freeze panes.

public bool HasSplitOrFreezePanes { get; }
Public ReadOnly Property HasSplitOrFreezePanes As Boolean
Property Value
System.Boolean

HeadersFooters

Gets or sets headers/footers for the sheet.

public SheetHeaderFooter HeadersFooters { get; set; }
Public Property HeadersFooters As SheetHeaderFooter
Property Value
SheetHeaderFooter

HorizontalPageBreaks

Gets collection of all horizontal page breaks (HorizontalPageBreak) in the worksheet.

public HorizontalPageBreakCollection HorizontalPageBreaks { get; }
Public ReadOnly Property HorizontalPageBreaks As HorizontalPageBreakCollection
Property Value
HorizontalPageBreakCollection

Gets hyperlinks in this sheet.

public SpreadsheetHyperlinkCollection Hyperlinks { get; }
Public ReadOnly Property Hyperlinks As SpreadsheetHyperlinkCollection
Property Value
SpreadsheetHyperlinkCollection

IgnoredErrors

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

public IgnoredErrorCollection IgnoredErrors { get; }
Public ReadOnly Property IgnoredErrors As IgnoredErrorCollection
Property Value
IgnoredErrorCollection

The ignored errors applied on cell ranges of this sheet.

Remarks

A cell is considered to have an error condition when it meets one of the conditions specified in the ErrorTypes member. For example, if a cell is formatted as text but contains a numeric value, this is considered to be a potential error because the number won't be treated as a number, for example, in calculations.

Note that this is simply a guess by the implementing application, and a recommendation to the user. Cells with the errors specified in IgnoredErrorTypes may have perfectly valid reasons for being in such a state, for example a cell formatted as text which contains numeric Postal Codes or Order numbers. It is useful to format these cells as text so that leading zeros remain as part of the value instead of being removed.

Index

Gets worksheet zero-based index.

public int Index { get; }
Public ReadOnly Property Index As Integer
Property Value
System.Int32

Name

Gets or sets worksheet name.

public string Name { get; set; }
Public Property Name As String
Property Value
System.String
Remarks

If not unique (worksheet with that name already exists in Worksheets collection) exception is thrown.

Exceptions
System.ArgumentException

Worksheet name is not unique.

NamedRanges

Gets NamedRangeCollection containing descriptive names which are used to represent cells, ranges of cells, formulas, or constant values.

public NamedRangeCollection NamedRanges { get; }
Public ReadOnly Property NamedRanges As NamedRangeCollection
Property Value
NamedRangeCollection
Remarks

You can use the labels of columns and rows on a worksheet to refer to the cells within those columns and rows. Or you can create descriptive names to represent cells, ranges of cells, formulas, or constant values. Labels can be used in formulas that refer to data on the same worksheet; if you want to represent a range on another worksheet, use a name. You can also create 3-D names that represent the same cell or range of cells across multiple worksheets.

See Also

Panes

Gets or sets the panes in the worksheet.

public WorksheetPanes Panes { get; set; }
Public Property Panes As WorksheetPanes
Property Value
WorksheetPanes
Remarks

If there are no split or freeze panes in the worksheet, the value of this property is null.

Parent

Gets the parent ExcelFile of this ExcelWorksheet instance.

public ExcelFile Parent { get; }
Public ReadOnly Property Parent As ExcelFile
Property Value
ExcelFile

Pictures

Gets the worksheet pictures.

public ExcelPictureCollection Pictures { get; }
Public ReadOnly Property Pictures As ExcelPictureCollection
Property Value
ExcelPictureCollection

The worksheet pictures.

Remarks

Supported picture formats are listed in ExcelPictureFormat.

See Also

PivotTables

Gets the collection of all PivotTables contained in this worksheet.

Currently supported in XLSX only.

public PivotTableCollection PivotTables { get; }
Public ReadOnly Property PivotTables As PivotTableCollection
Property Value
PivotTableCollection

The collection of all PivotTables contained in this worksheet.

Remarks

Pivot Tables are used to arrange and aggregate complicated data and drill down on details.

Source of data for Pivot Table can be:

  • WorksheetSource - CellRange, NamedRange, table, etc.
  • External - database, OLAP cube, textual file, web query, etc.
  • Other - multiple consolidation ranges in the workbook, another Pivot Table, etc.
note

GemBox.Spreadsheet currently supports creating only WorksheetSource.

All other sources are supported through preservation (to correctly round-trip to XLSX format).

Source of the data for Pivot Table contains following information:

  • Scheme - data source fields/columns, its data types, etc.
  • Data - actual data source records.

Pivot Table does not directly reference its data source, but instead it references a cache of a data source. Cache of a data source contains an in-memory copy of data source scheme and, optionally, data and is stored in a workbook which may contain multiple caches of various sources of data.

Cache can be updated/refreshed from a data source automatically (for example, when opening a workbook) or on request. Cache is used to provide better performance of Pivot Table functionality which requires frequent access to data records and their values and to enable sharing the same data source between multiple Pivot Tables.

GemBox.Spreadsheet represents cache with a PivotCache type. Cache source is represented with a PivotCacheSource type (and its sub-types) and can be accessed from a CacheSource property. PivotCache instances are stored in a PivotCaches collection. PivotCache can be refreshed by using the Refresh() method.

PivotTable is created from a PivotCache with the specified name and the specified cell in the worksheet where it is placed using the Add(PivotCache, String, String) method. PivotTable contains the PivotFields collection which PivotField elements can be Add(Int32) into the following Pivot Table areas:

Each (non-calculated) PivotField can contain zero or more PivotItems which are used for filtering and grouping by a specific value.

Except using fields defined in Pivot Table data source (more precisely, in PivotCache) which cannot be removed, Pivot Table can also define calculated PivotFields and calculated PivotItems which use a formula to specify their values. Calculated PivotField can be added using a AddCalculated(String, String) method and removed using a RemoveCalculated(String) method. Property IsCalculated tells if a field is calculated or is a field from the data source. Calculated PivotField is automatically moved to a Values Area (DataFields collection) and only Values Area can contain calculated PivotFields. Calculated PivotItem can be added using a AddCalculated(String, String) method and removed using a RemoveCalculated(String) method. Property IsCalculated tells if an item is calculated.

note

If calculated PivotField is added/removed in one PivotTable, then it is also added/removed from all other PivotTables which reference the same PivotCache.

Since adding/removing calculated PivotFields means changing the data scheme, which is defined in PivotCache, actual modification is made in PivotCache and propagated to all PivotTables which are referencing it.

PivotFields can be re-ordered in a specific area using the Move(Int32, Int32) method. PivotItems can be re-ordered using the Move(Int32, Int32) method.

GemBox.Spreadsheet provides functionality to calculate PivotTables by using the Calculate() method which updates the cells in the ExcelWorksheet. The Calculate() method uses values stored in PivotCache. If the cache is currently empty, or the values are outdated (because the underlying data source has changed), the cache needs to be refreshed using the Refresh() method.

PrintOptions

Contains MS Excel print and print related options.

public ExcelPrintOptions PrintOptions { get; }
Public ReadOnly Property PrintOptions As ExcelPrintOptions
Property Value
ExcelPrintOptions

Protected

Gets or sets the worksheet protection flag.

public bool Protected { get; set; }
Public Property Protected As Boolean
Property Value
System.Boolean
Remarks

This property is simply written to Excel file and has no effect on the behavior of this library. For more information on worksheet protection, consult Microsoft Excel documentation.

See Also

ProtectedRanges

Gets the protected ranges. Supported in XLSX and XLSB only.

public ProtectedRangeCollection ProtectedRanges { get; }
Public ReadOnly Property ProtectedRanges As ProtectedRangeCollection
Property Value
ProtectedRangeCollection

The protected ranges.

ProtectionSettings

Gets or sets worksheet protection properties. This settings will be used only if Protected is set to true; Otherwise it will be ignored.

public WorksheetProtection ProtectionSettings { get; }
Public ReadOnly Property ProtectionSettings As WorksheetProtection
Property Value
WorksheetProtection

Rows

Gets collection of all rows (ExcelRow) in the worksheet.

public ExcelRowCollection Rows { get; }
Public ReadOnly Property Rows As ExcelRowCollection
Property Value
ExcelRowCollection

SelectedRanges

Gets the selected ranges of cells in the worksheet.

Supported only in XLSX and XLSB.

public CellRangeCollection SelectedRanges { get; }
Public ReadOnly Property SelectedRanges As CellRangeCollection
Property Value
CellRangeCollection

The selected ranges of cells in the worksheet.

Shapes

Gets the worksheet shapes.

public ExcelShapeCollection Shapes { get; }
Public ReadOnly Property Shapes As ExcelShapeCollection
Property Value
ExcelShapeCollection

The worksheet shapes.

See Also

Sort

Gets or sets the active SortState. Returns null 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 SortState Sort { get; set; }
Public Property Sort As SortState
Property Value
SortState

The active SortState or null if no SortState is active.

Remarks

Use Sort(Boolean) method to create or get a SortState instance.

Exceptions
System.ArgumentException

Sort range doesn't belong to this worksheet.

TabColor

Gets or sets the worksheet tab color.

Supported only in XLSX, XLSB and HTML.

public SpreadsheetColor TabColor { get; set; }
Public Property TabColor As SpreadsheetColor
Property Value
SpreadsheetColor

The worksheet tab color.

Tables

Gets the collection of all Tables contained in this worksheet.

public TableCollection Tables { get; }
Public ReadOnly Property Tables As TableCollection
Property Value
TableCollection

The collection of all Tables contained in this worksheet.

Type

Gets worksheet type.

public SheetType Type { get; }
Public ReadOnly Property Type As SheetType
Property Value
SheetType

The worksheet type.

VerticalPageBreaks

Gets collection of all vertical page breaks (VerticalPageBreak) in the worksheet.

public VerticalPageBreakCollection VerticalPageBreaks { get; }
Public ReadOnly Property VerticalPageBreaks As VerticalPageBreakCollection
Property Value
VerticalPageBreakCollection

ViewOptions

Contains MS Excel display and view related options.

public ExcelViewOptions ViewOptions { get; }
Public ReadOnly Property ViewOptions As ExcelViewOptions
Property Value
ExcelViewOptions

Visibility

Gets or sets the worksheet visible property.

public SheetVisibility Visibility { get; set; }
Public Property Visibility As SheetVisibility
Property Value
SheetVisibility
Remarks

Workbook must contain at least one visible sheet.

Methods

Calculate()

Calculates formula for every excel cell in this worksheet.

public void Calculate()
Public Sub Calculate

CalculateFormula(String)

Calculates the given formula.

public object[, ] CalculateFormula(string formula)
Public Function CalculateFormula(formula As String) As Object(,)
Parameters
formula
System.String

The formula to be calculated.

Returns
System.Object[,]

A two-dimensional array of objects representing the calculated values, where the array dimensions match the range that the formula covers.

CalculateMaxUsedColumns()

Iterates all rows and finds maximum number of used columns.

public int CalculateMaxUsedColumns()
Public Function CalculateMaxUsedColumns As Integer
Returns
System.Int32

Maximum number of occupied columns in this sheet.

Clear()

Clears this worksheet.

public void Clear()
Public Sub Clear
Remarks

References to following members will become invalid after calling this method. If they are stored in external variables, you will need to get them again from worksheet. Here is the list:

CreateDataTable(CreateDataTableOptions)

Creates a System.Data.DataTable and fills it with data from this ExcelWorksheet in a manner specified by create options, see CreateDataTableOptions.

public DataTable CreateDataTable(CreateDataTableOptions options)
Public Function CreateDataTable(options As CreateDataTableOptions) As DataTable
Parameters
options
CreateDataTableOptions

Options that describe how System.Data.DataTable will be created and filled with data extracted from ExcelWorksheet.

Returns
System.Data.DataTable

System.Data.DataTable with data from specified cells.

DeleteBackgroundPicture()

Removes the worksheet background picture.

public void DeleteBackgroundPicture()
Public Sub DeleteBackgroundPicture

ExtractToDataTable(DataTable, ExtractToDataTableOptions)

Extracts data to System.Data.DataTable with specified extract options, see ExtractToDataTableOptions.

public void ExtractToDataTable(DataTable dataTable, ExtractToDataTableOptions options)
Public Sub ExtractToDataTable(dataTable As DataTable, options As ExtractToDataTableOptions)
Parameters
dataTable
System.Data.DataTable

Destination System.Data.DataTable.

options
ExtractToDataTableOptions

Options that describe how data will be extracted, see ExtractToDataTableOptions.

GetDefaultColumnWidth(LengthUnit)

Gets the default column width in arbitrary length unit.

public double GetDefaultColumnWidth(LengthUnit lengthUnit)
Public Function GetDefaultColumnWidth(lengthUnit As LengthUnit) As Double
Parameters
lengthUnit
LengthUnit

The length unit in which to return default column width.

Returns
System.Double

The default column width in arbitrary length unit.

GetUsedCellRange(Boolean)

Calculates cell range that is used in the worksheet.

public CellRange GetUsedCellRange(bool ignoreCellsWithoutValue)
Public Function GetUsedCellRange(ignoreCellsWithoutValue As Boolean) As CellRange
Parameters
ignoreCellsWithoutValue
System.Boolean

Specifies if allocated cells with Value set to null are ignored.

Returns
CellRange

Cell range that is used in the worksheet.

InsertDataTable(DataTable)

Inserts a System.Data.DataTable into the current worksheet.

public int InsertDataTable(DataTable dataTable)
Public Function InsertDataTable(dataTable As DataTable) As Integer
Parameters
dataTable
System.Data.DataTable

The System.Data.DataTable which will be inserted into the current worksheet.

Returns
System.Int32

Number of inserted rows.

InsertDataTable(DataTable, InsertDataTableOptions)

Inserts a System.Data.DataTable into the current worksheet.

public int InsertDataTable(DataTable dataTable, InsertDataTableOptions options)
Public Function InsertDataTable(dataTable As DataTable, options As InsertDataTableOptions) As Integer
Parameters
dataTable
System.Data.DataTable

The System.Data.DataTable which will be inserted into the current worksheet.

options
InsertDataTableOptions

The InsertDataTableOptions which can be used to define settings for the insert operation.

Returns
System.Int32

Number of inserted rows.

SetBackgroundPicture(MemoryStream, ExcelPictureFormat)

Sets the worksheet background picture.

public void SetBackgroundPicture(MemoryStream pictureStream, ExcelPictureFormat pictureFormat)
Public Sub SetBackgroundPicture(pictureStream As MemoryStream, pictureFormat As ExcelPictureFormat)
Parameters
pictureStream
System.IO.MemoryStream

The picture's stream.

pictureFormat
ExcelPictureFormat

The picture's format.

SetBackgroundPicture(String)

Sets the worksheet background picture.

public void SetBackgroundPicture(string picturePath)
Public Sub SetBackgroundPicture(picturePath As String)
Parameters
picturePath
System.String

The picture's path.

SetDefaultColumnWidth(Double, LengthUnit)

Sets the default column width in arbitrary length unit.

public void SetDefaultColumnWidth(double value, LengthUnit lengthUnit)
Public Sub SetDefaultColumnWidth(value As Double, lengthUnit As LengthUnit)
Parameters
value
System.Double

Default column width value.

lengthUnit
LengthUnit

The length unit in which value is specified.

Examples

See Also