Cell and Cell Range Access

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 >

The interface com.sun.star.sheet.XSheetCellRange is derived from com.sun.star.table.XCellRange. It provides access to cells of the range and sub ranges, and is supported by the spreadsheet and sub-ranges of a spreadsheet. The methods in com.sun.star.sheet.XSheetCellRange are:

 com::sun::star::table::XCell getCellByPosition( [in] long nColumn, [in] long nRow)
 com::sun::star::table::XCellRange getCellRangeByPosition( [in] long nLeft, [in] long nTop,
                                                           [in] long nRight, [in] long nBottom)
 com::sun::star::table::XCellRange getCellRangeByName ( [in] string aRange)
 com::sun::star::sheet::XSpreadsheet getSpreadsheet() 

The interface com.sun.star.table.XCellRange provides methods to access cell ranges and single cells from a cell range.

Cells are retrieved by their position. Cell addresses consist of a row index and a column index. The index is zero-based, that is, the index 0 means the first row or column of the table.

Cell ranges are retrieved:

by position

Addresses of cell ranges consist of indexes to the first and last row, and the first and last column. Range indexes are always zero-based, that is, the index 0 points to the first row or column of the table.

by name

It is possible to address a cell range over its name in A1:B2 notation as it would appear in the application.

In a spreadsheet, "A1:B2", "$C$1:$D$2", or "E5" are valid ranges. Even user defined cell names, range names, or database range names can be used.

Additionally, XCellRange contains the method getSpreadsheet() that returns the com.sun.star.sheet.XSpreadsheet interface of the spreadsheet which contains the cell range.

 // --- First cell in a cell range. ---
 com.sun.star.table.XCell xCell = xCellRange.getCellByPosition(0, 0);
 
 // --- Spreadsheet that contains the cell range. ---
 com.sun.star.sheet.XSpreadsheet xSheet = xCellRange.getSpreadsheet(); 

There are no methods to modify the contents of all cells of a cell range. Access to cell range formatting is supported. Refer to the chapter Formatting for additional details.

In the following example, xRange is an existing cell range (a com.sun.star.table.XCellRange interface):

  com.sun.star.beans.XPropertySet xPropSet = null;
  com.sun.star.table.XCellRange xCellRange = null;
  
  // *** Accessing a CELL RANGE ***
  
  // Accessing a cell range over its position.
  xCellRange = xRange.getCellRangeByPosition(2, 0, 3, 1);
  
  // Change properties of the range.
  xPropSet = (com.sun.star.beans.XPropertySet)
      UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, xCellRange);
  xPropSet.setPropertyValue("CellBackColor", new Integer(0x8080FF));
  
  // Accessing a cell range over its name.
  xCellRange = xRange.getCellRangeByName("C4:D5");
  
  // Change properties of the range.
  xPropSet = (com.sun.star.beans.XPropertySet)
      UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, xCellRange);
  xPropSet.setPropertyValue("CellBackColor", new Integer(0xFFFF80));

Content on this page is licensed under the Public Documentation License (PDL).