This Python macro seems to do the trick on the first sheet:
Code: Select all
import re
def highlight_Embraced():
from com.sun.star.sheet.CellFlags import STRING
regex = '\((.*?)\)'
doc = XSCRIPTCONTEXT.getDocument()
sh = doc.Sheets.getByIndex(0)
rgtxt = sh.queryContentCells(STRING)
dsc = sh.createSearchDescriptor()
dsc.SearchRegularExpression = True
dsc.SearchType = 1
dsc.setSearchString(regex)
rgs = rgtxt.findAll(dsc)
# doc.CurrentController.select(rgs)
enum = rgs.Cells.createEnumeration()
while enum.hasMoreElements():
cell = enum.nextElement()
txt = cell.getText()
s = cell.getString()
rgx = re.compile(regex)
i = rgx.finditer(s)
for m in i:
# print(m.span(1))
a = m.span(1)[0]
b = m.span(1)[1] - a
c = txt.createTextCursor()
c.collapseToStart()
c.goRight(a, False)
c.goRight(b, True)
c.CharColor = int('0xFF0000', 16)
# print(c.getString())
First I query all cell ranges having constant strings since we do not want to format any formulas (=> XSheetCellRanges).
Within the string cells I find all cells having any pair of braces (=> XSheetCellRanges). Undocumented dsc.SearchType = 1 seems to search within values instead of formulas which would obsolete queryContentCells, but I keep it anyway in this example code. It is a very fast method to boil down the amount of cells.
Within the found sheet cell ranges I enumerate every single cell and do the regex matching based on the precompiled, non-greedy expression
\((.*?)\) and query the iteration of all matches within the cell string.
The inner braces of the expression denote a group, the first group.
n.span(1) returns a pair of 2 numbers (start, end) denoting the match positions of the first group.
a=n.span(1)[0] is the start
b=n.span(1)[1] -a is the char count
At this point you have to create a new text cursor for every match, collapseToStart, jump a chars to the right and expand another b chars to the right in order to get the wanted text range.
P.S.: Now the formatted cells could be queried with queryContentCells(com.sun.star.sheet.CellFlags.FORMATTED) which is a subset of c.s.s.CellFlags.STRING. Formatted string cells are those having formatted text portions, hyperlink fields or line breaks.
Edit: Added an installer document with a paramterized version of the above code. Download the document to a trusted directory, open it and push the button to install the Python module. |