Class Range
Namespace: Aspose.Cells
Assembly: Aspose.Cells.dll (25.2.0)
Encapsulates the object that represents a range of cells within a spreadsheet.
public class RangeInheritance
Inherited Members
object.GetType(), object.MemberwiseClone(), object.ToString(), object.Equals(object?), object.Equals(object?, object?), object.ReferenceEquals(object?, object?), object.GetHashCode()
Examples
The following example shows how to create a range and set value the range of Excel.
//Instantiating a Workbook object
Workbook workbook = new Workbook();
// Get the first Worksheet Cells.
Cells cells = workbook.Worksheets[0].Cells;
// Create a range (A1:D3).
Range range = cells.CreateRange("A1", "D3");
// Set value to the range.
range.Value = "Hello";
//Save the Excel file
workbook.Save("book1.xlsm");'Instantiating a Workbook object
Dim workbook As Workbook = New Workbook()
'Get the first Worksheet Cells.
Dim cells as Cells = workbook.Worksheets[0].Cells
'Create a range (A1:D3).
Dim range as Range = cells.CreateRange("A1", "D3")
'Set value to the range.
range.Value = "Hello"
'Save the Excel file
workbook.Save("book1.xlsm")Remarks
The Range class denotes a region of Excel spreadsheet. With this, you can format and set value of the range. And you can simply copy range of Excel too.
Properties
Address
Gets address of the range.
public string Address { get; }Property Value
ColumnCount
Gets the count of columns in the range.
public int ColumnCount { get; }Property Value
ColumnWidth
Sets or gets the column width of this range
public double ColumnWidth { get; set; }Property Value
CurrentRegion
Returns a Range object that represents the current region. The current region is a range bounded by any combination of blank rows and blank columns.
public Range CurrentRegion { get; }Property Value
EntireColumn
Gets a Range object that represents the entire column (or columns) that contains the specified range.
public Range EntireColumn { get; }Property Value
EntireRow
Gets a Range object that represents the entire row (or rows) that contains the specified range.
public Range EntireRow { get; }Property Value
FirstColumn
Gets the index of the first column of the range.
public int FirstColumn { get; }Property Value
FirstRow
Gets the index of the first row of the range.
public int FirstRow { get; }Property Value
Height
Gets the width of a range in points.
public double Height { get; }Property Value
Hyperlinks
Gets all hyperlink in the range.
public Hyperlink[] Hyperlinks { get; }Property Value
Left
Gets the distance, in points, from the left edge of column A to the left edge of the range.
public double Left { get; }Property Value
Name
Gets or sets the name of the range.
public string Name { get; set; }Property Value
Remarks
Named range is supported. For example,
range.Name = “Sheet1!MyRange”;
RefersTo
Gets the range’s refers to.
public string RefersTo { get; }Property Value
RowCount
Gets the count of rows in the range.
public int RowCount { get; }Property Value
RowHeight
Sets or gets the height of rows in this range
public double RowHeight { get; set; }Property Value
Top
Gets the distance, in points, from the top edge of row 1 to the top edge of the range.
public double Top { get; }Property Value
Value
Gets and sets the value of the range.
public object Value { get; set; }Property Value
Remarks
If the range contains multiple cells, the returned/applied object should be a two-dimension System.Array object.
Width
Gets the width of a range in points.
public double Width { get; }Property Value
Worksheet
Gets the Aspose.Cells.Range.Worksheetobject which contains this range.
public Worksheet Worksheet { get; }Property Value
this[int, int]
Gets Aspose.Cells.Cell object in this range.
public Cell this[int rowOffset, int columnOffset] { get; }Property Value
Methods
AddHyperlink(string, string, string)
Adds a hyperlink to a specified cell or a range of cells.
public Hyperlink AddHyperlink(string address, string textToDisplay, string screenTip)Parameters
address string
Address of the hyperlink.
textToDisplay string
The text to be displayed for the specified hyperlink.
screenTip string
The screenTip text for the specified hyperlink.
Returns
Aspose.Cells.Hyperlink object.
ApplyStyle(Style, StyleFlag)
Applies formats for a whole range.
public void ApplyStyle(Style style, StyleFlag flag)Parameters
style Style
The style object which will be applied.
flag StyleFlag
Flags which indicates applied formatting properties.
Remarks
Each cell in this range will contains a Aspose.Cells.Style object. So this is a memory-consuming method. Please use it carefully.
AutoFill(Range)
Automaticall fill the target range.
public void AutoFill(Range target)Parameters
target Range
the target range.
Examples
//Instantiating a Workbook object
Workbook workbook = new Workbook();
// Get the first Worksheet Cells.
Cells cells = workbook.Worksheets[0].Cells;
cells["A1"].PutValue(1);
cells["A2"].PutValue(2);
Aspose.Cells.Range source = cells.CreateRange("A1:A2");
Aspose.Cells.Range target = cells.CreateRange("A3:A10");
//fill 3,4,5....10 to the range A3:A10
source.AutoFill(target);
//Save the Excel file
workbook.Save("book1.xlsm");'Instantiating a Workbook object
Dim workbook As Workbook = New Workbook("Book1.xlsx")
// Get the first Worksheet Cells.
Dim cells as Cells = workbook.Worksheets[0].Cells
cells("A1").PutValue(1)
cells("A2").PutValue(2)
Dim source as Aspose.Cells.Range = cells.CreateRange("A1:A2")
Dim target as Aspose.Cells.Range = cells.CreateRange("A3:A10")
'fill 3,4,5....10 to the range A3:A10
source.AutoFill(target)
'Save the Excel file
workbook.Save("book1.xlsm")AutoFill(Range, AutoFillType)
Automaticall fill the target range.
public void AutoFill(Range target, AutoFillType autoFillType)Parameters
target Range
The targed range.
autoFillType AutoFillType
The auto fill type.
Copy(Range, PasteOptions)
Copying the range with paste special options.
public void Copy(Range range, PasteOptions options)Parameters
range Range
The source range.
options PasteOptions
The paste special options.
Copy(Range)
Copies data (including formulas), formatting, drawing objects etc. from a source range.
public void Copy(Range range)Parameters
range Range
Source Aspose.Cells.Range object.
Examples
//Instantiating a Workbook object
Workbook workbook = new Workbook();
// Get the first Worksheet Cells.
Cells cells = workbook.Worksheets[0].Cells;
Range range1 = cells.CreateRange("A1:A5");
Range range2 = cells.CreateRange("A6:A10");
//Copy the range.
range1.Copy(range2);
//Save the Excel file
workbook.Save("book1.xlsm");'Instantiating a Workbook object
Dim workbook As Workbook = New Workbook()
'Get the first Worksheet Cells.
Dim cells as Cells = workbook.Worksheets[0].Cells
Range range1 = cells.CreateRange("A1:A5")
Range range2 = cells.CreateRange("A6:A10")
//Copy the range
range1.Copy(range2)
'Save the Excel file
workbook.Save("book1.xlsm")CopyData(Range)
Copies cell data (including formulas) from a source range.
public void CopyData(Range range)Parameters
range Range
Source Aspose.Cells.Range object.
CopyStyle(Range)
Copies style settings from a source range.
public void CopyStyle(Range range)Parameters
range Range
Source Aspose.Cells.Range object.
CopyValue(Range)
Copies cell value from a source range.
public void CopyValue(Range range)Parameters
range Range
Source Aspose.Cells.Range object.
ExportDataTable()
Exports data in this range to a System.Data.DataTable object.
public DataTable ExportDataTable()Returns
Exported System.Data.DataTable object.
ExportDataTable(ExportTableOptions)
Exports data in this range to a System.Data.DataTable object.
public DataTable ExportDataTable(ExportTableOptions options)Parameters
options ExportTableOptions
The options of exporting range to datatable.
Returns
Exported System.Data.DataTable object.
ExportDataTableAsString()
Exports data in this range to a System.Data.DataTable object.
public DataTable ExportDataTableAsString()Returns
Exported System.Data.DataTable object.
Remarks
All data in the Aspose.Cells.Cells collection are converted to strings.
GetCellOrNull(int, int)
Gets Aspose.Cells.Cell object or null in this range.
public Cell GetCellOrNull(int rowOffset, int columnOffset)Parameters
rowOffset int
Row offset in this range, zero based.
columnOffset int
Column offset in this range, zero based.
Returns
Aspose.Cells.Cell object.
GetEnumerator()
Gets the enumerator for cells in this Range.
public IEnumerator GetEnumerator()Returns
The cells enumerator
Examples
Workbook workbook = new Workbook("template.xlsx");
Cells cells = workbook.Worksheets[0].Cells;
IEnumerator en = cells.CreateRange("B2:C3").GetEnumerator();
while (en.MoveNext())
{
Cell cell = (Cell)en.Current;
Console.WriteLine(cell.Name + ": " + cell.Value);
}</code></pre>
#### Remarks
When traversing elements by the returned Enumerator, the cells collection
should not be modified(such as operations that will cause new Cell/Row be instantiated or existing Cell/Row be deleted).
Otherwise the enumerator may not be able to traverse all cells correctly(some elements may be traversed repeatedly or skipped).
### <a id="Aspose_Cells_Range_GetOffset_System_Int32_System_Int32_"></a> GetOffset\(int, int\)
Gets Aspose.Cells.Range range by offset.
```csharp
public Range GetOffset(int rowOffset, int columnOffset)Parameters
rowOffset int
Row offset in this range, zero based.
columnOffset int
Column offset in this range, zero based.
Returns
Intersect(Range)
Returns a Aspose.Cells.Range object that represents the rectangular intersection of two ranges.
public Range Intersect(Range range)Parameters
range Range
The intersecting range.
Returns
Returns a Aspose.Cells.Range object
Examples
[C#]
//Instantiating a Workbook object
Workbook workbook = new Workbook();
// Get the first Worksheet Cells.
Cells cells = workbook.Worksheets[0].Cells;
Range range1 = cells.CreateRange("A1:A5");
Range range2 = cells.CreateRange("A3:A10");
//Get intersected range of the two ranges.
Range intersectRange = range1.Intersect(range2);
//Save the Excel file
workbook.Save("book1.xlsm");
```
```vb
'Instantiating a Workbook object
Dim workbook As Workbook = New Workbook()
'Get the first Worksheet Cells.
Dim cells as Cells = workbook.Worksheets[0].Cells
Range range1 = cells.CreateRange("A1:A5")
Range range2 = cells.CreateRange("A3:A10")
'Get intersected range of the two ranges.
Range intersectRange = range1.Intersect(range2)
'Save the Excel file
workbook.Save("book1.xlsm")
```
#### Remarks
If the two ranges are not intersected, returns null.
### IsBlank\(\)
Indicates whether the range contains values.
```csharp
public bool IsBlank()
```
#### Returns
[bool](https://learn.microsoft.com/dotnet/api/system.boolean)
### IsIntersect\(Range\)
Indicates whether the range is intersect.
```csharp
public bool IsIntersect(Range range)
```
#### Parameters
`range` [Range](/cells/aspose.cells.range)
The range.
#### Returns
[bool](https://learn.microsoft.com/dotnet/api/system.boolean)
Whether the range is intersect.
#### Remarks
If the two ranges area not in the same worksheet ,return false.
### Merge\(\)
Combines a range of cells into a single cell.
```csharp
public void Merge()
```
#### Remarks
Reference the merged cell via the address of the upper-left cell in the range.
### MoveTo\(int, int\)
Move the current range to the dest range.
```csharp
public void MoveTo(int destRow, int destColumn)
```
#### Parameters
`destRow` [int](https://learn.microsoft.com/dotnet/api/system.int32)
The start row of the dest range.
`destColumn` [int](https://learn.microsoft.com/dotnet/api/system.int32)
The start column of the dest range.
### PutValue\(string, bool, bool\)
Puts a value into the range, if appropriate the value will be converted to other data type and cell's number format will be reset.
```csharp
public void PutValue(string stringValue, bool isConverted, bool setStyle)
```
#### Parameters
`stringValue` [string](https://learn.microsoft.com/dotnet/api/system.string)
Input value
`isConverted` [bool](https://learn.microsoft.com/dotnet/api/system.boolean)
True: converted to other data type if appropriate.
`setStyle` [bool](https://learn.microsoft.com/dotnet/api/system.boolean)
True: set the number format to cell's style when converting to other data type
### SetInsideBorders\(BorderType, CellBorderType, CellsColor\)
Set inside borders of the range.
```csharp
public void SetInsideBorders(BorderType borderEdge, CellBorderType lineStyle, CellsColor borderColor)
```
#### Parameters
`borderEdge` [BorderType](/cells/aspose.cells.bordertype)
Inside borde type, only can be Aspose.Cells.BorderType.Vertical and Aspose.Cells.BorderType.Horizontal.
`lineStyle` [CellBorderType](/cells/aspose.cells.cellbordertype)
The border style.
`borderColor` [CellsColor](/cells/aspose.cells.cellscolor)
The color of the border.
### SetOutlineBorder\(BorderType, CellBorderType, CellsColor\)
Sets outline border around a range of cells.
```csharp
public void SetOutlineBorder(BorderType borderEdge, CellBorderType borderStyle, CellsColor borderColor)
```
#### Parameters
`borderEdge` [BorderType](/cells/aspose.cells.bordertype)
Border edge.
`borderStyle` [CellBorderType](/cells/aspose.cells.cellbordertype)
Border style.
`borderColor` [CellsColor](/cells/aspose.cells.cellscolor)
Border color.
### SetOutlineBorder\(BorderType, CellBorderType, Color\)
Sets outline border around a range of cells.
```csharp
public void SetOutlineBorder(BorderType borderEdge, CellBorderType borderStyle, Color borderColor)
```
#### Parameters
`borderEdge` [BorderType](/cells/aspose.cells.bordertype)
Border edge.
`borderStyle` [CellBorderType](/cells/aspose.cells.cellbordertype)
Border style.
`borderColor` [Color](https://learn.microsoft.com/dotnet/api/system.drawing.color)
Border color.
### SetOutlineBorders\(CellBorderType, CellsColor\)
Sets the outline borders around a range of cells with same border style and color.
```csharp
public void SetOutlineBorders(CellBorderType borderStyle, CellsColor borderColor)
```
#### Parameters
`borderStyle` [CellBorderType](/cells/aspose.cells.cellbordertype)
Border style.
`borderColor` [CellsColor](/cells/aspose.cells.cellscolor)
Border color.
### SetOutlineBorders\(CellBorderType, Color\)
Sets the outline borders around a range of cells with same border style and color.
```csharp
public void SetOutlineBorders(CellBorderType borderStyle, Color borderColor)
```
#### Parameters
`borderStyle` [CellBorderType](/cells/aspose.cells.cellbordertype)
Border style.
`borderColor` [Color](https://learn.microsoft.com/dotnet/api/system.drawing.color)
Border color.
### SetOutlineBorders\(CellBorderType\[\], Color\[\]\)
Sets out line borders around a range of cells.
```csharp
public void SetOutlineBorders(CellBorderType[] borderStyles, Color[] borderColors)
```
#### Parameters
`borderStyles` [CellBorderType](/cells/aspose.cells.cellbordertype)\[\]
Border styles.
`borderColors` [Color](https://learn.microsoft.com/dotnet/api/system.drawing.color)\[\]
Border colors.
#### Remarks
Both the length of borderStyles and borderStyles must be 4.
The order of borderStyles and borderStyles must be top,bottom,left,right
### SetStyle\(Style, bool\)
Apply the cell style.
```csharp
public void SetStyle(Style style, bool explicitFlag)
```
#### Parameters
`style` [Style](/cells/aspose.cells.style)
The cell style.
`explicitFlag` [bool](https://learn.microsoft.com/dotnet/api/system.boolean)
True, only overwriting formatting which is explicitly set.
### SetStyle\(Style\)
Sets the style of the range.
```csharp
public void SetStyle(Style style)
```
#### Parameters
`style` [Style](/cells/aspose.cells.style)
The Style object.
### ToHtml\(HtmlSaveOptions\)
Convert the range to html .
```csharp
public byte[] ToHtml(HtmlSaveOptions saveOptions)
```
#### Parameters
`saveOptions` [HtmlSaveOptions](/cells/aspose.cells.htmlsaveoptions)
Options for coverting range to html.
#### Returns
[byte](https://learn.microsoft.com/dotnet/api/system.byte)\[\]
### ToImage\(ImageOrPrintOptions\)
Converts the range to image.
```csharp
public byte[] ToImage(ImageOrPrintOptions options)
```
#### Parameters
`options` [ImageOrPrintOptions](/cells/aspose.cells.rendering.imageorprintoptions)
The options for converting this range to image
#### Returns
[byte](https://learn.microsoft.com/dotnet/api/system.byte)\[\]
### ToJson\(JsonSaveOptions\)
Convert the range to JSON value.
```csharp
public string ToJson(JsonSaveOptions options)
```
#### Parameters
`options` [JsonSaveOptions](/cells/aspose.cells.jsonsaveoptions)
The options of converting
#### Returns
[string](https://learn.microsoft.com/dotnet/api/system.string)
### ToString\(\)
Returns a string represents the current Range object.
```csharp
public override string ToString()
```
#### Returns
[string](https://learn.microsoft.com/dotnet/api/system.string)
### Transpose\(\)
Transpose (rotate) data from rows to columns or vice versa.
```csharp
public void Transpose()
```
### UnMerge\(\)
Unmerges merged cells of this range.
```csharp
public void UnMerge()
```
### UnionRanges\(Range\[\]\)
Returns the union result of two ranges.
```csharp
public UnionRange UnionRanges(Range[] ranges)
```
#### Parameters
`ranges` [Range](/cells/aspose.cells.range)\[\]
The range
#### Returns
[UnionRange](/cells/aspose.cells.unionrange)
The union of two ranges.