Ask Your Question
0

Macro to create multi-value Calc AutoFilter

asked 2020-08-27 14:35:29 +0100

wrighch gravatar image

updated 2020-08-28 14:57:12 +0100

I'm trying to develop a macro that allows me to define and save AutoFilter settings so that I can quickly impose complex filters on a sheet. The sheet holds a database with 17 columns, Headers in the first row, and AutoFilter in place. Here is some prototype code that imposes a filter showing rows where column A EQUALS "Value1" and column K CONTAINS "Value2":

Sub CreateFilter

Dim theRange As Object          ' A Range Object
Dim theFilterDesc As Object     ' A FilterDescriptor Object
Dim theFilterFields() As Object     ' An Array of FilterField objects
Dim aFilterField As Object      ' A single item for the FilterFields Array

'   Get the Database Range we're going to filter
theRange = ThisComponent.getPropertyValue("DatabaseRanges").getByName("ImportDatabase")

ReDim theFilterFields(1)        ' Redimension the FilterFields array

aFilterField = New com.sun.star.sheet.TableFilterField  ' Create a FilterField
aFilterField.Connection = 0     ' 0 = AND, 1 = OR (irrelevant for item 0)
aFilterField.IsNumeric = FALSE      ' Define an EQUAL filter
aFilterField.StringValue = "Value1"
aFilterField.Field = 0
aFilterField.Operator = com.sun.star.sheet.FilterOperator.EQUAL
theFilterFields(0) = aFilterField   ' Add the first FilterField to the FilterFields array

aFilterField.Connection = 0     ' Define a CONTAINS filter
aFilterField.IsNumeric = FALSE  
aFilterField.StringValue = "Value2"
aFilterField.Field = 10
aFilterField.Operator = com.sun.star.sheet.FilterOperator2.CONTAINS
theFilterFields(1) = aFilterField   ' Add the second FilterField to FilterFields array

theFilterDesc = theRange.getFilterDescriptor(TRUE)  ' Get an EMPTY FilterDescriptor
With theFilterDesc          ' Set
    .ContainsHeader = TRUE      '  the
    .CopyOutputData = FALSE     '   Properties
    .IsCaseSensitive = FALSE    '    of the
    .UseRegularExpressions = FALSE  '     FilterDescriptor
End With                '      itself
theFilterDesc.setFilterFields(theFilterFields)  ' Place the FilterFields array into the descriptor
theRange.refresh()          ' Finally, invoke the filter

End Sub

That code works fine when trying to filter Column A to a SINGLE value. The issue I'm struggling with is trying to create a filter where, for example, Column A can equal any of MULTIPLE values.

If I use the Calc user interface to manually create and apply such a filter, I simply click the Column-A AutoFilter pull-down, and check the boxes to select the multiple values of interest.

Examining the FilterDescriptor that Calc creates for that filter, I see that the multiple values are declared within theRange.FilterDescriptor.FilterFields3(0).Values(n), where each Values(n) declares the properties for a single value selected with a checked box.

I simply cannot figure out how to properly declare or populate a TableFilterField for such a filter or how to add a TableFilterField3 with multiple Values to theFilterFields array.

Additional Info added following my initial post:

I can create a TableFilterField3 and set its properties using:

Dim aFilterField3 As Object 
aFilterField3 = New com.sun.star.sheet.TableFilterField3
aFilterField3.Connection = 0                                        
aFilterField3.Field = 10
aFilterField3.Operator = com.sun.star.sheet.FilterOperator2.EQUAL

And I could conceivably add it using theFilterDesc.setFilterFields3(theFilterFields). But aFilterField3 gets created with an empty array for its aFilterField3.Values property and I cannot figure out how to re-dimension that property so I can populate it with values. And, even if I could dimension the Values property array to allow me to assign a separate Values entry for each of the multiple ... (plus)

edit retag flag offensive close merge delete

Comments

hello @wrighch, hello @mauricio,
as i understand mauricios sample was not intended as a solution, but as a sample for own solutions,
as i understand you have got it quite far,
afaik auto-, standard- and advanced filter are different interfaces for similar functionality, using the same descriptor structure!, sometimes conflicting reg. different capabilities?
reg. 'issues with AutoFilter' be aware! that calc has problems calculating the .field values for filter conditions after a save-load cycle if the database range doesn't start on one of the cells A1-B2-C3 ... there is some mess around depending on 'table orientation' which is changed on first re-load?
see tdf#133529 for that,
depending on your use case it might be easier defining filter conditions in 'criteria-tables' usable by 'advanced filter' (outside the rows to be filtered) and apply such filter with reference to (different) such tables per macro, just an idea, not tested,
reg.

newbie-02 gravatar imagenewbie-02 ( 2020-08-28 06:36:25 +0100 )edit

Thanks @newbie-02 for pointing out issue tdf#133529. I saw that as I was working toward my answer. Since I intend my code to be accessible via button click, then clearing and re-imposing my filter after a re-load should not be an issue for me. And, thank-goodness I'm not messing with table orientation. My eventual implementation will mimic the 'criteria-tables' idea you propose as I already have a design in mind. In fact, this problem came to my attention as I finally got around to translating an old Excel-VBA macro into LibreOffice-Basic.

wrighch gravatar imagewrighch ( 2020-08-28 15:07:29 +0100 )edit

@wrighch:
'Since I intend my code to be accessible via button click' - there is an issue tdf#70883, pls. read,
'clearing and re-imposing my filter after a re-load should not be an issue for me' - i'd expect that to work,
'not messing with table orientation' - that - under certain circumstances - completely independently messes up your work :-(
'My eventual implementation will mimic the 'criteria-tables' idea' - would like feedback whether it worked well,
'translating an old Excel-VBA macro into LibreOffice-Basic.' - alas to say ... :-( VBA is much easier and more understandable, i can't decide if LO basic is more powerful ...

newbie-02 gravatar imagenewbie-02 ( 2020-08-28 22:16:47 +0100 )edit

Thanks again @newbie-02. But tdf#70883 doesn't apply to what I'm doing. It's Title ("uno: copy" via the dispatcher does not work when a button has focus) makes it clear: The issue is with "uno:copy" Typically, I avoid using UNO dispatcher calls altogether if possible, mostly because their parameters are obscure and hard to find. Secondly, I'm only trying to capture the Button-click event, not Focus events. So I configure my Button(s) to NOT take focus when clicked. They just send the click event to my macro. Finally, I fully agree: VBA is much easier -- if only because there appear to be far more examples on the web to learn from. But LO is quite powerful. And, if you can figure out the layers of it Object Model (every bit as complex as some of the Java Object Models) you can do some remarkable things.

wrighch gravatar imagewrighch ( 2020-08-29 15:55:13 +0100 )edit

2 Answers

Sort by » oldest newest most voted
1

answered 2020-08-28 14:56:23 +0100

wrighch gravatar image

updated 2020-08-28 14:58:08 +0100

Thanks to @mauricio below for his example. As he indicates, his example works for a SINGLE field with two conditions. But those conditions must be com.sun.star.sheet.FilterOperator conditions. To use conditions like "CONTAINS" or "BEGINS_WITH" I believe you must use com.sun.star.sheet.FilterOperator2 constants, which I do not think can be put in place as FilterFields objects. Rather they must be declared as FilterFields3 objects.

Also, I failed to specify in my original post that I'm trying to implement a filter with MULTIPLE fields and MULTIPLE conditions on EACH field. So, @mauricio's example must be adapted for my purposes.

I have finally been able to get the following to work:

Sub CreateFilter

Dim theRange As Object
Dim theFilterDesc As Object
Dim theFilterFields3() As Object
Dim aFilterField3 As Object 
Dim ff3values() As Object
Dim aValue As Object

' Get the Database Range we're going to filter
theRange = ThisComponent.getPropertyValue("DatabaseRanges").getByName("ImportDatabase")

theFilterDesc = theRange.getFilterDescriptor(TRUE) ' Get an EMPTY FilterDescriptor
With theFilterDesc
    .ContainsHeader = TRUE
    .CopyOutputData = FALSE
    .IsCaseSensitive = FALSE
    .UseRegularExpressions = FALSE
End With

ReDim theFilterFields3(1)       ' Allow two fields  
ReDim ff3values(1)          ' Allow two values for each field

aFilterField3 = New com.sun.star.sheet.TableFilterField3
aValue = New com.sun.star.sheet.FilterFieldValue

aFilterField3.connection = com.sun.star.sheet.FilterConnection.AND
aFilterField3.field = 10
aFilterField3.operator = com.sun.star.sheet.FilterOperator2.CONTAINS
aValue.IsNumeric = FALSE
aValue.StringValue = "Value1"
ff3values(0) = aValue
aValue.StringValue = "Value2"
ff3values(1) = aValue
aFilterField3.Values = ff3values
theFilterFields3(0) = aFilterField3

aFilterField3.connection = com.sun.star.sheet.FilterConnection.AND
aFilterField3.field = 0
aFilterField3.operator = com.sun.star.sheet.FilterOperator2.EQUAL
aValue.IsNumeric = FALSE
aValue.StringValue = "Value3"
ff3values(0) = aValue
aValue.StringValue = "Value4"
ff3values(1) = aValue
aFilterField3.Values = ff3values
theFilterFields3(1) = aFilterField3
theFilterDesc.FilterFields3 = theFilterFields3
' Or use the method: theFilterDesc.setFilterFields3(theFilterFields3)
theRange.refresh()

End Sub

The above works as desired. Obviously, it's simplistic, as I'll need to ReDim the theFilterFields3 and ff3values Arrays dynamically depending on the number of fields and values-per-field. But the above code gets the idea across.

As @newbie-02 points out, Auto-, Standard- and Advanced-filters are three different interfaces for similar functionality. Each has its own advantages and disadvantages. My experience is that if I put a a complex filter in place as coded above while AutoFilter = TRUE, the AutoFilter Pull-Downs are not necessarily colored blue as they would be if entered manually. And the AutoFilter selections do not match the criteria imposed by the above code. But the visible rows do appear to properly represent the filter defined by the code. Similarly, after executing the above code, when I use Calc's GUI to examine the Standard filter, the displayed conditions and criteria do not match the filters set by the code. Regardless, using:

Dim noFilterFields() As com.sun.star.sheet.TableFilterField
theRange.getFilterDescriptor.setFilterFields(noFilterFields())
theRange.refresh()

Will clear the filter and you can manually use AutoFilter pull-downs or Standard Filter to impose other filters as desired, and executing the above code will over-ride any AutoFilter or ... (mais)

edit flag offensive delete link more

Comments

I don't have enough points to accept my own answer. Perhaps someone else with appropriate karma can do that for me.

wrighch gravatar imagewrighch ( 2020-08-28 15:08:59 +0100 )edit

The true solution was given by @mauricio, so please mark their answer clicking ✔ on it..

m.a.riosv gravatar imagem.a.riosv ( 2020-08-28 19:54:59 +0100 )edit

@mauricio provided an answer which works. But it did not answer my question. Per my Original Post: "(my original) code works fine when trying to filter Column A to a SINGLE value. The issue I'm struggling with is trying to create a filter where ... Column A can equal any of MULTIPLE values." The answer @mauricio provided is a good, fully-functional, and much simpler version of my original code. But to filter a given field to any of MULTIPLE values (that is: field1 = "value1" OR field1 = "Value2") requires a TableFilterField3 object which includes an array of multiple FilterFieldValues. My original question was asking: "How do I create such an array within the TableFilterField3.Values property. My answer above creates and populates such an array. So, while @mauricio provided good information, I consider my answer to be the correctanswer. But ask.LibreOffice.org tells me that ">20 points required ...(more)

wrighch gravatar imagewrighch ( 2020-08-28 21:07:46 +0100 )edit
0

answered 2020-08-27 19:05:18 +0100

Simple example, field with two conditions.

Sub main()
Dim filters(1) As New com.sun.star.sheet.TableFilterField

    doc = ThisComponent
    dbr = doc.DataBaseRanges()

    name = "data"
    if not dbr.hasByName(name) Then
        Exit Sub
    End if

    range = dbr.getByName(name)
    FD = range.getFilterDescriptor() 

    filters(0).Field = 0
    filters(0).Operator = com.sun.star.sheet.FilterOperator.EQUAL
    filters(0).IsNumeric = True
    filters(0).NumericValue = 1
    'Second condition
    filters(1).Field = 0
    'Relation with last filter
    filters(1).Connection = com.sun.star.sheet.FilterConnection.OR
    filters(1).Operator = com.sun.star.sheet.FilterOperator.EQUAL
    filters(1).IsNumeric = True
    filters(1).NumericValue = 3

    FD.FilterFields = filters
    range.refresh()

End Sub
edit flag offensive delete link more

Comments

See the SECOND revision to my original post above

wrighch gravatar imagewrighch ( 2020-08-27 21:16:23 +0100 )edit

OK, I answered my own question, and revised the post to reflect it.

wrighch gravatar imagewrighch ( 2020-08-28 14:58:55 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-08-27 14:35:29 +0100

Seen: 71 times

Last updated: Aug 28