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
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