Click or drag to resize
CellRange Class
Cell range is a rectangular group of worksheet cells.
Inheritance Hierarchy

Namespace:  GemBox.Spreadsheet
Assembly:  GemBox.Spreadsheet (in GemBox.Spreadsheet.dll) Version: 41.3.30.1018
Syntax
public class CellRange : AbstractRange, IEnumerable<ExcelCell>, 
	IEnumerable

The CellRange type exposes the following members.

Properties
  NameDescription
Public propertyComment
Gets or sets comment
(Overrides AbstractRangeComment.)
Public propertyEndPosition
Gets name of the last (bottom-right) cell in this cell range.
Public propertyFirstColumnIndex
Gets index of the first (leftmost) column.
Public propertyFirstRowIndex
Gets index of the first (topmost) row.
Public propertyCode exampleFormula
Gets or sets formula string.
(Overrides AbstractRangeFormula.)
Public propertyHeight
Gets height of this cell range, in rows.
Public propertyHyperlink
Gets or sets hyperlink
(Overrides AbstractRangeHyperlink.)
Public propertyCode exampleIndexingMode
Gets indexing mode used for cell range.
Public propertyIsAnyCellMerged
Returns is any cell in this cell range is merged; otherwise, .
Public propertyIsStyleDefault Obsolete.
Returns if all cells in AbstractRange have default cell style; otherwise, .
(Inherited from AbstractRange.)
Public propertyCode exampleItemInt32
Gets excel cell at the specified index.
Public propertyCode exampleItemString
Gets excel cell with the specified full or partial name.
Public propertyCode exampleItemInt32, Int32
Gets excel cell at the specified relative position.
Public propertyLastColumnIndex
Gets index of the last (rightmost) column.
Public propertyLastRowIndex
Gets index of the last (bottommost) row.
Public propertyMerged
Gets or sets whether cells in this range are merged.
Public propertyName
Gets the name of AbstractRange instance.
(Inherited from AbstractRange.)
Public propertyStartPosition
Gets name of the first (top-left) cell in this cell range.
Public propertyCode exampleStyle
Gets or sets the cell formatting of one or more cells.
(Inherited from AbstractRange.)
Public propertyValue
Gets or sets cell value on one or more excel cells.
(Overrides AbstractRangeValue.)
Public propertyWidth
Gets width of this cell range, in columns.
Top
Methods
  NameDescription
Public methodCalculate
Calculates formula for every excel cell in this range.
(Overrides AbstractRangeCalculate.)
Public methodClear
Clears the range (content, formatting, comment, hyperlink or all).
(Inherited from AbstractRange.)
Public methodCopyTo(String)
Copies this cell range to another position in the same worksheet or in another worksheet.
Public methodCopyTo(Int32, Int32)
Copies this cell range to another position in the same worksheet.
Public methodCopyTo(ExcelWorksheet, String)
Copies this cell range to another worksheet.
Public methodCopyTo(ExcelWorksheet, Int32, Int32)
Copies this cell range to another worksheet.
Public methodEquals
Determines whether the specified Object is equal to this CellRange instance.
(Overrides ObjectEquals(Object).)
Public methodCode exampleFilter

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

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

Public methodCode exampleFilter(Boolean)

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

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

Public methodFindText
Finds the first occurrence of the specified text in the current cell range.
Public methodGetCharacters(Int32)
Returns a FormattedCharacterRange object that represents a range of characters within the cell text. The range starts at a specified character position. Supported in XLSX only.
(Inherited from AbstractRange.)
Public methodGetCharacters(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. Supported in XLSX only.
(Inherited from AbstractRange.)
Public methodGetEnumerator
Returns an enumerator for the CellRange (all cells).
Public methodGetHashCode
Returns a hash code for this CellRange instance.
(Overrides ObjectGetHashCode.)
Public methodGetReadEnumerator
Returns enumerator for the CellRange (only allocated cells).
Public methodGetSubrange(String)
Returns new cell range using A1 style notation.
Public methodCode exampleGetSubrange(String, String)
Returns new cell range using start and end position.
Public methodGetSubrangeAbsolute
Returns new cell range using absolute indexing.
Public methodGetSubrangeRelative
Returns new cell range using relative indexing.
Public methodOverlaps
Checks if this cell range overlaps with another cell range.
Public methodStatic memberPositionToRowColumn
Converts position string ("A1", "BN27", etc.) to row and column index.
Public methodReplaceText(String, String)
Replaces all occurrences of the specified text with another text in the current AbstractRange.
(Inherited from AbstractRange.)
Public methodReplaceText(String, String, Boolean)
Replaces all occurrences of the specified text with another text in the current AbstractRange.
(Inherited from AbstractRange.)
Public methodReplaceText(String, String, Boolean, Boolean)
Replaces all occurrences of the specified text with another text in the current AbstractRange.
(Inherited from AbstractRange.)
Public methodStatic memberRowColumnToPosition
Converts row and column index to position string ("A1", "BN27", etc.).
Public methodSetBorders Obsolete.
Sets borders on one or more excel cells, taking cell position into account.
(Inherited from AbstractRange.)
Public methodCode exampleSort

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

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

Public methodToString
Returns a String that represents this AbstractRange instance.
(Inherited from AbstractRange.)
Top
Operators
  NameDescription
Public operatorStatic memberEquality
Determines whether first and second CellRanges are equal.
Public operatorStatic memberInequality
Determines whether first and second CellRanges are not equal.
Top
Explicit Interface Implementations
  NameDescription
Explicit interface implementationPrivate methodIEnumerableGetEnumerator
Returns an enumerator for the CellRange (all cells).
Top
Remarks

Cell range is determined by its top, left, bottom and right 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.

Examples

Following code creates horizontal, vertical and rectangular cell ranges and demonstrates how indexing works different in different context. SetBorders(MultipleBorders, SpreadsheetColor, LineStyle) method is used to mark outside borders of the rectangular range.

CellRange cr = excelFile.Worksheets[0].Rows[1].Cells;

cr[0].Value = cr.IndexingMode;
cr[3].Value = "D2";
cr["B"].Value = "B2";

cr = excelFile.Worksheets[0].Columns[4].Cells;

cr[0].Value = cr.IndexingMode;
cr[2].Value = "E3";
cr["5"].Value = "E5";

cr = excelFile.Worksheets[0].Cells.GetSubrange("F2", "J8");
cr.Style.Borders.SetBorders(MultipleBorders.Outside, SpreadsheetColor.FromName(ColorName.Blue), LineStyle.Dashed);

cr["I7"].Value = cr.IndexingMode;
cr[0, 0].Value = "F2";
cr["G3"].Value = "G3";
cr[5].Value = "F3"; // Cell range width is 5 (F G H I J).

cr = excelFile.Worksheets[0].Cells.GetSubrange("F10:J16"); // Using A1 notation.
cr.Style.Borders.SetBorders(MultipleBorders.Outside, SpreadsheetColor.FromName(ColorName.Blue), LineStyle.Dashed);

cr["I15"].Value = cr.IndexingMode;
cr[0].Value = "F10";
cr["F11"].Value = "F11";
See Also