Class CellRange
- java.lang.Object
- com.gembox.spreadsheet.AbstractRange
- com.gembox.spreadsheet.CellRange
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
Constructor and Description |
---|
CellRange(ExcelWorksheet parent, int firstRow, int firstColumn, int lastRow, int lastColumn) |
Method Summary
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 |
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 |
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 |
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 |
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.).
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
istrue
; otherwise, a new AutoFilter instance, which is set as active on the parent worksheet if parameteractive
istrue
.
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.
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.
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.
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.
getWidth
public final int getWidth()
Gets width of this cell range, in columns.
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
.
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.
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
istrue
; otherwise, a new SortState instance, which is set as active on the parent worksheet if parameteractive
istrue
.