Macro to create auto filter selection, LibreOffice Calc

[Kes]

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?

[PYS]

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

[Kes]

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.

[mariosv]

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 @PYS to add an advanced filter. AdvancedFilter.ods

Advanced filter help

[ckelley]

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/ref/FilterOperator2_8idl.html

It also exists here:

https://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/FilterOperator2.html

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.sun.star.sheet.FilterOperator2.DOES_NOT_CONTAIN
oFilterField(2).StringValue = "Department"
			
with oFilter									
	.ContainsHeader = true						
	.CopyOutputData = false						
	.IsCaseSensitive = false				
	.UseRegularExpressions = false
	.filterFields2 = oFilterField()
end with
			
oRange.filter(oFilter)

end sub 

Can anyone offer any advice? I have taken classes on coding, so I have at least a minor understanding of what this does, but I don’t know the API, so I’m not sure why this isn’t working. For what it’s worth, I have installed the LibreOffice SDK. Per the documentation at http://api.libreoffice.org/docs/install.html, I also have the optional Java SDK installed, as well as the required .NET (version 4.6), G++ Compiler, zip, and cat and sed tools. Nothing seems to make a difference. Any help at all will be greatly appreciated!

Thank you!