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
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
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 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 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 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
  oSheet.filter(oFilterDesc)
End Sub



OpenOffice Calc 3.x Guide
Previous Page Home Next Page

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