Class ExcelWorksheet¶

Namespace: OfficeOpenXml
Assembly: EPPlus.dll

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

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

Inheritance¶

object ← XmlHelper ← ExcelWorksheet

Derived¶

ExcelChartsheet

Implements¶

IDisposable
IEqualityComparer<ExcelWorksheet>

Inherited Members¶

object.Equals(object?, object?)
object.Equals(object?)
object.GetHashCode()
object.GetType()
object.MemberwiseClone()
object.ReferenceEquals(object?, object?)
object.ToString()

Extension Methods¶

CalculationExtension.Calculate(ExcelWorksheet, Action<ExcelCalculationOption>)
CalculationExtension.Calculate(ExcelWorksheet, ExcelCalculationOption)
CalculationExtension.Calculate(ExcelWorksheet, string, ExcelCalculationOption)
CalculationExtension.Calculate(ExcelWorksheet, string)
CalculationExtension.Calculate(ExcelWorksheet)

Constructors¶

ExcelWorksheet(XmlNamespaceManager, ExcelPackage, string, Uri, string, int, int, eWorkSheetHidden?)¶

A worksheet

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

Parameters¶

ns XmlNamespaceManager

Namespacemanager

excelPackage ExcelPackage

Package

relID string

Relationship ID

uriWorksheet Uri

URI

sheetName string

Name of the sheet

sheetID int

Sheet id

positionID int

Position

hide eWorkSheetHidden?

hide

Properties¶

AutoFilter¶

Autofilter settings

public ExcelAutoFilter AutoFilter { get; }

Property Value¶

ExcelAutoFilter

BackgroundImage¶

An image displayed as the background of the worksheet.

public ExcelBackgroundImage BackgroundImage { get; }

Property Value¶

ExcelBackgroundImage

Cells¶

Provides access to a range of cells

public ExcelRange Cells { get; }

Property Value¶

ExcelRange

CodeModule¶

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

public ExcelVBAModule CodeModule { get; }

Property Value¶

ExcelVBAModule

Columns¶

A collection of column specific properties in the worksheet.

public ExcelColumnCollection Columns { get; }

Property Value¶

ExcelColumnCollection

Comments¶

Collection of comments

public ExcelCommentCollection Comments { get; }

Property Value¶

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.

public ExcelConditionalFormattingCollection ConditionalFormatting { get; }

Property Value¶

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

public bool CustomHeight { get; set; }

Property Value¶

bool

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.

public ExcelDataValidationCollection DataValidations { get; }

Property Value¶

ExcelDataValidationCollection

See Also¶

ExcelDataValidationCollection

DefaultColWidth¶

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

public double DefaultColWidth { get; set; }

Property Value¶

double

DefaultRowHeight¶

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

public double DefaultRowHeight { get; set; }

Property Value¶

double

Dimension¶

Dimension address for the worksheet for cells with a value or a style set. Top left cell to Bottom right. If the worksheet has no cells, null is returned

public ExcelRangeBase Dimension { get; }

Property Value¶

ExcelRangeBase

DimensionByValue¶

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

public ExcelRangeBase DimensionByValue { get; }

Property Value¶

ExcelRangeBase

Drawings¶

Collection of drawing-objects like shapes, images and charts

public ExcelDrawings Drawings { get; }

Property Value¶

ExcelDrawings

FirstValueCell¶

The first cell with a value in the worksheet that differs from null. Normally this is the top-left cell, unless the worksheet is set to RightToLeft mode. RightToLeft

public ExcelRangeBase FirstValueCell { get; }

Property Value¶

ExcelRangeBase

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

public ExcelHeaderFooter HeaderFooter { get; }

Property Value¶

ExcelHeaderFooter

Remarks¶

To format the text you can use the following format

Hidden¶

Indicates if the worksheet is hidden in the workbook

public eWorkSheetHidden Hidden { get; set; }

Property Value¶

eWorkSheetHidden

IgnoredErrors¶

Ignore Errors for the specified ranges and error types.

public ExcelIgnoredErrorCollection IgnoredErrors { get; }

Property Value¶

ExcelIgnoredErrorCollection

Index¶

The index in the worksheets collection

public int Index { get; }

Property Value¶

int

LastValueCell¶

The last cell with a value in the worksheet that differs from null. Normally this is the bottom-right cell, unless the worksheet is set to RightToLeft mode. RightToLeft

public ExcelRangeBase LastValueCell { get; }

Property Value¶

ExcelRangeBase

MergedCells¶

Addresses to merged ranges

public ExcelWorksheet.MergeCellsCollection MergedCells { get; }

Property Value¶

ExcelWorksheet.MergeCellsCollection

Name¶

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

public string Name { get; set; }

Property Value¶

string

Names¶

Provides access to named ranges

public ExcelNamedRangeCollection Names { get; }

Property Value¶

ExcelNamedRangeCollection

OutLineApplyStyle¶

Automatic styles

public bool OutLineApplyStyle { get; set; }

Property Value¶

bool

OutLineSummaryBelow¶

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

public bool OutLineSummaryBelow { get; set; }

Property Value¶

bool

OutLineSummaryRight¶

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

public bool OutLineSummaryRight { get; set; }

Property Value¶

bool

PivotTables¶

Pivot tables defined in the worksheet.

public ExcelPivotTableCollection PivotTables { get; }

Property Value¶

ExcelPivotTableCollection

PrinterSettings¶

Printer settings

public ExcelPrinterSettings PrinterSettings { get; }

Property Value¶

ExcelPrinterSettings

ProtectedRanges¶

Access to protected ranges in the worksheet

public ExcelProtectedRangeCollection ProtectedRanges { get; }

Property Value¶

ExcelProtectedRangeCollection

Protection¶

Access to sheet protection properties

public ExcelSheetProtection Protection { get; }

Property Value¶

ExcelSheetProtection

QueryTables¶

A collection of query tables associated with the worksheet. These query tables are considered legacy and are used in older versions of Excel. Please consider to use AddQueryTable(ExcelAddressBase, string, ExcelConnection, string[]) instead.

public ExcelQueryTableCollection QueryTables { get; }

Property Value¶

ExcelQueryTableCollection

Remarks¶

Query tables are data tables that are linked to external data sources, such as databases or web queries.

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.

public bool RowZeroHeight { get; set; }

Property Value¶

bool

Rows¶

A collection of row specific properties in the worksheet.

public ExcelRowsCollection Rows { get; }

Property Value¶

ExcelRowsCollection

SelectedRange¶

Provides access to the selected range of cells

public ExcelRange SelectedRange { get; }

Property Value¶

ExcelRange

SignatureLines¶

Collection of signatureLines

public SignatureLineCollection SignatureLines { get; }

Property Value¶

SignatureLineCollection

SortState¶

Sets the sort state

public SortState SortState { get; }

Property Value¶

SortState

SparklineGroups¶

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

public ExcelSparklineGroupCollection SparklineGroups { get; }

Property Value¶

ExcelSparklineGroupCollection

TabColor¶

Color of the sheet tab. To remove color, set TabColor to Color.Empty.

public Color TabColor { get; set; }

Property Value¶

Color

Tables¶

Tables defined in the worksheet.

public ExcelTableCollection Tables { get; }

Property Value¶

ExcelTableCollection

ThreadedComments¶

A collection of threaded comments referenced in the worksheet.

public ExcelWorksheetThreadedComments ThreadedComments { get; }

Property Value¶

ExcelWorksheetThreadedComments

View¶

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

public ExcelWorksheetView View { get; }

Property Value¶

ExcelWorksheetView

Workbook¶

The workbook object

public ExcelWorkbook Workbook { get; }

Property Value¶

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.

public XmlDocument WorksheetXml { get; }

Property Value¶

XmlDocument

Methods¶

ClearFormulaValues()¶

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

public void ClearFormulaValues()

ClearFormulas()¶

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

public void ClearFormulas()

Column(int)¶

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

public ExcelColumn Column(int col)

Parameters¶

col int

The column number in the worksheet

Returns¶

ExcelColumn

DeleteColumn(int)¶

Delete the specified column from the worksheet.

public void DeleteColumn(int column)

Parameters¶

column int

The column to be deleted

DeleteColumn(int, int)¶

Delete the specified columns from the worksheet.

public void DeleteColumn(int columnFrom, int columns)

Parameters¶

columnFrom int

The start column

columns int

Number of columns to delete

DeleteRow(int)¶

Delete the specified row from the worksheet.

public void DeleteRow(int row)

Parameters¶

row int

A row to be deleted

DeleteRow(int, int)¶

Delete the specified rows from the worksheet.

public void DeleteRow(int rowFrom, int rows)

Parameters¶

rowFrom int

The start row

rows int

Number of rows to delete

Dispose()¶

Disposes the worksheet

public void Dispose()

Equals(ExcelWorksheet, ExcelWorksheet)¶

Check if a worksheet is equal to another

public bool Equals(ExcelWorksheet x, ExcelWorksheet y)

Parameters¶

x ExcelWorksheet

First worksheet

y ExcelWorksheet

Second worksheet

Returns¶

bool

GetFormulaRange(int, int)¶

Gets the range for the formula in the cell. A shared formula will return the range for the entire series. An array formula will return the range of the output of the formula. If you want the range of a dynamic array formula, you must calculate the formula first.

public ExcelRangeBase GetFormulaRange(int row, int column)

Parameters¶

row int

The row of the cell containing the formula.

column int

The column of the cell containing the formula.

Returns¶

ExcelRangeBase

The range the formula spans

GetHashCode(ExcelWorksheet)¶

Returns a hashcode generated from the WorksheetXml

public int GetHashCode(ExcelWorksheet obj)

Parameters¶

obj ExcelWorksheet

The worksheet

Returns¶

int

The hashcode

GetMergeCellId(int, int)¶

Get MergeCell Index No

public int GetMergeCellId(int row, int column)

Parameters¶

row int

column int

Returns¶

int

GetValue(int, int)¶

Get the cell value from the worksheet

public object GetValue(int Row, int Column)

Parameters¶

Row int

The row number

Column int

The row number

Returns¶

object

The value

GetValue<T>(int, int)¶

Get a strongly typed cell value from the worksheet

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

Parameters¶

Row int

The row number

Column int

The row number

Returns¶

T

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

Type Parameters¶

T

The type

InsertColumn(int, int)¶

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).

public void InsertColumn(int columnFrom, int columns)

Parameters¶

columnFrom int

The position of the new column(s)

columns int

Number of columns to insert

InsertColumn(int, int, int)¶

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).

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

Parameters¶

columnFrom int

The position of the new column(s)

columns int

Number of columns to insert.

copyStylesFromColumn int

Copy Styles from this column. Applied to all inserted columns

InsertRow(int, int)¶

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).

public void InsertRow(int rowFrom, int rows)

Parameters¶

rowFrom int

The position of the new row(s)

rows int

Number of rows to insert

InsertRow(int, int, int)¶

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).

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

Parameters¶

rowFrom int

The position of the new row(s)

rows int

Number of rows to insert.

copyStylesFromRow int

Copy Styles from this row. Applied to all inserted rows

Row(int)¶

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

public ExcelRow Row(int row)

Parameters¶

row int

The row number in the worksheet

Returns¶

ExcelRow

Select()¶

Make the current worksheet active.

public void Select()

Select(string)¶

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

public void Select(string Address)

Parameters¶

Address string

An address range

Select(string, bool)¶

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

public void Select(string Address, bool SelectSheet)

Parameters¶

Address string

A range of cells

SelectSheet bool

Make the sheet active

Select(ExcelAddress)¶

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

public void Select(ExcelAddress Address)

Parameters¶

Address ExcelAddress

An address range

Select(ExcelAddress, bool)¶

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

public void Select(ExcelAddress Address, bool SelectSheet)

Parameters¶

Address ExcelAddress

A range of cells

SelectSheet bool

Make the sheet active

SetValue(int, int, object)¶

Set the value of a cell

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

Parameters¶

Row int

The row number

Column int

The column number

Value object

The value

SetValue(string, object)¶

Set the value of a cell

public void SetValue(string Address, object Value)

Parameters¶

Address string

The Excel address

Value object

The value

ToString()¶

Returns the name of the worksheet

public override string ToString()

Returns¶

string

The name of the worksheet