Range

Range class

Encapsulates the object that represents a range of cells within a spreadsheet.

class Range;

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

Property	Type	Description
currentRegion	Range	Readonly. 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.
hyperlinks	Hyperlink[]	Readonly. Gets all hyperlink in the range.
rowCount	number	Readonly. Gets the count of rows in the range.
columnCount	number	Readonly. Gets the count of columns in the range.
name	string	Gets or sets the name of the range.
refersTo	string	Readonly. Gets the range’s refers to.
address	string	Readonly. Gets address of the range.
left	number	Readonly. Gets the distance, in points, from the left edge of column A to the left edge of the range.
top	number	Readonly. Gets the distance, in points, from the top edge of row 1 to the top edge of the range.
width	number	Readonly. Gets the width of a range in points.
height	number	Readonly. Gets the width of a range in points.
firstRow	number	Readonly. Gets the index of the first row of the range.
firstColumn	number	Readonly. Gets the index of the first column of the range.
value	Object	Gets and sets the value of the range.
columnWidth	number	Sets or gets the column width of this range
rowHeight	number	Sets or gets the height of rows in this range
entireColumn	Range	Readonly. Gets a Range object that represents the entire column (or columns) that contains the specified range.
entireRow	Range	Readonly. Gets a Range object that represents the entire row (or rows) that contains the specified range.
worksheet	Worksheet	Readonly. Gets the Worksheetobject which contains this range.

Methods

Method	Description
get(number, number)	Gets Cell object in this range.
getCurrentRegion()	@deprecated. Please use the ‘currentRegion’ property instead. 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.
getHyperlinks()	@deprecated. Please use the ‘hyperlinks’ property instead. Gets all hyperlink in the range.
getRowCount()	@deprecated. Please use the ‘rowCount’ property instead. Gets the count of rows in the range.
getColumnCount()	@deprecated. Please use the ‘columnCount’ property instead. Gets the count of columns in the range.
getName()	@deprecated. Please use the ’name’ property instead. Gets or sets the name of the range.
setName(string)	@deprecated. Please use the ’name’ property instead. Gets or sets the name of the range.
getRefersTo()	@deprecated. Please use the ‘refersTo’ property instead. Gets the range’s refers to.
getAddress()	@deprecated. Please use the ‘address’ property instead. Gets address of the range.
getLeft()	@deprecated. Please use the ’left’ property instead. Gets the distance, in points, from the left edge of column A to the left edge of the range.
getTop()	@deprecated. Please use the ’top’ property instead. Gets the distance, in points, from the top edge of row 1 to the top edge of the range.
getWidth()	@deprecated. Please use the ‘width’ property instead. Gets the width of a range in points.
getHeight()	@deprecated. Please use the ‘height’ property instead. Gets the width of a range in points.
getFirstRow()	@deprecated. Please use the ‘firstRow’ property instead. Gets the index of the first row of the range.
getFirstColumn()	@deprecated. Please use the ‘firstColumn’ property instead. Gets the index of the first column of the range.
getValue()	@deprecated. Please use the ‘value’ property instead. Gets and sets the value of the range.
setValue(Object)	@deprecated. Please use the ‘value’ property instead. Gets and sets the value of the range.
getColumnWidth()	@deprecated. Please use the ‘columnWidth’ property instead. Sets or gets the column width of this range
setColumnWidth(number)	@deprecated. Please use the ‘columnWidth’ property instead. Sets or gets the column width of this range
getRowHeight()	@deprecated. Please use the ‘rowHeight’ property instead. Sets or gets the height of rows in this range
setRowHeight(number)	@deprecated. Please use the ‘rowHeight’ property instead. Sets or gets the height of rows in this range
getEntireColumn()	@deprecated. Please use the ’entireColumn’ property instead. Gets a Range object that represents the entire column (or columns) that contains the specified range.
getEntireRow()	@deprecated. Please use the ’entireRow’ property instead. Gets a Range object that represents the entire row (or rows) that contains the specified range.
getWorksheet()	@deprecated. Please use the ‘worksheet’ property instead. Gets the Worksheetobject which contains this range.
autoFill(Range)	Automaticall fill the target range.
autoFill(Range, AutoFillType)	Automaticall fill the target range.
addHyperlink(string, string, string)	Adds a hyperlink to a specified cell or a range of cells.
getEnumerator()	Gets the enumerator for cells in this Range.
isIntersect(Range)	Indicates whether the range is intersect.
intersect(Range)	Returns a Range object that represents the rectangular intersection of two ranges.
unionRang(Range)	Returns the union result of two ranges.
unionRanges(Range[])	Returns the union result of two ranges.
isBlank()	Indicates whether the range contains values.
merge()	Combines a range of cells into a single cell.
unMerge()	Unmerges merged cells of this range.
putValue(string, boolean, boolean)	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.
setStyle(Style, boolean)	Apply the cell style.
setStyle(Style)	Sets the style of the range.
applyStyle(Style, StyleFlag)	Applies formats for a whole range.
setOutlineBorders(CellBorderType, CellsColor)	Sets the outline borders around a range of cells with same border style and color.
setOutlineBorders(CellBorderType, Color)	Sets the outline borders around a range of cells with same border style and color.
setOutlineBorders(CellBorderType[], Color[])	Sets out line borders around a range of cells.
setOutlineBorder(BorderType, CellBorderType, CellsColor)	Sets outline border around a range of cells.
setOutlineBorder(BorderType, CellBorderType, Color)	Sets outline border around a range of cells.
setInsideBorders(BorderType, CellBorderType, CellsColor)	Set inside borders of the range.
moveTo(number, number)	Move the current range to the dest range.
copyData(Range)	Copies cell data (including formulas) from a source range.
copyValue(Range)	Copies cell value from a source range.
copyStyle(Range)	Copies style settings from a source range.
copy(Range, PasteOptions)	Copying the range with paste special options.
copy(Range)	Copies data (including formulas), formatting, drawing objects etc. from a source range.
transpose()	Transpose (rotate) data from rows to columns or vice versa.
getCellOrNull(number, number)	Gets Cell object or null in this range.
getOffset(number, number)	Gets Range range by offset.
toImage(ImageOrPrintOptions)	Converts the range to image.
toJson(JsonSaveOptions)	Convert the range to JSON value.
toHtml(HtmlSaveOptions)	Convert the range to html .
clear()	Clears this range.
clearContents()	Clears the contents of this range.
clearFormats()	Clears the formats of this range.
clearComments()	Clears the comments of this range.
clearHyperlinks(boolean)	Only removes hyperlinks.
isNull()	Checks whether the implementation object is null.
toString()	Returns a string represents the current Range object.

currentRegion

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

currentRegion : Range;

hyperlinks

Readonly. Gets all hyperlink in the range.

hyperlinks : Hyperlink[];

rowCount

Readonly. Gets the count of rows in the range.

rowCount : number;

columnCount

Readonly. Gets the count of columns in the range.

columnCount : number;

name

Gets or sets the name of the range.

name : string;

Remarks

Named range is supported. For example,

range.Name = “Sheet1!MyRange”;

refersTo

Readonly. Gets the range’s refers to.

refersTo : string;

address

Readonly. Gets address of the range.

address : string;

left

Readonly. Gets the distance, in points, from the left edge of column A to the left edge of the range.

left : number;

top

Readonly. Gets the distance, in points, from the top edge of row 1 to the top edge of the range.

top : number;

width

Readonly. Gets the width of a range in points.

width : number;

height

Readonly. Gets the width of a range in points.

height : number;

firstRow

Readonly. Gets the index of the first row of the range.

firstRow : number;

firstColumn

Readonly. Gets the index of the first column of the range.

firstColumn : number;

value

Gets and sets the value of the range.

value : Object;

Remarks

If the range contains multiple cells, the returned/applied object should be a two-dimension Array object.

columnWidth

Sets or gets the column width of this range

columnWidth : number;

rowHeight

Sets or gets the height of rows in this range

rowHeight : number;

entireColumn

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

entireColumn : Range;

entireRow

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

entireRow : Range;

worksheet

Readonly. Gets the Worksheetobject which contains this range.

worksheet : Worksheet;

get(number, number)

Gets Cell object in this range.

get(rowOffset: number, columnOffset: number) : Cell;

Parameters:

Parameter	Type	Description
rowOffset	number	Row offset in this range, zero based.
columnOffset	number	Column offset in this range, zero based.

Returns

Cell object.

getCurrentRegion()

@deprecated. Please use the ‘currentRegion’ property instead. 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.

getCurrentRegion() : Range;

Returns

Range

getHyperlinks()

@deprecated. Please use the ‘hyperlinks’ property instead. Gets all hyperlink in the range.

getHyperlinks() : Hyperlink[];

Returns

Hyperlink[]

getRowCount()

@deprecated. Please use the ‘rowCount’ property instead. Gets the count of rows in the range.

getRowCount() : number;

getColumnCount()

@deprecated. Please use the ‘columnCount’ property instead. Gets the count of columns in the range.

getColumnCount() : number;

getName()

@deprecated. Please use the ’name’ property instead. Gets or sets the name of the range.

getName() : string;

Remarks

Named range is supported. For example,

range.Name = “Sheet1!MyRange”;

setName(string)

@deprecated. Please use the ’name’ property instead. Gets or sets the name of the range.

setName(value: string) : void;

Parameters:

Parameter	Type	Description
value	string	The value to set.

Remarks

Named range is supported. For example,

range.Name = “Sheet1!MyRange”;

getRefersTo()

@deprecated. Please use the ‘refersTo’ property instead. Gets the range’s refers to.

getRefersTo() : string;

getAddress()

@deprecated. Please use the ‘address’ property instead. Gets address of the range.

getAddress() : string;

getLeft()

@deprecated. Please use the ’left’ property instead. Gets the distance, in points, from the left edge of column A to the left edge of the range.

getLeft() : number;

getTop()

@deprecated. Please use the ’top’ property instead. Gets the distance, in points, from the top edge of row 1 to the top edge of the range.

getTop() : number;

getWidth()

@deprecated. Please use the ‘width’ property instead. Gets the width of a range in points.

getWidth() : number;

getHeight()

@deprecated. Please use the ‘height’ property instead. Gets the width of a range in points.

getHeight() : number;

getFirstRow()

@deprecated. Please use the ‘firstRow’ property instead. Gets the index of the first row of the range.

getFirstRow() : number;

getFirstColumn()

@deprecated. Please use the ‘firstColumn’ property instead. Gets the index of the first column of the range.

getFirstColumn() : number;

getValue()

@deprecated. Please use the ‘value’ property instead. Gets and sets the value of the range.

getValue() : Object;

Remarks

If the range contains multiple cells, the returned/applied object should be a two-dimension Array object.

setValue(Object)

@deprecated. Please use the ‘value’ property instead. Gets and sets the value of the range.

setValue(value: Object) : void;

Parameters:

Parameter	Type	Description
value	Object	The value to set.

Remarks

If the range contains multiple cells, the returned/applied object should be a two-dimension Array object.

getColumnWidth()

@deprecated. Please use the ‘columnWidth’ property instead. Sets or gets the column width of this range

getColumnWidth() : number;

setColumnWidth(number)

@deprecated. Please use the ‘columnWidth’ property instead. Sets or gets the column width of this range

setColumnWidth(value: number) : void;

Parameters:

Parameter	Type	Description
value	number	The value to set.

getRowHeight()

@deprecated. Please use the ‘rowHeight’ property instead. Sets or gets the height of rows in this range

getRowHeight() : number;

setRowHeight(number)

@deprecated. Please use the ‘rowHeight’ property instead. Sets or gets the height of rows in this range

setRowHeight(value: number) : void;

Parameters:

Parameter	Type	Description
value	number	The value to set.

getEntireColumn()

@deprecated. Please use the ’entireColumn’ property instead. Gets a Range object that represents the entire column (or columns) that contains the specified range.

getEntireColumn() : Range;

Returns

Range

getEntireRow()

@deprecated. Please use the ’entireRow’ property instead. Gets a Range object that represents the entire row (or rows) that contains the specified range.

getEntireRow() : Range;

Returns

Range

getWorksheet()

@deprecated. Please use the ‘worksheet’ property instead. Gets the Worksheetobject which contains this range.

getWorksheet() : Worksheet;

Returns

Worksheet

autoFill(Range)

Automaticall fill the target range.

autoFill(target: Range) : void;

Parameters:

Parameter	Type	Description
target	Range	the target range.

autoFill(Range, AutoFillType)

Automaticall fill the target range.

autoFill(target: Range, autoFillType: AutoFillType) : void;

Parameters:

Parameter	Type	Description
target	Range	The targed range.
autoFillType	AutoFillType	The auto fill type.

addHyperlink(string, string, string)

Adds a hyperlink to a specified cell or a range of cells.

addHyperlink(address: string, textToDisplay: string, screenTip: string) : Hyperlink;

Parameters:

Parameter	Type	Description
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

Hyperlink object.

getEnumerator()

Gets the enumerator for cells in this Range.

getEnumerator() : CellEnumerator;

Returns

The cells enumerator

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

isIntersect(Range)

Indicates whether the range is intersect.

isIntersect(range: Range) : boolean;

Parameters:

Parameter	Type	Description
range	Range	The range.

Returns

Whether the range is intersect.

Remarks

If the two ranges area not in the same worksheet ,return false.

intersect(Range)

Returns a Range object that represents the rectangular intersection of two ranges.

intersect(range: Range) : Range;

Parameters:

Parameter	Type	Description
range	Range	The intersecting range.

Returns

Returns a Range object

Remarks

If the two ranges are not intersected, returns null.

unionRang(Range)

Returns the union result of two ranges.

unionRang(range: Range) : Range[];

Parameters:

Parameter	Type	Description
range	Range	The range

Returns

The union of two ranges.

Remarks

NOTE: This method is now obsolete. Instead, please use Range.UnionRanges() method. This method will be removed 12 months later since May 2024. Aspose apologizes for any inconvenience you may have experienced.

unionRanges(Range[])

Returns the union result of two ranges.

unionRanges(ranges: Range[]) : UnionRange;

Parameters:

Parameter	Type	Description
ranges	Range[]	The range

Returns

The union of two ranges.

isBlank()

Indicates whether the range contains values.

isBlank() : boolean;

merge()

Combines a range of cells into a single cell.

merge() : void;

Remarks

Reference the merged cell via the address of the upper-left cell in the range.

unMerge()

Unmerges merged cells of this range.

unMerge() : void;

putValue(string, boolean, boolean)

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.

putValue(stringValue: string, isConverted: boolean, setStyle: boolean) : void;

Parameters:

Parameter	Type	Description
stringValue	string	Input value
isConverted	boolean	True: converted to other data type if appropriate.
setStyle	boolean	True: set the number format to cell’s style when converting to other data type

setStyle(Style, boolean)

Apply the cell style.

setStyle(style: Style, explicitFlag: boolean) : void;

Parameters:

Parameter	Type	Description
style	Style	The cell style.
explicitFlag	boolean	True, only overwriting formatting which is explicitly set.

setStyle(Style)

Sets the style of the range.

setStyle(style: Style) : void;

Parameters:

Parameter	Type	Description
style	Style	The Style object.

applyStyle(Style, StyleFlag)

Applies formats for a whole range.

applyStyle(style: Style, flag: StyleFlag) : void;

Parameters:

Parameter	Type	Description
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 Style object. So this is a memory-consuming method. Please use it carefully.

setOutlineBorders(CellBorderType, CellsColor)

Sets the outline borders around a range of cells with same border style and color.

setOutlineBorders(borderStyle: CellBorderType, borderColor: CellsColor) : void;

Parameters:

Parameter	Type	Description
borderStyle	CellBorderType	Border style.
borderColor	CellsColor	Border color.

setOutlineBorders(CellBorderType, Color)

Sets the outline borders around a range of cells with same border style and color.

setOutlineBorders(borderStyle: CellBorderType, borderColor: Color) : void;

Parameters:

Parameter	Type	Description
borderStyle	CellBorderType	Border style.
borderColor	Color	Border color.

setOutlineBorders(CellBorderType[], Color[])

Sets out line borders around a range of cells.

setOutlineBorders(borderStyles: CellBorderType[], borderColors: Color[]) : void;

Parameters:

Parameter	Type	Description
borderStyles	CellBorderType[]	Border styles.
borderColors	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

setOutlineBorder(BorderType, CellBorderType, CellsColor)

Sets outline border around a range of cells.

setOutlineBorder(borderEdge: BorderType, borderStyle: CellBorderType, borderColor: CellsColor) : void;

Parameters:

Parameter	Type	Description
borderEdge	BorderType	Border edge.
borderStyle	CellBorderType	Border style.
borderColor	CellsColor	Border color.

setOutlineBorder(BorderType, CellBorderType, Color)

Sets outline border around a range of cells.

setOutlineBorder(borderEdge: BorderType, borderStyle: CellBorderType, borderColor: Color) : void;

Parameters:

Parameter	Type	Description
borderEdge	BorderType	Border edge.
borderStyle	CellBorderType	Border style.
borderColor	Color	Border color.

setInsideBorders(BorderType, CellBorderType, CellsColor)

Set inside borders of the range.

setInsideBorders(borderEdge: BorderType, lineStyle: CellBorderType, borderColor: CellsColor) : void;

Parameters:

Parameter	Type	Description
borderEdge	BorderType	Inside borde type, only can be BorderType.Vertical and BorderType.Horizontal.
lineStyle	CellBorderType	The border style.
borderColor	CellsColor	The color of the border.

moveTo(number, number)

Move the current range to the dest range.

moveTo(destRow: number, destColumn: number) : void;

Parameters:

Parameter	Type	Description
destRow	number	The start row of the dest range.
destColumn	number	The start column of the dest range.

copyData(Range)

Copies cell data (including formulas) from a source range.

copyData(range: Range) : void;

Parameters:

Parameter	Type	Description
range	Range	Source Range object.

copyValue(Range)

Copies cell value from a source range.

copyValue(range: Range) : void;

Parameters:

Parameter	Type	Description
range	Range	Source Range object.

copyStyle(Range)

Copies style settings from a source range.

copyStyle(range: Range) : void;

Parameters:

Parameter	Type	Description
range	Range	Source Range object.

copy(Range, PasteOptions)

Copying the range with paste special options.

copy(range: Range, options: PasteOptions) : void;

Parameters:

Parameter	Type	Description
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.

copy(range: Range) : void;

Parameters:

Parameter	Type	Description
range	Range	Source Range object.

transpose()

Transpose (rotate) data from rows to columns or vice versa.

transpose() : void;

getCellOrNull(number, number)

Gets Cell object or null in this range.

getCellOrNull(rowOffset: number, columnOffset: number) : Cell;

Parameters:

Parameter	Type	Description
rowOffset	number	Row offset in this range, zero based.
columnOffset	number	Column offset in this range, zero based.

Returns

Cell object.

getOffset(number, number)

Gets Range range by offset.

getOffset(rowOffset: number, columnOffset: number) : Range;

Parameters:

Parameter	Type	Description
rowOffset	number	Row offset in this range, zero based.
columnOffset	number	Column offset in this range, zero based.

Returns

Range

toImage(ImageOrPrintOptions)

Converts the range to image.

toImage(options: ImageOrPrintOptions) : Uint8Array;

Parameters:

Parameter	Type	Description
options	ImageOrPrintOptions	The options for converting this range to image

toJson(JsonSaveOptions)

Convert the range to JSON value.

toJson(options: JsonSaveOptions) : string;

Parameters:

Parameter	Type	Description
options	JsonSaveOptions	The options of converting

toHtml(HtmlSaveOptions)

Convert the range to html .

toHtml(saveOptions: HtmlSaveOptions) : Uint8Array;

Parameters:

Parameter	Type	Description
saveOptions	HtmlSaveOptions	Options for coverting range to html.

clear()

Clears this range.

clear() : void;

clearContents()

Clears the contents of this range.

clearContents() : void;

clearFormats()

Clears the formats of this range.

clearFormats() : void;

clearComments()

Clears the comments of this range.

clearComments() : void;

clearHyperlinks(boolean)

Only removes hyperlinks.

clearHyperlinks(clearFormat: boolean) : void;

Parameters:

Parameter	Type	Description
clearFormat	boolean	Indicates whether to clear the format of hyperlinks.

isNull()

Checks whether the implementation object is null.

isNull() : boolean;

toString()

Returns a string represents the current Range object.

toString() : string;