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
Method | Description |
---|---|
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:
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()
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
getHyperlinks()
Gets all hyperlink in the range.
getHyperlinks() : Hyperlink[];
Returns
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:
Parameter | Type | Description |
---|---|---|
value | string | The 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:
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()
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:
Parameter | Type | Description |
---|---|---|
value | number | The 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:
Parameter | Type | Description |
---|---|---|
value | number | The value to set. |
getEntireColumn()
Gets a Range object that represents the entire column (or columns) that contains the specified range.
getEntireColumn() : Range;
Returns
getEntireRow()
Gets a Range object that represents the entire row (or rows) that contains the specified range.
getEntireRow() : Range;
Returns
getWorksheet()
Gets the Worksheetobject which contains this range.
getWorksheet() : Worksheet;
Returns
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
toString()
Returns a string represents the current Range object.
toString() : string;
toImage(ImageOrPrintOptions)
Converts the range to image.
toImage(options: ImageOrPrintOptions) : number[];
Parameters:
Parameter | Type | Description |
---|---|---|
options | ImageOrPrintOptions | The options for converting this range to image |
Returns
number[]
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) : number[];
Parameters:
Parameter | Type | Description |
---|---|---|
saveOptions | HtmlSaveOptions | Options for coverting range to html. |
Returns
number[]
isNull()
Checks whether the implementation object is null.
isNull() : boolean;