Filtering

From Apache OpenOffice Wiki
Jump to: navigation, search



A com.sun.star.sheet.SheetFilterDescriptor object is created using the createFilterDescriptor() method from the range's com.sun.star.sheet.XSheetFilterable interface to filter data in a cell range. After applying the settings to the descriptor, it is passed to the filter() method.

If true is passed as a bEmpty parameter to createFilterDescriptor(), the returned descriptor contains default values for all settings. If false is passed and the cell range is a database range that has a stored filter operation, the settings for that filter are used.

SheetFilterDescriptor

The com.sun.star.sheet.XSheetFilterDescriptor interface is used to set the filter criteria as a sequence of com.sun.star.sheet.TableFilterField elements. The com.sun.star.sheet.TableFilterField struct describes a single condition and contains the following members:

  • Connection has the values AND or OR, and specifies how the condition is connected to the previous condition in the sequence. For the first entry, Connection is ignored.
  • Field is the number of the field that the condition is applied to.
  • Operator is the type of the condition, such as EQUAL or GREATER
  • IsNumeric selects a numeric or textual condition.
  • NumericValue contains the value that is used in the condition if IsNumeric is true.
  • StringValue contains the text that is used in the condition if IsNumeric is false.

Additionally, the filter descriptor contains a com.sun.star.beans.XPropertySet interface for settings that affect the whole filter operation.

If the property CopyOutputData is true, the data that matches the filter criteria is copied to a cell range in the document that starts at the position specified by the OutputPosition property. Otherwise, the rows that do not match the filter criteria are filtered (hidden) in the original cell range.

The following example filters the range that is in the variable xRange for values greater or equal to 1998 in the second column:

  // --- filter for second column >= 1998 ---
  com.sun.star.sheet.XSheetFilterable xFilter = (com.sun.star.sheet.XSheetFilterable)
      UnoRuntime.queryInterface(com.sun.star.sheet.XSheetFilterable.class, xRange);
  com.sun.star.sheet.XSheetFilterDescriptor xFilterDesc =
      xFilter.createFilterDescriptor(true);
  com.sun.star.sheet.TableFilterField[] aFilterFields =
      new com.sun.star.sheet.TableFilterField[1];
  aFilterFields[0] = new com.sun.star.sheet.TableFilterField();
  aFilterFields[0].Field = 1;
  aFilterFields[0].IsNumeric = true;
  aFilterFields[0].Operator = com.sun.star.sheet.FilterOperator.GREATER_EQUAL;
  aFilterFields[0].NumericValue = 1998;
  xFilterDesc.setFilterFields(aFilterFields);
  com.sun.star.beans.XPropertySet xFilterProp = (com.sun.star.beans.XPropertySet)
      UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, xFilterDesc);
  xFilterProp.setPropertyValue("ContainsHeader", new Boolean(true));
  xFilter.filter(xFilterDesc);

The com.sun.star.sheet.XSheetFilterableEx interface is used to create a filter descriptor from criteria in a cell range in the same manner as the "Advanced Filter" dialog. The com.sun.star.sheet.XSheetFilterableEx interface must be queried from the range that contains the conditions, and the com.sun.star.sheet.XSheetFilterable interface of the range to be filtered must be passed to the createFilterDescriptorByObject() call.

The following example performs the same filter operation as the example before, but reads the filter criteria from a cell range:

  // --- do the same filter as above, using criteria from a cell range ---
  com.sun.star.table.XCellRange xCritRange = xSheet.getCellRangeByName("B27:B28");
  com.sun.star.sheet.XCellRangeData xCritData = (com.sun.star.sheet.XCellRangeData)
      UnoRuntime.queryInterface(com.sun.star.sheet.XCellRangeData.class, xCritRange);
  Object[][] aCritValues = {{"Year"}, {">= 1998"}};
  xCritData.setDataArray(aCritValues);
  com.sun.star.sheet.XSheetFilterableEx xCriteria = (com.sun.star.sheet.XSheetFilterableEx)
      UnoRuntime.queryInterface(com.sun.star.sheet.XSheetFilterableEx.class, xCritRange);
  xFilterDesc = xCriteria.createFilterDescriptorByObject(xFilter);
  if (xFilterDesc != null)
      xFilter.filter(xFilterDesc);
Content on this page is licensed under the Public Documentation License (PDL).
Personal tools
In other languages