Ask Your Question
0

Macro to create auto filter selection, LibreOffice Calc

asked 2016-10-13 10:03:20 +0200

Kes gravatar image

updated 2016-10-13 10:39:14 +0200

Hi,

I have a regular filter that I use, and re-typing it everytime is a waste, when I could call the whole thing up automatically with a macro.

I have a filtered dataset. The top row has the autofilter on it.

I wish to do the following with it, in a macro, see graphic. Ie click on the auto filter arrow at the top of the spreadsheet on the given column, in this case column J and then filter in this way

column J

Operator --> Field Name --> Condition --> Value

OR J contains Mike

OR J contains Julie

OR J contains Samantha

OR J contains John

How to do this in Libreoffice Calc with a macro?

edit retag flag offensive close merge delete

4 Answers

Sort by » oldest newest most voted
0

answered 2016-10-14 11:09:04 +0200

Kes gravatar image

Hi,

Thank you; that's really good of you.

It works for EQUAL but not for CONTAINS which is specifically what I need. Your macro is going to save someone a lot of time, for specific and exact field matches, but not where someone is looking for a partial match within the field as I am, in which case CONTAINS is specifically needed.

I'm sure you already knew that.

FilterOperator (see this link) does not allow use of CONTAINS, but FilterOperator2 (see this link) does.

The thing is my version of LibreOffice Calc Version: 5.1.4.2, Build ID: 1:5.1.4-0ubuntu1, CPU Threads: 2; OS Version: Linux 4.4; UI Render: default; Locale: en-GB (en_GB.UTF-8) does not interpret the line

oFilterField(0).Operator = com.sun.star.sheet.FilterOperator2.CONTAINS

It simply works as EQUAL.

The gist of what you have given is more than enough to get me to where I needed to be to get this going, and understand that, as far as I know, the LibreOffice Calc macro interpreter is not yet fully functional.

Thank you.

edit flag offensive delete link more
0

answered 2016-10-13 14:14:17 +0200

pierre-yves samyn gravatar image

updated 2016-10-14 13:00:05 +0200

Hi

Something like:

sub PysFilter

dim oSheet as object, oRange as object, oFilter as object
dim oFilterField(3) As New com.sun.star.sheet.TableFilterField   

oSheet = thisComponent.Sheets.getByName("Sheet1") 
oRange = oSheet.getCellRangeByName("I1:L11")

oFilter= oRange.createFilterDescriptor(true)
'oRange.filter(oFilter)' 

with oFilter                                    
    .ContainsHeader = true                      
    .CopyOutputData = false                     
    .IsCaseSensitive = false                
    .UseRegularExpressions = false
end with

oFilterField(0).Field = 1
oFilterField(0).Operator = com.sun.star.sheet.FilterOperator.EQUAL
oFilterField(0).StringValue = "Mike"
oFilterField(1).Connection = com.sun.star.sheet.FilterConnection.OR
oFilterField(1).Field = 1
oFilterField(1).Operator = com.sun.star.sheet.FilterOperator.EQUAL
oFilterField(1).StringValue = "Julie"
oFilterField(2).Connection = com.sun.star.sheet.FilterConnection.OR
oFilterField(2).Field = 1
oFilterField(2).Operator = com.sun.star.sheet.FilterOperator.EQUAL
oFilterField(2).StringValue = "John"
oFilterField(3).Connection = com.sun.star.sheet.FilterConnection.OR
oFilterField(3).Field = 1
oFilterField(3).Operator = com.sun.star.sheet.FilterOperator.EQUAL
oFilterField(3).StringValue = "Samantha"


oFilter.setFilterFields(oFilterField())
oRange.filter(oFilter)

end sub

See Filtre.ods

[EDIT]

Sorry, I have indeed answered rather quickly with an example that I already had. I modified Filtre.ods to manage CONTAINS operator

Note: for LibreOffice API I recommend: IDL Reference

Regards

edit flag offensive delete link more
0

answered 2016-10-15 17:18:39 +0200

m.a.riosv gravatar image

Another way it's using the Menu/Data/More Filter/Advanced filter, criteria can be in a range of cells, so it's easy to change the criterias. And adding it to the toolbar make even easy the work. It's really powerful because regular expressions can be used or copy the result on other place.

Modifyed the @pierre-yves samyn to add an advanced filter. AdvancedFilter.ods

Advanced filter help

edit flag offensive delete link more
0

answered 2017-02-14 20:49:23 +0200

ckelley gravatar image

This has been the closest answer I could find to solve my problem. Here is what I am working with:

I have data that is exported from another program into an excel spreadsheet. I have an ODS spreadsheet that I am working with where that data from the initial export gets pasted in to Sheet1, some calculations are made, then the data from two columns on the Sheet1 gets copied to Sheet2.

My problem is that there are lines within the initial data that gets copied that I don't need. Those lines contain either "Total", "Category", or "Department", but they are mixed into the initial data in random places depending on the initial report. I am looking to make a macro that will filter out those lines that contain any of those keywords (eventually through the use of a button linked to the macro - I have that part figured out, just not the macro itself).

I copied the macro Pierre-Yves Samyn built, then did some digging to try to find out how to convert it from CONTAINS to the opposite. I found that I should be able to use DOES_NOT_CONTAIN in place of CONTAINS, so I put that in place, changed the keywords and tried to run it. It does not work. I found that on the following page:

http://api.libreoffice.org/docs/idl/r...

It also exists here:

https://www.openoffice.org/api/docs/c...

Here is a small sample of the data I am working with:

UF Chocolate Milk .5 gal
UF Intense Chocolate Milk 1 pt
UF Intense Chocolate Milk 1 qt
UF Intense Vanilla Milk 1 pt
UF Milk 1% .5 galu
UF Milk 1% 1 gal
UF Milk 2% .5 gal
UF Milk 2% 1 gal
UF Milk 2% 1 pt
UF Milk 2% 1 qt
UF Milk Homo .5 gal
UF Milk Homo 1 pt
UF Milk Homo 1 qt
UF Milk Homo Gal
UF Milk Skim .5 gal
UF Milk Skim 1 qt
UF Mint Chocolate Chip Milk 1 pt
UF Ult Half & Half 1 qt
Total Category: Dairy
Category: New Age   Type: Retail
UF Orange Juice .5 gal
Total Category: New Age
Total Store: 1
Grand Total

Note that the line "UF Orange Juice .5 gal" falls between several lines that contain the keywords I am trying to filter out.

Here is the macro with the changes I made:

sub PysFilter

dim oSheet as object, oRange as object, oFilter as object
dim oFilterField(3) As New com.sun.star.sheet.TableFilterField2   

oSheet = thisComponent.Sheets.getByName("Build Up")
oRange = oSheet.getCellRangeByName("A4:A500")

oFilter= oRange.createFilterDescriptor(true)
'oRange.filter(oFilter) 

oFilterField(0).Field = 1
oFilterField(0).Operator = com.sun.star.sheet.FilterOperator2.DOES_NOT_CONTAIN
oFilterField(0).StringValue = "Total"
oFilterField(1).Connection = com.sun.star.sheet.FilterConnection.AND
oFilterField(1).Field = 1
oFilterField(1).Operator = com.sun.star.sheet.FilterOperator2.DOES_NOT_CONTAIN
oFilterField(1).StringValue = "Category"
oFilterField(2).Connection = com.sun.star.sheet.FilterConnection.AND
oFilterField(2).Field = 1
oFilterField(2).Operator = com ...
(more)
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2016-10-13 10:03:20 +0200

Seen: 1,890 times

Last updated: Feb 14 '17