Class ExcelWorksheet

Represents an Excel worksheet and provides access to its properties and methods

Inheritance

System.Object

XmlHelper

ExcelWorksheet

ExcelChartsheet

Implements

System.Collections.Generic.IEqualityComparer<ExcelWorksheet>

System.IDisposable

Inherited Members

System.Object.Equals(System.Object)

System.Object.Equals(System.Object, System.Object)

System.Object.GetHashCode()

System.Object.GetType()

System.Object.MemberwiseClone()

System.Object.ReferenceEquals(System.Object, System.Object)

Namespace: OfficeOpenXml

Assembly: EPPlus.dll

Syntax

public class ExcelWorksheet : XmlHelper, IEqualityComparer<ExcelWorksheet>, IDisposable

Constructors

ExcelWorksheet(XmlNamespaceManager, ExcelPackage, String, Uri, String, Int32, Int32, Nullable<eWorkSheetHidden>)

A worksheet

Declaration

public ExcelWorksheet(XmlNamespaceManager ns, ExcelPackage excelPackage, string relID, Uri uriWorksheet, string sheetName, int sheetID, int positionID, eWorkSheetHidden? hide)

Parameters

Type	Name	Description
System.Xml.XmlNamespaceManager	ns	Namespacemanager
ExcelPackage	excelPackage	Package
System.String	relID	Relationship ID
System.Uri	uriWorksheet	URI
System.String	sheetName	Name of the sheet
System.Int32	sheetID	Sheet id
System.Int32	positionID	Position
System.Nullable<eWorkSheetHidden>	hide	hide

Properties

AutoFilter

Autofilter settings

Declaration

public ExcelAutoFilter AutoFilter { get; }

Property Value

Type	Description
ExcelAutoFilter

AutoFilterAddress

The auto filter address. null means no auto filter.

Declaration

public ExcelAddressBase AutoFilterAddress { get; }

Property Value

Type	Description
ExcelAddressBase

BackgroundImage

An image displayed as the background of the worksheet.

Declaration

public ExcelBackgroundImage BackgroundImage { get; }

Property Value

Type	Description
ExcelBackgroundImage

Cells

Provides access to a range of cells

Declaration

public ExcelRange Cells { get; }

Property Value

Type	Description
ExcelRange

CodeModule

The VBA code modul for the worksheet, if the package contains a VBA project. CreateVBAProject()

Declaration

public ExcelVBAModule CodeModule { get; }

Property Value

Type	Description
ExcelVBAModule

Columns

A collection of column specific properties in the worksheet.

Declaration

public ExcelColumnCollection Columns { get; }

Property Value

Type	Description
ExcelColumnCollection

Comments

Collection of comments

Declaration

public ExcelCommentCollection Comments { get; }

Property Value

Type	Description
ExcelCommentCollection

ConditionalFormatting

ConditionalFormatting defined in the worksheet. Use the Add methods to create ConditionalFormatting and add them to the worksheet. Then set the properties on the instance returned.

Declaration

public ExcelConditionalFormattingCollection ConditionalFormatting { get; }

Property Value

Type	Description
ExcelConditionalFormattingCollection

See Also

ExcelConditionalFormattingCollection

CustomHeight

'True' if defaultRowHeight value has been manually set, or is different from the default value. Is automaticlly set to 'True' when assigning the DefaultRowHeight property

Declaration

public bool CustomHeight { get; set; }

Property Value

Type	Description
System.Boolean

DataValidations

DataValidation defined in the worksheet. Use the Add methods to create DataValidations and add them to the worksheet. Then set the properties on the instance returned. Must know worksheet or at least worksheet name to determine if extLst when user input DataValidations in API.

Declaration

public ExcelDataValidationCollection DataValidations { get; }

Property Value

Type	Description
ExcelDataValidationCollection

See Also

ExcelDataValidationCollection

DefaultColWidth

Get/set the default width of all columns in the worksheet

Declaration

public double DefaultColWidth { get; set; }

Property Value

Type	Description
System.Double

DefaultRowHeight

Get/set the default height of all rows in the worksheet

Declaration

public double DefaultRowHeight { get; set; }

Property Value

Type	Description
System.Double

Dimension

Dimension address for the worksheet. Top left cell to Bottom right. If the worksheet has no cells, null is returned

Declaration

public ExcelAddressBase Dimension { get; }

Property Value

Type	Description
ExcelAddressBase

Drawings

Collection of drawing-objects like shapes, images and charts

Declaration

public ExcelDrawings Drawings { get; }

Property Value

Type	Description
ExcelDrawings

HeaderFooter

A reference to the header and footer class which allows you to set the header and footer for all odd, even and first pages of the worksheet

Declaration

public ExcelHeaderFooter HeaderFooter { get; }

Property Value

Type	Description
ExcelHeaderFooter

Remarks

To format the text you can use the following format

Prefix	Description
&U	Underlined
&E	Double Underline
&K:xxxxxx	Color. ex &K:FF0000 for red
&"Font,Regular Bold Italic"	Changes the font. Regular or Bold or Italic or Bold Italic can be used. ex &"Arial,Bold Italic"
&nn	Change font size. nn is an integer. ex &24
&G	Placeholder for images. Images cannot be added by the library, but its possible to use in a template.

Hidden

Indicates if the worksheet is hidden in the workbook

Declaration

public eWorkSheetHidden Hidden { get; set; }

Property Value

Type	Description
eWorkSheetHidden

IgnoredErrors

Ignore Errors for the specified ranges and error types.

Declaration

public ExcelIgnoredErrorCollection IgnoredErrors { get; }

Property Value

Type	Description
ExcelIgnoredErrorCollection

Index

The index in the worksheets collection

Declaration

public int Index { get; }

Property Value

Type	Description
System.Int32

MergedCells

Addresses to merged ranges

Declaration

public ExcelWorksheet.MergeCellsCollection MergedCells { get; }

Property Value

Type	Description
ExcelWorksheet.MergeCellsCollection

Name

The worksheet's display name as it appears on the tab

Declaration

public string Name { get; set; }

Property Value

Type	Description
System.String

Names

Provides access to named ranges

Declaration

public ExcelNamedRangeCollection Names { get; }

Property Value

Type	Description
ExcelNamedRangeCollection

OutLineApplyStyle

Automatic styles

Declaration

public bool OutLineApplyStyle { get; set; }

Property Value

Type	Description
System.Boolean

OutLineSummaryBelow

If true, summary rows are showen below the details, otherwise above.

Declaration

public bool OutLineSummaryBelow { get; set; }

Property Value

Type	Description
System.Boolean

OutLineSummaryRight

If true, summary columns are to right of details otherwise to the left.

Declaration

public bool OutLineSummaryRight { get; set; }

Property Value

Type	Description
System.Boolean

PivotTables

Pivottables defined in the worksheet.

Declaration

public ExcelPivotTableCollection PivotTables { get; }

Property Value

Type	Description
ExcelPivotTableCollection

PrinterSettings

Printer settings

Declaration

public ExcelPrinterSettings PrinterSettings { get; }

Property Value

Type	Description
ExcelPrinterSettings

ProtectedRanges

Access to protected ranges in the worksheet

Declaration

public ExcelProtectedRangeCollection ProtectedRanges { get; }

Property Value

Type	Description
ExcelProtectedRangeCollection

Protection

Access to sheet protection properties

Declaration

public ExcelSheetProtection Protection { get; }

Property Value

Type	Description
ExcelSheetProtection

Rows

A collection of row specific properties in the worksheet.

Declaration

public ExcelRowsCollection Rows { get; }

Property Value

Type	Description
ExcelRowsCollection

RowZeroHeight

If true, empty rows are hidden by default. This reduces the size of the package and increases performance if most of the rows in a worksheet are hidden.

Declaration

public bool RowZeroHeight { get; set; }

Property Value

Type	Description
System.Boolean

SelectedRange

Provides access to the selected range of cells

Declaration

public ExcelRange SelectedRange { get; }

Property Value

Type	Description
ExcelRange

SortState

Sets the sort state

Declaration

public SortState SortState { get; }

Property Value

Type	Description
SortState

SparklineGroups

Collection of Sparkline-objects. Sparklines are small in-cell charts.

Declaration

public ExcelSparklineGroupCollection SparklineGroups { get; }

Property Value

Type	Description
ExcelSparklineGroupCollection

TabColor

Color of the sheet tab

Declaration

public Color TabColor { get; set; }

Property Value

Type	Description
System.Drawing.Color

Tables

Tables defined in the worksheet.

Declaration

public ExcelTableCollection Tables { get; }

Property Value

Type	Description
ExcelTableCollection

ThreadedComments

A collection of threaded comments referenced in the worksheet.

Declaration

public ExcelWorksheetThreadedComments ThreadedComments { get; }

Property Value

Type	Description
ExcelWorksheetThreadedComments

View

Returns a ExcelWorksheetView object that allows you to set the view state properties of the worksheet

Declaration

public ExcelWorksheetView View { get; }

Property Value

Type	Description
ExcelWorksheetView

Workbook

The workbook object

Declaration

public ExcelWorkbook Workbook { get; }

Property Value

Type	Description
ExcelWorkbook

WorksheetXml

The XML document holding the worksheet data. All column, row, cell, pagebreak, merged cell and hyperlink-data are loaded into memory and removed from the document when loading the document.

Declaration

public XmlDocument WorksheetXml { get; }

Property Value

Type	Description
System.Xml.XmlDocument

Methods

ClearFormulas()

Removes all formulas within the entire worksheet, but keeps the calculated values.

Declaration

public void ClearFormulas()

ClearFormulaValues()

Removes all values of cells with formulas in the entire worksheet, but keeps the formulas.

Declaration

public void ClearFormulaValues()

Column(Int32)

Provides access to an individual column within the worksheet so you can set its properties.

Declaration

public ExcelColumn Column(int col)

Parameters

Type	Name	Description
System.Int32	col	The column number in the worksheet

Returns

Type	Description
ExcelColumn

DeleteColumn(Int32)

Delete the specified column from the worksheet.

Declaration

public void DeleteColumn(int column)

Parameters

Type	Name	Description
System.Int32	column	The column to be deleted

DeleteColumn(Int32, Int32)

Delete the specified columns from the worksheet.

Declaration

public void DeleteColumn(int columnFrom, int columns)

Parameters

Type	Name	Description
System.Int32	columnFrom	The start column
System.Int32	columns	Number of columns to delete

DeleteRow(Int32)

Delete the specified row from the worksheet.

Declaration

public void DeleteRow(int row)

Parameters

Type	Name	Description
System.Int32	row	A row to be deleted

DeleteRow(Int32, Int32)

Delete the specified rows from the worksheet.

Declaration

public void DeleteRow(int rowFrom, int rows)

Parameters

Type	Name	Description
System.Int32	rowFrom	The start row
System.Int32	rows	Number of rows to delete

DeleteRow(Int32, Int32, Boolean)

Deletes the specified rows from the worksheet.

Declaration

[Obsolete("Use the two-parameter method instead")]
public void DeleteRow(int rowFrom, int rows, bool shiftOtherRowsUp)

Parameters

Type	Name	Description
System.Int32	rowFrom	The number of the start row to be deleted
System.Int32	rows	Number of rows to delete
System.Boolean	shiftOtherRowsUp	Not used. Rows are always shifted

Dispose()

Disposes the worksheet

Declaration

public void Dispose()

Equals(ExcelWorksheet, ExcelWorksheet)

Check if a worksheet is equal to another

Declaration

public bool Equals(ExcelWorksheet x, ExcelWorksheet y)

Parameters

Type	Name	Description
ExcelWorksheet	x	First worksheet
ExcelWorksheet	y	Second worksheet

Returns

Type	Description
System.Boolean

GetHashCode(ExcelWorksheet)

Returns a hashcode generated from the WorksheetXml

Declaration

public int GetHashCode(ExcelWorksheet obj)

Parameters

Type	Name	Description
ExcelWorksheet	obj	The worksheet

Returns

Type	Description
System.Int32	The hashcode

GetMergeCellId(Int32, Int32)

Get MergeCell Index No

Declaration

public int GetMergeCellId(int row, int column)

Parameters

Type	Name	Description
System.Int32	row
System.Int32	column

Returns

Type	Description
System.Int32

GetValue(Int32, Int32)

Get the cell value from thw worksheet

Declaration

public object GetValue(int Row, int Column)

Parameters

Type	Name	Description
System.Int32	Row	The row number
System.Int32	Column	The row number

Returns

Type	Description
System.Object	The value

GetValue<T>(Int32, Int32)

Get a strongly typed cell value from the worksheet

Declaration

public T GetValue<T>(int Row, int Column)

Parameters

Type	Name	Description
System.Int32	Row	The row number
System.Int32	Column	The row number

Returns

Type	Description
T	The value. If the value can't be converted to the specified type, the default value will be returned

Type Parameters

Name	Description
T	The type

InsertColumn(Int32, Int32)

Inserts new columns into the spreadsheet. Existing columns below the position are shifted down. All formula are updated to take account of the new column(s).

Declaration

public void InsertColumn(int columnFrom, int columns)

Parameters

Type	Name	Description
System.Int32	columnFrom	The position of the new column(s)
System.Int32	columns	Number of columns to insert

InsertColumn(Int32, Int32, Int32)

Inserts new columns into the spreadsheet. Existing column to the left are shifted. All formula are updated to take account of the new column(s).

Declaration

public void InsertColumn(int columnFrom, int columns, int copyStylesFromColumn)

Parameters

Type	Name	Description
System.Int32	columnFrom	The position of the new column(s)
System.Int32	columns	Number of columns to insert.
System.Int32	copyStylesFromColumn	Copy Styles from this column. Applied to all inserted columns

InsertRow(Int32, Int32)

Inserts new rows into the spreadsheet. Existing rows below the position are shifted down. All formula are updated to take account of the new row(s).

Declaration

public void InsertRow(int rowFrom, int rows)

Parameters

Type	Name	Description
System.Int32	rowFrom	The position of the new row(s)
System.Int32	rows	Number of rows to insert

InsertRow(Int32, Int32, Int32)

Inserts new rows into the spreadsheet. Existing rows below the position are shifted down. All formula are updated to take account of the new row(s).

Declaration

public void InsertRow(int rowFrom, int rows, int copyStylesFromRow)

Parameters

Type	Name	Description
System.Int32	rowFrom	The position of the new row(s)
System.Int32	rows	Number of rows to insert.
System.Int32	copyStylesFromRow	Copy Styles from this row. Applied to all inserted rows

Row(Int32)

Provides access to an individual row within the worksheet so you can set its properties.

Declaration

public ExcelRow Row(int row)

Parameters

Type	Name	Description
System.Int32	row	The row number in the worksheet

Returns

Type	Description
ExcelRow

Select()

Make the current worksheet active.

Declaration

public void Select()

Select(ExcelAddress)

Selects a range in the worksheet. The active cell is the topmost cell of the first address. Make the current worksheet active.

Declaration

public void Select(ExcelAddress Address)

Parameters

Type	Name	Description
ExcelAddress	Address	An address range

Select(ExcelAddress, Boolean)

Selects a range in the worksheet. The active cell is the topmost cell of the first address.

Declaration

public void Select(ExcelAddress Address, bool SelectSheet)

Parameters

Type	Name	Description
ExcelAddress	Address	A range of cells
System.Boolean	SelectSheet	Make the sheet active

Select(String)

Selects a range in the worksheet. The active cell is the topmost cell. Make the current worksheet active.

Declaration

public void Select(string Address)

Parameters

Type	Name	Description
System.String	Address	An address range

Select(String, Boolean)

Selects a range in the worksheet. The actice cell is the topmost cell.

Declaration

public void Select(string Address, bool SelectSheet)

Parameters

Type	Name	Description
System.String	Address	A range of cells
System.Boolean	SelectSheet	Make the sheet active

SetValue(Int32, Int32, Object)

Set the value of a cell

Declaration

public void SetValue(int Row, int Column, object Value)

Parameters

Type	Name	Description
System.Int32	Row	The row number
System.Int32	Column	The column number
System.Object	Value	The value

SetValue(String, Object)

Set the value of a cell

Declaration

public void SetValue(string Address, object Value)

Parameters

Type	Name	Description
System.String	Address	The Excel address
System.Object	Value	The value

ToString()

Returns the name of the worksheet

Declaration

public override string ToString()

Returns

Type	Description
System.String	The name of the worksheet

Overrides

System.Object.ToString()

Implements

System.Collections.Generic.IEqualityComparer<T>

System.IDisposable

Extension Methods

CalculationExtension.Calculate(ExcelWorksheet)

CalculationExtension.Calculate(ExcelWorksheet, Action<ExcelCalculationOption>)

CalculationExtension.Calculate(ExcelWorksheet, ExcelCalculationOption)

CalculationExtension.Calculate(ExcelWorksheet, String)

CalculationExtension.Calculate(ExcelWorksheet, String, ExcelCalculationOption)