Equally Formatted Cell Ranges

From Apache OpenOffice Wiki
Jump to: navigation, search

It is possible to get collections of all equally formatted cell ranges contained in a source cell range.

Cell Format Ranges

The service com.sun.star.sheet.CellFormatRanges represents a collection of equally formatted cell ranges. The cells inside of a cell range of the collection have the same formatting attributes. All cells of the source range are contained in one of the ranges. If there is a non-rectangular, equal-formatted range, it is split into several rectangular ranges.

Cell Format Ranges

Unique Cell Format Ranges

The service com.sun.star.sheet.UniqueCellFormatRanges represents, similar to Cell Format Ranges above, a collection of equally formatted cell ranges, but this collection contains cell range container objects (service com.sun.star.sheet.SheetCellRanges) that contain the cell ranges. The cells of all ranges inside of a cell range container are equally formatted. The formatting attributes of a range container differ from each other range container. All equally formatted ranges are consolidated into one container.


In the following example, the cells have two different background colors. The formatted ranges of the range A1:G3 are queried in both described ways.


A com.sun.star.sheet.CellFormatRanges object contains the following ranges: A1:C2, D1:G1, D2:F2, G2:G2, and A3:G3.

A com.sun.star.sheet.UniqueCellFormatRanges object contains two com.sun.star.sheet.SheetCellRanges range collections. One collection contains the white ranges, that is, A1:C2, D1:G1, G2:G2, and the other collection, the gray ranges, that is, D2:F2, A3:G3.

The following code is an example of accessing the formatted ranges in Java. The getCellRangeAddressString is a helper method that returns the range address as a string.

  /** All samples regarding formatted cell ranges. */
  public void doFormattedCellRangesSamples(com.sun.star.sheet.XSpreadsheet xSheet)
          throws RuntimeException, Exception {
      // All ranges in one container
      xCellRange = xSheet.getCellRangeByName("A1:G3");
      System.out.println("Service CellFormatRanges:");
      com.sun.star.sheet.XCellFormatRangesSupplier xFormatSupp =
          (com.sun.star.sheet.XCellFormatRangesSupplier) UnoRuntime.queryInterface(
              com.sun.star.sheet.XCellFormatRangesSupplier.class, xCellRange);
      com.sun.star.container.XIndexAccess xRangeIA = xFormatSupp.getCellFormatRanges();
      System.out.println( getCellRangeListString(xRangeIA));
      // Ranges sorted in SheetCellRanges containers
      System.out.println("\nService UniqueCellFormatRanges:");
      com.sun.star.sheet.XUniqueCellFormatRangesSupplier xUniqueFormatSupp =
          (com.sun.star.sheet.XUniqueCellFormatRangesSupplier) UnoRuntime.queryInterface(
              com.sun.star.sheet.XUniqueCellFormatRangesSupplier.class, xCellRange);
      com.sun.star.container.XIndexAccess xRangesIA = xUniqueFormatSupp.getUniqueCellFormatRanges();
      int nCount = xRangesIA.getCount();
      for (int nIndex = 0; nIndex < nCount; ++nIndex) {
          Object aRangesObj = xRangesIA.getByIndex(nIndex);
          xRangeIA = (com.sun.star.container.XIndexAccess) UnoRuntime.queryInterface(
              com.sun.star.container.XIndexAccess.class, aRangesObj);
              "Container " + (nIndex + 1) + ": " + getCellRangeListString(xRangeIA));
  /** Returns a list of addresses of all cell ranges contained in the collection.
      @param xRangesIA The XIndexAccess interface of the collection.
      @return A string containing the cell range address list.
  private String getCellRangeListString( com.sun.star.container.XIndexAccess xRangesIA )
          throws RuntimeException, Exception {
      String aStr = "";
      int nCount = xRangesIA.getCount();
      for (int nIndex = 0; nIndex < nCount; ++nIndex) {
          if (nIndex > 0)
              aStr += " ";
          Object aRangeObj = xRangesIA.getByIndex(nIndex);
          com.sun.star.sheet.XSheetCellRange xCellRange = (com.sun.star.sheet.XSheetCellRange)
              UnoRuntime.queryInterface(com.sun.star.sheet.XSheetCellRange.class, aRangeObj);
          aStr += getCellRangeAddressString(xCellRange, false);
      return aStr;
Content on this page is licensed under the Public Documentation License (PDL).
Personal tools
In other languages