2

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!

moggi
  • 1,466
  • 4
  • 18
  • 29
Elsi
  • 23
  • 4

1 Answers1

1

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 -- \&.

Jim K
  • 12,824
  • 2
  • 22
  • 51
  • I did some tiny changes on your code but all in all it works just great. Thank you so much! – Elsi Jul 18 '16 at 08:59