Create Standard Filter

From The Document Foundation Wiki
< Macros‎ | Calc
Jump to navigation Jump to search
Other languages:
English • ‎italiano

Summary

This page provides a LibreOffice Basic macro procedure that creates a simple standard filter. It is an example drawn from Chapter 13 ("Calc as a Database") of the 7.0 Calc Guide.

Description

In a macro, filtering a data range is performed by calling the filter() method either on the range or the sheet in which the range is contained. Like a sort descriptor, a filter descriptor contains the filter settings for the current sheet, such as whether the first row or column in the sheet contains headers which should not be filtered. A filter descriptor can be generated by calling the createFilterDescriptor() method on a sheet or on a cell range:

createFilterDescriptor(Empty)
Creates a filter descriptor. If the boolean flag Empty is set to TRUE, then an empty filter descriptor is created. If Empty is FALSE, then the descriptor is filled with the previous settings of the current object (such as a database range).

As with sort descriptors, filter descriptors can be created using this method, then modified and passed as an argument to the filter method. The macro below demonstrates this process by creating a simple standard filter for the first sheet in a document.

Im-jabber.svg

Note:
When the filter method is called on a sheet, every empty row in the sheet is hidden. When filter is called on a range, only empty rows in the range itself are hidden.

Code

 1Sub SimpleSheetFilter()
 2  Dim oSheet       ' Sheet that will contain the filter.
 3  Dim oFilterDesc  ' Filter descriptor.
 4  Dim oFields(0) As New com.sun.star.sheet.TableFilterField
 5  
 6  oSheet = ThisComponent.getSheets().getByIndex(0)
 7  
 8  REM If argument is True, creates an empty filter
 9  REM descriptor. If argument is False, create a 
10  REM descriptor with the previous settings.
11  oFilterDesc = oSheet.createFilterDescriptor(True)
12  
13  With oFields(0)
14    REM You could use the Connection property to indicate
15    REM how to connect to the previous field. This is
16    REM the first field, so this is not required.
17    '.Connection = com.sun.star.sheet.FilterConnection.AND
18    '.Connection = com.sun.star.sheet.FilterConnection.OR
19
20    REM The Field property is the zero based column
21    REM number to filter. If you have the cell, you
22    REM can use .Field = oCell.CellAddress.Column.
23    .Field = 5	' The Quiz #2 grades field
24
25    REM Compare using a numeric or a string?
26    .IsNumeric = True
27
28    REM The NumericValue property is used 
29    REM because .IsNumeric = True from above.
30    .NumericValue = 80
31
32    REM If IsNumeric was False, then the 
33    REM StringValue property would be used.
34    REM .StringValue = " what ever"
35
36    REM Valid operators include EMPTY, NOT_EMPTY, EQUAL,
37    REM NOT_EQUAL, GREATER, GREATER_EQUAL, LESS,
38    REM LESS_EQUAL, TOP_VALUES, TOP_PERCENT,
39    REM BOTTOM_VALUES, and BOTTOM_PERCENT
40    .Operator = com.sun.star.sheet.FilterOperator.GREATER_EQUAL
41  End With
42  
43  REM The filter descriptor supports the following
44  REM properties: IsCaseSensitive, SkipDuplicates,
45  REM UseRegularExpressions, 
46  REM SaveOutputPosition, Orientation, ContainsHeader, 
47  REM CopyOutputData, OutputPosition, and MaxFieldCount.
48  oFilterDesc.setFilterFields(oFields())
49  oFilterDesc.ContainsHeader = True
50  oSheet.filter(oFilterDesc)
51End Sub

This Calc spreadsheet contains the above LibreOffice Basic code.