Filtering Multiple Columns and Filtering with Regular Expressions

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 filters two columns and uses regular expressions. It is an example drawn from Chapter 13 ("Calc as a Database") of the 7.0 Calc Guide.

Description

The macro procedure below demonstrates a filter that filters two columns and uses regular expressions. Note that the filter method is called on a range rather than its sheet in this example.

As an example, the macro below filters a class grade sheet in cells $Sheet1.A1:H11 by Test #1 scores <= 90% and student names that begin with "B". The linked spreadsheet contains such a grade sheet.

Code

 1Sub SimpleRangeFilter()
 2  Dim oSheet          ' Sheet to filter.
 3  Dim oRange          ' Range to be filtered.
 4  Dim oFilterDesc     ' Filter descriptor.
 5  Dim oFields(1) As New com.sun.star.sheet.TableFilterField
 6  
 7  oSheet = ThisComponent.getSheets().getByIndex(0)
 8  oRange = oSheet.getCellRangeByName("A1:H11")
 9  
10  REM If argument is True, creates an 
11  REM empty filter descriptor.
12  oFilterDesc = oRange.createFilterDescriptor(True)
13
14  REM Setup a field to view cells with content that 
15  REM start with the letter B.
16  With oFields(0)   
17    .Field = 0              ' Filter column A (Student names).
18    .IsNumeric = False      ' Use a string, not a number.
19    .StringValue = "b.*"    ' Every name starting with a B.
20    .Operator = com.sun.star.sheet.FilterOperator.EQUAL
21  End With
22  REM Set up a field that requires at least one of the conditions.
23  REM This new condition requires a value less than or 
24  REM equal to 90.
25  With oFields(1)
26    .Connection = com.sun.star.sheet.FilterConnection.OR
27    .Field = 6              ' Filter column G (Test #1 grades).
28    .IsNumeric = True       ' Use a number
29    .NumericValue = 90      ' Scores less than 90
30    .Operator = com.sun.star.sheet.FilterOperator.LESS_EQUAL
31  End With
32
33  oFilterDesc.setFilterFields(oFields())
34  oFilterDesc.ContainsHeader = True
35  oFilterDesc.UseRegularExpressions = True
36  oRange.filter(oFilterDesc)
37End Sub

This Calc spreadsheet contains the above LibreOffice Basic code.