Named Ranges

OpenOffice.org Developer's Guide

Spreadsheet Documents

< Previous Section

Next Section >

• Example: Adding a New Spreadsheet
• Example: Editing Spreadsheet Cells

• Handling Spreadsheet Documents Files

• Saving Spreadsheet Documents

• Filter Options

• Printing Spreadsheet Documents

• Working With Spreadsheet Documents

• Spreadsheet Services - Overview

• Capabilities of Spreadsheet
• Capabilities of SheetCellRange
• Capabilities of SheetCell
• Capabilities of SheetCellRanges Container
• Capabilities of Columns and Rows

• Spreadsheet
• Cell Ranges

• Properties of Cell Ranges
• Cell and Cell Range Access
• Merging Cell Ranges into a Single Cell
• Column and Row Access
• Data Array
• Absolute Address
• Fill Series
• Operations
• Multiple Operations
• Handling Array Formulas

• Cells

• Properties of SheetCell
• Access to Formulas, Values and Errors
• Access to Text Content
• Absolute Address
• Cell Annotations

• Cell Ranges and Cells Container
• Columns and Rows

• Formatting

• Character and Paragraph Format
• Indentation
• Equally Formatted Cell Ranges
• Table Auto Formats
• Conditional Formats

• Navigating

• Cell Cursor
• Referencing Ranges by Name
• Named Ranges
• Label Ranges
• Querying for Cells with Specific Properties
• Search and Replace

• Sorting
• Database Operations

• Filtering
• Subtotals
• Database Import
• Database Ranges

• Linking External Data

• Sheet Links
• Cell Area Links
• DDE Links

• DataPilot Tables

• Creating a New DataPilot Table
• Modifying a DataPilot Table
• Removing a DataPilot Table

• DataPilot Sources

• Source Object
• Dimensions
• Hierarchies
• Levels
• Members

• Protecting Spreadsheets
• Sheet Outline
• Detective
• Other Table Operations

• Overall Document Features

• Function Handling
• Settings

• Spreadsheet Document Controller

• View Panes
• View Settings
• Range Selection

• Spreadsheet Add-Ins

• Function Descriptions
• Service Names
• Compatibility Names
• Custom Functions
• Variable Results

< Previous Page

Next Page >

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.

Named ranges

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_CRITERIA bit is set, the named range is offered as a criteria range in the "Advanced Filter" dialog.
  • If the PRINT_AREA, COLUMN_HEADER or ROW_HEADER bit is set, the named range is selected as "Print range", "Columns to repeat" or "Rows to repeat" in the Edit Print Ranges dialog.

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.
• For 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.
• For RIGHT, the names are taken from the range's last column.

The 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).