CellRange Class

Namespace:: GemBox.Spreadsheet

Assembly:: GemBox.Spreadsheet.dll

Cell range is a rectangular group of worksheet cells.

C#
VB.NET

public class CellRange : AbstractRange, IEnumerable<ExcelCell>, IEnumerable

Public Class CellRange
    Inherits AbstractRange
    Implements IEnumerable(Of ExcelCell), IEnumerable

Inheritance:: System.Object
AbstractRange
CellRange

Implements

System.Collections.Generic.IEnumerable<ExcelCell>

System.Collections.IEnumerable

Remarks

Cell range is determined by its FirstRowIndex, FirstColumnIndex, LastRowIndex and LastColumnIndex borders. These properties are read-only, so if you require different cell range use one of the following methods: GetSubrangeAbsolute(Int32, Int32, Int32, Int32), GetSubrangeRelative(Int32, Int32, Int32, Int32), GetSubrange(String) or GetSubrange(String, String). Specific cell can be accessed in a few ways, depending on IndexingMode. Cells in the range can be merged / unmerged by the use of Merged property.

Value property set will set value of multiple cells or of a merged range. Value property get has meaning only if range is merged; otherwise, exception is thrown.

Properties

Comment

Gets or sets merged cell comment.

C#
VB.NET

public override ExcelComment Comment { get; set; }

Public Overrides Property Comment As ExcelComment

Property Value

ExcelComment

Overrides

AbstractRange.Comment

Remarks

If the CellRange is not Merged, an exception is thrown.

Exceptions

System.InvalidOperationException

Cell range has defined comment only if it's merged.

EndPosition

Gets name of the last (bottom-right) cell in this cell range.

C#
VB.NET

public string EndPosition { get; }

Public ReadOnly Property EndPosition As String

Property Value

System.String

FirstColumnIndex

Gets index of the first (leftmost) column.

C#
VB.NET

public int FirstColumnIndex { get; }

Public ReadOnly Property FirstColumnIndex As Integer

Property Value

System.Int32

FirstRowIndex

Gets index of the first (topmost) row.

C#
VB.NET

public int FirstRowIndex { get; }

Public ReadOnly Property FirstRowIndex As Integer

Property Value

System.Int32

Formula

Gets or sets formula string.

C#
VB.NET

public override string Formula { get; set; }

Public Overrides Property Formula As String

Property Value

System.String

Overrides

AbstractRange.Formula

Remarks

In XLSX files, reading and writing of all formulas are supported.

In XLS files, reading and writing of all common and most of the advanced formulas are supported.

In XLSB files, reading and writing of all common and most of the advanced formulas are supported.

In ODS files, reading and writing of all formulas are supported, however, OpenOffice formulas don't have the same syntax as Excel formulas.

In CSV files, reading of all formulas is supported.

For more information on formulas, consult Microsoft Excel documentation.

Exceptions

System.InvalidOperationException

Property get is attempted on a cell range which is not merged.

FormulaR1C1

Gets or sets a formula string in R1C1 notation.

C#
VB.NET

public override string FormulaR1C1 { get; set; }

Public Overrides Property FormulaR1C1 As String

Property Value

System.String

Overrides

AbstractRange.FormulaR1C1

Height

Gets height of this cell range, in rows.

C#
VB.NET

public int Height { get; }

Public ReadOnly Property Height As Integer

Property Value

System.Int32

Hyperlink

Gets or sets merged cell hyperlink.

C#
VB.NET

public override SpreadsheetHyperlink Hyperlink { get; set; }

Public Overrides Property Hyperlink As SpreadsheetHyperlink

Property Value

SpreadsheetHyperlink

Overrides

AbstractRange.Hyperlink

Remarks

If the CellRange is not Merged, an exception is thrown.

Exceptions

System.InvalidOperationException

Cell range has defined hyperlink only if it's merged.

IndexingMode

Gets indexing mode used for cell range.

C#
VB.NET

public RangeIndexingMode IndexingMode { get; }

Public ReadOnly Property IndexingMode As RangeIndexingMode

Property Value

RangeIndexingMode

Remarks

If Height is 1, indexing mode is Horizontal.

Otherwise, if Width is 1, indexing mode is Vertical.

Otherwise, indexing mode is Rectangular.

IsAnyCellMerged

Returns true is any cell in this cell range is merged; otherwise, false.

C#
VB.NET

public bool IsAnyCellMerged { get; }

Public ReadOnly Property IsAnyCellMerged As Boolean

Property Value

System.Boolean

Item[Int32]

Gets excel cell at the specified index.

C#
VB.NET

public ExcelCell this[int contextIndex] { get; }

Public ReadOnly Property Item(contextIndex As Integer) As ExcelCell

Parameters

contextIndex: System.Int32

The zero-based context index of the cell.

Property Value

ExcelCell

Remarks

If IndexingMode is Horizontal context index is specifying relative column position.

If IndexingMode is Vertical context index is specifying relative row position.

If IndexingMode is Rectangular context index is specifying cell index inside cell range. The cell at StartPosition has index 0, and the cell at EndPosition has index of Width x Height - 1.

Item[Int32, Int32]

Gets excel cell at the specified relative position.

C#
VB.NET

public ExcelCell this[int relativeRow, int relativeColumn] { get; }

Public ReadOnly Property Item(relativeRow As Integer, relativeColumn As Integer) As ExcelCell

Parameters

relativeRow: System.Int32

The zero-based relative row position.

relativeColumn: System.Int32

The zero-based relative column position.

Property Value

ExcelCell

Remarks

Absolute position of excel cell is calculated by adding relativeRow and relativeColumn to FirstRowIndex and FirstColumnIndex.

Item[String]

Gets excel cell with the specified full or partial name.

C#
VB.NET

public ExcelCell this[string contextName] { get; }

Public ReadOnly Property Item(contextName As String) As ExcelCell

Parameters

contextName: System.String

Full or partial name of the cell.

Property Value

ExcelCell

Remarks

If IndexingMode is Rectangular full name of the cell must be used (for example; "A1", "D7", etc.).

If IndexingMode is Horizontal column name must be used (for example; "A", "D", etc.).

If IndexingMode is Vertical row name must be used (for example; "1", "7", etc.).

LastColumnIndex

Gets index of the last (rightmost) column.

C#
VB.NET

public int LastColumnIndex { get; }

Public ReadOnly Property LastColumnIndex As Integer

Property Value

System.Int32

LastRowIndex

Gets index of the last (bottommost) row.

C#
VB.NET

public int LastRowIndex { get; }

Public ReadOnly Property LastRowIndex As Integer

Property Value

System.Int32

Merged

Gets or sets whether cells in this range are merged.

C#
VB.NET

public virtual bool Merged { get; set; }

Public Overridable Property Merged As Boolean

Property Value

System.Boolean

Remarks

By setting this property to true, you are merging all the cells (ExcelCell) in this range. Merging process will fail if any of the cells in the range is already merged.

When modifying merged cell, whole merged range is modified. For example, if you set Value, value of merged range will be modified. You can find out if the cell is merged by checking if MergedRange property is different than null.

Exceptions

System.ArgumentException

Merged range can't be created because some of the cells in the range are already merged.

StartPosition

Gets name of the first (top-left) cell in this cell range.

C#
VB.NET

public string StartPosition { get; }

Public ReadOnly Property StartPosition As String

Property Value

System.String

Value

Gets or sets cell value on one or more excel cells.

C#
VB.NET

public override object Value { get; set; }

Public Overrides Property Value As Object

Property Value

System.Object

Overrides

AbstractRange.Value

Remarks

Property set will set value of multiple cells or of a merged range.

Property get has meaning only if range is Merged; otherwise, exception is thrown.

Exceptions

System.InvalidOperationException

Property get is attempted on a cell range which is not merged.

Width

Gets width of this cell range, in columns.

C#
VB.NET

public int Width { get; }

Public ReadOnly Property Width As Integer

Property Value

System.Int32

Methods

AutoFitColumnWidth()

Changes the range's column widths to fit the contents.

Use this method instead of multiple calls to AutoFit() method if auto-fitting multiple columns.

C#
VB.NET

public void AutoFitColumnWidth()

Public Sub AutoFitColumnWidth

AutoFitRowHeight(Boolean)

Changes the range's row heights to fit the contents.

Use this method instead of multiple calls to AutoFit() method if auto-fitting multiple rows.

C#
VB.NET

public void AutoFitRowHeight(bool autoFitMergedRanges = false)

Public Sub AutoFitRowHeight(autoFitMergedRanges As Boolean = False)

Parameters

autoFitMergedRanges: System.Boolean

true if merged ranges should be taken into account; otherwise, false.

Calculate()

Calculates formula for every excel cell in this range.

C#
VB.NET

public override void Calculate()

Public Overrides Sub Calculate

Overrides

AbstractRange.Calculate()

CopyTo(ExcelWorksheet, Int32, Int32)

Copies this cell range to another worksheet.

C#
VB.NET

[ComVisible(false)]
public void CopyTo(ExcelWorksheet destinationWorksheet, int absoluteRow, int absoluteColumn)

<ComVisible(False)>
Public Sub CopyTo(destinationWorksheet As ExcelWorksheet, absoluteRow As Integer, absoluteColumn As Integer)

Parameters

destinationWorksheet: ExcelWorksheet

Destination worksheet.

absoluteRow: System.Int32

Absolute index of the destination row.

absoluteColumn: System.Int32

Absolute index of the destination column.

Remarks

Destination cell range has the same width and height as this cell range.

System.ArgumentOutOfRangeException is thrown if destination range:

breaks Excel worksheet row or column limit,
overlaps with source range, or
overlaps with existing merged range.

Exceptions

System.ArgumentOutOfRangeException

Destination range is invalid.

System.ArgumentNullException

Destination worksheet is null.

CopyTo(ExcelWorksheet, Int32, Int32, CopyOptions)

Copies this cell range to another worksheet with specified copy options.

C#
VB.NET

[ComVisible(false)]
public void CopyTo(ExcelWorksheet destinationWorksheet, int absoluteRow, int absoluteColumn, CopyOptions copyOptions)

<ComVisible(False)>
Public Sub CopyTo(destinationWorksheet As ExcelWorksheet, absoluteRow As Integer, absoluteColumn As Integer, copyOptions As CopyOptions)

Parameters

destinationWorksheet: ExcelWorksheet

Destination worksheet.

absoluteRow: System.Int32

Absolute index of the destination row.

absoluteColumn: System.Int32

Absolute index of the destination column.

copyOptions: CopyOptions

The copy options.

Remarks

Destination cell range has the same width and height as this cell range.

System.ArgumentOutOfRangeException is thrown if destination range:

breaks Excel worksheet row or column limit,
overlaps with source range, or
overlaps with existing merged range.

Exceptions

System.ArgumentOutOfRangeException

Destination range is invalid.

System.ArgumentNullException

Destination worksheet is null.

CopyTo(ExcelWorksheet, String)

Copies this cell range to another worksheet.

C#
VB.NET

[ComVisible(false)]
public void CopyTo(ExcelWorksheet destinationWorksheet, string topLeftCell)

<ComVisible(False)>
Public Sub CopyTo(destinationWorksheet As ExcelWorksheet, topLeftCell As String)

Parameters

destinationWorksheet: ExcelWorksheet

Destination worksheet.

topLeftCell: System.String

Full name of the top-left cell of the destination range.

Remarks

Destination cell range has the same width and height as this cell range.

System.ArgumentOutOfRangeException is thrown if destination range:

breaks Excel worksheet row or column limit,
overlaps with source range, or
overlaps with existing merged range.

Exceptions

System.ArgumentOutOfRangeException

Destination range is invalid.

System.ArgumentNullException

Destination worksheet is null.

System.ArgumentException

Top-left cell is incorrectly specified.

CopyTo(ExcelWorksheet, String, CopyOptions)

Copies this cell range to another worksheet with specified copy options.

C#
VB.NET

[ComVisible(false)]
public void CopyTo(ExcelWorksheet destinationWorksheet, string topLeftCell, CopyOptions copyOptions)

<ComVisible(False)>
Public Sub CopyTo(destinationWorksheet As ExcelWorksheet, topLeftCell As String, copyOptions As CopyOptions)

Parameters

destinationWorksheet: ExcelWorksheet

Destination worksheet.

topLeftCell: System.String

Full name of the top-left cell of the destination range.

copyOptions: CopyOptions

The copy options.

Remarks

Destination cell range has the same width and height as this cell range.

System.ArgumentOutOfRangeException is thrown if destination range:

breaks Excel worksheet row or column limit,
overlaps with source range, or
overlaps with existing merged range.

Exceptions

System.ArgumentOutOfRangeException

Destination range is invalid.

System.ArgumentNullException

Destination worksheet is null.

System.ArgumentException

Top-left cell is incorrectly specified.

CopyTo(Int32, Int32)

Copies this cell range to another position in the same worksheet.

C#
VB.NET

[ComVisible(false)]
public void CopyTo(int absoluteRow, int absoluteColumn)

<ComVisible(False)>
Public Sub CopyTo(absoluteRow As Integer, absoluteColumn As Integer)

Parameters

absoluteRow: System.Int32

Absolute index of the destination row.

absoluteColumn: System.Int32

Absolute index of the destination column.

Remarks

Destination cell range has the same width and height as this cell range.

System.ArgumentOutOfRangeException is thrown if destination range:

breaks Excel worksheet row or column limit,
overlaps with source range, or
overlaps with existing merged range.

Exceptions

System.ArgumentOutOfRangeException

Destination range is invalid.

System.ArgumentNullException

Destination worksheet is null.

CopyTo(Int32, Int32, CopyOptions)

Copies this cell range to another position in the same worksheet with specified copy options.

C#
VB.NET

[ComVisible(false)]
public void CopyTo(int absoluteRow, int absoluteColumn, CopyOptions copyOptions)

<ComVisible(False)>
Public Sub CopyTo(absoluteRow As Integer, absoluteColumn As Integer, copyOptions As CopyOptions)

Parameters

absoluteRow: System.Int32

Absolute index of the destination row.

absoluteColumn: System.Int32

Absolute index of the destination column.

copyOptions: CopyOptions

The copy options.

Remarks

Destination cell range has the same width and height as this cell range.

System.ArgumentOutOfRangeException is thrown if destination range:

breaks Excel worksheet row or column limit,
overlaps with source range, or
overlaps with existing merged range.

Exceptions

System.ArgumentOutOfRangeException

Destination range is invalid.

System.ArgumentNullException

Destination worksheet is null.

CopyTo(String)

Copies this cell range to another position in the same worksheet or in another worksheet.

C#
VB.NET

[ComVisible(true)]
public void CopyTo(string topLeftCell)

<ComVisible(True)>
Public Sub CopyTo(topLeftCell As String)

Parameters

topLeftCell: System.String

Full name of the top-left cell of the destination range (e.g. "D4", "Sheet2!D5", etc.).

Remarks

Destination cell range has the same width and height as this cell range.

System.ArgumentOutOfRangeException is thrown if destination range:

breaks Excel worksheet row or column limit,
overlaps with source range, or
overlaps with existing merged range.

Exceptions

System.ArgumentOutOfRangeException

Destination range is invalid.

System.ArgumentNullException

Destination worksheet is null.

System.ArgumentException

Top-left cell is incorrectly specified.

CopyTo(String, CopyOptions)

Copies this cell range to another position in the same worksheet or in another worksheet with specified copy options.

C#
VB.NET

[ComVisible(false)]
public void CopyTo(string topLeftCell, CopyOptions copyOptions)

<ComVisible(False)>
Public Sub CopyTo(topLeftCell As String, copyOptions As CopyOptions)

Parameters

topLeftCell: System.String

Full name of the top-left cell of the destination range (e.g. "D4", "Sheet2!D5", etc.).

copyOptions: CopyOptions

The copy options.

Remarks

Destination cell range has the same width and height as this cell range.

System.ArgumentOutOfRangeException is thrown if destination range:

breaks Excel worksheet row or column limit,
overlaps with source range, or
overlaps with existing merged range.

Exceptions

System.ArgumentOutOfRangeException

Destination range is invalid.

System.ArgumentNullException

Destination worksheet is null.

System.ArgumentException

Top-left cell is incorrectly specified.

Equals(Object)

Determines whether the specified System.Object is equal to this CellRange instance.

C#
VB.NET

public override bool Equals(object obj)

Public Overrides Function Equals(obj As Object) As Boolean

Parameters

obj: System.Object

The System.Object to compare with this CellRange instance.

Returns

System.Boolean

true if the specified System.Object is a CellRange and is equal to this CellRange instance; otherwise, false.

Overrides

System.Object.Equals(System.Object)

Filter()

Gets the AutoFilter active on the parent worksheet (Filter) or Table (Filter) if active Range is equal to this CellRange; otherwise, a new AutoFilter instance, which is set as active on the parent worksheet or table.

AutoFilter is used to store filter settings and to apply filter on this range of cells.

C#
VB.NET

public AutoFilter Filter()

Public Function Filter As AutoFilter

Returns

AutoFilter

The AutoFilter active on the parent worksheet (Filter) or table if active Range is equal to this CellRange; otherwise, a new AutoFilter instance, which is set as active on the parent worksheet or table.

Remarks

This method has the same affect as Filter(Boolean) with parameter set to true.

The filter is set as active filter on a table if this range is inside any table in this worksheet. Otherwise the filter is set as active filter on the parent worksheet

Filter(Boolean)

Gets the AutoFilter active on the parent worksheet (Filter) or Table (Filter) if active Range is equal to this CellRange and parameter active is true; otherwise, a new AutoFilter instance, which is set as active on the parent worksheet or table if parameter active is true.

AutoFilter is used to store filter settings and to apply filter on this range of cells.

C#
VB.NET

public AutoFilter Filter(bool active)

Public Function Filter(active As Boolean) As AutoFilter

Parameters

active: System.Boolean

if set to true, gets or sets the AutoFilter active on the parent worksheet (Filter) or Table (Filter).

Returns

AutoFilter

The AutoFilter active on the parent worksheet (Filter) or Table (Filter) if active Range is equal to this CellRange and parameter active is true; otherwise, a new AutoFilter instance, which is set as active on the parent worksheet or table if parameter active is true.

Remarks

Use this method (with parameter active set to false) to create multiple independent AutoFilters for the same CellRange instance, even if it has an active AutoFilter (Filter), which can then be stored and used later on.

If the parameter active is set to true the filter is set as active filter on a table if this range is inside any table in this worksheet. Otherwise the filter is set as active filter on the parent worksheet

FindAllText(String)

Finds all occurrences which match the specified System.Text.RegularExpressions.Regex in the current cell range.

C#
VB.NET

public IEnumerable<ExcelCell> FindAllText(string text)

Public Function FindAllText(text As String) As IEnumerable(Of ExcelCell)

Parameters

text: System.String

Text to search.

Returns

System.Collections.Generic.IEnumerable<ExcelCell>

All ExcelCells where the text was found.

Exceptions

System.ArgumentNullException

text is null.

FindAllText(String, Boolean)

Finds all occurrences which match the specified System.Text.RegularExpressions.Regex in the current cell range.

C#
VB.NET

public IEnumerable<ExcelCell> FindAllText(string text, bool matchCase)

Public Function FindAllText(text As String, matchCase As Boolean) As IEnumerable(Of ExcelCell)

Parameters

text: System.String

Text to search.

matchCase: System.Boolean

true to match exact case, false otherwise.

Returns

System.Collections.Generic.IEnumerable<ExcelCell>

All ExcelCells where the text was found.

Exceptions

System.ArgumentNullException

text is null.

FindAllText(String, Boolean, Boolean)

Finds all occurrences which match the specified System.Text.RegularExpressions.Regex in the current cell range.

C#
VB.NET

public IEnumerable<ExcelCell> FindAllText(string text, bool matchCase, bool matchEntireCellContents)

Public Function FindAllText(text As String, matchCase As Boolean, matchEntireCellContents As Boolean) As IEnumerable(Of ExcelCell)

Parameters

text: System.String

Text to search.

matchCase: System.Boolean

true to match exact case, false otherwise.

matchEntireCellContents: System.Boolean

true to match entire cell contents, false otherwise.

Returns

System.Collections.Generic.IEnumerable<ExcelCell>

All ExcelCells where the text was found.

Exceptions

System.ArgumentNullException

text is null.

FindAllText(Regex)

Finds all occurrences which match the specified System.Text.RegularExpressions.Regex in the current cell range.

C#
VB.NET

public IEnumerable<ExcelCell> FindAllText(Regex regex)

Public Function FindAllText(regex As Regex) As IEnumerable(Of ExcelCell)

Parameters

regex: System.Text.RegularExpressions.Regex

The System.Text.RegularExpressions.Regex which should be matched.

Returns

System.Collections.Generic.IEnumerable<ExcelCell>

All ExcelCells where the text was found.

Exceptions

System.ArgumentNullException

regex is null.

FindText(String, out ExcelCell)

Finds the first occurrence of the specified text in the current cell range.

C#
VB.NET

public bool FindText(string text, out ExcelCell cell)

Public Function FindText(text As String, ByRef cell As ExcelCell) As Boolean

Parameters

text: System.String

Text to search.

cell: ExcelCell

The ExcelCell where the text was found or null if no text was found.

Returns

System.Boolean

true if text is found, false otherwise.

Exceptions

System.ArgumentNullException

text is null.

FindText(String, Boolean, out ExcelCell)

Finds the first occurrence of the specified text in the current cell range.

C#
VB.NET

public bool FindText(string text, bool matchCase, out ExcelCell cell)

Public Function FindText(text As String, matchCase As Boolean, ByRef cell As ExcelCell) As Boolean

Parameters

text: System.String

Text to search.

matchCase: System.Boolean

true to match exact case, false otherwise.

cell: ExcelCell

The ExcelCell where the text was found or null if no text was found.

Returns

System.Boolean

true if text is found, false otherwise.

Exceptions

System.ArgumentNullException

text is null.

FindText(String, Boolean, Boolean, out ExcelCell)

Finds the first occurrence of the specified text in the current cell range.

C#
VB.NET

public bool FindText(string text, bool matchCase, bool matchEntireCellContents, out ExcelCell cell)

Public Function FindText(text As String, matchCase As Boolean, matchEntireCellContents As Boolean, ByRef cell As ExcelCell) As Boolean

Parameters

text: System.String

Text to search.

matchCase: System.Boolean

true to match exact case, false otherwise.

matchEntireCellContents: System.Boolean

true to match entire cell contents, false otherwise.

cell: ExcelCell

The ExcelCell where the text was found or null if no text was found.

Returns

System.Boolean

true if text is found, false otherwise.

Exceptions

System.ArgumentNullException

text is null.

FindText(String, Boolean, Boolean, out Int32, out Int32)

Finds the first occurrence of the specified text in the current cell range.

C#
VB.NET

public bool FindText(string text, bool matchCase, bool matchEntireCellContents, out int row, out int column)

Public Function FindText(text As String, matchCase As Boolean, matchEntireCellContents As Boolean, ByRef row As Integer, ByRef column As Integer) As Boolean

Parameters

text: System.String

Text to search.

matchCase: System.Boolean

true to match exact case, false otherwise.

matchEntireCellContents: System.Boolean

true to match entire cell contents, false otherwise.

row: System.Int32

Index of the row where the text was found or -1 if no text was found.

column: System.Int32

Index of the column where the text was found or -1 if no text was found.

Returns

System.Boolean

true if text is found, false otherwise.

Exceptions

System.ArgumentNullException

text is null.

FindText(String, Boolean, out Int32, out Int32)

Finds the first occurrence of the specified text in the current cell range.

C#
VB.NET

public bool FindText(string text, bool matchCase, out int row, out int column)

Public Function FindText(text As String, matchCase As Boolean, ByRef row As Integer, ByRef column As Integer) As Boolean

Parameters

text: System.String

Text to search.

matchCase: System.Boolean

true to match exact case, false otherwise.

row: System.Int32

Index of the row where the text was found or -1 if no text was found.

column: System.Int32

Index of the column where the text was found or -1 if no text was found.

Returns

System.Boolean

true if text is found, false otherwise.

Exceptions

System.ArgumentNullException

text is null.

FindText(String, out Int32, out Int32)

Finds the first occurrence of the specified text in the current cell range.

C#
VB.NET

public bool FindText(string text, out int row, out int column)

Public Function FindText(text As String, ByRef row As Integer, ByRef column As Integer) As Boolean

Parameters

text: System.String

Text to search.

row: System.Int32

Index of the row where the text was found or -1 if no text was found.

column: System.Int32

Index of the column where the text was found or -1 if no text was found.

Returns

System.Boolean

true if text is found, false otherwise.

Exceptions

System.ArgumentNullException

text is null.

FindText(Regex, out ExcelCell)

Finds the first occurrence which matches the specified System.Text.RegularExpressions.Regex in the current cell range.

C#
VB.NET

public bool FindText(Regex regex, out ExcelCell cell)

Public Function FindText(regex As Regex, ByRef cell As ExcelCell) As Boolean

Parameters

regex: System.Text.RegularExpressions.Regex

The System.Text.RegularExpressions.Regex which should be matched.

cell: ExcelCell

The ExcelCell where the text was found or null if no text was found.

Returns

System.Boolean

true if text is found, false otherwise.

Exceptions

System.ArgumentNullException

regex is null.

FindText(Regex, out Int32, out Int32)

Finds the first occurrence which matches the specified System.Text.RegularExpressions.Regex in the current cell range.

C#
VB.NET

public bool FindText(Regex regex, out int row, out int column)

Public Function FindText(regex As Regex, ByRef row As Integer, ByRef column As Integer) As Boolean

Parameters

regex: System.Text.RegularExpressions.Regex

The System.Text.RegularExpressions.Regex which should be matched.

row: System.Int32

Index of the row where the text was found or -1 if no text was found.

column: System.Int32

Index of the column where the text was found or -1 if no text was found.

Returns

System.Boolean

true if text is found, false otherwise.

Exceptions

System.ArgumentNullException

regex is null.

GetCurrentRegion()

Selects a worksheet region to which the cell range belongs to.

C#
VB.NET

public CellRange GetCurrentRegion()

Public Function GetCurrentRegion As CellRange

Returns

CellRange

The worksheet region to which the cell range belongs to.

GetEnumerator()

Returns an enumerator for the CellRange (all cells).

C#
VB.NET

public IEnumerator<ExcelCell> GetEnumerator()

Public Function GetEnumerator As IEnumerator(Of ExcelCell)

Returns

System.Collections.Generic.IEnumerator<ExcelCell>

An enumerator for the CellRange (all cells).

Remarks

Returns default enumerator that iterates all cells in the range. If you are only reading existing cells (values or formatting), use more appropriate GetReadEnumerator().

GetHashCode()

Returns a hash code for this CellRange instance.

C#
VB.NET

public override int GetHashCode()

Public Overrides Function GetHashCode As Integer

Returns

System.Int32

An integer value that specifies a hash value for this CellRange instance.

Overrides

System.Object.GetHashCode()

GetReadEnumerator()

Returns enumerator for the CellRange (only allocated cells).

C#
VB.NET

public CellRangeEnumerator GetReadEnumerator()

Public Function GetReadEnumerator As CellRangeEnumerator

Returns

CellRangeEnumerator

An enumerator for the CellRange (only allocated cells).

Remarks

Returns enumerator that iterates only already allocated cells in the range. If you are only reading existing cells (values or formatting), use this enumerator as it is faster and doesn't allocate unnecessary cells.

GetSubrange(String)

Returns new cell range using A1 style notation.

C#
VB.NET

public CellRange GetSubrange(string reference)

Public Function GetSubrange(reference As String) As CellRange

Parameters

reference: System.String

The cell range reference in A1 style notation.

Returns

CellRange

New cell range using A1 style notation.

Remarks

New cell range must be within this cell range. Multiple area selection is not supported (e.g. "A1:B2,C3:D4,E5:F6").

Parameters

firstCell: System.String

Name of first (top-left) cell.

lastCell: System.String

Name of last (bottom-right) cell.

Returns

CellRange

New cell range using start and end position.

Remarks

New cell range must be within this cell range.

Exceptions

System.ArgumentOutOfRangeException

Arguments are out of range.

GetSubrangeAbsolute(Int32, Int32, Int32, Int32)

Returns new cell range using absolute indexing.

C#
VB.NET

public CellRange GetSubrangeAbsolute(int firstAbsoluteRow, int firstAbsoluteColumn, int lastAbsoluteRow, int lastAbsoluteColumn)

Public Function GetSubrangeAbsolute(firstAbsoluteRow As Integer, firstAbsoluteColumn As Integer, lastAbsoluteRow As Integer, lastAbsoluteColumn As Integer) As CellRange

Parameters

firstAbsoluteRow: System.Int32

Absolute index of the first row.

firstAbsoluteColumn: System.Int32

Absolute index of the first column.

lastAbsoluteRow: System.Int32

Absolute index of the last row.

lastAbsoluteColumn: System.Int32

Absolute index of the last column.

Returns

CellRange

New cell range using absolute indexing.

Remarks

New cell range must be within this cell range.

Exceptions

System.ArgumentOutOfRangeException

Arguments are out of range.

GetSubrangeRelative(Int32, Int32, Int32, Int32)

Returns new cell range using relative indexing.

C#
VB.NET

public CellRange GetSubrangeRelative(int relativeRow, int relativeColumn, int width, int height)

Public Function GetSubrangeRelative(relativeRow As Integer, relativeColumn As Integer, width As Integer, height As Integer) As CellRange

Parameters

relativeRow: System.Int32

Relative index of the first row.

relativeColumn: System.Int32

Relative index of the first column.

width: System.Int32

Width of new cell range in columns.

height: System.Int32

Height of new cell range in rows.

Returns

CellRange

New cell range using relative indexing.

Remarks

New cell range must be within this cell range.

Exceptions

System.ArgumentOutOfRangeException

Arguments are out of range.

Insert(InsertShiftDirection)

Inserts a range by shifting cells in the specified direction.

C#
VB.NET

public override void Insert(InsertShiftDirection direction)

Public Overrides Sub Insert(direction As InsertShiftDirection)

Parameters

direction: InsertShiftDirection

The shift direction.

Overrides

AbstractRange.Insert(InsertShiftDirection)

Overlaps(CellRange)

Checks if this cell range overlaps with another cell range.

C#
VB.NET

public bool Overlaps(CellRange range)

Public Function Overlaps(range As CellRange) As Boolean

Parameters

range: CellRange

Cell range.

Returns

System.Boolean

true if cell ranges overlap; otherwise, false.

Exceptions

System.ArgumentNullException

range is null.

PositionToRowColumn(String, out Int32, out Int32)

Converts position string ("A1", "BN27", etc.) to row and column index.

C#
VB.NET

public static void PositionToRowColumn(string position, out int row, out int column)

Public Shared Sub PositionToRowColumn(position As String, ByRef row As Integer, ByRef column As Integer)

Parameters

position: System.String

Position string.

row: System.Int32

Row index.

column: System.Int32

Column index.

Remove(RemoveShiftDirection)

Removes a range by shifting cells in the specified direction.

C#
VB.NET

public override void Remove(RemoveShiftDirection direction)

Public Overrides Sub Remove(direction As RemoveShiftDirection)

Parameters

direction: RemoveShiftDirection

The shift direction.

Overrides

AbstractRange.Remove(RemoveShiftDirection)

RowColumnToPosition(Int32, Int32)

Converts row and column index to position string ("A1", "BN27", etc.).

C#
VB.NET

public static string RowColumnToPosition(int row, int column)

Public Shared Function RowColumnToPosition(row As Integer, column As Integer) As String

Parameters

row: System.Int32

Row index.

column: System.Int32

Column index.

Returns

System.String

Position string.

SetArrayFormula(String)

Sets array formula (also known as CSE formula) to the range of cells.

C#
VB.NET

public void SetArrayFormula(string formula)

Public Sub SetArrayFormula(formula As String)

Parameters

formula: System.String

The array formula.

Sort(Boolean)

Gets the SortState active on the parent worksheet (Sort) if active Range is equal to this CellRange and parameter active is true; otherwise, a new SortState instance, which is set as active on the parent worksheet if parameter active is true.

SortState is used to store sort settings and to apply sort on this range of cells.

C#
VB.NET

public SortState Sort(bool active)

Public Function Sort(active As Boolean) As SortState

Parameters

active: System.Boolean

if set to true, gets or sets the SortState active on the parent worksheet (Sort).

Returns

SortState

The SortState active on the parent worksheet (Sort) if active Range is equal to this CellRange and parameter active is true; otherwise, a new SortState instance, which is set as active on the parent worksheet if parameter active is true.

Remarks

Use this method (with parameter active set to false) to create multiple independent SortStates for the same CellRange instance, even if it has an active SortState (Sort), which can then be stored and used later on.

Subtotal(Int32, ConsolidationFunction, Int32[])

Creates subtotals for the current cell range.

C#
VB.NET

public void Subtotal(int groupBy, ConsolidationFunction function, int[] totalList)

Public Sub Subtotal(groupBy As Integer, function As ConsolidationFunction, totalList As Integer())

Parameters

groupBy: System.Int32

The column index based on which grouping should be done.

function: ConsolidationFunction

The subtotal function to be applied.

totalList: System.Int32[]

An array of column indexes to which the subtotals are added.

Subtotal(Int32, ConsolidationFunction, Int32[], Boolean)

Creates subtotals for the current cell range.

C#
VB.NET

public void Subtotal(int groupBy, ConsolidationFunction function, int[] totalList, bool replace)

Public Sub Subtotal(groupBy As Integer, function As ConsolidationFunction, totalList As Integer(), replace As Boolean)

Parameters

groupBy: System.Int32

The column index based on which grouping should be done.

function: ConsolidationFunction

The subtotal function to be applied.

totalList: System.Int32[]

An array of column indexes to which the subtotals are added.

replace: System.Boolean

true to replace existing subtotals, false otherwise.

Subtotal(Int32, ConsolidationFunction, Int32[], Boolean, Boolean)

Creates subtotals for the current cell range.

C#
VB.NET

public void Subtotal(int groupBy, ConsolidationFunction function, int[] totalList, bool replace, bool pageBreaks)

Public Sub Subtotal(groupBy As Integer, function As ConsolidationFunction, totalList As Integer(), replace As Boolean, pageBreaks As Boolean)

Parameters

groupBy: System.Int32

The column index based on which grouping should be done.

function: ConsolidationFunction

The subtotal function to be applied.

totalList: System.Int32[]

An array of column indexes to which the subtotals are added.

replace: System.Boolean

true to replace existing subtotals, false otherwise.

pageBreaks: System.Boolean

true to add page breaks after each group, false otherwise.

Subtotal(Int32, ConsolidationFunction, Int32[], Boolean, Boolean, Boolean)

Creates subtotals for the current cell range.

C#
VB.NET

public void Subtotal(int groupBy, ConsolidationFunction function, int[] totalList, bool replace, bool pageBreaks, bool summaryBelowData)

Public Sub Subtotal(groupBy As Integer, function As ConsolidationFunction, totalList As Integer(), replace As Boolean, pageBreaks As Boolean, summaryBelowData As Boolean)

Parameters

groupBy: System.Int32

The column index based on which grouping should be done.

function: ConsolidationFunction

The subtotal function to be applied.

totalList: System.Int32[]

An array of column indexes to which the subtotals are added.

replace: System.Boolean

true to replace existing subtotals, false otherwise.

pageBreaks: System.Boolean

true to add page breaks after each group, false otherwise.

summaryBelowData: System.Boolean

true to place the summary below data, false otherwise.

Operators

Equality(CellRange, CellRange)

Determines whether first and second CellRanges are equal.

C#
VB.NET

public static bool operator ==(CellRange first, CellRange second)

Public Shared Operator =(first As CellRange, second As CellRange) As Boolean

Parameters

first: CellRange

The first CellRange.

second: CellRange

The second CellRange.

Returns

System.Boolean

true if first and second CellRanges are equal; otherwise, false.

Inequality(CellRange, CellRange)

Determines whether first and second CellRanges are not equal.

C#
VB.NET

public static bool operator !=(CellRange first, CellRange second)

Public Shared Operator <>(first As CellRange, second As CellRange) As Boolean

Parameters

first: CellRange

The first CellRange.

second: CellRange

The second CellRange.

Returns

System.Boolean

true if first and second CellRanges are not equal; otherwise, false.

Inherited Properties

CharacterRanges

Returns all FormattedCharacterRange objects that represents a range of characters within the cell text.

(Inherited from AbstractRange)

Name

Gets the name of AbstractRange instance.

(Inherited from AbstractRange)

Style

Gets or sets the cell formatting of one or more cells.

(Inherited from AbstractRange)

Inherited Methods

Clear(ClearOptions)	Clears the range (content, formatting, comment, hyperlink or all). (Inherited from AbstractRange)
GetCharacters(System.Int32)	Returns a FormattedCharacterRange object that represents a range of characters within the cell text. The range starts at a specified character position. (Inherited from AbstractRange)
GetCharacters(System.Int32, System.Int32)	Returns a FormattedCharacterRange object that represents a range of characters within the cell text. The range starts at a specified character position and has a specified length. (Inherited from AbstractRange)
ReplaceText(System.Text.RegularExpressions.Regex, System.String)	Replaces all matches of the specified System.Text.RegularExpressions.Regex with specified text in the current AbstractRange. (Inherited from AbstractRange)
ReplaceText(System.String, System.String)	Replaces all occurrences of the specified text with another text in the current AbstractRange. (Inherited from AbstractRange)
ReplaceText(System.String, System.String, System.Boolean)	Replaces all occurrences of the specified text with another text in the current AbstractRange. (Inherited from AbstractRange)
ReplaceText(System.String, System.String, System.Boolean, System.Boolean)	Replaces all occurrences of the specified text with another text in the current AbstractRange. (Inherited from AbstractRange)
ToString()	Returns a System.String that represents this AbstractRange instance. (Inherited from AbstractRange)

Implements

System.Collections.Generic.IEnumerable<T>

System.Collections.IEnumerable

Examples

Referencing ExcelCell and CellRange in C# and VB.NET

Excel AutoFiltering example

Excel Sorting example

CellRange Class

Implements

Remarks

Properties

Comment

Property Value

Overrides

Remarks

Exceptions

See Also

EndPosition

Property Value

FirstColumnIndex

Property Value

FirstRowIndex

Property Value

Formula

Property Value

Overrides

Remarks

Exceptions

FormulaR1C1

Property Value

Overrides

Height

Property Value

Hyperlink

Property Value

Overrides

Remarks

Exceptions

See Also

IndexingMode

Property Value

Remarks

IsAnyCellMerged

Property Value

Item[Int32]

Parameters

Property Value

Remarks

See Also

Item[Int32, Int32]

Parameters

Property Value

Remarks

Item[String]

Parameters

Property Value

Remarks

See Also

LastColumnIndex

Property Value

LastRowIndex

Property Value

Merged

Property Value

Remarks

Exceptions

StartPosition

Property Value

Value

Property Value

Overrides

Remarks

Exceptions

See Also

Width

Property Value

Methods

AutoFitColumnWidth()

AutoFitRowHeight(Boolean)

Parameters

Calculate()

Overrides

CopyTo(ExcelWorksheet, Int32, Int32)

Parameters

Remarks

Exceptions

CopyTo(ExcelWorksheet, Int32, Int32, CopyOptions)