CellRange Class

GemBox.Spreadsheet Help

Cell range is a rectangular group of worksheet cells.

Inheritance Hierarchy

System Object
GemBox.Spreadsheet AbstractRange
GemBox.Spreadsheet CellRange

Namespace: GemBox.Spreadsheet
Assembly: GemBox.Spreadsheet (in GemBox.Spreadsheet.dll) Version: 37.3.30.1035

Syntax

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

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

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

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

Remarks

Cell range is determined by its top (FirstRowIndex), left (FirstColumnIndex), bottom (LastRowIndex) and right (LastColumnIndex) borders. This properties are read-only, so if you require different cell range use one of GetSubrange methods (GetSubrangeAbsolute(Int32, Int32, Int32, Int32), GetSubrangeRelative(Int32, Int32, Int32, Int32) 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.

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

Remarks

Note that for Style property set on a cell range that is not merged, you can't use the following format:

Copy to Clipboard Print

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

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

Dim cr As CellRange = excelFile.Worksheets(0).Rows(1).Cells
cr.Style.Rotation = 30

Dim cr As CellRange = excelFile.Worksheets(0).Rows(1).Cells
cr.Style.Rotation = 30

because that would first call Style property get method and that will certainly fail because Style property get is defined only for a merged cell range.

Instead you can use two different code patterns, depending on whether you want to replace or combine the existing cell range styles with the new style.

Examples

If you want to replace cell style on every cell in a cell range use the following code:

Copy to Clipboard Print

CellRange cr = excelFile.Worksheets[0].Cells.GetSubrangeAbsolute(0, 0, 0, 10);
CellStyle style = new CellStyle();
style.Rotation = 30;
cr.Style = style;

CellRange cr = excelFile.Worksheets[0].Cells.GetSubrangeAbsolute(0, 0, 0, 10);
CellStyle style = new CellStyle();
style.Rotation = 30;
cr.Style = style;

Dim cr As CellRange = excelFile.Worksheets(0).Cells.GetSubrangeAbsolute(0, 0, 0, 10)
Dim style As CellStyle = New CellStyle()
style.Rotation = 30
cr.Style = style

Dim cr As CellRange = excelFile.Worksheets(0).Cells.GetSubrangeAbsolute(0, 0, 0, 10)
Dim style As CellStyle = New CellStyle()
style.Rotation = 30
cr.Style = style

If you want to set cell style property on every cell in a cell range (other cell style property values will remain unchanged) use the following code:

Copy to Clipboard Print

foreach (ExcelCell cell in excelFile.Worksheets[0].Rows[1].Cells)
    cell.Style.Rotation = 30;

foreach (ExcelCell cell in excelFile.Worksheets[0].Rows[1].Cells)
    cell.Style.Rotation = 30;

Dim cell As ExcelCell
For Each cell In excelFile.Worksheets(0).Rows(1).Cells
    cell.Style.Rotation = 30 
Next

Dim cell As ExcelCell
For Each cell In excelFile.Worksheets(0).Rows(1).Cells
    cell.Style.Rotation = 30 
Next

Examples

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

Copy to Clipboard Print

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.SetBorders(MultipleBorders.Outside, Color.Navy, 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).

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.SetBorders(MultipleBorders.Outside, Color.Navy, 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).

Dim cr As CellRange = 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.SetBorders(MultipleBorders.Outside, Color.Navy, 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).

Dim cr As CellRange = 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.SetBorders(MultipleBorders.Outside, Color.Navy, 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).