CODEWITHSUNDEEP
pythonlibreofficelibreoffice-calcuno
Elsi
Elsi

Reputation: 23

Python - LibreOffice Calc - Find & Replace with Regular Expression

I try to code a Find & Replace method with Python in LibreOffice's Calc to replace all the ".+" with "&" (in a single column - not so important) - unfortunately, even a standard Find & Replace method seems to be impossible (to me). That's what I have up to now:

import uno
def search()
    desktop = XSCRIPTCONTEXT.getDesktop()
    document = XSCRIPTCONTEXT.getDocument()
    ctx = uno.getComponentContext()
    sm = ctx.ServiceManager
    dispatcher = sm.createInstanceWithContext("com.sun.star.frame.DispatchHelper", ctx)
    model = desktop.getCurrentComponent()
    doc = model.getCurrentController()
    sheet = model.Sheets.getByIndex(0)

    replace = sheet.createReplaceDescriptor()
    replace.SearchRegularExpression = True
    replace.SearchString = ".+$"
    replace.ReplaceString ="&"
    return None

And what happens: totally nothing! I will be happy and thankful for every hint, sample code and motivating words!

Upvotes: 2

Views: 1061

Answers (1)

Jim K
Jim K

Reputation: 13800

This code changes all non-empty cells in column A to &:

def calc_search_and_replace():
    desktop = XSCRIPTCONTEXT.getDesktop()
    model = desktop.getCurrentComponent()
    sheet = model.Sheets.getByIndex(0)
    COLUMN_A = 0
    cellRange = sheet.getCellRangeByPosition(COLUMN_A, 0, COLUMN_A, 65536);
    replace = cellRange.createReplaceDescriptor()
    replace.SearchRegularExpression = True
    replace.SearchString = r".+$"
    replace.ReplaceString = r"\&"
    cellRange.replaceAll(replace)

Notice that the code calls replaceAll to actually do something. Also, from the User Guide:

& will insert the same string found with the Search RegExp.

So the replace string needs to be literal -- \&.

Upvotes: 1

Related Questions