Filtering
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.
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 valuesAND
orOR
, 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 asEQUAL
orGREATER
-
IsNumeric
selects a numeric or textual condition. -
NumericValue
contains the value that is used in the condition ifIsNumeric
is true. -
StringValue
contains the text that is used in the condition ifIsNumeric
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). |