Table of Contents

Class CellRange

Namespace
Spire.Xls
Assembly
Spire.XLS.dll
public class CellRange : XlsRange, ICombinedRange, IXLSRange, IExcelApplication, IEnumerable<IXLSRange>, IEnumerable, IDisposable
Inheritance
CellRange
Implements
Inherited Members

Properties

Borders

Returns a Borders collection that represents the borders of a style or a range of cells (including a range defined as part of a conditional format). The following code illustrates how to access Borders property of the Range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set text
worksheet["C2"].Text = "Sample";

//Set borders
IBorders borders = worksheet["C2"].Borders;

//Set line style
borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;

//Set border color
borders[BordersLineType.EdgeTop].Color = Color.Red;
borders[BordersLineType.EdgeBottom].Color = Color.Red;

//Save to file
workbook.SaveToFile("CellFormats.xlsx");
public BordersCollection Borders { get; }

Property Value

BordersCollection

CellList

Returns a Range object that represents the cells in the specified range. The following code illustrates how to access CellList property of the Range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set text. The content contained by ![CDATA[]] will be expressed as plain text
ListCellRange cells = worksheet["A1:E8"].CellList;

//Do some manipulations
foreach (CellRange Range in cells)
    Range.Text = Range.RangeAddressLocal;

//Save to file
workbook.SaveToFile("CellList.xlsx");
public List<CellRange> CellList { get; }

Property Value

List<CellRange>

Cells

Returns a Range object that represents the cells in the specified range.

[Obsolete("Please use CellList")]
public CellRange[] Cells { get; }

Property Value

CellRange[]

Columns

Returns a Range object that represents the columns in the specified range. The following code illustrates how to access columns:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set columns
IXLSRange[] columns = worksheet["A1:E8"].Columns;

//Do some manipulations
foreach (IXLSRange column in columns)
  column.Text = column.RangeAddressLocal;

//Save to file
workbook.SaveToFile("Columns.xlsx");
public CellRange[] Columns { get; }

Property Value

CellRange[]

Comment

Returns a Comment object that represents the comment associated with the cell in the upper-left corner of the range. The following code illustrates how to access Comments property of the Range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Adding comments to a cell
worksheet.Range["A1"].AddComment().Text = "Comments";

//Add Rich Text Comments
CellRange range = worksheet.Range["A6"];
range.AddComment().RichText.Text = "RichText";
IRichTextString rtf = range.Comment.RichText;

//Formatting first 4 characters
IFont redFont = workbook.CreateFont();
redFont.IsBold = true;
redFont.Color = Color.Red;
rtf.SetFont(0, 3, redFont);

//Save to file
workbook.SaveToFile("DataValidation.xlsx");
public ExcelComment Comment { get; }

Property Value

ExcelComment

EndCell

Returns a Range object that represents the cell at the end of the region that contains the source range.

public CellRange EndCell { get; }

Property Value

CellRange

EntireColumn

Returns a Range object that represents the entire column (or columns) that contains the specified range.

public CellRange EntireColumn { get; }

Property Value

CellRange

EntireRow

Returns a Range object that represents the entire row (or rows) that contains the specified range.

public CellRange EntireRow { get; }

Property Value

CellRange

this[int, int]

Gets cell range. Row and column indexes are one-based.

public CellRange this[int row, int column] { get; set; }

Parameters

row int
column int

Property Value

CellRange

this[int, int, int, int]

Gets cell range. Row and column indexes are one-based.

public CellRange this[int row, int column, int lastRow, int lastColumn] { get; }

Parameters

row int
column int
lastRow int
lastColumn int

Property Value

CellRange

this[string]

Gets cell range.

public CellRange this[string name] { get; }

Parameters

name string

Property Value

CellRange

this[string, bool]

Gets cell range

public CellRange this[string name, bool IsR1C1Notation] { get; }

Parameters

name string
IsR1C1Notation bool

Property Value

CellRange

MergeArea

Returns a Range object that represents the merged range containing the specified cell. The following code illustrates how to access MergeArea property:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set text
worksheet["C2"].Text = "Sample text in cell";

//Set merge
worksheet["C2:D3"].Merge();

//Check merge area
Console.Write(worksheet["C2"].MergeArea.AddressLocal);
public CellRange MergeArea { get; }

Property Value

CellRange

RichText

Returns a RichTextString object that represents the rich text style. The following code illustrates how to set rich text formatting in the range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Create style
IStyle style = workbook.Styles.Add("CustomStyle");

//Set rich text
IRichTextString richText = worksheet["C2"].RichText;
richText.Text = "Sample text";

//Set rich text font
IFont font = style.Font;
font.IsBold = true;
richText.SetFont(0, 5, font);

//Save to file
workbook.SaveToFile("RichText.xlsx");
public RichText RichText { get; }

Property Value

RichText

Rows

Returns the number of the first row of the first area in the range. The following code illustrates how to access rows:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set rows
IXLSRange[] rows = worksheet["A1:E8"].Rows;

//Do some manipulations
foreach (IXLSRange row in rows)
  row.Text = row.RangeAddressLocal;

//Save to file
workbook.SaveToFile("Rows.xlsx");
public CellRange[] Rows { get; }

Property Value

CellRange[]

Style

Returns a Style object that represents the style of the specified range. The following code illustrates how to the style of the specified range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set text
worksheet["C2"].Text = "Sample";

//Add and set style
CellStyle style = workbook.Styles.Add("BorderStyle");
style.Color = Color.Red;
worksheet["C2"].Style = style;

//Save to file
workbook.SaveToFile("Style.xlsx");
public CellStyle Style { get; set; }

Property Value

CellStyle

Worksheet

Returns a worksheet object that represents the worksheet containing the specified range.

public Worksheet Worksheet { get; }

Property Value

Worksheet

Methods

Activate()

Active single cell in the worksheet

public CellRange Activate()

Returns

CellRange

AddComment()

Adds a comment to the range. The following code illustrates how to insert Comments in the Range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Adding comments to a cell
ICommentShape comment = worksheet.Range["A1"].AddComment();
comment.Text= "Comments";

//Save to file
workbook.SaveToFile("AddComment.xlsx");
public ExcelComment AddComment()

Returns

ExcelComment

Created comment or exists one.

AddComment(ExcelComment)

Adds a comment to the range.

public void AddComment(ExcelComment comment)

Parameters

comment ExcelComment

Comment to add

Clone(object, Dictionary<string, string>, Workbook)

Clones current IXLSRange.

public CellRange Clone(object parent, Dictionary<string, string> rangeNames, Workbook book)

Parameters

parent object

Parent object.

rangeNames Dictionary<string, string>

new names.

book Workbook

Parent workbook.

Returns

CellRange

Cloned instance.

Copy(CellRange)

Copies the range to the specified range.

public CellRange Copy(CellRange destRange)

Parameters

destRange CellRange

Destination range

Returns

CellRange

Destination range

Copy(CellRange, CopyRangeOptions)

Copies the range to the specified range.

public CellRange Copy(CellRange destRange, CopyRangeOptions copyOptions)

Parameters

destRange CellRange

Destination range.

copyOptions CopyRangeOptions

Copy options.

Returns

CellRange

Copy(CellRange, bool)

Copies the range to the specified range.

public CellRange Copy(CellRange destRange, bool updateReference)

Parameters

destRange CellRange
updateReference bool

Indicates whether to update reference cells.

Returns

CellRange

Copy(CellRange, bool, bool)

Copies the range to the specified range.

public CellRange Copy(CellRange destRange, bool updateReference, bool copyStyles)

Parameters

destRange CellRange

Destination range.

updateReference bool

Indicates whether to update reference cells.

copyStyles bool

Indicates whether to copy styles.

Returns

CellRange

Destination range

FindAll(string, FindType, ExcelFindOptions)

Finds the all ranges with specified string value.

public CellRange[] FindAll(string findValue, FindType flags, ExcelFindOptions findOptions)

Parameters

findValue string

Value to search.

flags FindType

Type of value to search.

findOptions ExcelFindOptions

Way to search.

Returns

CellRange[]

Found ranges

FindAllBool(bool)

Finds the cell with the input bool.

public CellRange[] FindAllBool(bool boolValue)

Parameters

boolValue bool

Bool value to search for

Returns

CellRange[]

Found ranges

FindAllDateTime(DateTime)

Finds the cell with the input datetime.

public CellRange[] FindAllDateTime(DateTime dateTimeValue)

Parameters

dateTimeValue DateTime

DateTime value to search for

Returns

CellRange[]

Found ranges

FindAllNumber(double, bool)

Finds the cell with the input double.

public CellRange[] FindAllNumber(double doubleValue, bool formulaValue)

Parameters

doubleValue double

Double value to search for

formulaValue bool

Indicates whether to find formula value

Returns

CellRange[]

Found ranges

FindAllString(string, bool, bool)

Finds the cell with the input string.

public CellRange[] FindAllString(string stringValue, bool formula, bool formulaValue)

Parameters

stringValue string

String value to search for

formula bool

Indicates whether include formula

formulaValue bool

Indicates whether include formula value

Returns

CellRange[]

Found ranges

FindAllTimeSpan(TimeSpan)

Finds the cell with input timespan

public CellRange[] FindAllTimeSpan(TimeSpan timeSpanValue)

Parameters

timeSpanValue TimeSpan

time span value to search for

Returns

CellRange[]

Found ranges

FindBool(bool)

Finds the cell with the input bool.

public CellRange FindBool(bool boolValue)

Parameters

boolValue bool

Bool value to search for

Returns

CellRange

Found range

FindDateTime(DateTime)

Finds the cell with the input datetime.

public CellRange FindDateTime(DateTime dateTimeValue)

Parameters

dateTimeValue DateTime

Datetime value to search for

Returns

CellRange

Found range

FindNumber(double, bool)

Finds the cell with the input double.

public CellRange FindNumber(double doubleValue, bool formulaValue)

Parameters

doubleValue double

Double value to search for

formulaValue bool

Indicates whether includes formula value to search for

Returns

CellRange

Found range

FindString(string, bool, bool)

Finds the cell with the input string.

public CellRange FindString(string stringValue, bool formula, bool formulaValue)

Parameters

stringValue string

String value to search for

formula bool

Indicates whether includes formula to search for

formulaValue bool

Indicates whether includes formula value to search for

Returns

CellRange

Found range

FindTimeSpan(TimeSpan)

Finds the cell with the input time span.

public CellRange FindTimeSpan(TimeSpan timeSpanValue)

Parameters

timeSpanValue TimeSpan

Time span value to search for.

Returns

CellRange

Found range.

GetDependentRanges(bool) 

public CellRange[] GetDependentRanges(bool isAll)

Parameters

isAll bool

Returns

CellRange[]

GetReferRanges() 

public ReferRangeArea[] GetReferRanges()

Returns

ReferRangeArea[]

Intersect(CellRange)

Get intersection range with the specified range.

public CellRange Intersect(CellRange range)

Parameters

range CellRange

Range which to intersect.

Returns

CellRange

Range intersection.

Merge(CellRange)

Creates a merged cell from the specified Range object. The following code illustrates how to check whether two ranges are mergable or not:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Merge range
worksheet["A2:B2"].Merge();

//Get mergable range
IXLSRange mergableRange = worksheet["A2"].MergeArea.Merge(worksheet["C2"]);

//Check mergable Area
Console.Write(mergableRange.RangeAddressLocal);

//Save to file
workbook.SaveToFile("Intersect.xlsx");
public CellRange Merge(CellRange range)

Parameters

range CellRange

The Range to merge with.

Returns

CellRange

Merged ranges.

Move(CellRange)

Moves the cells to the specified Range.

public void Move(CellRange destRange)

Parameters

destRange CellRange

Move(CellRange, bool, bool)

Moves the cells to the specified Range.

public void Move(CellRange destRange, bool copyStyle, bool updateReference)

Parameters

destRange CellRange
copyStyle bool
updateReference bool

Indicates whether to update reference range.

SetDataValidation(Validation)

Sets data validation for the range.

public void SetDataValidation(Validation dataValidation)

Parameters

dataValidation Validation