Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Mail Systems
Eclipse Documentation

How To Guides
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Problem Solutions




OpenOffice Calc 3.x Guide
Previous Page Home Next Page

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
  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 =
    '.Connection =
    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,
    .Operator =
  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.ContainsHeader = True
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)
End Sub

Listing 7 demonstrates a more advanced filter that filters two columns and uses regular expressions. I noticed some unexpected behavior 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 sheet filter using two columns.

Sub SimpleSheetFilter_2()
  Dim oSheet          ' Sheet to filter.
  Dim oRange          ' Range to be filtered.
  Dim oFilterDesc     ' Filter descriptor.
  Dim oFields(1) As New
  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 =
  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 =
    .Field = 5              ' Filter column F.
    .IsNumeric = True       ' Use a number
    .NumericValue = 70      ' Values greater than 70
    .Operator =
  End With
  oFilterDesc.ContainsHeader = False
  oFilterDesc.UseRegularExpressions = True
End Sub

OpenOffice Calc 3.x Guide
Previous Page Home Next Page

  Published under the terms of the Creative Commons License Design by Interspire