A named range is a named formula expression, where a cell range is just one possible content. Thus, the content of a named range is always set as a string.
The collection of named ranges is accessed using the document's
NamedRanges property. A new named range is added by calling the com.sun.star.sheet.XNamedRanges interface's
addNewByName() method. The method's parameters are:
- The name for the new named range.
- The content. This must be a string containing a valid formula expression. A commonly used type of expression is an absolute cell range reference like "$Sheet1.$A1:$C3".
- A reference position for relative references. If the content contains relative cell references, and the named range is used in a formula, the references are adjusted for the formula's position. The reference position states which cell the references are relative to.
- The type of the named range that controls if the named range is included in some dialogs. The type must be a combination of the com.sun.star.sheet.NamedRangeFlag constants:
- If the
FILTER_CRITERIAbit is set, the named range is offered as a criteria range in the "Advanced Filter" dialog.
- If the
ROW_HEADERbit is set, the named range is selected as "Print range", "Columns to repeat" or "Rows to repeat" in the Edit Print Ranges dialog.
- If the
addNewFromTitles() method creates named ranges from header columns or rows in a cell range. The com.sun.star.sheet.Border enum parameter selects which named ranges are created:
- If the value is
TOP, a named range is created for each column of the cell range with the name taken from the range's first row, and the other cells of that column within the cell range as content.
BOTTOM, the names are taken from the range's last row.
- If the value is
LEFT, a named range is created for each row of the cell range with the name taken from the range's first column, and the other cells of that row within the cell range as content.
RIGHT, the names are taken from the range's last column.
removeByName() method is used to remove a named range. The
outputList() method writes a list of all the named ranges into the document, starting at the specified cell position.
The com.sun.star.sheet.NamedRange service accesses an existing named range. The com.sun.star.container.XNamed interface changes the name, and the com.sun.star.sheet.XNamedRange interface changes the other settings. See the
addNewByName description above for the meaning of the individual values.
If the content of the name is a single cell range reference, the com.sun.star.sheet.XCellRangeReferrer interface is used to access that cell range.
The following example creates a named range that calculates the sum of the two cells above the position where it is used. This is done by using the relative reference "G43:G44" with the reference position G45. Then, the example uses the named range in two formulas.
// insert a named range com.sun.star.beans.XPropertySet xDocProp = (com.sun.star.beans.XPropertySet) UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, xDocument); Object aRangesObj = xDocProp.getPropertyValue("NamedRanges"); com.sun.star.sheet.XNamedRanges xNamedRanges = (com.sun.star.sheet.XNamedRanges) UnoRuntime.queryInterface(com.sun.star.sheet.XNamedRanges.class, aRangesObj); com.sun.star.table.CellAddress aRefPos = new com.sun.star.table.CellAddress(); aRefPos.Sheet = 0; aRefPos.Column = 6; aRefPos.Row = 44; xNamedRanges.addNewByName("ExampleName", "SUM(G43:G44)", aRefPos, 0); // use the named range in formulas xSheet.getCellByPosition(6, 44).setFormula("=ExampleName"); xSheet.getCellByPosition(7, 44).setFormula("=ExampleName");
|Content on this page is licensed under the Public Documentation License (PDL).|