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.

Methods

MethodDescription
get(number, number)Gets Cell object in this range.
getCurrentRegion()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()Gets all hyperlink in the range.
getRowCount()Gets the count of rows in the range.
getColumnCount()Gets the count of columns in the range.
getName()Gets or sets the name of the range.
setName(string)Gets or sets the name of the range.
getRefersTo()Gets the range’s refers to.
getAddress()Gets address of the range.
getLeft()Gets the distance, in points, from the left edge of column A to the left edge of the range.
getTop()Gets the distance, in points, from the top edge of row 1 to the top edge of the range.
getWidth()Gets the width of a range in points.
getHeight()Gets the width of a range in points.
getFirstRow()Gets the index of the first row of the range.
getFirstColumn()Gets the index of the first column of the range.
getValue()Gets and sets the value of the range.
setValue(object)Gets and sets the value of the range.
getColumnWidth()Sets or gets the column width of this range
setColumnWidth(number)Sets or gets the column width of this range
getRowHeight()Sets or gets the height of rows in this range
setRowHeight(number)Sets or gets the height of rows in this range
getEntireColumn()Gets a Range object that represents the entire column (or columns) that contains the specified range.
getEntireRow()Gets a Range object that represents the entire row (or rows) that contains the specified range.
getWorksheet()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.
toString()Returns a string represents the current Range object.
toImage(ImageOrPrintOptions)Converts the range to image.
toJson(JsonSaveOptions)Convert the range to JSON value.
toHtml(HtmlSaveOptions)Convert the range to html .
isNull()Checks whether the implementation object is null.

get(number, number)

Gets Cell object in this range.

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

Parameters:

ParameterTypeDescription
rowOffsetnumberRow offset in this range, zero based.
columnOffsetnumberColumn offset in this range, zero based.

Returns

Cell object.

getCurrentRegion()

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

Gets all hyperlink in the range.

getHyperlinks() : Hyperlink[];

Returns

Hyperlink[]

getRowCount()

Gets the count of rows in the range.

getRowCount() : number;

getColumnCount()

Gets the count of columns in the range.

getColumnCount() : number;

getName()

Gets or sets the name of the range.

getName() : string;

Remarks

Named range is supported. For example,

range.Name = “Sheet1!MyRange”;

setName(string)

Gets or sets the name of the range.

setName(value: string) : void;

Parameters:

ParameterTypeDescription
valuestringThe value to set.

Remarks

Named range is supported. For example,

range.Name = “Sheet1!MyRange”;

getRefersTo()

Gets the range’s refers to.

getRefersTo() : string;

getAddress()

Gets address of the range.

getAddress() : string;

getLeft()

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

getLeft() : number;

getTop()

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

getTop() : number;

getWidth()

Gets the width of a range in points.

getWidth() : number;

getHeight()

Gets the width of a range in points.

getHeight() : number;

getFirstRow()

Gets the index of the first row of the range.

getFirstRow() : number;

getFirstColumn()

Gets the index of the first column of the range.

getFirstColumn() : number;

getValue()

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)

Gets and sets the value of the range.

setValue(value: object) : void;

Parameters:

ParameterTypeDescription
valueobjectThe value to set.

Remarks

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

getColumnWidth()

Sets or gets the column width of this range

getColumnWidth() : number;

setColumnWidth(number)

Sets or gets the column width of this range

setColumnWidth(value: number) : void;

Parameters:

ParameterTypeDescription
valuenumberThe value to set.

getRowHeight()

Sets or gets the height of rows in this range

getRowHeight() : number;

setRowHeight(number)

Sets or gets the height of rows in this range

setRowHeight(value: number) : void;

Parameters:

ParameterTypeDescription
valuenumberThe value to set.

getEntireColumn()

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

getEntireColumn() : Range;

Returns

Range

getEntireRow()

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

getEntireRow() : Range;

Returns

Range

getWorksheet()

Gets the Worksheetobject which contains this range.

getWorksheet() : Worksheet;

Returns

Worksheet

autoFill(Range)

Automaticall fill the target range.

autoFill(target: Range) : void;

Parameters:

ParameterTypeDescription
targetRangethe target range.

autoFill(Range, AutoFillType)

Automaticall fill the target range.

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

Parameters:

ParameterTypeDescription
targetRangeThe targed range.
autoFillTypeAutoFillTypeThe auto fill type.

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

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

Parameters:

ParameterTypeDescription
addressstringAddress of the hyperlink.
textToDisplaystringThe text to be displayed for the specified hyperlink.
screenTipstringThe 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:

ParameterTypeDescription
rangeRangeThe 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:

ParameterTypeDescription
rangeRangeThe 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:

ParameterTypeDescription
rangeRangeThe 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:

ParameterTypeDescription
rangesRange[]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:

ParameterTypeDescription
stringValuestringInput value
isConvertedbooleanTrue: converted to other data type if appropriate.
setStylebooleanTrue: 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:

ParameterTypeDescription
styleStyleThe cell style.
explicitFlagbooleanTrue, only overwriting formatting which is explicitly set.

setStyle(Style)

Sets the style of the range.

setStyle(style: Style) : void;

Parameters:

ParameterTypeDescription
styleStyleThe Style object.

applyStyle(Style, StyleFlag)

Applies formats for a whole range.

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

Parameters:

ParameterTypeDescription
styleStyleThe style object which will be applied.
flagStyleFlagFlags 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:

ParameterTypeDescription
borderStyleCellBorderTypeBorder style.
borderColorCellsColorBorder 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:

ParameterTypeDescription
borderStyleCellBorderTypeBorder style.
borderColorColorBorder color.

setOutlineBorders(CellBorderType[], Color[])

Sets out line borders around a range of cells.

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

Parameters:

ParameterTypeDescription
borderStylesCellBorderType[]Border styles.
borderColorsColor[]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:

ParameterTypeDescription
borderEdgeBorderTypeBorder edge.
borderStyleCellBorderTypeBorder style.
borderColorCellsColorBorder color.

setOutlineBorder(BorderType, CellBorderType, Color)

Sets outline border around a range of cells.

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

Parameters:

ParameterTypeDescription
borderEdgeBorderTypeBorder edge.
borderStyleCellBorderTypeBorder style.
borderColorColorBorder color.

setInsideBorders(BorderType, CellBorderType, CellsColor)

Set inside borders of the range.

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

Parameters:

ParameterTypeDescription
borderEdgeBorderTypeInside borde type, only can be BorderType.Vertical and BorderType.Horizontal.
lineStyleCellBorderTypeThe border style.
borderColorCellsColorThe color of the border.

moveTo(number, number)

Move the current range to the dest range.

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

Parameters:

ParameterTypeDescription
destRownumberThe start row of the dest range.
destColumnnumberThe start column of the dest range.

copyData(Range)

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

copyData(range: Range) : void;

Parameters:

ParameterTypeDescription
rangeRangeSource Range object.

copyValue(Range)

Copies cell value from a source range.

copyValue(range: Range) : void;

Parameters:

ParameterTypeDescription
rangeRangeSource Range object.

copyStyle(Range)

Copies style settings from a source range.

copyStyle(range: Range) : void;

Parameters:

ParameterTypeDescription
rangeRangeSource Range object.

copy(Range, PasteOptions)

Copying the range with paste special options.

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

Parameters:

ParameterTypeDescription
rangeRangeThe source range.
optionsPasteOptionsThe paste special options.

copy(Range)

Copies data (including formulas), formatting, drawing objects etc. from a source range.

copy(range: Range) : void;

Parameters:

ParameterTypeDescription
rangeRangeSource 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:

ParameterTypeDescription
rowOffsetnumberRow offset in this range, zero based.
columnOffsetnumberColumn offset in this range, zero based.

Returns

Cell object.

getOffset(number, number)

Gets Range range by offset.

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

Parameters:

ParameterTypeDescription
rowOffsetnumberRow offset in this range, zero based.
columnOffsetnumberColumn offset in this range, zero based.

Returns

Range

toString()

Returns a string represents the current Range object.

toString() : string;

toImage(ImageOrPrintOptions)

Converts the range to image.

toImage(options: ImageOrPrintOptions) : number[];

Parameters:

ParameterTypeDescription
optionsImageOrPrintOptionsThe options for converting this range to image

Returns

number[]

toJson(JsonSaveOptions)

Convert the range to JSON value.

toJson(options: JsonSaveOptions) : string;

Parameters:

ParameterTypeDescription
optionsJsonSaveOptionsThe options of converting

toHtml(HtmlSaveOptions)

Convert the range to html .

toHtml(saveOptions: HtmlSaveOptions) : number[];

Parameters:

ParameterTypeDescription
saveOptionsHtmlSaveOptionsOptions for coverting range to html.

Returns

number[]

isNull()

Checks whether the implementation object is null.

isNull() : boolean;