Regex search in Calc macro no longer works

asked 2015-09-09 10:57:33 +0100

stanton gravatar image

updated 2015-10-05 21:48:59 +0100

Alex Kemp gravatar image

I have the following snippet of code, which I created in OOo a while back and which has worked for a long time. However, when In recently tried this code in LibreOffice 4.4.2.2, the search never returned any results:

   SearchDesc = Sheet.getColumns().getByIndex(0).createSearchDescriptor
   SearchDesc.setSearchString(TextToRegex(strWord))
   SearchDesc.SearchRegularExpression = True
   SearchDesc.SearchCaseSensitive = False
   SearchDesc.SearchWords = True
   SearchRes = Sheet.getColumns().getByIndex(0).findFirst(SearchDesc)

It seems that the regex is at fault, as the following code works (for direct matches, that is):

   SearchDesc = Sheet.getColumns().getByIndex(0).createSearchDescriptor
   SearchDesc.setSearchString(strWord)
   SearchDesc.SearchCaseSensitive = False
   SearchDesc.SearchWords = True
   SearchRes = Sheet.getColumns().getByIndex(0).findFirst(SearchDesc)

Trying a somewhat more crude regex also works:

   SearchDesc = Sheet.getColumns().getByIndex(0).createSearchDescriptor
   SearchDesc.setSearchString(".*" + strWord + ".*")
   SearchDesc.SearchRegularExpression = True
   SearchDesc.SearchCaseSensitive = False
   SearchDesc.SearchWords = True
   SearchRes = Sheet.getColumns().getByIndex(0).findFirst(SearchDesc)

What's the issue here? It seems the TextToRegex() function returns a regex which doesn't match the original text...

edit retag flag offensive close merge delete

Comments

Do you really have strWord in contents of cells? May be you search it in formulas, not in values of cells? Try to change SearchDesc.SearchType from 0 (by default) to 1 ("search in cell formulas values")

JohnSUN gravatar imageJohnSUN ( 2015-09-10 11:49:57 +0100 )edit

My crystal ball is broken, I can't see whatever TextToRegex(strWord) returns.

erAck gravatar imageerAck ( 2015-09-10 23:46:50 +0100 )edit

I think that it is a userdefined function, something like ".*" + strWord + ".*"

JohnSUN gravatar imageJohnSUN ( 2015-09-11 17:19:36 +0100 )edit

Obviously it returns something different as you mentioned in your third example that SearchDesc.setSearchString(".*" + strWord + ".*")does work.

erAck gravatar imageerAck ( 2015-09-14 18:53:54 +0100 )edit

Yes you are right. But only if strWord no contains points, dollars, backslashes, brackets etc.

JohnSUN gravatar imageJohnSUN ( 2015-09-15 05:27:47 +0100 )edit

If strWord contains regular expression metacharacters or operators that are meant to be literal characters to search for, then each has to be escaped with a preceding \ backslash. Either that, or turn off regular expression search for such a string. But that was always the case. For such characters and expressions see http://userguide.icu-project.org/stri... and following.

erAck gravatar imageerAck ( 2015-11-10 18:17:53 +0100 )edit

For clarification: yes, strWord does occur in cell contents. That is why non-Regex search works. The strings are natural words, which normally don’t contain any characters with special meaning inside a regex. At the most, they might have diacritics (ą, ž etc.).

stanton gravatar imagestanton ( 2017-01-26 11:55:51 +0100 )edit