com.gembox.spreadsheet

Class CellRange

All implemented interfaces:: Iterable<ExcelCell>

public class CellRange
extends AbstractRange
implements Iterable<ExcelCell>

Cell range is a rectangular group of worksheet cells.

Cell range is determined by its top, left, bottom and right borders. These methods are read-only, so if you require different cell range use one of the following methods: CellRange#getSubrangeAbsolute, CellRange#getSubrangeRelative, CellRange#getSubrange(String) or CellRange#getSubrange(String, String). Specific cell can be accessed in a few ways, depending on CellRange#getIndexingMode. Cells in the range can be merged / unmerged by the use of CellRange#setMerged method.

CellRange#setValue method will set value of multiple cells or of a merged range. CellRange#getValue method has meaning only if range is merged; otherwise, exception is thrown.

Following code creates horizontal, vertical and rectangular cell ranges and demonstrates how indexing works different in different context. CellBorder.setBorders method is used to mark outside borders of the rectangular range.

CellRange cr = excelFile.getWorksheet(0).getRow(1).getCells();
 
cr.get(0).setValue(cr.getIndexingMode());
cr.get(3).setValue("D2");
cr.get("B").setValue("B2");

cr = excelFile.getWorksheet(0).getColumn(4).getCells();

cr.get(0).setValue(cr.getIndexingMode());
cr.get(2).setValue("E3");
cr.get("5").setValue("E5");

cr = excelFile.getWorksheet(0).getCells().getSubrange("F2", "J8");
cr.getStyle().getBorders().setBorders(MultipleBorders.outside(), SpreadsheetColor.fromName(ColorName.BLUE), LineStyle.DASHED);

cr.get("I7").setValue(cr.getIndexingMode());
cr.get(0, 0).setValue("F2");
cr.get("G3").setValue("G3");
cr.get(5).setValue("F3"); // Cell range width is 5 (F G H I J).

cr = excelFile.getWorksheet(0).getCells().getSubrange("F10:J16"); // Using A1 notation.
cr.getStyle().getBorders().setBorders(MultipleBorders.outside(), SpreadsheetColor.fromName(ColorName.BLUE), LineStyle.DASHED);

cr.get("I15").setValue(cr.getIndexingMode());
cr.get(0).setValue("F10");
cr.get("F11").setValue("F11");

Constructor Summary

Constructors
Constructor and Description
`CellRange(ExcelWorksheet parent, int firstRow, int firstColumn, int lastRow, int lastColumn)`

Method Summary

All Methods
Modifer and Type	Method and Description
`void`	`calculate()` Calculates formula for every excel cell in this range.
`void`	`clearFormat()` Clears the range format.
`void`	`copyTo(ExcelWorksheet destinationWorksheet, int absoluteRow, int absoluteColumn)` Copies this cell range to another worksheet.
`void`	`copyTo(ExcelWorksheet destinationWorksheet, String topLeftCell)` Copies this cell range to another worksheet.
`void`	`copyTo(int absoluteRow, int absoluteColumn)` Copies this cell range to another position in the same worksheet.
`void`	`copyTo(String topLeftCell)` Copies this cell range to another position in the same worksheet or in another worksheet.
`boolean`	`equals(Object obj)` Determines whether the specified `java.lang.Object` is equal to this CellRange instance.
`AutoFilter`	`filter()` Gets the AutoFilter active on the parent worksheet (ExcelWorksheet#getFilter) or Table (Table#getFilter) if active range is equal to this CellRange; otherwise, a new AutoFilter instance, which is set as active on the parent worksheet or table.
`AutoFilter`	`filter(boolean active)` Gets the AutoFilter active on the parent worksheet (ExcelWorksheet#getFilter) or Table (Table#getFilter) if active range is equal to this CellRange and parameter `active` is `true`; otherwise, a new AutoFilter instance, which is set as active on the parent worksheet or table if parameter `active` is `true`.
`RowColumn`	`findText(String text)` Finds the first occurrence of the specified text in the current cell range.
`RowColumn`	`findText(String text, boolean matchCase)` Finds the first occurrence of the specified text in the current cell range.
`RowColumn`	`findText(String text, boolean matchCase, boolean matchEntireCellContents)` Finds the first occurrence of the specified text in the current cell range.
`RowColumn`	`findText(Pattern pattern)` Finds the first occurrence which matches the specified Pattern in the current cell range.
`ExcelCell`	`get(int contextIndex)` Gets excel cell at the specified index.
`ExcelCell`	`get(int relativeRow, int relativeColumn)` Gets excel cell at the specified relative position.
`ExcelCell`	`get(String contextName)` Gets excel cell with the specified full or partial name.
`ExcelComment`	`getComment()` Gets comment
`void`	`getCurrentRegion()` Selects a worksheet region to which the cell range belongs to.
`String`	`getEndPosition()` Gets name of the last (bottom-right) cell in this cell range.
`int`	`getFirstColumnIndex()` Gets index of the first (leftmost) column.
`int`	`getFirstRowIndex()` Gets index of the first (topmost) row.
`String`	`getFormula()` Gets formula string.
`int`	`getHeight()` Gets height of this cell range, in rows.
`SpreadsheetHyperlink`	`getHyperlink()` Gets hyperlink
`RangeIndexingMode`	`getIndexingMode()` Gets indexing mode used for cell range.
`int`	`getLastColumnIndex()` Gets index of the last (rightmost) column.
`int`	`getLastRowIndex()` Gets index of the last (bottommost) row.
`CellRangeIterator`	`getReadIterator()` Returns iterator for the CellRange (only allocated cells).
`String`	`getStartPosition()` Gets name of the first (top-left) cell in this cell range.
`void`	`getSubrange(String reference)` Returns new cell range using A1 style notation.
`void`	`getSubrange(String firstCell, String lastCell)` Returns new cell range using start and end position.
`void`	`getSubrangeAbsolute(int firstAbsoluteRow, int firstAbsoluteColumn, int lastAbsoluteRow, int lastAbsoluteColumn)` Returns new cell range using absolute indexing.
`void`	`getSubrangeRelative(int relativeRow, int relativeColumn, int width, int height)` Returns new cell range using relative indexing.
`Object`	`getValue()` Gets cell value on one or more excel cells.
`int`	`getWidth()` Gets width of this cell range, in columns.
`int`	`hashCode()` Returns a hash code for this CellRange instance.
`void`	`insert(InsertShiftDirection direction)` Inserts a range by shifting cells in the specified direction.
`boolean`	`isAnyCellMerged()` Returns `true` is any cell in this cell range is merged; otherwise, `false`.
`boolean`	`isMerged()` Gets whether cells in this range are merged.
`Iterator<ExcelCell>`	`iterator()` Returns an iterator for the CellRange (all cells).
`boolean`	`overlaps(CellRange range)` Checks if this cell range overlaps with another cell range.
`RowColumn`	`positionToRowColumn(String position)` Converts position String ("A1", "BN27", etc.) to row and column index.
`void`	`remove(RemoveShiftDirection direction)` Removes a range by shifting cells in the specified direction.
`String`	`rowColumnToPosition(int row, int column)` Converts row and column index to position string ("A1", "BN27", etc.).
`void`	`setComment(ExcelComment value)` Sets comment
`void`	`setFormula(String formula)` Sets formula string.
`void`	`setHyperlink(SpreadsheetHyperlink value)` Sets hyperlink
`void`	`setMerged(boolean value)` Sets whether cells in this range are merged.
`void`	`setValue(Object value)` Sets cell value on one or more excel cells.
`SortState`	`sort(boolean active)` Gets the SortState active on the parent worksheet (ExcelWorksheet#getSort) if active range is equal to this CellRange and parameter `active` is `true`; otherwise, a new SortState instance, which is set as active on the parent worksheet if parameter `active` is `true`.

Constructor Detail

CellRange

protected CellRange(ExcelWorksheet parent, int firstRow, int firstColumn, int lastRow, int lastColumn)

Parameters:: parent; firstRow; firstColumn; lastRow; lastColumn

Method Detail

calculate

public void calculate()

Calculates formula for every excel cell in this range.

clearFormat

protected void clearFormat()

Clears the range format.

copyTo

public final void copyTo(ExcelWorksheet destinationWorksheet, int absoluteRow, int absoluteColumn)

Copies this cell range to another worksheet.

absoluteRow and absoluteColumn specify position of the top-left cell of the destination cell range.

Destination cell range has the same width and height as this cell range.

IndexOutOfBoundsException is thrown if destination range:

breaks Excel worksheet row or column limit,
overlaps with source range, or
overlaps with existing merged range.

Parameters:: destinationWorksheet -
Destination worksheet.; absoluteRow -
Absolute index of the destination row.; absoluteColumn -
Absolute index of the destination column.

copyTo

public final void copyTo(ExcelWorksheet destinationWorksheet, String topLeftCell)

Copies this cell range to another worksheet.

topLeftCell specifies position of the top-left cell of the destination cell range.

Destination cell range has the same width and height as this cell range.

IndexOutOfBoundsException is thrown if destination range:

breaks Excel worksheet row or column limit,
overlaps with source range, or
overlaps with existing merged range.

Parameters:: destinationWorksheet -
Destination worksheet.; topLeftCell -
Full name of the top-left cell of the destination range.

copyTo

public final void copyTo(int absoluteRow, int absoluteColumn)

Copies this cell range to another position in the same worksheet.

absoluteRow and absoluteColumn specify position of the top-left cell of the destination cell range.

Destination cell range has the same width and height as this cell range.

IndexOutOfBoundsException is thrown if destination range:

breaks Excel worksheet row or column limit,
overlaps with source range, or
overlaps with existing merged range.

Parameters:: absoluteRow -
Absolute index of the destination row.; absoluteColumn -
Absolute index of the destination column.

copyTo

public final void copyTo(String topLeftCell)

Copies this cell range to another position in the same worksheet or in another worksheet.

topLeftCell specifies position of the top-left cell of the destination cell range (e.g. "D4, "Sheet2!D5", etc.).

Destination cell range has the same width and height as this cell range.

IndexOutOfBoundsException is thrown if destination range:

breaks Excel worksheet row or column limit,
overlaps with source range, or
overlaps with existing merged range.

Parameters:: topLeftCell -
Full name of the top-left cell of the destination range (e.g. "D4", "Sheet2!D5", etc.).

equals

public boolean equals(Object obj)

Determines whether the specified java.lang.Object is equal to this CellRange instance.

Parameters:: obj -
The Object to compare with this CellRange instance.
Returns:: true if the specified java.lang.Object is a CellRange and is equal to this CellRange instance; otherwise, false.

filter

public final AutoFilter filter()

Gets the AutoFilter active on the parent worksheet (ExcelWorksheet#getFilter) or Table (Table#getFilter) if active range is equal to this CellRange; otherwise, a new AutoFilter instance, which is set as active on the parent worksheet or table.

AutoFilter is used to store filter settings and to apply filter on this range of cells.

This method has the same affect as #filter(boolean) with parameter set to true.

The filter is set as active filter on a table if this range is inside any table in this worksheet. Otherwise the filter is set as active filter on the parent worksheet

Returns:: The AutoFilter active on the parent worksheet (ExcelWorksheet#getFilter) if active range is equal to this CellRange; otherwise, a new AutoFilter instance, which is set as active on the parent worksheet or table.

filter

public final AutoFilter filter(boolean active)

Gets the AutoFilter active on the parent worksheet (ExcelWorksheet#getFilter) or Table (Table#getFilter) if active range is equal to this CellRange and parameter active is true; otherwise, a new AutoFilter instance, which is set as active on the parent worksheet or table if parameter active is true.

AutoFilter is used to store filter settings and to apply filter on this range of cells.

Use this method (with parameter active set to false) to create multiple independent AutoFilters for the same CellRange instance, even if it has an active AutoFilter (ExcelWorksheet#getFilter), which can then be stored and used later on.

If the parameter active is set to false the filter is set as active filter on a table if this range is inside any table in this worksheet. Otherwise the filter is set as active filter on the parent worksheet

Parameters:: active -
if set to true, Gets the AutoFilter active on the parent worksheet (ExcelWorksheet#getFilter).
Returns:: The AutoFilter active on the parent worksheet (ExcelWorksheet#getFilter) if active range is equal to this CellRange and parameter active is true; otherwise, a new AutoFilter instance, which is set as active on the parent worksheet if parameter active is true.

findText

public final RowColumn findText(String text)

Finds the first occurrence of the specified text in the current cell range.

Parameters:: text -
Test to search.
Returns:: null if text is not found, otherwise RowColumn consisting of the index of the row and column where the text was found.

findText

public final RowColumn findText(String text, boolean matchCase)

Finds the first occurrence of the specified text in the current cell range.

Parameters:: text -
Test to search.; matchCase -
true to match exact case, false otherwise.
Returns:: null if text is not found, otherwise RowColumn consisting of the index of the row and column where the text was found.

findText

public final RowColumn findText(String text, boolean matchCase, boolean matchEntireCellContents)

Finds the first occurrence of the specified text in the current cell range.

Parameters:: text -
Test to search.; matchCase -
true to match exact case, false otherwise.; matchEntireCellContents -
true to match entire cell contents, false otherwise.
Returns:: null if text is not found, otherwise RowColumn consisting of the index of the row and column where the text was found.

findText

public final RowColumn findText(Pattern pattern)

Finds the first occurrence which matches the specified Pattern in the current cell range.

Parameters:: pattern -
The Pattern which should be matched.
Returns:: null if text is not found, otherwise RowColumn consisting of the index of the row and column where the text was found.

get

public final ExcelCell get(int contextIndex)

Gets excel cell at the specified index.

If CellRange#getIndexingMode is RangeIndexingMode#HORIZONTAL context index is specifying relative column position.

If CellRange#getIndexingMode() is RangeIndexingMode.Vertical context index is specifying relative row position.

If CellRange#getIndexingMode is RangeIndexingMode#RECTANGULAR context index is specifying cell index inside cell range. The cell at start position has index 0, and the cell at CellRange#getEndPosition has index of CellRange#getWidth x CellRange#getHeight - 1.

Parameters:: contextIndex -
The zero-based context index of the cell.

get

public final ExcelCell get(int relativeRow, int relativeColumn)

Gets excel cell at the specified relative position.

Absolute position of excel cell is calculated by adding relativeRow and relativeColumn to CellRange#getFirstRowIndex and CellRange#getFirstColumnIndex.

Parameters:: relativeRow -
The zero-based relative row position.; relativeColumn -
The zero-based relative column position.

get

public final ExcelCell get(String contextName)

Gets excel cell with the specified full or partial name.

If CellRange#getIndexingMode is RangeIndexingMode.Rectangular full name of the cell must be used (for example; "A1", "D7", etc.).

If CellRange#getIndexingMode is RangeIndexingMode.Horizontal column name must be used (for example; "A", "D", etc.).

If CellRange#getIndexingMode is RangeIndexingMode.Vertical row name must be used (for example; "1", "7", etc.).

Parameters:: contextName -
Full or partial name of the cell.

getComment

public ExcelComment getComment()

Gets comment

getCurrentRegion

public CellRange getCurrentRegion()

Selects a worksheet region to which the cell range belongs to.

Returns:: The worksheet region to which the cell range belongs to.

getEndPosition

public final String getEndPosition()

Gets name of the last (bottom-right) cell in this cell range.

getFirstColumnIndex

public final int getFirstColumnIndex()

Gets index of the first (leftmost) column.

getFirstRowIndex

public final int getFirstRowIndex()

Gets index of the first (topmost) row.

getFormula

public String getFormula()

Gets formula string.

New XLSX (Open XML) format stores formulas as strings and leaves formula parsing to applications that read XLSX documents. Therefore, ALL formulas are supported when writing/reading XLSX files.

GemBox.Spreadsheet formula parser will use English culture to parse numbers.

Currently unsupported formula features are:

Const array
Array formula
R1C1 reference

For more information on formulas, consult Microsoft Excel documentation.

Returns:: formula

getHeight

public final int getHeight()

Gets height of this cell range, in rows.

getHyperlink

public SpreadsheetHyperlink getHyperlink()

Gets hyperlink

getIndexingMode

public final RangeIndexingMode getIndexingMode()

Gets indexing mode used for cell range.

If CellRange#getHeight() is 1, indexing mode is RangeIndexingMode#HORIZONTAL.

Otherwise, if CellRange#getWidth is 1, indexing mode is RangeIndexingMode#VERTICAL.

Otherwise, indexing mode is RangeIndexingMode#RECTANGULAR Rectangular

getLastColumnIndex

public final int getLastColumnIndex()

Gets index of the last (rightmost) column.

getLastRowIndex

public final int getLastRowIndex()

Gets index of the last (bottommost) row.

getReadIterator

public final CellRangeIterator getReadIterator()

Returns iterator for the CellRange (only allocated cells).

Returns iterator that iterates only already allocated cells in the range. If you are only reading existing cells (values or formatting), use this iterator as it is faster and doesn't allocate unnecessary cells.

Returns:: An iterator for the CellRange (only allocated cells).

getStartPosition

public final String getStartPosition()

Gets name of the first (top-left) cell in this cell range.

getSubrange

public final CellRange getSubrange(String reference)

Returns new cell range using A1 style notation.

New cell range must be within this cell range. Multiple area selection is not supported (e.g. "A1:B2,C3:D4,E5:F6").

Parameters:: reference -
The cell range reference in A1 style notation.
Returns:: New cell range using A1 style notation.

getSubrange

public final CellRange getSubrange(String firstCell, String lastCell)

Returns new cell range using start and end position.

New cell range must be within this cell range.

Parameters:: firstCell -
Name of first (top-left) cell.; lastCell -
Name of last (bottom-right) cell.
Returns:: New cell range using start and end position.

getSubrangeAbsolute

public final CellRange getSubrangeAbsolute(int firstAbsoluteRow, int firstAbsoluteColumn, int lastAbsoluteRow, int lastAbsoluteColumn)

Returns new cell range using absolute indexing.

New cell range must be within this cell range.

Parameters:: firstAbsoluteRow -
Absolute index of the first row.; firstAbsoluteColumn -
Absolute index of the first column.; lastAbsoluteRow -
Absolute index of the last row.; lastAbsoluteColumn -
Absolute index of the last column.
Returns:: New cell range using absolute indexing.

getSubrangeRelative

public final CellRange getSubrangeRelative(int relativeRow, int relativeColumn, int width, int height)

Returns new cell range using relative indexing.

New cell range must be within this cell range.

Parameters:: relativeRow -
Relative index of the first row.; relativeColumn -
Relative index of the first column.; width -
Width of new cell range in columns.; height -
Height of new cell range in rows.
Returns:: New cell range using relative indexing.

getValue

public Object getValue()

Gets cell value on one or more excel cells.

Has meaning only if range is merged; otherwise, exception is thrown.

getWidth

public final int getWidth()

Gets width of this cell range, in columns.

hashCode

public int hashCode()

Returns a hash code for this CellRange instance.

Returns:: An integer value that specifies a hash value for this CellRange instance.

insert

public void insert(InsertShiftDirection direction)

Inserts a range by shifting cells in the specified direction.

Parameters:: direction -
The shift direction.

isAnyCellMerged

public final boolean isAnyCellMerged()

Returns true is any cell in this cell range is merged; otherwise, false.

isMerged

public boolean isMerged()

Gets whether cells in this range are merged.

You can find out if the cell is merged by checking if ExcelCell MergedRange is different than null.

iterator

public final Iterator<ExcelCell> iterator()

Returns an iterator for the CellRange (all cells).

Returns default iterator that iterates all cells in the range. If you are only reading existing cells (values or formatting), use more appropriate CellRange#getReadIterator.

Returns:: An iterator for the CellRange (all cells).

overlaps

public final boolean overlaps(CellRange range)

Checks if this cell range overlaps with another cell range.

Parameters:: range -
Cell range.
Returns:: true if cell ranges overlap; otherwise, false.

positionToRowColumn

public static RowColumn positionToRowColumn(String position)

Converts position String ("A1", "BN27", etc.) to row and column index.

Parameters:: position -
Position string.
Returns:: row and column index tuple

remove

public void remove(RemoveShiftDirection direction)

Removes a range by shifting cells in the specified direction.

Parameters:: direction -
The shift direction.

rowColumnToPosition

public static String rowColumnToPosition(int row, int column)

Converts row and column index to position string ("A1", "BN27", etc.).

Parameters:: row -
Row index.; column -
Column index.
Returns:: Position string.

setComment

public void setComment(ExcelComment value)

Sets comment

Parameters:: value -
Sets comment

setFormula

public void setFormula(String formula)

Sets formula string.

New XLSX (Open XML) format stores formulas as strings and leaves formula parsing to applications that read XLSX documents. Therefore, ALL formulas are supported when writing/reading XLSX files.

GemBox.Spreadsheet formula parser will use English culture to parse numbers.

Currently unsupported formula features are:

Const array
Array formula
R1C1 reference

For more information on formulas, consult Microsoft Excel documentation.

Following code demonstrates how to use formulas and named ranges. It shows following features: cell references (both absolute and relative), unary and binary operators, constant operands (integer and floating point), functions and named cell ranges. At the end, code shows how to calculate worksheet formulas.

ws.getCell("A1").setValue(5);
ws.getCell("A2").setValue(6);
ws.getCell("A3").setValue(10);

ws.getCell("C1").setFormula("=A1+A2");
ws.getCell("C2").setFormula("=$A$1-A3");
ws.getCell("C3").setFormula("=COUNT(A1:A3)");
ws.getCell("C4").setFormula("=AVERAGE($A$1:$A$3)");
ws.getCell("C5").setFormula("=SUM(A1:A3,2,3)");
ws.getCell("C7").setFormula("= 123 - (-(-(23.5)))");

ws.addNamedRange("DataRange", ws.getCells().getSubrange("A1", "A3"));
ws.getCell("C8").setFormula("=MAX(DataRange)");

CellRange cr = ws.getCells().getSubrange("B9", "C10");
cr.setMerged(true);
cr.setFormula("=A1*25");

ws.calculate();

Parameters:: formula -
formula

setHyperlink

public void setHyperlink(SpreadsheetHyperlink value)

Sets hyperlink

Parameters:: value -
Sets hyperlink

setMerged

public void setMerged(boolean value)

Sets whether cells in this range are merged.

By setting this property to true, you are merging all the cells (ExcelCell) in this range. Merging process will fail if any of the cells in the range is already merged.

When modifying merged cell, whole merged range is modified. For example, if you set ExcelCell value, value of merged range will be modified. You can find out if the cell is merged by checking if ExcelCell MergedRange is different than null.

Parameters:: value -
Sets whether cells in this range are merged.

setValue

public void setValue(Object value)

Sets cell value on one or more excel cells.

It will set value of multiple cells or of a merged range.

Parameters:: value -
Sets cell value on one or more excel cells.

sort

public final SortState sort(boolean active)

Gets the SortState active on the parent worksheet (ExcelWorksheet#getSort) if active range is equal to this CellRange and parameter active is true; otherwise, a new SortState instance, which is set as active on the parent worksheet if parameter active is true.

SortState is used to store sort settings and to apply sort on this range of cells.

Use this method (with parameter active set to false) to create multiple independent SortStates for the same CellRange instance, even if it has an active SortState (ExcelWorksheet#getSort), which can then be stored and used later on.

Parameters:: active -
if set to true, Gets the SortState active on the parent worksheet (ExcelWorksheet#getSort).
Returns:: The SortState active on the parent worksheet (ExcelWorksheet#getSort) if active range is equal to this CellRange and parameter active is true; otherwise, a new SortState instance, which is set as active on the parent worksheet if parameter active is true.