Return a list of matches in an area as a column

Question

I want to have the results of a regex search on a particular sheet show up as a column on another sheet. It wouldn't be too hard to do, say, conditional highlighting or something, to just eyeball where the results are, but what I really need is the ability to make autogenerated columns containing, e.g. all cells bearing say the tag @home or #projectname.

Using LibreOffice Base is basically one long crash, and plus the UX of spreadsheets is pleasantly flexible. Suggestions welcome. I'm Linux-only at the moment.

score 0 · Answer 1 · answered Jul 16 '16 at 15:32

using LibreOffice Base is basically one long crash

Have you tried Base with a split database setup such as MySQL? Running embedded HSQLDB is not recommended.

Anyway here is some python code that does what you want:

def calc_regex_to_column():
    desktop = XSCRIPTCONTEXT.getDesktop()
    model = desktop.getCurrentComponent()
    sheet_src = model.getSheets().getByIndex(0)
    sheet_dst = model.getSheets().getByIndex(1)
    search = sheet_src.createSearchDescriptor()
    search.SearchString = r".*@home|#projectname.*"
    search.SearchCaseSensitive = True
    search.SearchRegularExpression = True
    selsFound = sheet_src.findAll(search)
    sheet_dst_row = 0
    for selIndex in range(0, selsFound.getCount()):
        string_found = selsFound.getByIndex(selIndex).getString()
        COLUMN_A = 0
        cell = sheet_dst.getCellByPosition(COLUMN_A, sheet_dst_row)
        cell.setString(string_found)
        sheet_dst_row += 1

g_exportedScripts = calc_regex_to_column,

See here for where to put the script.

Return a list of matches in an area as a column

1 Answers1