Find & Replace working on cell contents in Calc

Shared Libraries
Forum rules
For sharing working examples of macros / scripts. These can be in any script language supported by OpenOffice.org [Basic, Python, Netbean] or as source code files in Java or C# even - but requires the actual source code listing. This section is not for asking questions about writing your own macros.
Post Reply
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Find & Replace working on cell contents in Calc

Post by Lupp »

Any object supporting one of the sevices com.sun.star.sheet.SheetcellRange or com.sun.star.sheet.SheetcellRanges (returned for a current multiselection e.g.) should export the interface XSearchable which also can be used to replace. Also TextDocuments export XSearchable, but despite the common name the results obtained by methods (findAll e.g.) are gravely different in both cases:
TextDocuments return the found matches as a com.sun.star.text.TextRanges object where each single range contains exactly one matching string.
Any Range/Ranges in a SpreadsheetDocument returns a .SheetcellRanges object (or Null).
With other words: In text documents you get the details, in spreadsheets you get only the the cells.

In pursuit of the topic viewtopic.php?f=7&t=98452 I looked for a way to also get able to apply the F&R to the content of any found cell.

The only passable way I found was to introduce a helper model of type com.sun.star.text.TextDocument. See below. The code is split into two Functions for the Calc part and the Writer part where the Calc part finds the cells, but only returns the total number of replacements, while the Writer part returns the processed strings and helps counting by a side-effect.

As always: Interested in less sophisticated solutions and in criticisms.

Code: Select all

Function apply_FR_to_CalcSearchable_intoCellContents(pCalcSearchable As Object, pFind As String, pRegEx As Boolean, pReplace As String)
Dim count As Long
Dim compPropHidden As New com.sun.star.beans.PropertyValue
compPropHidden.Name  = "Hidden"
compPropHidden.Value = True
sd0 = pCalcSearchable.createSearchDescriptor()
sd0.SearchRegularExpression = pRegEx
sd0.SearchString = pFind
foundRgs = pCalcSearchable.findAll(sd0)
helperTdoc = StarDesktop.loadComponentFromUrl("private:factory/swriter", "_blank", 0, Array(compPropHidden))
sd1 = helperTdoc.createSearchDescriptor()
sd1.SearchRegularExpression = pRegEx
sd1.SearchString = pFind
sd1.ReplaceString = pReplace
For Each oneRg in foundRgs
  With oneRg.RangeAddress
    For c = .StartColumn To .EndColumn
      For r = .StartRow To .EndRow
        crCell = pCalcSearchable.getCellByPosition(c, r)
        Select Case crCell.Type
          Case 3
            crCell.Formula = applyFRtoCellContent(helperTdoc, crCell.Formula, sd1, count)
          Case 2, 1
          REM In case 2 internal formatting will be ignored and lost!
            crCell.String = applyFRtoCellContent(helperTdoc, crCell.String, sd1, count)
          Case Else
            Exit Function
        End Select
      Next r
    Next c
  End With
Next oneRg
helperTdoc.close(True)
apply_FR_to_CalcSearchable_intoCellContents = count
End Function

Function applyFRtoCellContent(pHelperTdoc, pString, pSd, ByRef pCount As Long) As String
pHelperTDoc.Text.String = pString 
REM The following line only is a simple example. 
REM The effects obtained this way you can alo get with ordinary F&R.
REM but using found = pHelperTdoc.findAll(pSd)
REM you can define the replacement to any detail.
pCount = pCount + pHelperTdoc.replaceAll(pSd)
applyFRtoCellContent = pHelperTDoc.Text.String
End Function
And this was the caller for testing (in one version going back to the mentioned thread):

Code: Select all

SSub testCall()
REM This was a test I actually applied to a sheet with more than one match in more than one cells. 
theCdocSheet = ThisComponent.Sheets(0)
count = apply_FR_to_CalcSearchable_intoCellContents(theCdocSheet, "(?<=idStr idNr)(, [0-9]+)+(?= )", True, "_match_")
MsgBox "There were " & count & " replacements."
End Sub
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Find & Replace working on cell contents in Calc

Post by Lupp »

Coming back to the post above I noticed that it was badly worded in parts.
Of course you can F&R the .replaceAll way in Calc to the expected effect on the cell contents at any occurrence of the search string (or a match for the RegEx).

What I thought to need the posted routine for was the capability to get working a .findAll on the content of each found cell, and thus to be able to create the specific replacement for every occurrence in whatever way I want.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply