Working with Spreadsheet Documents

From Apache OpenOffice Wiki
Jump to: navigation, search
  • Working With Spreadsheet Documents



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.

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.

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:

  /** 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:

Methods of com.sun.star.sheet.XSpreadsheets
insertNewByName() Creates a new empty spreadsheet with the specified name and inserts it at the specified position.
moveByName() Moves the spreadsheet with the specified name to a new position.
copyByName() Creates a copy of a spreadsheet, renames it and inserts it at a new position.

The method below shows how a new spreadsheet is inserted into the spreadsheet collection of a document with the specified name.

  /** Inserts a new empty spreadsheet with the specified name.
      @param xDocument The XSpreadsheetDocument interface of the document.
      @param aName The name of the new sheet.
      @param nIndex The insertion index.
      @return The XSpreadsheet interface of the new sheet.
   */
  public com.sun.star.sheet.XSpreadsheet insertSpreadsheet(
          com.sun.star.sheet.XSpreadsheetDocument xDocument,
          String aName, short nIndex ) {
      // 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;
  }


Content on this page is licensed under the Public Documentation License (PDL).
Personal tools
In other languages