public class CellRange extends AbstractRange implements Iterable<ExcelCell>
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: getSubrangeAbsolute(int, int, int, int)
, getSubrangeRelative(int, int, int, int)
, getSubrange(String)
or getSubrange(String, String)
.
Specific cell can be accessed in a few ways, depending on getIndexingMode()
.
Cells in the range can be merged / unmerged by the use of setMerged(boolean)
method.
setValue(java.lang.Object)
method will set value of multiple cells or of a merged range.
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");
Modifier and Type | Method and Description |
---|---|
void |
calculate()
Calculates formula for every excel cell in this range.
|
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() ) if active range is equal to this CellRange ; otherwise, a new AutoFilter instance, which is set as active on the parent worksheet. |
AutoFilter |
filter(boolean active)
Gets 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 . |
RowColumn |
findText(Pattern pattern)
Finds the first occurrence which matches the specified
Pattern in the current cell range. |
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.
|
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
|
CellRange |
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.
|
CellRange |
getSubrange(String reference)
Returns new cell range using A1 style notation.
|
CellRange |
getSubrange(String firstCell,
String lastCell)
Returns new cell range using start and end position.
|
CellRange |
getSubrangeAbsolute(int firstAbsoluteRow,
int firstAbsoluteColumn,
int lastAbsoluteRow,
int lastAbsoluteColumn)
Returns new cell range using absolute indexing.
|
CellRange |
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.
|
static 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.
|
boolean |
replaceTextCore(Pattern pattern,
String oldValue,
String newValue,
boolean matchCase,
boolean matchEntireCellContents) |
static 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 . |
clear, getCharacterRanges, getCharacters, getCharacters, getName, getStyle, replaceText, replaceText, replaceText, replaceText, setStyle, toString
forEach, spliterator
public void calculate()
calculate
in class AbstractRange
public final void copyTo(ExcelWorksheet destinationWorksheet, int absoluteRow, int absoluteColumn)
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:
destinationWorksheet
- Destination worksheet.absoluteRow
- Absolute index of the destination row.absoluteColumn
- Absolute index of the destination column.NullPointerException
- Destination worksheet is null
.IndexOutOfBoundsException
- Destination range is invalid.public final void copyTo(ExcelWorksheet destinationWorksheet, String topLeftCell)
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:
destinationWorksheet
- Destination worksheet.topLeftCell
- Full name of the top-left cell of the destination range.IndexOutOfBoundsException
- Destination range is invalid.public final void copyTo(int absoluteRow, int absoluteColumn)
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:
absoluteRow
- Absolute index of the destination row.absoluteColumn
- Absolute index of the destination column.IndexOutOfBoundsException
- Destination range is invalid.public final void copyTo(String topLeftCell)
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:
topLeftCell
- Full name of the top-left cell of the destination range (e.g. "D4", "Sheet2!D5", etc.).IllegalArgumentException
- Top-left cell is incorrectly specified.IndexOutOfBoundsException
- Destination range is invalid.public boolean equals(Object obj)
java.lang.Object
is equal to this CellRange
instance.public final AutoFilter filter()
Gets 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.
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
.
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.AutoFilter for examples on filtering
,
Excel AutoFiltering examplepublic final AutoFilter filter(boolean active)
Gets 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
.
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 AutoFilter
s for the same CellRange
instance, even if it has an active AutoFilter
(ExcelWorksheet.getFilter()
), which can then be stored and used later on.
active
- if set to true
, Gets the AutoFilter
active on the parent worksheet (ExcelWorksheet.getFilter()
).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
.AutoFilter for examples on filtering
,
Excel AutoFiltering examplepublic final RowColumn findText(Pattern pattern)
Pattern
in the current cell range.pattern
- The Pattern
which should be matched.null
if text is not found, otherwise RowColumn
consisting of the
index of the row and column where the text was found.NullPointerException
- regex
is null
.public final RowColumn findText(String text)
text
- Test to search.null
if text is not found, otherwise RowColumn
consisting of the
index of the row and column where the text was found.NullPointerException
- text
is null
.public final RowColumn findText(String text, boolean matchCase)
text
- Test to search.matchCase
- true
to match exact case, false
otherwise.null
if text is not found, otherwise RowColumn
consisting of the
index of the row and column where the text was found.NullPointerException
- text
is null
.public final RowColumn findText(String text, boolean matchCase, boolean matchEntireCellContents)
text
- Test to search.matchCase
- true
to match exact case, false
otherwise.matchEntireCellContents
- true
to match entire cell contents, false
otherwise.null
if text is not found, otherwise RowColumn
consisting of the
index of the row and column where the text was found.NullPointerException
- text
is null
.public final ExcelCell get(int contextIndex)
If getIndexingMode()
is
RangeIndexingMode.HORIZONTAL
context index
is specifying relative column position.
If getIndexingMode()
is
RangeIndexingMode.Vertical
context index
is specifying relative row position.
If 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
getEndPosition()
has index of
getWidth()
x
getHeight()
- 1.
contextIndex
- The zero-based context index of the cell.getIndexingMode()
public final ExcelCell get(int relativeRow, int relativeColumn)
Absolute position of excel cell is calculated by adding relativeRow
and relativeColumn
to
getFirstRowIndex()
and
getFirstColumnIndex()
.
relativeRow
- The zero-based relative row position.relativeColumn
- The zero-based relative column position.public final ExcelCell get(String contextName)
If getIndexingMode()
is
RangeIndexingMode.Rectangular
full name of
the cell must be used (for example; "A1", "D7", etc.).
If getIndexingMode()
is
RangeIndexingMode.Horizontal
column name
must be used (for example; "A", "D", etc.).
If getIndexingMode()
is
RangeIndexingMode.Vertical
row name
must be used (for example; "1", "7", etc.).
contextName
- Full or partial name of the cell.getIndexingMode()
public ExcelComment getComment()
getComment
in class AbstractRange
public CellRange getCurrentRegion()
public final String getEndPosition()
public final int getFirstColumnIndex()
public final int getFirstRowIndex()
public String getFormula()
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.
Depending on ExcelFile.isDelayFormulaParse()
property, formula string can be parsed when this property is set
or when one of ExcelFile.save(String)
methods is called.
GemBox.Spreadsheet formula parser will use English culture to parse numbers.
Currently unsupported formula features are:
For more information on formulas, consult Microsoft Excel documentation.
getFormula
in class AbstractRange
IllegalStateException
- Property get is attempted on a cell range which is not merged.setFormula(String) for examples
,
NamedRangeCollection.add
public final int getHeight()
public SpreadsheetHyperlink getHyperlink()
getHyperlink
in class AbstractRange
public final RangeIndexingMode getIndexingMode()
If getHeight()
is 1, indexing mode
is RangeIndexingMode.HORIZONTAL
.
Otherwise, if getWidth()
is 1, indexing mode
is RangeIndexingMode.VERTICAL
.
Otherwise, indexing mode is RangeIndexingMode.RECTANGULAR
Rectangular
public final int getLastColumnIndex()
public final int getLastRowIndex()
public final CellRangeIterator getReadIterator()
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.
CellRange
(only allocated cells).public final String getStartPosition()
public final CellRange getSubrange(String reference)
New cell range must be within this cell range. Multiple area selection is not supported (e.g. "A1:B2,C3:D4,E5:F6").
reference
- The cell range reference in A1 style notation.getStartPosition()
,
getEndPosition()
,
getSubrangeAbsolute(int, int, int, int)
,
getSubrangeRelative(int, int, int, int)
,
getSubrange(String, String)
public final CellRange getSubrange(String firstCell, String lastCell)
New cell range must be within this cell range.
firstCell
- Name of first (top-left) cell.lastCell
- Name of last (bottom-right) cell.IndexOutOfBoundsException
- Arguments are out of range.getStartPosition()
,
getEndPosition()
,
getSubrangeAbsolute(int, int, int, int)
,
getSubrangeRelative(int, int, int, int)
,
getSubrange(String)
public final CellRange getSubrangeAbsolute(int firstAbsoluteRow, int firstAbsoluteColumn, int lastAbsoluteRow, int lastAbsoluteColumn)
New cell range must be within this cell range.
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.IndexOutOfBoundsException
- Arguments are out of range.getFirstRowIndex()
,
getFirstColumnIndex()
,
getLastRowIndex()
,
getLastColumnIndex()
,
getSubrangeRelative(int, int, int, int)
,
getSubrange(String)
,
getSubrange(String, String)
public final CellRange getSubrangeRelative(int relativeRow, int relativeColumn, int width, int height)
New cell range must be within this cell range.
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.IndexOutOfBoundsException
- Arguments are out of range.getWidth()
,
getHeight()
,
getSubrangeAbsolute(int, int, int, int)
,
getSubrange(String)
,
getSubrange(String, String)
public Object getValue()
Has meaning only if range is merged
;
otherwise, exception is thrown.
getValue
in class AbstractRange
IllegalStateException
- Property get is attempted on a cell range
which is not merged.isMerged()
public final int getWidth()
public int hashCode()
CellRange
instance.public void insert(InsertShiftDirection direction)
insert
in class AbstractRange
direction
- The shift direction.public final boolean isAnyCellMerged()
true
is any cell in this cell range is merged; otherwise, false
.public boolean isMerged()
You can find out if the cell is merged by checking if
ExcelCell MergedRange
is different
than null
.
public final Iterator<ExcelCell> iterator()
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 getReadIterator()
.
public final boolean overlaps(CellRange range)
range
- Cell range.true
if cell ranges overlap; otherwise, false
.NullPointerException
- range
is null
.public static RowColumn positionToRowColumn(String position)
position
- Position string.public void remove(RemoveShiftDirection direction)
remove
in class AbstractRange
direction
- The shift direction.public boolean replaceTextCore(Pattern pattern, String oldValue, String newValue, boolean matchCase, boolean matchEntireCellContents)
replaceTextCore
in class AbstractRange
public static String rowColumnToPosition(int row, int column)
row
- Row index.column
- Column index.public void setComment(ExcelComment value)
setComment
in class AbstractRange
public void setFormula(String formula)
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.
Depending on ExcelFile.isDelayFormulaParse()
property, formula string can be parsed when this property is set
or when one of ExcelFile.save(String)
methods is called.
GemBox.Spreadsheet formula parser will use English culture to parse numbers.
Currently unsupported formula features are:
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();
setFormula
in class AbstractRange
formula
- formulaNamedRangeCollection.add
public void setHyperlink(SpreadsheetHyperlink value)
setHyperlink
in class AbstractRange
public void setMerged(boolean value)
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
.
IllegalArgumentException
- Merged range can't be created because some of the cells in the range are already merged.public void setValue(Object value)
It will set value of multiple cells or of a merged range.
setValue
in class AbstractRange
setMerged(boolean)
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 SortState
s for the same CellRange
instance, even if it has an active SortState
(ExcelWorksheet.getSort()
), which can then be stored and used later on.
active
- if set to true
, Gets the SortState
active on the parent worksheet (ExcelWorksheet.getSort()
).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 for an example
,
Excel Sorting example© GemBox d.o.o. — All rights reserved.