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

Implements

IEqualityComparer<ExcelWorksheet>

IDisposable

Derived

ExcelChartsheet

Inherited Members

object.GetType()

object.MemberwiseClone()

object.Equals(object)

object.Equals(object, object)

object.ReferenceEquals(object, object)

object.GetHashCode()

Extension Methods

CalculationExtension.Calculate(ExcelWorksheet)

CalculationExtension.Calculate(ExcelWorksheet, ExcelCalculationOption)

CalculationExtension.Calculate(ExcelWorksheet, Action<ExcelCalculationOption>)

CalculationExtension.Calculate(ExcelWorksheet, string, ExcelCalculationOption)

CalculationExtension.Calculate(ExcelWorksheet, string)

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

AutoFilterAddress

The auto filter address. null means no auto filter.

[Obsolete("AutoFilterAddress is deprecated please use AutoFilter.Address instead.")]
public ExcelAddressBase AutoFilterAddress { get; }

Property Value

ExcelAddressBase

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

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

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

Pivottables 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

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

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

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

DeleteRow(int, int, bool)

Deletes the specified rows from the worksheet.

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

Parameters

rowFrom int

The number of the start row to be deleted

rows int

Number of rows to delete

shiftOtherRowsUp bool

Not used. Rows are always shifted

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(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

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

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