Difference between revisions of "Documentation/DevGuide/Spreadsheets/Cell and Cell Range Access"

Revision as of 06:51, 12 June 2008

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.

Template:Documentation/Tip

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));

Please expand...

• is there a way to use cell names in calls to tools such as Solver and GoalSeek?
• when is a cell range a satisfactory substitute for a single cell address?
• can I convert a CellRange to a CellAddress somehow?