CellRange Class

Namespace:
GemBox.Spreadsheet
Assembly:
GemBox.Spreadsheet.dll

Cell range is a rectangular group of worksheet cells.

public class CellRange : AbstractRange, IEnumerable<ExcelCell>, IEnumerable
Public Class CellRange
    Inherits AbstractRange
    Implements IEnumerable(Of ExcelCell), IEnumerable
Inheritance:
CellRange
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 comment

public override ExcelComment Comment { get; set; }
Public Overrides Property Comment As ExcelComment
Property Value
ExcelComment
Overrides

EndPosition

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

public string EndPosition { get; }
Public ReadOnly Property EndPosition As String
Property Value
String

FirstColumnIndex

Gets index of the first (leftmost) column.

public int FirstColumnIndex { get; }
Public ReadOnly Property FirstColumnIndex As Integer
Property Value
Int32

FirstRowIndex

Gets index of the first (topmost) row.

public int FirstRowIndex { get; }
Public ReadOnly Property FirstRowIndex As Integer
Property Value
Int32

Formula

Gets or sets formula string.

public override string Formula { get; set; }
Public Overrides Property Formula As String
Property Value
String
Overrides
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
InvalidOperationException

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

FormulaR1C1

Gets or sets a formula string in R1C1 notation.

public override string FormulaR1C1 { get; set; }
Public Overrides Property FormulaR1C1 As String
Property Value
String
Overrides

Height

Gets height of this cell range, in rows.

public int Height { get; }
Public ReadOnly Property Height As Integer
Property Value
Int32

Gets or sets hyperlink

public override SpreadsheetHyperlink Hyperlink { get; set; }
Public Overrides Property Hyperlink As SpreadsheetHyperlink
Property Value
SpreadsheetHyperlink
Overrides

IndexingMode

Gets indexing mode used for cell range.

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.

public bool IsAnyCellMerged { get; }
Public ReadOnly Property IsAnyCellMerged As Boolean
Property Value
Boolean

Item[Int32]

Gets excel cell at the specified index.

public ExcelCell this[int contextIndex] { get; }
Public ReadOnly Property Item(contextIndex As Integer) As ExcelCell
Parameters
contextIndex
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.

See Also

Item[Int32, Int32]

Gets excel cell at the specified relative position.

public ExcelCell this[int relativeRow, int relativeColumn] { get; }
Public ReadOnly Property Item(relativeRow As Integer, relativeColumn As Integer) As ExcelCell
Parameters
relativeRow
Int32

The zero-based relative row position.

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

public ExcelCell this[string contextName] { get; }
Public ReadOnly Property Item(contextName As String) As ExcelCell
Parameters
contextName
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.).

See Also

LastColumnIndex

Gets index of the last (rightmost) column.

public int LastColumnIndex { get; }
Public ReadOnly Property LastColumnIndex As Integer
Property Value
Int32

LastRowIndex

Gets index of the last (bottommost) row.

public int LastRowIndex { get; }
Public ReadOnly Property LastRowIndex As Integer
Property Value
Int32

Merged

Gets or sets whether cells in this range are merged.

public virtual bool Merged { get; set; }
Public Overridable Property Merged As Boolean
Property Value
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
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.

public string StartPosition { get; }
Public ReadOnly Property StartPosition As String
Property Value
String

Value

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

public override object Value { get; set; }
Public Overrides Property Value As Object
Property Value
Object
Overrides
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
InvalidOperationException

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

See Also

Width

Gets width of this cell range, in columns.

public int Width { get; }
Public ReadOnly Property Width As Integer
Property Value
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.

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.

public void AutoFitRowHeight(bool autoFitMergedRanges = false)
Public Sub AutoFitRowHeight(autoFitMergedRanges As Boolean = False)
Parameters
autoFitMergedRanges
Boolean

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

Calculate()

Calculates formula for every excel cell in this range.

public override void Calculate()
Public Overrides Sub Calculate
Overrides

CopyTo(ExcelWorksheet, Int32, Int32)

Copies this cell range to another worksheet.

[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
Int32

Absolute index of the destination row.

absoluteColumn
Int32

Absolute index of the destination column.

Remarks

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

ArgumentOutOfRangeException is thrown if destination range:

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

Destination range is invalid.

ArgumentNullException

Destination worksheet is null.

CopyTo(ExcelWorksheet, Int32, Int32, CopyOptions)

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

[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
Int32

Absolute index of the destination row.

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

ArgumentOutOfRangeException is thrown if destination range:

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

Destination range is invalid.

ArgumentNullException

Destination worksheet is null.

CopyTo(ExcelWorksheet, String)

Copies this cell range to another worksheet.

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

ArgumentOutOfRangeException is thrown if destination range:

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

Destination range is invalid.

ArgumentNullException

Destination worksheet is null.

ArgumentException

Top-left cell is incorrectly specified.

CopyTo(ExcelWorksheet, String, CopyOptions)

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

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

ArgumentOutOfRangeException is thrown if destination range:

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

Destination range is invalid.

ArgumentNullException

Destination worksheet is null.

ArgumentException

Top-left cell is incorrectly specified.

CopyTo(Int32, Int32)

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

[ComVisible(false)]
public void CopyTo(int absoluteRow, int absoluteColumn)
<ComVisible(False)>
Public Sub CopyTo(absoluteRow As Integer, absoluteColumn As Integer)
Parameters
absoluteRow
Int32

Absolute index of the destination row.

absoluteColumn
Int32

Absolute index of the destination column.

Remarks

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

ArgumentOutOfRangeException is thrown if destination range:

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

Destination range is invalid.

ArgumentNullException

Destination worksheet is null.

CopyTo(Int32, Int32, CopyOptions)

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

[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
Int32

Absolute index of the destination row.

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

ArgumentOutOfRangeException is thrown if destination range:

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

Destination range is invalid.

ArgumentNullException

Destination worksheet is null.

CopyTo(String)

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

[ComVisible(true)]
public void CopyTo(string topLeftCell)
<ComVisible(True)>
Public Sub CopyTo(topLeftCell As String)
Parameters
topLeftCell
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.

ArgumentOutOfRangeException is thrown if destination range:

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

Destination range is invalid.

ArgumentNullException

Destination worksheet is null.

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.

[ComVisible(false)]
public void CopyTo(string topLeftCell, CopyOptions copyOptions)
<ComVisible(False)>
Public Sub CopyTo(topLeftCell As String, copyOptions As CopyOptions)
Parameters
topLeftCell
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.

ArgumentOutOfRangeException is thrown if destination range:

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

Destination range is invalid.

ArgumentNullException

Destination worksheet is null.

ArgumentException

Top-left cell is incorrectly specified.

Equals(Object)

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

public override bool Equals(object obj)
Public Overrides Function Equals(obj As Object) As Boolean
Parameters
obj
Object

The Object to compare with this CellRange instance.

Returns
Boolean

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

Overrides

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.

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.

public AutoFilter Filter(bool active)
Public Function Filter(active As Boolean) As AutoFilter
Parameters
active
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 Regex in the current cell range.

public IEnumerable<ExcelCell> FindAllText(string text)
Public Function FindAllText(text As String) As IEnumerable(Of ExcelCell)
Parameters
text
String

Text to search.

Returns
IEnumerable<ExcelCell>

All ExcelCells where the text was found.

Exceptions
ArgumentNullException

text is null.

FindAllText(String, Boolean)

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

public IEnumerable<ExcelCell> FindAllText(string text, bool matchCase)
Public Function FindAllText(text As String, matchCase As Boolean) As IEnumerable(Of ExcelCell)
Parameters
text
String

Text to search.

matchCase
Boolean

true to match exact case, false otherwise.

Returns
IEnumerable<ExcelCell>

All ExcelCells where the text was found.

Exceptions
ArgumentNullException

text is null.

FindAllText(String, Boolean, Boolean)

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

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
String

Text to search.

matchCase
Boolean

true to match exact case, false otherwise.

matchEntireCellContents
Boolean

true to match entire cell contents, false otherwise.

Returns
IEnumerable<ExcelCell>

All ExcelCells where the text was found.

Exceptions
ArgumentNullException

text is null.

FindAllText(Regex)

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

public IEnumerable<ExcelCell> FindAllText(Regex regex)
Public Function FindAllText(regex As Regex) As IEnumerable(Of ExcelCell)
Parameters
regex
Regex

The Regex which should be matched.

Returns
IEnumerable<ExcelCell>

All ExcelCells where the text was found.

Exceptions
ArgumentNullException

regex is null.

FindText(String, out ExcelCell)

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

public bool FindText(string text, out ExcelCell cell)
Public Function FindText(text As String, ByRef cell As ExcelCell) As Boolean
Parameters
text
String

Text to search.

cell
ExcelCell

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

Returns
Boolean

true if text is found, false otherwise.

Exceptions
ArgumentNullException

text is null.

FindText(String, Boolean, out ExcelCell)

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

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
String

Text to search.

matchCase
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
Boolean

true if text is found, false otherwise.

Exceptions
ArgumentNullException

text is null.

FindText(String, Boolean, Boolean, out ExcelCell)

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

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
String

Text to search.

matchCase
Boolean

true to match exact case, false otherwise.

matchEntireCellContents
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
Boolean

true if text is found, false otherwise.

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

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
String

Text to search.

matchCase
Boolean

true to match exact case, false otherwise.

matchEntireCellContents
Boolean

true to match entire cell contents, false otherwise.

row
Int32

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

column
Int32

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

Returns
Boolean

true if text is found, false otherwise.

Exceptions
ArgumentNullException

text is null.

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

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

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
String

Text to search.

matchCase
Boolean

true to match exact case, false otherwise.

row
Int32

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

column
Int32

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

Returns
Boolean

true if text is found, false otherwise.

Exceptions
ArgumentNullException

text is null.

FindText(String, out Int32, out Int32)

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

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
String

Text to search.

row
Int32

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

column
Int32

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

Returns
Boolean

true if text is found, false otherwise.

Exceptions
ArgumentNullException

text is null.

FindText(Regex, out ExcelCell)

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

public bool FindText(Regex regex, out ExcelCell cell)
Public Function FindText(regex As Regex, ByRef cell As ExcelCell) As Boolean
Parameters
regex
Regex

The Regex which should be matched.

cell
ExcelCell

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

Returns
Boolean

true if text is found, false otherwise.

Exceptions
ArgumentNullException

regex is null.

FindText(Regex, out Int32, out Int32)

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

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
Regex

The Regex which should be matched.

row
Int32

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

column
Int32

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

Returns
Boolean

true if text is found, false otherwise.

Exceptions
ArgumentNullException

regex is null.

GetCurrentRegion()

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

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

public IEnumerator<ExcelCell> GetEnumerator()
Public Function GetEnumerator As IEnumerator(Of ExcelCell)
Returns
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.

public override int GetHashCode()
Public Overrides Function GetHashCode As Integer
Returns
Int32

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

Overrides

GetReadEnumerator()

Returns enumerator for the CellRange (only allocated cells).

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.

public CellRange GetSubrange(string reference)
Public Function GetSubrange(reference As String) As CellRange
Parameters
reference
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").

See Also

GetSubrange(String, String)

Returns new cell range using start and end position.

public CellRange GetSubrange(string firstCell, string lastCell)
Public Function GetSubrange(firstCell As String, lastCell As String) As CellRange
Parameters
firstCell
String

Name of first (top-left) cell.

lastCell
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
ArgumentOutOfRangeException

Arguments are out of range.

See Also

GetSubrangeAbsolute(Int32, Int32, Int32, Int32)

Returns new cell range using absolute indexing.

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
Int32

Absolute index of the first row.

firstAbsoluteColumn
Int32

Absolute index of the first column.

lastAbsoluteRow
Int32

Absolute index of the last row.

lastAbsoluteColumn
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
ArgumentOutOfRangeException

Arguments are out of range.

See Also

GetSubrangeRelative(Int32, Int32, Int32, Int32)

Returns new cell range using relative indexing.

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
Int32

Relative index of the first row.

relativeColumn
Int32

Relative index of the first column.

width
Int32

Width of new cell range in columns.

height
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
ArgumentOutOfRangeException

Arguments are out of range.

See Also

Insert(InsertShiftDirection)

Inserts a range by shifting cells in the specified direction.

public override void Insert(InsertShiftDirection direction)
Public Overrides Sub Insert(direction As InsertShiftDirection)
Parameters
direction
InsertShiftDirection

The shift direction.

Overrides

Overlaps(CellRange)

Checks if this cell range overlaps with another cell range.

public bool Overlaps(CellRange range)
Public Function Overlaps(range As CellRange) As Boolean
Parameters
range
CellRange

Cell range.

Returns
Boolean

true if cell ranges overlap; otherwise, false.

Exceptions
ArgumentNullException

range is null.

PositionToRowColumn(String, out Int32, out Int32)

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

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
String

Position string.

row
Int32

Row index.

column
Int32

Column index.

Remove(RemoveShiftDirection)

Removes a range by shifting cells in the specified direction.

public override void Remove(RemoveShiftDirection direction)
Public Overrides Sub Remove(direction As RemoveShiftDirection)
Parameters
direction
RemoveShiftDirection

The shift direction.

Overrides

RowColumnToPosition(Int32, Int32)

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

public static string RowColumnToPosition(int row, int column)
Public Shared Function RowColumnToPosition(row As Integer, column As Integer) As String
Parameters
row
Int32

Row index.

column
Int32

Column index.

Returns
String

Position string.

SetArrayFormula(String)

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

public void SetArrayFormula(string formula)
Public Sub SetArrayFormula(formula As String)
Parameters
formula
String

The array formula.

See Also

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.

public SortState Sort(bool active)
Public Function Sort(active As Boolean) As SortState
Parameters
active
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.

public void Subtotal(int groupBy, ConsolidationFunction function, int[] totalList)
Public Sub Subtotal(groupBy As Integer, function As ConsolidationFunction, totalList As Integer())
Parameters
groupBy
Int32

The column index based on which grouping should be done.

function
ConsolidationFunction

The subtotal function to be applied.

totalList
Int32[]

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

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

Creates subtotals for the current cell range.

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
Int32

The column index based on which grouping should be done.

function
ConsolidationFunction

The subtotal function to be applied.

totalList
Int32[]

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

replace
Boolean

true to replace existing subtotals, false otherwise.

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

Creates subtotals for the current cell range.

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
Int32

The column index based on which grouping should be done.

function
ConsolidationFunction

The subtotal function to be applied.

totalList
Int32[]

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

replace
Boolean

true to replace existing subtotals, false otherwise.

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

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
Int32

The column index based on which grouping should be done.

function
ConsolidationFunction

The subtotal function to be applied.

totalList
Int32[]

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

replace
Boolean

true to replace existing subtotals, false otherwise.

pageBreaks
Boolean

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

summaryBelowData
Boolean

true to place the summary below data, false otherwise.

Operators

Equality(CellRange, CellRange)

Determines whether first and second CellRanges are equal.

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
Boolean

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

Inequality(CellRange, CellRange)

Determines whether first and second CellRanges are not equal.

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
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(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(Int32, 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(Regex, String)

Replaces all matches of the specified Regex with specified text in the current AbstractRange.

(Inherited from AbstractRange)

ReplaceText(String, String)

Replaces all occurrences of the specified text with another text in the current AbstractRange.

(Inherited from AbstractRange)

ReplaceText(String, String, Boolean)

Replaces all occurrences of the specified text with another text in the current AbstractRange.

(Inherited from AbstractRange)

ReplaceText(String, String, Boolean, Boolean)

Replaces all occurrences of the specified text with another text in the current AbstractRange.

(Inherited from AbstractRange)

ToString()

Returns a String that represents this AbstractRange instance.

(Inherited from AbstractRange)

Implements

Examples