Macro to create multi-value Calc FilterDescriptor

English
, ,
#1

I’m trying to develop a Basic macro that can create an XSheetFilterDescriptor to filter the rows of a Calc sheet to display rows for which the string values in Column A match one of several string values in the same way as an AutoFilter does when only a few individual values are selected. However, no matter what I do, the result seems to ignore all but the first specified value. Here is my code:

    Dim oSheet As Object
    Dim oFilter As Object
    Dim filterValues() As Variant
    Dim filterFields(0) As new com.sun.star.sheet.TableFilterField3
    Dim i As Integer
    Dim op as Long

    ' Get the active sheet
    oSheet = ThisComponent.getCurrentController.getActiveSheet()
    ' Define the filter values to be matched
    filterValues = Array("Apple", "Banana", "Cherry")    
    Dim vals(UBound(filterValues)) as new com.sun.star.sheet.FilterFieldValue
    ' Create a new empty filter descriptor
    oFilter = oSheet.CreateFilterDescriptor(True)
    ' Populate filter conditions
    For i = 0 To UBound(filterValues)
        filterFields(0).Connection = com.sun.star.sheet.FilterConnection.OR
        filterFields(0).Field = 0  ' Column A
        filterFields(0).Operator = com.sun.star.sheet.FilterOperator2.EQUAL
        Dim val As New com.sun.star.sheet.FilterFieldValue
        val.IsNumeric = False
        val.StringValue = filterValues(i)
        val.FilterType = com.sun.star.sheet.FilterFieldType.STRING
        vals(i) = val
    Next i
    filterFields(0).Values = vals    
    ' Apply the filter
    oFilter.setFilterFields3(filterFields)
    oSheet.filter(oFilter)

When run, it displays only rows holding “Apple” in column A, the other rows are hidden, even if they contain one of the other specified values.

Any assistance would be appreciated.

#2

Have you tried this approach?

#3

Thanks for the suggestion @sokol92. I’m both surprised and honored to see some of my own code posted back to me as part of a proposed solution. It turns out that I’m the @wrighch named in the embedded comment that links to a 2023 post to openoffice.org. Somehow, I lost the @wrighch handle when the site transitioned to ask.libreoffice several years ago.

After finding and posting that solution in 2023, I stopped updating my LibreOffice release to prevent changes in the UNO object model from breaking my code. But, after several years of running LibreOffice v7.2, I decided I should upgrade. So I’m trying to make my code run under V25, which, apparently, uses the LibreOffice 24.2 SDK APIs and which has again broken my code.

The example you suggested sets a filter that selects a SINGLE value for the named column using the TableFilterField3.Values property. And it works as advertised. I’m trying to create a TableFilterField3.Values array with multiple items, so the filter will show rows that match any value in the Values array. But in all the testing I’ve done so far, the 24.2 API oFilterDesc.setFilterFields3 method apparently honors only the first value in the array whereas the v7.2 APIs honored all the values.

Still struggling with this.

1 Like
#4

Tomorrow we will redouble our efforts. :slight_smile:

1 Like
#5

I don’t see any symptoms of the disease yet.

  1. Open the attached file
  2. Run the TestAddFilterField macro. The strawberry hides.
    AutoFilter.ods (10.2 KB)
#6

Thanks again for your persistence @sokol92. After letting some of that code that you sent sink in over night, I see that it is more sophisticated than my initial review concluded and it does exactly what I want. The comment that @Villeroy made back in 2023:

Calc macros performing filter actions are rather difficult.

was definitely an understatement.

Every time I fiddle around with filters, I get something working and think that I finally understand them. Then I fiddle around some more, and realize there’s so much more that I don’t understand. So, as long as we’re talking, let me propose a similar challenge and then I’ll ask another question.

First, the challenge. The code you provided allows one to provide an array that will set/clear a filter that selects one or more string values on a specified field/column. Effectively: Show me all the rows that EQUAL any one of the values in Array(“Apple”, “Cherry”) in column 2 – essentially OR’ing together the values. Suppose I want the reverse: Show me all the rows that do NOT EQUAL any one of the values in Array(“Apple”, “Cherry”). I cannot simply specify operator=3 because that case requires “AND NOT EQUAL” rather than the logically-useless “OR NOT EQUAL”. Would it work if I specified FilterConnection.AND? Or must I continue to use FilterConnection.OR and create a separate, single-value TableFilterField3 item for each of “Apple” and “Cherry” using the NOT-EQUAL operator?
Now the additional question. I notice that AddFilterField uses oFilterDesc.setFilterFields when removing filters, but uses oFilterDesc.setFilterFields3 when adding filters. It’s easy to miss that rather subtle but obviously crucial difference. Can you explain?

#7

in spoken words:
anything (EQUAL to »Apple«) OR (EQUAL to »Cherry« )
and the opposite:
anything (NOT_EQUAL to »Apple«) AND (NOT_EQUAL to »Cherry«)

you get it?

#8

Got it. But how to successfully implement the second one of those using FilterDescriptors is not obvious (to me, at least). Hence my question.

#9

No, because I’m not interested in filtering spreadsheets by macro code. When using a spreadsheet, I do not want to program anything that is already built-in. When using a spreadsheet, I don’t have the programmer’s hat on.
I use databases with plain simple SQL as “formula language” whenever I have to filter, sort, group and calculate with record sets.

#10

I think so, but it’s hard to do compactly.


Autofilters have been expanding their capabilities for over 30 years since their introduction (this applies to both Excel and Calc).
At the beginning, there was only a filter by condition (no more than 3 conditions). TableFilterField corresponds to this stage. Then, with version OOo 3.2, the list of possible conditions was expanded (TableFilterField2).
Starting with version LibreOffice 3.5 (TableFilterField3), it became possible to filter by a list of values (items). When filtering by a list of values, we must specify all the values that are included in the filter, this is how it is specified when saving the file. For example, we have numbers from 1 to 999 in our column, and we want to exclude those that are divisible by 5. In the list of values, we must specify 1,2,3,4,6,7,8,9,…,996,997,998,999.


To be fair, it should be noted that in Excel, setting up complex autofilters using macros is not a trivial task (especially when it comes to date filters).

#11

@Villeroy, your point of view is well taken. I totally see where you are coming from. But I have a slightly different POV. There are times and places for Databases and times and places for Spreadsheets. And there’s lots of overlap. Your comments are appreciated. And, I may eventually come around to your POV. But, for now, I’m still trying to go down the Spreadsheet path.

#12

(post deleted by author)

#13

Thanks @sokol92. Not a trivial task, indeed! I started in Excel decades ago and transitioned to LibreOffice about 10 years ago when I retired because I got tired of buying a new Excel license every 2 or 3 years. Yeah. I’m cheap.

So I interpret your explanation as saying that I can include a potentially very long list of desired values (every value except “Apple” and “Cherry”, assuming I know all the possible values I want to exclude) to be OR’d together in a single TableFilterField3 or I can create a smaller number of separate TableFilterField3 items, each of which holds only a single value I want to exclude (“Apple” in the first, “Cherry” in the second, …) each TableFilterField3 item declaring the NOT-EQUAL operator and each connected to the others using the AND connector?

#14

When specifying field filter as list of values (as far as I can guess, I haven’t checked), other operators except EQUAL don’t make sense.


I wrote a macro to hide the technical difficulties.

#15

ask125152.ods (33.7 KB)

Usually, any document can explain everything about itself. The attached document has a database range filtered by the year 2018. The filter descriptor of that database range has only one FilterField3 having an array of 143 Values c.s.s.sheet.FilterFieldValue, one for each day of 2018 occurring in that list.
MRI recorded this macro while inspecting the filter descriptor:

Sub Snippet
  Dim oDatabaseRanges As Variant
  Dim oObj1 As Variant
  Dim oFilterDescriptor As Variant
  Dim oFilterFields3 As Variant
  Dim aTableFilterField3 As New com.sun.star.sheet.TableFilterField3
  Dim oValues As Variant

  oDatabaseRanges = ThisComponent.DatabaseRanges
  oObj1 = oDatabaseRanges.getByIndex(0)
  oFilterDescriptor = oObj1.getFilterDescriptor()
  
  oFilterFields3 = oFilterDescriptor.getFilterFields3()
  aTableFilterField3 = oFilterFields3(0)
  oValues = aTableFilterField3.Values
  
End Sub
#16

To the extent that the values in a list of values are OR’d with the field itself, I would agree, though CONTAINS could work too if it were allowed. But it appears that EQUALS is the only operator that appears that actually works in the current object model implementation.

#17

The new FilterField3 values are adjusted to the auto filter drop-downs where you can select values from a list of unique values. These lists of filter criteria imply OR.

#18

Thanks @villeroy. Very helpful. The code provided by @sokol92 allows me to fairly easily show rows containing only those values in a list (“Apple”, “Cherry”). It effectively does the same thing as manually manipulating the AutoFilter pull-down list to clear the list, then check only the few desired values. Your spreadsheet makes it clear that the list of desired values I provide can be quite long.

That pretty thoroughly answers my OP, for which I’ll mark a ‘Solution’ eventually.

But before closing out this ‘ask’, I want to try to answer the related/reverse question: What if I only know the values that I DO NOT want to see. What if I want everything EXCEPT 2018, or everything EXCEPT “Apple” and “Cherry”? Must I exhaustively enumerate every value that I DO want to see? Or is there a way to mimic a manual AutoFilter entry where I scroll down and UNcheck 2018, or UNcheck “Apple” and “Cherry” without requiring me to even care what the other available values are? In other words:

Can anyone describe a way, without knowing the full range of values available within a specific field, of selecting every row EXCEPT those in a list of values?

Or do I have to use a Pivot Table (or some other mechanism?) to produce a list of all the available values, remove the ones I’m not interested in, and build a filter using that list?

#19

This is trivial to do with a database. SELECT * FROM "Table" WHERE "Column" NOT IN (array of criteria).

#20

The standard mechanism in Calc (or Excel) for custom filter criteria is to add column(s) to the range with formulas that define the filter conditions.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.