[ Previous document | Content Table | Next document ]

9  Spreadsheet Documents

9.1  Overview

OpenOffice.org API knows three variants of tables: text tables (see 8.3.4 Text Documents - Working with Text Documents - Tables), database tables (see 13.4.3 Database Access - Database Design - Using SDBCX to Access the Database Design - Table Service) and spreadsheets. Each of the table concepts have their own purpose. Text tables handle text contents, database tables offer database functionality and spreadsheets operate on data cells that can be evaluated. Being specialized in such a way means that each concept has its strength. Text tables offer full functionality for text formatting, where spreadsheets support complex calculations. Alternately, spreadsheets support only basic formatting capabilities and text tables perform elementary calculations.

The implementation of the various tables differ due to each of their specializations. Basic table features are defined in the module com.sun.star.table. Regarding the compatibility of text and spreadsheet tables, the corresponding features are also located in the module com.sun.star.table. In addition, spreadsheet tables are fully based on the specifications given and are extended by additional specifications from the module com.sun.star.sheet. Several services of the spreadsheet application representing cells and cell ranges extend the common services from the module com::sun::star::table. The following table shows the services for cells and cell ranges.

The spreadsheet document model in the OpenOffice.org API has five major architectural areas (see Illustration 9.1) The five areas are:

• Spreadsheets Container 

• Service Manager (document internal) 

• DrawPages 

• Content Properties 

• Objects for Styling 

Illustration 9.1: Spreadsheet Document Component

The core of the spreadsheet document model are the spreadsheets contained in the spreadsheets container. When working with document data, almost everything happens in the spreadsheet objects extracted from the spreadsheets container.

The service manager of the spreadsheet document model creates shape objects, text fields for page headers and form controls that can be added to spreadsheets. Note, that the document service manager is different from the main service manager used when connecting to the office. Each document model has its own service manager, so that the services can be adapted to the document they are required for. For instance, a text field is ordered and inserted into the page header text of a sheet using com.sun.star.text.XText:insertTextContent()or the service manager is asked for a shape object and inserts it into a sheet using add()at the drawpage.

Each sheet in a spreadsheet document can have a drawpage for drawing contents. A drawpage can be visualized as a transparent layer above a sheet. The spreadsheet model is able to provide all drawpages in a spreadsheet document at once.

Linked and named contents from all sheets are accessed through content properties at the document model. There are no content suppliers as in text documents, because the actual content of a spreadsheet document lies in its sheet objects. Rather, there are only certain properties for named and linked contents in all sheets. 

Finally, there are services that allow for document wide styling and structuring of the spreadsheet document. Among them are style family suppliers for cells and pages, and a number formats supplier. 

Besides these five architectural areas, there are document and calculation aspects shown at the bottom of the illustration. The document aspects of our model are: it is printable, storable, and modifiable, it can be protected and audited, and it supplies general information about itself. On the lower left of the illustration, the calculation aspects are listed. Although almost all spreadsheet functionality can be found at the spreadsheet objects, a few common functions are bound to the spreadsheet document model: goal seeking, consolidation and recalculation of all cells. 

Finally, the document model has a controller that provides access to the graphical user interface of the model and has knowledge about the current view status in the user interface (see the upper left of the illustration). 

The usage of the spreadsheet objects in the spreadsheets container is discussed in detail in the section 9.3 Spreadsheet Documents - Working with Spreadsheets. Before discussing spreadsheet objects, consider two examples and how they handle a spreadsheet document, that is, how to create, open, save and print.

9.1.1  Example: Adding a New Spreadsheet

The following helper method opens a new spreadsheet document component. The method getRemoteServiceManager() retrieves a connection. Refer to chapter 2 First Steps for additional information.

import com.sun.star.lang.XComponent; 

import com.sun.star.frame.XComponentLoader; 

import com.sun.star.beans.PropertyValue; 

... 

protected XComponent newSpreadsheetComponent() throws java.lang.Exception { 

    String loadUrl = "private:factory/scalc";

    xRemoteServiceManager = this.getRemoteServiceManager(unoUrl);

    Object desktop = xRemoteServiceManager.createInstanceWithContext(

        "com.sun.star.frame.Desktop", xRemoteContext);

    XComponentLoader xComponentLoader = (XComponentLoader)UnoRuntime.queryInterface(

        XComponentLoader.class, desktop);

    PropertyValue[] loadProps = new PropertyValue[0];

    return xComponentLoader.loadComponentFromURL(loadUrl, "_blank", 0, loadProps);    

} 

Our helper returns a com.sun.star.lang.XComponent interface for the recently loaded document. Now the XComponent is passed to the following method insertSpreadsheet() to add a new spreadsheet to our document. (Spreadsheet/SpreadsheetDocHelper.java)

import com.sun.star.sheet.XSpreadsheetDcoument; 

import com.sun.star.sheet.XSpreadsheet;  

... 

/** Inserts a new empty spreadsheet with the specified name. 

    @param xSheetComponent  The XComponent interface of a loaded document object

    @param aName  The name of the new sheet.

    @param nIndex  The insertion index.

    @return  The XSpreadsheet interface of the new sheet.

 */

public XSpreadsheet insertSpreadsheet( 

        XComponent xSheetComponent, String aName, short nIndex) {

    XSpreadsheetDocument xDocument = (XSpreadsheetDocument)UnoRuntime.queryInterface(

        XSpreadsheetDocument.class, xSheetComponent);

    // Collection of sheets

    com.sun.star.sheet.XSpreadsheets xSheets = xDocument.getSheets();

    com.sun.star.sheet.XSpreadsheet xSheet = null;

    try {

        xSheets.insertNewByName(aName, nIndex);

        xSheet = xSheets.getByName(aName);

    } catch (Exception ex) {

    }

    return xSheet;

} 

9.1.2  Example: Editing Spreadsheet Cells

The method insertSpreadsheet() returns a com.sun.star.sheet.XSpreadsheet interface. This interface is passed to the method below, which shows how to access and modify the content and formatting of single cells. The interface com.sun.star.sheet.XSpreadsheet returned by insertSpreadsheet() is derived from com.sun.star.table.XCellRange. By working with it, cells can be accessed immediately using getCellByPosition(): (Spreadsheet/GeneralTableSample.java)

void cellWork(XSpreadsheet xRange) { 

    com.sun.star.beans.XPropertySet xPropSet = null;

    com.sun.star.table.XCell xCell = null;

    // Access and modify a VALUE CELL

    xCell = xRange.getCellByPosition(0, 0);

    // Set cell value.

    xCell.setValue(1234);

    // Get cell value.

    double nDblValue = xCell.getValue() * 2;

    xRange.getCellByPosition(0, 1).setValue(nDblValue);

    // Create a FORMULA CELL

    xCell = xRange.getCellByPosition(0, 2);

    // Set formula string.

    xCell.setFormula("=1/0");

    // Get error type.

    boolean bValid = (xCell.getError() == 0);

    // Get formula string.

    String aText = "The formula " + xCell.getFormula() + " is ";

    aText += bValid ? "valid." : "erroneous.";

    // Insert a TEXT CELL using the XText interface

    xCell = xRange.getCellByPosition(0, 3);

    com.sun.star.text.XText xCellText = (com.sun.star.text.XText)

        UnoRuntime.queryInterface(com.sun.star.text.XText.class, xCell);

    com.sun.star.text.XTextCursor xTextCursor = xCellText.createTextCursor();

    xCellText.insertString(xTextCursor, aText, false);

} 

9.2  Handling Spreadsheet Document Files

9.2.1  Creating and Loading Spreadsheet Documents

If a document in OpenOffice.org API is required, begin by getting a com.sun.star.frame.Desktop service from the service manager. The desktop handles all document components in OpenOffice.org API. It is discussed thoroughly in the chapter 7 Office Development. Office documents are often called components, because they support the com.sun.star.lang.XComponent interface. An XComponent is a UNO object that can be disposed of directly and broadcast an event to other UNO objects when the object is disposed.

The Desktop can load new and existing components from a URL. For this purpose it has a com.sun.star.frame.XComponentLoader interface that has one single method to load and instantiate components from a URL into a frame:

com::sun::star::lang::XComponent loadComponentFromURL( [IN] string aURL,

           [IN] string aTargetFrameName,

           [IN] long nSearchFlags,

           [IN] sequence <com::sun::star::beans::PropertyValue[] aArgs > )

The interesting parameters in our context is the URL that describes the resource that is loaded and the load arguments. For the target frame, pass a "_blank" and set the search flags to 0. In most cases, existing frames are not reused.

The URL can be a file: URL, an http: URL, an ftp: URL or a private: URL. Locate the correct URL format in the Load URL box in the function bar of OpenOffice.org API. For new spreadsheet documents, a special URL scheme is used. The scheme is "private:", followed by "factory". The resource is "scalc" for OpenOffice.org API spreadsheet documents. For a new spreadsheet document, use "private:factory/scalc".

The load arguments are described in com.sun.star.document.MediaDescriptor. The properties AsTemplate and Hidden are boolean values and used for programming. If AsTemplate is true, the loader creates a new untitled document from the given URL. If it is false, template files are loaded for editing. If Hidden is true, the document is loaded in the background. This is useful to generate a document in the background without letting the user observe what is happening. For instance, use it to generate a document and print it out without previewing. Refer to 7 Office Development for other available options. This snippet loads a document in hidden mode:

    // the method getRemoteServiceManager is described in the chapter First Steps

    mxRemoteServiceManager = this.getRemoteServiceManager(unoUrl);

    // retrieve the Desktop object, we need its XComponentLoader

    Object desktop = mxRemoteServiceManager.createInstanceWithContext(

        "com.sun.star.frame.Desktop", mxRemoteContext);

    // query the XComponentLoader interface from the Desktop service

    XComponentLoader xComponentLoader = (XComponentLoader)UnoRuntime.queryInterface(

        XComponentLoader.class, desktop);

    // define load properties according to com.sun.star.document.MediaDescriptor

    /* or simply create an empty array of com.sun.star.beans.PropertyValue structs:

       PropertyValue[] loadProps = new PropertyValue[0]

     */

    // the boolean property Hidden tells the office to open a file in hidden mode

    PropertyValue[] loadProps = new PropertyValue[1];

    loadProps[0] = new PropertyValue();

    loadProps[0].Name = "Hidden";

    loadProps[0].Value = new Boolean(true);

    loadUrl = "file:///c:/MyCalcDocument.ods"

    // load

    return xComponentLoader.loadComponentFromURL(loadUrl, "_blank", 0, loadProps);          

9.2.2  Saving Spreadsheet Documents

Storing

Documents are storable through their interface com.sun.star.frame.XStorable. This interface is discussed in detail in 7 Office Development. An XStorable implements these operations:

boolean hasLocation()

string getLocation()

boolean isReadonly()

void store()

void storeAsURL([in] string aURL, [in] sequence< com::sun::star::beans::PropertyValue > aArgs)

void storeToURL([in] string aURL, [in] sequence< com::sun::star::beans::PropertyValue > aArgs)

The method names are evident. The method storeAsUrl() is the exact representation of File – Save As from the File menu, that is, it changes the current document location. In contrast, storeToUrl() stores a copy to a new location, but leaves the current document URL untouched.

Exporting

Filter Options

Loading and saving OpenOffice.org API documents is described in 7.1.5 Office Development - OpenOffice.org Application Environment - Handling Documents. This section lists all the filter names for spreadsheet documents and describes the filter options for text file import.

The filter name and options are passed on loading or saving a document in a sequence of com.sun.star.beans.PropertyValues. The property FilterName contains the name and the property FilterOptions contains the filter options.

All filter names are case-sensitive. For compatibility reasons the filter names will not be changed. Therefore, some of the filters seem to have “curious” names.

The list of filter names (the last two columns show the possible directions of the filters): 

Filter Options for Lotus, dBase and DIF Filters

Filter Options for the CSV Filter

9.2.3  Printing Spreadsheet Documents

Printer and Print Job Settings

Printing is a common office functionality. The chapter  7 Office Development provides in-depth information about it. The spreadsheet document implements the com.sun.star.view.XPrintable interface for printing. It consists of three methods:

sequence< com::sun::star::beans::PropertyValue > getPrinter()

void setPrinter([in] sequence< com::sun::star::beans::PropertyValue > aPrinter)

void print([in] sequence< com::sun::star::beans::PropertyValue > xOptions)

The following code is used with a given document xDoc to print to the standard printer without any settings: 

    // query the XPrintable interface from your document

    XPrintable xPrintable = (XPrintable)UnoRuntime.queryInterface(XPrintable.class, xDoc);

    // create an empty printOptions array

    PropertyValue[] printOpts = new PropertyValue[0];

    // kick off printing       

    xPrintable.print(printOpts);

There are two groups of properties involved in general printing. The first one is used with setPrinter() and getPrinter(), and controls the printer, and the second is passed to print() and controls the print job.

com.sun.star.view.PrinterDescriptor comprises the properties for the printer:

com.sun.star.view.PrintOptions contains the following possibilities for a print job:

The following method uses PrinterDescriptor and PrintOptions to print to a special printer, and preselect the pages to print.

protected void printDocComponent(XComponent xDoc) throws java.lang.Exception { 

    XPrintable xPrintable = (XPrintable)UnoRuntime.queryInterface(XPrintable.class, xDoc);

    PropertyValue[] printerDesc = new PropertyValue[1];

    printerDesc[0] = new PropertyValue();

    printerDesc[0].Name = "Name";

    printerDesc[0].Value = "5D PDF Creator";        

    xPrintable.setPrinter(printerDesc);        

    PropertyValue[] printOpts = new PropertyValue[1];

    printOpts[0] = new PropertyValue();

    printOpts[0].Name = "Pages";

    printOpts[0].Value = "3-5,7";        

    xPrintable.print(printOpts);

} 

Page Breaks and Scaling for Printout

Manual page breaks can be inserted and removed using the property IsStartOfNewPage of the services com.sun.star.table.TableColumn and com.sun.star.table.TableRow. For details, refer to the section about page breaks in the chapter 9 Spreadsheet Documents.

To reduce the page size of a sheet so that the sheet fits on a fixed number of printout pages, use the properties PageScale and ScaleToPages of the current page style. Both of the properties are short numbers. The PageScale property expects a percentage and ScaleToPages is the number of pages the printout is to fit. The page style is available through the interface com.sun.star.style.XStyleFamiliesSupplier of the document component, and is described in the chapter 9.4.1 Spreadsheet Documents - Overall Document Features - Styles.

Print Areas

The Interface com.sun.star.sheet.XPrintAreas is available at spreadsheets. It provides access to the addresses of all printable cell ranges, represented by a sequence of com.sun.star.table.CellRangeAddress structs.

9.3  Working with Spreadsheet Documents

9.3.1  Document Structure

Spreadsheet Document

The whole spreadsheet document is represented by the service com.sun.star.sheet.SpreadsheetDocument. It implements interfaces that provide access to the container of spreadsheets and methods to modify the document wide contents, for instance, data consolidation.

Illustration 9.2: Spreadsheet Document

A spreadsheet document contains a collection of spreadsheets with at least one spreadsheet, represented by the service com.sun.star.sheet.Spreadsheets. The method getSheets() of the Interface com.sun.star.sheet.XSpreadsheetDocument returns the interface com.sun.star.sheet.XSpreadsheets for accessing the container of sheets.

Illustration 9.3: Spreadsheets Container

When the spreadsheet container is retrieved from a document using its getSheets() method, it is possible to access the sheets in three different ways:

by index 

Using the interface com.sun.star.container.XIndexAccess allows access to spreadsheets by their index.

with an enumeration 

Using the service com.sun.star.sheet.SpreadsheetsEnumeration spreadsheets can be accessed as an enumeration.

by name 

The interface com.sun.star.sheet.XSpreadsheets is derived from com.sun.star.container.XNameContainer and therefore contains all methods for accessing the sheets with a name. It is possible to get a spreadsheet using com.sun.star.container.XNameAccess) to replace it with another sheet (interface com.sun.star.container.XNameReplace), and to insert and remove a spreadsheet (interface com.sun.star.container.XNameContainer).

The following two helper methods demonstrate how spreadsheets are accessed by their indexes and their names: (Spreadsheet/SpreadsheetDocHelper.java) 

/** Returns the spreadsheet with the specified index (0-based). 

    @param xDocument  The XSpreadsheetDocument interface of the document.

    @param nIndex  The index of the sheet.

    @return  The XSpreadsheet interface of the sheet. */

public com.sun.star.sheet.XSpreadsheet getSpreadsheet( 

        com.sun.star.sheet.XSpreadsheetDocument xDocument, int nIndex) {

    // Collection of sheets

    com.sun.star.sheet.XSpreadsheets xSheets = xDocument.getSheets();

    com.sun.star.sheet.XSpreadsheet xSheet = null;

    try {

        com.sun.star.container.XIndexAccess xSheetsIA = (com.sun.star.container.XIndexAccess)

            UnoRuntime.queryInterface(com.sun.star.container.XIndexAccess.class, xSheets);

        xSheet = (com.sun.star.sheet.XSpreadsheet) xSheetsIA.getByIndex(nIndex);

    } catch (Exception ex) {

    }

    return xSheet;

} 

/** Returns the spreadsheet with the specified name. 

    @param xDocument  The XSpreadsheetDocument interface of the document.

    @param aName  The name of the sheet.

    @return  The XSpreadsheet interface of the sheet. */

public com.sun.star.sheet.XSpreadsheet getSpreadsheet( 

        com.sun.star.sheet.XSpreadsheetDocument xDocument,

        String aName) {

    // Collection of sheets

    com.sun.star.sheet.XSpreadsheets xSheets = xDocument.getSheets();

    com.sun.star.sheet.XSpreadsheet xSheet = null;

    try {

        com.sun.star.container.XNameAccess xSheetsNA = (com.sun.star.container.XNameAccess)

            UnoRuntime.queryInterface(com.sun.star.container.XNameAccess.class, xSheets);

        xSheet = (com.sun.star.sheet.XSpreadsheet) xSheetsNA.getByName(aName);

    } catch (Exception ex) {

    }

    return xSheet;

} 

The interface com.sun.star.sheet.XSpreadsheets contains additional methods that use the name of spreadsheets to add new sheets, and to move and copy them:

The method below shows how a new spreadsheet is inserted into the spreadsheet collection of a document with the specified name. (Spreadsheet/SpreadsheetDocHelper.java) 

Spreadsheet Services - Overview

The previous section introduced the organization of the spreadsheets in a document and how they can be handled. This section discusses the spreadsheets themselves. The following illustration provides an overview about the main API objects that can be used in a spreadsheet.

Illustration 9.4: Main Spreadsheet Services

The main services in a spreadsheet are com.sun.star.sheet.Spreadsheet, com.sun.star.sheet.SheetCellRange, the cell service com.sun.star.sheet.SheetCell, the collection of cell ranges com.sun.star.sheet.SheetCellRanges and the services com.sun.star.table.TableColumn and com.sun.star.table.TableRow. An overview of the capabilities of these services is provided below.

Capabilities of Spreadsheet

The spreadsheet is a com.sun.star.sheet.Spreadsheet service that includes the service com.sun.star.sheet.SheetCellRange, that is, a spreadsheet is a cell range with additional capabilities concerning the entire sheet:

Illustration 9.5: Spreadsheet

• It can be named using com.sun.star.container.XNamed.

• It has interfaces for sheet analysis. Data pilot tables, sheet outlining, sheet auditing (detective) and scenarios all are controlled from the spreadsheet object. The corresponding interfaces are com.sun.star.sheet.XDataPilotTablesSupplier, com.sun.star.sheet.XScenariosSupplier, com.sun.star.sheet.XSheetOutline and com.sun.star.sheet.XSheetAuditing.

• Cells can be inserted, and entire cell ranges can be removed, moved or copied on the spreadsheet level using com.sun.star.sheet.XCellRangeMovement.

• Drawing elements in a spreadsheet are part of the draw page available through com.sun.star.drawing.XDrawPageSupplier.

• Certain sheet printing features are accessed at the spreadsheet. The com.sun.star.sheet.XPrintAreas and com.sun.star.sheet.XSheetPageBreak are used to get page breaks and control print areas.

• The spreadsheet maintains charts. The interface com.sun.star.table.XTableChartsSupplier controls charts in the spreadsheet.

• All cell annotations can be retrieved on the spreadsheet level with com.sun.star.sheet.XSheetAnnotationsSupplier.

• A spreadsheet can be permanently protected from changes through com.sun.star.util.XProtectable.

Capabilities of SheetCellRange

The spreadsheet, as well as the cell ranges in a spreadsheet are com.sun.star.sheet.SheetCellRange services. A SheetCellRange is a rectangular range of calculation cells that includes the following services:

Illustration 9.6: Services supported by SheetCellRange

The interfaces supported by a SheetCellRange are depicted in the following illustration: 

Illustration 9.7: SheetCellRange Interfaces

A SheetCellRange has the following capabilities:

• Supplies cells and sub-ranges of cells, as well as rows and columns. It has the interfaces com.sun.star.sheet.XSheetCellRange and com.sun.star.table.XColumnRowRange.

• Performs calculations with a SheetCellRange. The interface com.sun.star.sheet.XSheetOperation is for aggregate operations, com.sun.star.sheet.XMultipleOperation copies formulas adjusting their cell references, com.sun.star.sheet.XSubTotalCalculatable applies and removes sub totals, and com.sun.star.sheet.XArrayFormulaRange handles array formulas.

• Formats cells in a range. The settings affect all cells in the range. There are cell properties, character properties and paragraph properties for formatting purposes. Additionally, a SheetCellRange supports auto formats with com.sun.star.table.XAutoFormattable and the content of the cells can be indented using com.sun.star.util.XIndent. The interfaces com.sun.star.sheet.XCellFormatRangesSupplier and com.sun.star.sheet.XUniqueCellFormatRangesSupplier obtain enumeration of cells that differ in formatting.

• Works with the data in a cell range through a sequence of sequences of any that maps to the two-dimensional cell array of the range. This array is available through com.sun.star.sheet.XCellRangeData.

• Fills a cell range with data series automatically through its interface com.sun.star.sheet.XCellSeries.

• Imports data from a database using com.sun.star.util.XImportable.

• Searches and replaces cell contents using com.sun.star.util.XSearchable.

• Perform queries for cell contents, such as formula cells, formula result types, or empty cells. The interface com.sun.star.sheet.XCellRangesQuery of the included com.sun.star.sheet.SheetRangesQuery service is responsible for this task.

• Merges cells into a single cell through com.sun.star.util.XMergeable.

• Sorts and filters the content of a SheetCellRange, using com.sun.star.util.XSortable, com.sun.star.sheet.XSheetFilterable and com.sun.star.sheet.XSheetFilterableEx.

• Provides its unique range address in the spreadsheet document, that is, the start column and row, end column and row, and the sheet where it is located. The com.sun.star.sheet.XCellRangeAddressable:getRangeAddress() returns the corresponding address description struct com.sun.star.table.CellRangeAddress.

• Charts can be based on a SheetCellRange, because it supports com.sun.star.chart.XChartDataArray.

Capabilities of SheetCell

A com.sun.star.sheet.SheetCell is the base unit of OpenOffice.org Calc tables. Values, formulas and text required for calculation jobs are all written into sheet cells. The SheetCell includes the following services:

Illustration 9.8: SheetCell

The SheetCell exports the following interfaces:

Illustration 9.9: SheetCell Interfaces

The SheetCell service has the following capabilities:

• It can access the cell content. It can contain numeric values that are used for calculations, formulas that operate on these values, and text supporting full-featured formatting and hyperlink text fields. The access to the cell values and formulas is provided through the SheetCell parent service com.sun.star.table.Cell. The interface com.sun.star.table.XCell is capable of manipulating the values and formulas in a cell. For text, the service com.sun.star.text.Text with the main interface com.sun.star.text.XText is available at a SheetCell. Its text fields are accessed through com.sun.star.text.XTextFieldsSupplier.

• A SheetCell is a special case of a SheetCellRange. As such, it has all capabilities of the com.sun.star.sheet.SheetCellRange described above.

• It can have an annotation: com.sun.star.sheet.XSheetAnnotationAnchor.

• It can provide its unique cell address in the spreadsheet document, that is, its column, row and the sheet it is located in. The com.sun.star.sheet.XCellAddressable:getCellAddress() returns the appropriate com.sun.star.table.CellAddress struct.

• It can be locked temporarily against user interaction with com.sun.star.document.XActionLockable.

Capabilities of SheetCellRanges Container

The container of com.sun.star.sheet.SheetCellRanges is used where several cell ranges have to be handled at once for cell query results and other situations. The SheetCellRanges service includes cell, paragraph and character property services, and it offers a query option:

Illustration 9.10: Services of SheetCellRanges

The interfaces of com.sun.star.sheet.SheetCellRanges are element accesses for the ranges in the SheetCellRanges container. These interfaces are discussed below.

Illustration 9.11: Implemented interfaces of SheetCellRanges

The SheetCellRanges container has the following capabilities:

• It can be formatted using the character, paragraph and cell property services it includes. 

• It yields independent cell ranges through the element access interfaces com.sun.star.container.XIndexAccess, com.sun.star.container.XNameAccess and com.sun.star.container.XEnumerationAccess.

• It can access, replace, append and remove ranges by name through com.sun.star.container.XNameContainer

• It can add new ranges to SheetCellRanges by their address descriptions, access the ranges by index, and obtain the cells in the ranges. This is possible through the interface com.sun.star.sheet.XSheetCellRangeContainer that was originally based on com.sun.star.container.XIndexAccess. The SheetCellRanges maintain a sub-container of all cells in the ranges that are not empty, obtainable through the getCells() method.

• It can enumerate the ranges using com.sun.star.container.XEnumerationAccess.

• It can query the ranges for certain cell contents, such as formula cells, formula result types or empty cells. The interface com.sun.star.sheet.XCellRangesQuery of the included com.sun.star.sheet.SheetRangesQuery service is responsible for this task.

• The SheetCellRanges supports selected SheetCellRange features, such as searching and replacing, indenting, sheet operations and charting.

Capabilities of Columns and Rows

All cell ranges are organized in columns and rows, therefore column and row containers are retrieved from a spreadsheet, as well as from sub-ranges of a spreadsheet through com.sun.star.table.XColumnRowRange. These containers are com.sun.star.table.TableColumns and com.sun.star.table.TableRows. Both containers support index and enumeration access. Only the TableColumns supports name access to the single columns and rows (com.sun.star.table.TableColumn and com.sun.star.table.TableRow) of a SheetCellRange.

The following UML charts show table columns and rows. The first chart shows columns: 

Illustration 9.12: Collection of table columns

The collection of table rows differs from the collection of columns, that is, it does not support com.sun.star.container.XNameAccess:

Illustration 9.13: Collection of table rows

The services for table rows and columns control the table structure and grid size of a cell range: 

• The containers for columns and rows have methods to insert and remove columns, and rows by index in their main interfaces com.sun.star.table.XTableRows and com.sun.star.table.XTableColumns.

• The services TableColumn and TableRow have properties to adjust their column width and row height, toggle their visibility, and set page breaks.

Spreadsheet

A spreadsheet is a cell range with additional interfaces and is represented by the service com.sun.star.sheet.Spreadsheet.

Properties of Spreadsheet

Naming

The spreadsheet interface com.sun.star.container.XNamed obtains and changes the name of the spreadsheet, and uses it to get a spreadsheet from the spreadsheet collection. Refer to 9.3.1 Spreadsheet Documents - Working with Spreadsheets - Document Structure - Spreadsheet Document.

Inserting Cells, Moving and Copying Cell Ranges

The interface com.sun.star.sheet.XCellRangeMovement of the Spreadsheet service supports inserting and removing cells from a spreadsheet, and copying and moving cell contents. When cells are copied or moved, the relative references of all formulas are updated automatically. The sheet index included in the source range addresses should be equal to the index of the sheet of this interface.

The following example copies a cell range to another location in the sheet. (Spreadsheet/SpreadsheetSample.java)