public final class NamedRangeCollection extends Object implements Iterable<NamedRange>
You can use the labels of columns and rows on a worksheet to refer to the cells within those columns and rows. Or you can create descriptive names to represent cells, ranges of cells, formulas, or constant values. Labels can be used in formulas that refer to data on the same worksheet; if you want to represent a range on another worksheet, use a name. You can also create 3-D names that represent the same cell or range of cells across multiple worksheets.
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();
ExcelCell formula
Modifier and Type | Field and Description |
---|---|
static String |
FILTER_RESERVED_NAME |
static String |
PRINT_AREA_RESERVED_NAME |
static String |
PRINT_TITLES_RESERVED_NAME |
Modifier and Type | Method and Description |
---|---|
void |
add(String name,
CellRange range)
Adds a new named range.
|
void |
add(String name,
CellRange range,
boolean globalName)
Adds a new named range.
|
NamedRange |
get(int index)
Gets the
NamedRange at the specified index. |
NamedRange |
get(String name)
Gets the
NamedRange with the specified name. |
Iterator<NamedRange> |
iterator()
Returns an iterator that can iterate through a collection.
|
void |
setPrintArea(CellRange... ranges)
Sets multi-range print area.
|
void |
setPrintArea(CellRange range)
Sets print area.
|
void |
setPrintArea(Iterable<CellRange> ranges)
Sets multi-range print area.
|
void |
setPrintTitles(ExcelColumn startColumn,
int numberOfColumns)
Sets print titles (columns to repeat at left).
|
void |
setPrintTitles(ExcelColumnRowBase startRow,
int numberOfRows,
ExcelColumnRowBase startColumn,
int numberOfColumns)
Sets print titles.
|
void |
setPrintTitles(ExcelRow startRow,
int numberOfRows)
Sets print titles (rows to repeat at top).
|
int |
size()
Gets the number of named ranges contained in the collection.
|
equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
forEach, spliterator
public static final String FILTER_RESERVED_NAME
public static final String PRINT_AREA_RESERVED_NAME
public static final String PRINT_TITLES_RESERVED_NAME
public void add(String name, CellRange range)
You can use the labels of columns and rows on a worksheet to refer to the cells within those columns and rows. Or you can create descriptive names to represent cells, ranges of cells, formulas, or constant values. Labels can be used in formulas that refer to data on the same worksheet; if you want to represent a range on another worksheet, use a name. You can also create 3-D names that represent the same cell or range of cells across multiple worksheets.
name
- The user-defined name.range
- The range to be referred by name.ExcelCell formula
public void add(String name, CellRange range, boolean globalName)
You can use the labels of columns and rows on a worksheet to refer to the cells within those columns and rows. Or you can create descriptive names to represent cells, ranges of cells, formulas, or constant values. Labels can be used in formulas that refer to data on the same worksheet; if you want to represent a range on another worksheet, use a name. You can also create 3-D names that represent the same cell or range of cells across multiple worksheets.
name
- The user-defined name.range
- The range to be referred by name.globalName
- sets the range as global if set to trueExcelCell formula
public NamedRange get(int index)
NamedRange
at the specified index.index
- Range index.public NamedRange get(String name)
NamedRange
with the specified name.name
- Range name.public Iterator<NamedRange> iterator()
iterator
in interface Iterable<NamedRange>
public void setPrintArea(CellRange... ranges)
Multi-range print area is currently supported in XLSX only.
If specified, print area is used when exporting to HTML format as cell range which will be exported in the output file format.
ranges
- Ranges of cells that we want set as print area.public void setPrintArea(CellRange range)
Multi-range print area is currently supported in XLSX only.
If specified, print area is used when exporting to HTML format as cell range which will be exported in the output file format.
range
- Range of cells that we want set as print area.public void setPrintArea(Iterable<CellRange> ranges)
If specified, print area is used when exporting to HTML format as cell range which will be exported in the output file format.
ranges
- Ranges of cells that we want set as print area.public void setPrintTitles(ExcelColumn startColumn, int numberOfColumns)
startColumn
- First column.numberOfColumns
- Number of columns that repeat.public void setPrintTitles(ExcelColumnRowBase startRow, int numberOfRows, ExcelColumnRowBase startColumn, int numberOfColumns)
startRow
- First row.numberOfRows
- Number of rows that repeat.startColumn
- First column.numberOfColumns
- Number of columns that repeat.public void setPrintTitles(ExcelRow startRow, int numberOfRows)
startRow
- First row.numberOfRows
- Number of rows that repeat.public int size()
© GemBox Ltd. — All rights reserved.