Range

Inheritance: java.lang.Object

public class Range

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

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.

Example

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.getWorksheets().get(0).getCells();
         // Create a range (A1:D3).
         Range range = cells.createRange("A1", "D3");
         // Set value to the range.
         range.setValue("Hello");
         //Save the Excel file
         workbook.save("book1.xlsm");

Methods

MethodDescription
addHyperlink(String address, String textToDisplay, String screenTip)Adds a hyperlink to a specified cell or a range of cells.
applyStyle(Style style, StyleFlag flag)Applies formats for a whole range.
autoFill(Range target)Automaticall fill the target range.
autoFill(Range target, int autoFillType)Automaticall fill the target range.
copy(Range range)Copies data (including formulas), formatting, drawing objects etc. from a source range.
copy(Range range, PasteOptions options)Copying the range with paste special options.
copyData(Range range)Copies cell data (including formulas) from a source range.
copyStyle(Range range)Copies style settings from a source range.
copyValue(Range range)Copies cell value from a source range.
equals(Object arg0)
get(int rowOffset, int columnOffset)Gets Cell object in this range.
getAddress()Gets address of the range.
getCellOrNull(int rowOffset, int columnOffset)Gets Cell object or null in this range.
getClass()
getColumnCount()Gets the count of columns in the range.
getColumnWidth()Sets or gets the column width of this range
getCurrentRegion()Returns a Range object that represents the current region.
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.
getFirstColumn()Gets the index of the first column of the range.
getFirstRow()Gets the index of the first row of the range.
getHeight()Gets the width of a range in points.
getHyperlinks()Gets all hyperlink in the range.
getLeft()Gets the distance, in points, from the left edge of column A to the left edge of the range.
getName()Gets the name of the range.
getOffset(int rowOffset, int columnOffset)Gets Range range by offset.
getRefersTo()Gets the range’s refers to.
getRowCount()Gets the count of rows in the range.
getRowHeight()Sets or gets the height of rows in this range
getTop()Gets the distance, in points, from the top edge of row 1 to the top edge of the range.
getValue()Gets the value of the range.
getWidth()Gets the width of a range in points.
getWorksheet()Gets the getWorksheet()object which contains this range.
hashCode()
intersect(Range range)Returns a Range object that represents the rectangular intersection of two ranges.
isBlank()Indicates whether the range contains values.
isIntersect(Range range)Indicates whether the range is intersect.
iterator()Gets the enumerator for cells in this Range.
merge()Combines a range of cells into a single cell.
moveTo(int destRow, int destColumn)Move the current range to the dest range.
notify()
notifyAll()
putValue(String stringValue, boolean isConverted, boolean setStyle)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.
setColumnWidth(double value)Sets or gets the column width of this range
setInsideBorders(int borderEdge, int lineStyle, CellsColor borderColor)Set inside borders of the range.
setName(String value)Sets the name of the range.
setOutlineBorder(int borderEdge, int borderStyle, CellsColor borderColor)Sets outline border around a range of cells.
setOutlineBorder(int borderEdge, int borderStyle, Color borderColor)Sets outline border around a range of cells.
setOutlineBorders(int borderStyle, CellsColor borderColor)Sets the outline borders around a range of cells with same border style and color.
setOutlineBorders(int borderStyle, Color borderColor)Sets the outline borders around a range of cells with same border style and color.
setOutlineBorders(int[] borderStyles, Color[] borderColors)Sets out line borders around a range of cells.
setRowHeight(double value)Sets or gets the height of rows in this range
setStyle(Style style)Sets the style of the range.
setStyle(Style style, boolean explicitFlag)Apply the cell style.
setValue(Object value)Sets the value of the range.
toHtml(HtmlSaveOptions saveOptions)Convert the range to html .
toImage(ImageOrPrintOptions options)Converts the range to image.
toJson(JsonSaveOptions options)Convert the range to JSON value.
toString()Returns a string represents the current Range object.
transpose()Transpose (rotate) data from rows to columns or vice versa.
unMerge()Unmerges merged cells of this range.
union(Range range)Returns the union of two ranges.
unionRang(Range range)Returns the union result of two ranges.
unionRanges(Range[] ranges)Returns the union result of two ranges.
wait()
wait(long arg0)
wait(long arg0, int arg1)
public Hyperlink addHyperlink(String address, String textToDisplay, String screenTip)

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

Parameters:

ParameterTypeDescription
addressjava.lang.StringAddress of the hyperlink.
textToDisplayjava.lang.StringThe text to be displayed for the specified hyperlink.
screenTipjava.lang.StringThe screenTip text for the specified hyperlink.

Returns: Hyperlink - Hyperlink object.

applyStyle(Style style, StyleFlag flag)

public void applyStyle(Style style, StyleFlag flag)

Applies formats for a whole range.

Remarks

Each cell in this range will contains a Style object. So this is a memory-consuming method. Please use it carefully.

Parameters:

ParameterTypeDescription
styleStyleThe style object which will be applied.
flagStyleFlagFlags which indicates applied formatting properties.

autoFill(Range target)

public void autoFill(Range target)

Automaticall fill the target range.

Example

         //Instantiating a Workbook object
         Workbook workbook = new Workbook();
         // Get the first Worksheet Cells.
         Cells cells = workbook.getWorksheets().get(0).getCells();
         cells.get("A1").putValue(1);
         cells.get("A2").putValue(2);
         Range source = cells.createRange("A1:A2");
         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");

Parameters:

ParameterTypeDescription
targetRangethe target range.

autoFill(Range target, int autoFillType)

public void autoFill(Range target, int autoFillType)

Automaticall fill the target range.

Parameters:

ParameterTypeDescription
targetRangeThe targed range.
autoFillTypeintAutoFillType. The auto fill type.

copy(Range range)

public void copy(Range range)

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

Example

         //Instantiating a Workbook object
         Workbook workbook = new Workbook();
         // Get the first Worksheet Cells.
         Cells cells = workbook.getWorksheets().get(0).getCells();
         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");

Parameters:

ParameterTypeDescription
rangeRangeSource Range object.

copy(Range range, PasteOptions options)

public void copy(Range range, PasteOptions options)

Copying the range with paste special options.

Parameters:

ParameterTypeDescription
rangeRangeThe source range.
optionsPasteOptionsThe paste special options.

copyData(Range range)

public void copyData(Range range)

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

Parameters:

ParameterTypeDescription
rangeRangeSource Range object.

copyStyle(Range range)

public void copyStyle(Range range)

Copies style settings from a source range.

Parameters:

ParameterTypeDescription
rangeRangeSource Range object.

copyValue(Range range)

public void copyValue(Range range)

Copies cell value from a source range.

Parameters:

ParameterTypeDescription
rangeRangeSource Range object.

equals(Object arg0)

public boolean equals(Object arg0)

Parameters:

ParameterTypeDescription
arg0java.lang.Object

Returns: boolean

get(int rowOffset, int columnOffset)

public Cell get(int rowOffset, int columnOffset)

Gets Cell object in this range.

Parameters:

ParameterTypeDescription
rowOffsetintRow offset in this range, zero based.
columnOffsetintColumn offset in this range, zero based.

Returns: Cell - Cell object.

getAddress()

public String getAddress()

Gets address of the range.

Returns: java.lang.String

getCellOrNull(int rowOffset, int columnOffset)

public Cell getCellOrNull(int rowOffset, int columnOffset)

Gets Cell object or null in this range.

Parameters:

ParameterTypeDescription
rowOffsetintRow offset in this range, zero based.
columnOffsetintColumn offset in this range, zero based.

Returns: Cell - Cell object.

getClass()

public final native Class<?> getClass()

Returns: java.lang.Class

getColumnCount()

public int getColumnCount()

Gets the count of columns in the range.

Returns: int

getColumnWidth()

public double getColumnWidth()

Sets or gets the column width of this range

Returns: double

getCurrentRegion()

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

Returns: Range

getEntireColumn()

public Range getEntireColumn()

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

Returns: Range

getEntireRow()

public Range getEntireRow()

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

Returns: Range

getFirstColumn()

public int getFirstColumn()

Gets the index of the first column of the range.

Returns: int

getFirstRow()

public int getFirstRow()

Gets the index of the first row of the range.

Returns: int

getHeight()

public double getHeight()

Gets the width of a range in points.

Returns: double

public Hyperlink[] getHyperlinks()

Gets all hyperlink in the range.

Returns: com.aspose.cells.Hyperlink[]

getLeft()

public double getLeft()

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

Returns: double

getName()

public String getName()

Gets the name of the range.

Remarks

Named range is supported. For example,

range.Name = “Sheet1!MyRange”;

Returns: java.lang.String

getOffset(int rowOffset, int columnOffset)

public Range getOffset(int rowOffset, int columnOffset)

Gets Range range by offset.

Parameters:

ParameterTypeDescription
rowOffsetintRow offset in this range, zero based.
columnOffsetintColumn offset in this range, zero based.

Returns: Range -

getRefersTo()

public String getRefersTo()

Gets the range’s refers to.

Returns: java.lang.String

getRowCount()

public int getRowCount()

Gets the count of rows in the range.

Returns: int

getRowHeight()

public double getRowHeight()

Sets or gets the height of rows in this range

Returns: double

getTop()

public double getTop()

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

Returns: double

getValue()

public Object getValue()

Gets the value of the range.

Remarks

If the range contains multiple cells, the returned/applied object should be Object[][].

Returns: java.lang.Object

getWidth()

public double getWidth()

Gets the width of a range in points.

Returns: double

getWorksheet()

public Worksheet getWorksheet()

Gets the getWorksheet()object which contains this range.

Returns: Worksheet

hashCode()

public native int hashCode()

Returns: int

intersect(Range range)

public Range intersect(Range range)

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

Remarks

If the two ranges are not intersected, returns null.

Example

         //Instantiating a Workbook object
         Workbook workbook = new Workbook();
         // Get the first Worksheet Cells.
         Cells cells = workbook.getWorksheets().get(0).getCells();
         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");

Parameters:

ParameterTypeDescription
rangeRangeThe intersecting range.

Returns: Range - Returns a Range object

isBlank()

public boolean isBlank()

Indicates whether the range contains values.

Returns: boolean -

isIntersect(Range range)

public boolean isIntersect(Range range)

Indicates whether the range is intersect.

Remarks

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

Parameters:

ParameterTypeDescription
rangeRangeThe range.

Returns: boolean - Whether the range is intersect.

iterator()

public Iterator iterator()

Gets the enumerator for cells in this Range.

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

Example

         Workbook workbook = new Workbook("template.xlsx");
         	Cells cells = workbook.getWorksheets().get(0).getCells();
 
         	Iterator en = cells.createRange("B2:C3").iterator();
         	while (en.hasNext())
         	{
         	    Cell cell = (Cell)en.next();
         	    System.out.println(cell.getName() + ": " + cell.getValue());
         	}

Returns: java.util.Iterator - The cells enumerator

merge()

public void merge()

Combines a range of cells into a single cell.

Remarks

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

moveTo(int destRow, int destColumn)

public void moveTo(int destRow, int destColumn)

Move the current range to the dest range.

Parameters:

ParameterTypeDescription
destRowintThe start row of the dest range.
destColumnintThe start column of the dest range.

notify()

public final native void notify()

notifyAll()

public final native void notifyAll()

putValue(String stringValue, boolean isConverted, boolean setStyle)

public void putValue(String stringValue, boolean isConverted, boolean setStyle)

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.

Parameters:

ParameterTypeDescription
stringValuejava.lang.StringInput value
isConvertedbooleanTrue: converted to other data type if appropriate.
setStylebooleanTrue: set the number format to cell’s style when converting to other data type

setColumnWidth(double value)

public void setColumnWidth(double value)

Sets or gets the column width of this range

Parameters:

ParameterTypeDescription
valuedouble

setInsideBorders(int borderEdge, int lineStyle, CellsColor borderColor)

public void setInsideBorders(int borderEdge, int lineStyle, CellsColor borderColor)

Set inside borders of the range.

Parameters:

ParameterTypeDescription
borderEdgeintBorderType. Inside borde type, only can be BorderType.VERTICAL and BorderType.HORIZONTAL.
lineStyleintCellBorderType. The border style.
borderColorCellsColorThe color of the border.

setName(String value)

public void setName(String value)

Sets the name of the range.

Remarks

Named range is supported. For example,

range.Name = “Sheet1!MyRange”;

Parameters:

ParameterTypeDescription
valuejava.lang.String

setOutlineBorder(int borderEdge, int borderStyle, CellsColor borderColor)

public void setOutlineBorder(int borderEdge, int borderStyle, CellsColor borderColor)

Sets outline border around a range of cells.

Parameters:

ParameterTypeDescription
borderEdgeintBorderType. Border edge.
borderStyleintCellBorderType. Border style.
borderColorCellsColorBorder color.

setOutlineBorder(int borderEdge, int borderStyle, Color borderColor)

public void setOutlineBorder(int borderEdge, int borderStyle, Color borderColor)

Sets outline border around a range of cells.

Parameters:

ParameterTypeDescription
borderEdgeintBorderType. Border edge.
borderStyleintCellBorderType. Border style.
borderColorColorBorder color.

setOutlineBorders(int borderStyle, CellsColor borderColor)

public void setOutlineBorders(int borderStyle, CellsColor borderColor)

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

Parameters:

ParameterTypeDescription
borderStyleintCellBorderType. Border style.
borderColorCellsColorBorder color.

setOutlineBorders(int borderStyle, Color borderColor)

public void setOutlineBorders(int borderStyle, Color borderColor)

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

Parameters:

ParameterTypeDescription
borderStyleintCellBorderType. Border style.
borderColorColorBorder color.

setOutlineBorders(int[] borderStyles, Color[] borderColors)

public void setOutlineBorders(int[] borderStyles, Color[] borderColors)

Sets out line borders around a range of cells.

Remarks

Both the length of borderStyles and borderStyles must be 4. The order of borderStyles and borderStyles must be top,bottom,left,right

Parameters:

ParameterTypeDescription
borderStylesint[]CellBorderType. Border styles.
borderColorsColor[]Border colors.

setRowHeight(double value)

public void setRowHeight(double value)

Sets or gets the height of rows in this range

Parameters:

ParameterTypeDescription
valuedouble

setStyle(Style style)

public void setStyle(Style style)

Sets the style of the range.

Parameters:

ParameterTypeDescription
styleStyleThe Style object.

setStyle(Style style, boolean explicitFlag)

public void setStyle(Style style, boolean explicitFlag)

Apply the cell style.

Parameters:

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

setValue(Object value)

public void setValue(Object value)

Sets the value of the range.

Remarks

If the range contains multiple cells, the returned/applied object should be Object[][].

Parameters:

ParameterTypeDescription
valuejava.lang.Object

toHtml(HtmlSaveOptions saveOptions)

public byte[] toHtml(HtmlSaveOptions saveOptions)

Convert the range to html .

Parameters:

ParameterTypeDescription
saveOptionsHtmlSaveOptionsOptions for coverting range to html.

Returns: byte[] -

toImage(ImageOrPrintOptions options)

public byte[] toImage(ImageOrPrintOptions options)

Converts the range to image.

Parameters:

ParameterTypeDescription
optionsImageOrPrintOptionsThe options for converting this range to image

Returns: byte[] -

toJson(JsonSaveOptions options)

public String toJson(JsonSaveOptions options)

Convert the range to JSON value.

Parameters:

ParameterTypeDescription
optionsJsonSaveOptionsThe options of converting

Returns: java.lang.String -

toString()

public String toString()

Returns a string represents the current Range object.

Returns: java.lang.String -

transpose()

public void transpose()

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

unMerge()

public void unMerge()

Unmerges merged cells of this range.

union(Range range)

public ArrayList union(Range 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 November 2023. Aspose apologizes for any inconvenience you may have experienced.

Parameters:

ParameterTypeDescription
rangeRangeThe range

Returns: java.util.ArrayList - The union of two ranges.

unionRang(Range range)

public Range[] unionRang(Range range)

Returns the union result 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.

Parameters:

ParameterTypeDescription
rangeRangeThe range

Returns: com.aspose.cells.Range[] - The union of two ranges.

unionRanges(Range[] ranges)

public UnionRange unionRanges(Range[] ranges)

Returns the union result of two ranges.

Parameters:

ParameterTypeDescription
rangesRange[]The range

Returns: UnionRange - The union of two ranges.

wait()

public final void wait()

wait(long arg0)

public final native void wait(long arg0)

Parameters:

ParameterTypeDescription
arg0long

wait(long arg0, int arg1)

public final void wait(long arg0, int arg1)

Parameters:

ParameterTypeDescription
arg0long
arg1int