Filters

From Apache OpenOffice Wiki
Jump to: navigation, search



Use filters to limit the visible rows in a spreadsheet. Generic filters, common to all sorts of data manipulations, are automatically provided by the auto filter capability. You can also define your own filters.





Documentation caution.png After applying a filter, some rows are visible and some rows are not. If you select multiple rows in one operation, you will also select the invisible rows contained between the selected visible rows. Operations, such as delete, act on all of the selected rows. To avoid this problem, you must individually select each of the filtered rows using the control key.

Auto filters

Use auto filters to quickly create easily accessible filters found to be commonly used in many different types of applications. After creating an auto filter for a specific column, a combo box is added to the column. The combo box provides quick access to each of the auto filter types.

  • The All auto filter causes all rows to be visible.
  • The Standard auto filter opens the Standard Filter dialog and is the same as the standard filter.
  • The Top 10 auto filter displays the ten rows with the largest value. If the value 70 is in the top ten values, then all rows containing the value 70 in the filtered column are displayed. In other words, more than ten rows may be displayed.
  • An auto filter entry is created for each unique entry in the column.

To create an auto filter, first select the columns to filter. For example, using the data in Table 1, select data in columns B and C. If you do not select the title rows, Calc asks if the title row or the current row should be used. Although you can place the auto filter in any row, only the rows below the auto filter are filtered. Use Data > Filter > AutoFilter to insert the auto filter combo box in the appropriate cell. Finally, use the drop-down arrow to choose an appropriate auto filter.

Use an auto filter with column C.

Remove an auto filter by repeating the steps to create the auto filter—in other words, the menu option acts as a toggle to turn the auto filter on and off. When an auto filter is removed, the combo box is removed from the cell. The macro in Listing 4 demonstrates creating an auto filter for a range.

Standard filters

Use Data > Filter > Standard Filter to open Standard Filter dialog and limit the view based on 1 to 3 filter conditions. Use Data > Filter > Remove Filter to turn off the filter.

Use the standard filter

The macro in Listing 5 creates a simple filter for the first sheet.

Listing 5. Create a simple sheet filter.

Sub SimpleSheetFilter()
  Dim oSheet       ' Sheet that will contain the filter.
  Dim oFilterDesc  ' Filter descriptor.
  Dim oFields(0) As New com.sun.star.sheet.TableFilterField
 
  oSheet = ThisComponent.getSheets().getByIndex(0)
 
  REM If argument is True, creates an empty filter
  REM descriptor. If argument is False, create a 
  REM descriptor with the previous settings.
  oFilterDesc = oSheet.createFilterDescriptor(True)
 
  With oFields(0)
    REM I could use the Connection property to indicate 
    REM how to connect to the previous field. This is 
    REM the first field so this is not required.
    '.Connection = com.sun.star.sheet.FilterConnection.AND
    '.Connection = com.sun.star.sheet.FilterConnection.OR
 
    REM The Field property is the zero based column
    REM number to filter. If you have the cell, you
    REM can use .Field = oCell.CellAddress.Column.
    .Field = 5
 
    REM Compare using a numeric or a string?
    .IsNumeric = True
 
    REM The NumericValue property is used 
    REM because .IsNumeric = True from above.
    .NumericValue = 80
 
    REM If IsNumeric was False, then the 
    REM StringValue property would be used.
    REM .StringValue = "what ever"
 
    REM Valid operators include EMPTY, NOT_EMPTY, EQUAL,
    REM NOT_EQUAL, GREATER, GREATER_EQUAL, LESS,
    REM LESS_EQUAL, TOP_VALUES, TOP_PERCENT,
    REM BOTTOM_VALUES, and BOTTOM_PERCENT
    .Operator = com.sun.star.sheet.FilterOperator.GREATER_EQUAL
  End With
 
  REM The filter descriptor supports the following
  REM properties: IsCaseSensitive, SkipDuplicates,
  REM UseRegularExpressions, 
  REM SaveOutputPosition, Orientation, ContainsHeader, 
  REM CopyOutputData, OutputPosition, and MaxFieldCount.
  oFilterDesc.setFilterFields(oFields())
  oFilterDesc.ContainsHeader = True
  oSheet.filter(oFilterDesc)
End Sub

When a filter is applied to a sheet, it replaces any existing filter for the sheet. Setting an empty filter in a sheet will therefore remove all filters for that sheet (see Listing 6).

Listing 6. Remove the current sheet filter.

Sub RemoveSheetFilter()
  Dim oSheet          ' Sheet to filter.
  Dim oFilterDesc     ' Filter descriptor.
 
  oSheet = ThisComponent.getSheets().getByIndex(0)
  oFilterDesc = oSheet.createFilterDescriptor(True)
  oSheet.filter(oFilterDesc)
End Sub

Listing 7 demonstrates a more advanced filter that filters two columns and uses regular expressions. I noticed some unexpected behaviour while working with Listing 7. Although you can create a filter descriptor using any sheet cell range, the filter applies to the entire sheet.

Listing 7. A simple range filter using two columns.

Sub SimpleRangeFilter_2()
  Dim oSheet          ' Sheet to filter.
  Dim oRange          ' Range to be filtered.
  Dim oFilterDesc     ' Filter descriptor.
  Dim oFields(1) As New com.sun.star.sheet.TableFilterField
 
  oSheet = ThisComponent.getSheets().getByIndex(0)
  oRange = oSheet.getCellRangeByName("E12:G19")
 
  REM If argument is True, creates an 
  REM empty filter descriptor.
  oFilterDesc = oRange.createFilterDescriptor(True)
 
  REM Setup a field to view cells with content that 
  REM start with the letter b.
  With oFields(0)   
    .Field = 0              ' Filter column A.
    .IsNumeric = False      ' Use a string, not a number.
    .StringValue = "b.*"    ' Everything starting with b.
    .Operator = com.sun.star.sheet.FilterOperator.EQUAL
  End With
  REM Setup a field that requires both conditions and
  REM this new condition requires a value greater or 
  REM equal to 70.
  With oFields(1)
    .Connection = com.sun.star.sheet.FilterConnection.AND
    .Field = 5              ' Filter column F.
    .IsNumeric = True       ' Use a number
    .NumericValue = 70      ' Values greater than 70
    .Operator = com.sun.star.sheet.FilterOperator.GREATER_EQUAL
  End With
 
  oFilterDesc.setFilterFields(oFields())
  oFilterDesc.ContainsHeader = False
  oFilterDesc.UseRegularExpressions = True
  oRange.filter(oFilterDesc)
End Sub


Content on this page is licensed under the Creative Common Attribution 3.0 license (CC-BY).
Personal tools