ExcelWorksheet Class
Excel worksheet is a table with additional properties, identified by a unique name.
- Inheritance:
- System.ObjectExcelWorksheet
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
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.
Property Value
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
The worksheet charts.
CodeName
Gets or sets the name of the VbaModule that is bound to this ExcelWorksheet.
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
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
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
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
DefaultColumnWidth
Gets or sets the default column width.
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.
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
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.
Property Value
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.
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
The worksheet form controls.
See Also
GroupShapes
Gets the worksheet group shapes.
public ExcelGroupShapeCollection GroupShapes { get; }
Public ReadOnly Property GroupShapes As ExcelGroupShapeCollection
Property Value
The worksheet group shapes.
See Also
HasHeadersFooters
Returns true if worksheet has headers/footers.
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
HorizontalPageBreaks
Gets collection of all horizontal page breaks (HorizontalPageBreak) in the worksheet.
public HorizontalPageBreakCollection HorizontalPageBreaks { get; }
Public ReadOnly Property HorizontalPageBreaks As HorizontalPageBreakCollection
Property Value
Hyperlinks
Gets hyperlinks in this sheet.
public SpreadsheetHyperlinkCollection Hyperlinks { get; }
Public ReadOnly Property Hyperlinks As SpreadsheetHyperlinkCollection
Property Value
IgnoredErrors
Gets the ignored errors applied on cell ranges of this sheet.
public IgnoredErrorCollection IgnoredErrors { get; }
Public ReadOnly Property IgnoredErrors As IgnoredErrorCollection
Property Value
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.
Property Value
- System.Int32
Name
Gets or sets worksheet name.
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
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.
Property Value
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.
Property Value
Pictures
Gets the worksheet pictures.
public ExcelPictureCollection Pictures { get; }
Public ReadOnly Property Pictures As ExcelPictureCollection
Property Value
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
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:
- Report Filter Area represented by PageFields collection.
- Row Axis represented by RowFields collection.
- Column Axis represented by ColumnFields collection.
- Values Area represented by DataFields collection.
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
Protected
Gets or sets the worksheet protection flag.
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
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
Rows
Gets collection of all rows (ExcelRow) in the worksheet.
Property Value
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
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
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.
Property Value
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.
Property Value
The worksheet tab color.
Tables
Gets the collection of all Tables contained in this worksheet.
Property Value
The collection of all Tables contained in this worksheet.
Type
Gets worksheet type.
Property Value
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
ViewOptions
Contains MS Excel display and view related options.
public ExcelViewOptions ViewOptions { get; }
Public ReadOnly Property ViewOptions As ExcelViewOptions
Property Value
Visibility
Gets or sets the worksheet visible property.
public SheetVisibility Visibility { get; set; }
Public Property Visibility As SheetVisibility
Property Value
Remarks
Workbook must contain at least one visible sheet.
Methods
Calculate()
Calculates formula for every excel cell in this worksheet.
CalculateMaxUsedColumns()
Iterates all rows and finds maximum number of used columns.
Returns
- System.Int32
Maximum number of occupied columns in this sheet.
Clear()
Clears this worksheet.
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:
- DataValidations
- Each ExcelRow instance in Rows
- Each ExcelColumn instance in Columns
- Each ExcelCell instance in Cells
- Each ExcelPicture instance in Pictures
- Each ExcelChart instance in Charts
- Each ExcelShape instance in Shapes
- Each ExcelGroupShape instance in GroupShapes
- Each ExcelFormControl instance in FormControls
- Each HorizontalPageBreak instance in HorizontalPageBreaks
- Each VerticalPageBreak instance in VerticalPageBreak
- Each NamedRange instance in NamedRanges
- Each SpreadsheetHyperlink instance in Hyperlinks
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.
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
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.