Database Ranges

From Apache OpenOffice Wiki
Jump to: navigation, search



A database range is a name for a cell range that also stores filtering, sorting, subtotal and import settings, as well as some options.

The com.sun.star.sheet.SpreadsheetDocument service has a property DatabaseRanges that is used to get the document's collection of database ranges. A new database range is added using the com.sun.star.sheet.XDatabaseRanges interface's addNewByName() method that requires the name of the new database range, and a com.sun.star.table.CellRangeAddress with the address of the cell range as arguments. The removeByName() method removes a database range.

The com.sun.star.container.XNameAccess interface is used to get a single com.sun.star.sheet.DatabaseRange object. Its com.sun.star.sheet.XCellRangeReferrer interface is used to access the cell range that it is pointed to. The com.sun.star.sheet.XDatabaseRange interface retrieves or changes the com.sun.star.table.CellRangeAddress that is named, and gets the stored descriptors.

All descriptors of a database range are updated when a database operation is carried out on the cell range that the database range points to. The stored filter descriptor and subtotal descriptor can also be modified by changing the objects that are returned by the getFilterDescriptor() and getSubTotalDescriptor() methods. Calling the refresh() method carries out the stored operations again.

Whenever a database operation is carried out on a cell range where a database range is not defined, a temporary database range is used to hold the settings. This temporary database range has its IsUserDefined property set to false and is valid until another database operation is performed on a different cell range. In this case, the temporary database range is modified to refer to the new cell range.

The following example uses the IsUserDefined property to find the temporary database range, and applies a background color to the corresponding cell range. If run directly after the database import example above, this marks the imported data.

  // use the temporary database range to find the imported data's size
  com.sun.star.beans.XPropertySet xDocProp = (com.sun.star.beans.XPropertySet)
      UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, getDocument());
  Object aRangesObj = xDocProp.getPropertyValue("DatabaseRanges");
  com.sun.star.container.XNameAccess xRanges = (com.sun.star.container.XNameAccess)
      UnoRuntime.queryInterface(com.sun.star.container.XNameAccess.class, aRangesObj);
  String[] aNames = xRanges.getElementNames();
  for (int i=0; i<aNames.length; i++) {
      Object aRangeObj = xRanges.getByName(aNames[i] );
      com.sun.star.beans.XPropertySet xRangeProp = (com.sun.star.beans.XPropertySet)
          UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, aRangeObj);
      boolean bUser = ((Boolean) xRangeProp.getPropertyValue("IsUserDefined")).booleanValue();
      if (!bUser) {
          // this is the temporary database range - get the cell range and format it
          com.sun.star.sheet.XCellRangeReferrer xRef = (com.sun.star.sheet.XCellRangeReferrer)
              UnoRuntime.queryInterface(com.sun.star.sheet.XCellRangeReferrer.class, aRangeObj);
          com.sun.star.table.XCellRange xResultRange = xRef.getReferredCells();
          com.sun.star.beans.XPropertySet xResultProp = (com.sun.star.beans.XPropertySet)
              UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, xResultRange);
          xResultProp.setPropertyValue("IsCellBackgroundTransparent", new Boolean(false));
          xResultProp.setPropertyValue("CellBackColor", new Integer(0xFFFFCC));
      }
  }
Content on this page is licensed under the Public Documentation License (PDL).
Personal tools
In other languages