Python UNO (libreoffice): How to enable auto filters for a sheet

I have a program, that creates a CSV file.

Now I'd like to use a Python UNO script, which shall do several things:

1.) open the csv file in a spread sheet

2.) enable auto filter for all columns

3.) create a macro and add it to the document

4.) save the file as ODS file

This question concerns only 2.)

1.) is working

for 3.) I will probably create another question 1.) Is working (with pyoo and with unotools)

My steps so far:

I start manually:

libreoffice --accept='socket,host=localhost,port=2202;urp;' --norestore --nologo --nodefault

My python script:

With pyoo

import pyoo
# Step 0) connect to UNO bridge
desktop = pyoo.Desktop('localhost', 2002)

# Step 1) open the doc and get the sheet
# This works only if the field separator is a comma.
# I don't know how for example to specify tab as separator instead
doc = desktop.open_spreadsheet('tst.csv')
# I see the spreadsheet opening
sheet = doc.sheets[0] # I get an object of type Sheet

# Step2) set autofilter for active sheet
# no idea how to do

# Step3) create a macro and add it to the document
# no idea how to do but will create another question as 
# soon as step2 is solved

# Step 4) save the sheet
doc.save("tst_pyoo.ods")

Or with unotools

import unotools
from unotools.component.calc import Calc
from unotools.unohelper import convert_path_to_url

# Step 0) connect to UNO bridge
context = unotools.connect(unotools.Socket('localhost', 2002))

# Step 1) open document
doc = Calc(ctx, convert_path_to_url('tst.csv')
# I see the spreadsheet opening
sheet = doc.get_sheet_by_index(0)
# I get an object of type unotools.component.calc.Spreadsheet

# Step2) set autofilter for active sheet
# no idea how to do

# Step3) create a macro and add it to the document
# no idea how to do but will create another question as 
# soon as step2 is solved

# Step 4)
doc.store_to_url(convert_path_to_url("tst_unotools.ods"))

Thanks in advance for any feedback

Solution

Here is code that uses straight PyUNO rather than a wrapper library. It is adapted from http://www.imaccanici.org/en.libreofficeforum.org/node/5413.html.

import os
import uno

class UnoObjs:
    "Initializes and stores UNO objects to connect to a document."""
    def __init__(self, filepath=None):
        localContext = uno.getComponentContext()
        resolver = localContext.ServiceManager.createInstanceWithContext(
            "com.sun.star.bridge.UnoUrlResolver", localContext )
        self.ctx = resolver.resolve(
            "uno:socket,host=localhost,port=2002;urp;"
            "StarOffice.ComponentContext")
        self.smgr = self.ctx.ServiceManager
        desktop = self.smgr.createInstanceWithContext(
            "com.sun.star.frame.Desktop", self.ctx)
        if filepath:
            fileUrl = uno.systemPathToFileUrl(os.path.realpath(filepath))
            self.document = desktop.loadComponentFromURL(
                fileUrl, "_default", 0, ())
        else:
            self.document = desktop.getCurrentComponent()

def add_autofilter(unoObjs):
    """This adds an autofilter by selecting only the filled spreadsheet area. 
    NOTE: If any cell in the header row of the selection is empty this will
    trigger a popup for interactive user action (must click Yes for the
    Autofilter column header message box)
    """
    dispatcher = unoObjs.smgr.createInstanceWithContext(
        "com.sun.star.frame.DispatchHelper", unoObjs.ctx)
    controller = unoObjs.document.getCurrentController()
    sheet = unoObjs.document.getSheets().getByIndex(0)
    # select a sufficiently big "guess" area, hopefully
    guessRange = sheet.getCellRangeByPosition(0, 0, 150, 10000)
    # look up the actual used area within the guess area
    cursor = sheet.createCursorByRange(guessRange)
    cursor.gotoEndOfUsedArea(False)
    lastcol = cursor.RangeAddress.EndColumn
    lastrow = cursor.RangeAddress.EndRow
    # select the filled part of the spreadsheet
    actualRange = sheet.getCellRangeByPosition(0, 0, lastcol, lastrow)
    controller.select(actualRange)
    # add autofilter
    dispatcher.executeDispatch(
        unoObjs.document.getCurrentController(), ".uno:DataFilterAutoFilter",
        "", 0, ())

add_autofilter(UnoObjs("tst.csv"))

Dispatcher calls such as .uno:DataFilterAutoFilter are difficult to figure out the parameters. In most cases it's better to use UNO API calls such as XTextCursor instead. However there are a few options to figure out dispatcher calls:

Use the macro recorder.
Look through a list like this one.
Find the calls in the LibreOffice source code. This is the most reliable, but it can still sometimes be difficult to determine the arguments.

Regarding dispatcher calls, see https://forum.openoffice.org/en/forum/viewtopic.php?f=20&t=61127.