Cell Ranges and Cells Container

From Apache OpenOffice Wiki
Jump to: navigation, search



Cell range collections are represented by the service com.sun.star.sheet.SheetCellRanges. They are returned by several methods, for instance the cell query methods of com.sun.star.sheet.SheetRangesQuery. Besides standard container operations, it performs a few spreadsheet functions also usable with a single cell range.

Properties of SheetCellRanges

Properties of com.sun.star.sheet.SheetCellRanges
ConditionalFormat

ConditionalFormatLocal

Used to access conditional formats. See Conditional Formats for details.
Validation

ValidationLocal

Used to access data validation. See Data Validation for details.

Access to Single Cell Ranges in SheetCellRanges Container

The interfaces com.sun.star.container.XEnumerationAccess and com.sun.star.container.XIndexAccess iterates over all contained cell ranges by index or enumeration. With the com.sun.star.container.XNameContainer, it is possible to insert ranges with a user-defined name. Later the range can be found, replaced or removed using the name.

The following interfaces and service perform cell range actions on all ranges contained in the collection:

The interfaces com.sun.star.sheet.XSheetCellRangeContainer and com.sun.star.sheet.XSheetCellRanges support basic handling of cell range collections.

  • The method getRangeAddressesAsString() returns the string representation of all cell ranges.
  • The method getRangeAddresses() returns a sequence with all cell range addresses.

The interface com.sun.star.sheet.XSheetCellRangeContainer is derived from the interface com.sun.star.sheet.XSheetCellRanges to insert and remove cell ranges.

  • The methods addRangeAddress() and addRangeAddresses() insert one or more ranges into the collection. If the boolean parameter bMergeRanges is set to true, the methods try to merge the new range(s) with the ranges of the collection.
  • The methods removeRangeAddress() and removeRangeAddresses() remove existing ranges from the collection. Only ranges that are contained in the collection are removed. The methods do not try to shorten a range.

The interface com.sun.star.sheet.XSheetCellRanges implements methods for access to cells and cell ranges:

The service com.sun.star.sheet.Cells represents a collection of cells.

Cell collections

The following example demonstrates the usage of cell range collections and cell collections.

  /** All samples regarding cell range collections. */
  public void doCellRangesSamples(com.sun.star.sheet.XSpreadsheetDocument xDocument)
          throws RuntimeException, Exception {
 
      // Create a new cell range container
      com.sun.star.lang.XMultiServiceFactory xDocFactory =
          (com.sun.star.lang.XMultiServiceFactory) UnoRuntime.queryInterface(
              com.sun.star.lang.XMultiServiceFactory.class, xDocument);
      com.sun.star.sheet.XSheetCellRangeContainer xRangeCont =
          (com.sun.star.sheet.XSheetCellRangeContainer) UnoRuntime.queryInterface(
              com.sun.star.sheet.XSheetCellRangeContainer.class,
              xDocFactory.createInstance("com.sun.star.sheet.SheetCellRanges"));
 
      // Insert ranges
      insertRange(xRangeCont, 0, 0, 0, 0, 0, false); // A1:A1
      insertRange(xRangeCont, 0, 0, 1, 0, 2, true); // A2:A3
      insertRange(xRangeCont, 0, 1, 0, 1, 2, false); // B1:B3
 
      // Query the list of filled cells
      System.out.print("All filled cells: ");
      com.sun.star.container.XEnumerationAccess xCellsEA = xRangeCont.getCells();
      com.sun.star.container.XEnumeration xEnum = xCellsEA.createEnumeration();
      while (xEnum.hasMoreElements()) {
          Object aCellObj = xEnum.nextElement();
          com.sun.star.sheet.XCellAddressable xAddr = (com.sun.star.sheet.XCellAddressable)
              UnoRuntime.queryInterface(com.sun.star.sheet.XCellAddressable.class, aCellObj);
          com.sun.star.table.CellAddress aAddr = xAddr.getCellAddress();
          System.out.print(getCellAddressString(aAddr.Column, aAddr.Row) + " ");
      }
      System.out.println();
  }
 
  /** Inserts a cell range address into a cell range container and prints a message.
      @param xContainer The com.sun.star.sheet.XSheetCellRangeContainer interface of the container.
      @param nSheet Index of sheet of the range.
      @param nStartCol Index of first column of the range.
      @param nStartRow Index of first row of the range.
      @param nEndCol Index of last column of the range.
      @param nEndRow Index of last row of the range.
      @param bMerge Determines whether the new range should be merged with the existing ranges.
   */
  private void insertRange(
          com.sun.star.sheet.XSheetCellRangeContainer xContainer,
          int nSheet, int nStartCol, int nStartRow, int nEndCol, int nEndRow,
          boolean bMerge) throws RuntimeException, Exception {
      com.sun.star.table.CellRangeAddress aAddress = new com.sun.star.table.CellRangeAddress();
      aAddress.Sheet = (short)nSheet;
      aAddress.StartColumn = nStartCol;
      aAddress.StartRow = nStartRow;
      aAddress.EndColumn = nEndCol;
      aAddress.EndRow = nEndRow;
      xContainer.addRangeAddress(aAddress, bMerge);
      System.out.println(
          "Inserting " + (bMerge ? " with" : "without") + " merge,"
          + " result list: " + xContainer.getRangeAddressesAsString());
  }
Content on this page is licensed under the Public Documentation License (PDL).
Personal tools
In other languages