如何在LibreOffice Calc的Basic宏中实现字符串正则表达式替换

直接使用以下封装好的自定义函数，即可实现你需要的y = regex(x, "^¿(.*)\?$", "$1")调用语法，全程操作内存字符串变量，无需依赖工作表单元格：

第一步：复制封装函数到你的宏模块 #

Function REGEX(ByVal sourceStr As String, ByVal pattern As String, ByVal replaceStr As String, Optional ByVal flags As String) As String
    Dim searchService As Object
    Dim searchOpts As New com.sun.star.util.SearchOptions
    Dim result As String
    Dim findPos As Long
    Dim matchLen As Long
    Dim globalReplace As Boolean
    Dim caseInsensitive As Boolean
    
    ' 初始化替换服务
    searchService = CreateUnoService("com.sun.star.util.TextSearch")
    
    ' 解析标志参数
    globalReplace = False
    caseInsensitive = False
    If Not IsMissing(flags) Then
        If InStr(LCase(flags), "g") > 0 Then globalReplace = True
        If InStr(LCase(flags), "i") > 0 Then caseInsensitive = True
    End If
    
    ' 配置正则参数
    searchOpts.algorithmType = com.sun.star.util.SearchAlgorithms.REGEXP
    searchOpts.searchString = pattern
    searchOpts.replaceString = replaceStr
    searchOpts.caseSensitive = Not caseInsensitive
    searchService.setOptions(searchOpts)
    
    result = sourceStr
    findPos = 0
    Do
        Dim searchRes As Object
        searchRes = searchService.searchForward(result, findPos, Len(result))
        If searchRes.subRegExpressions = 0 Then Exit Do
        
        matchLen = searchRes.endOffset(0) - searchRes.startOffset(0)
        ' 处理分组替换
        Dim tmpReplace As String
        tmpReplace = replaceStr
        For i = 1 To searchRes.subRegExpressions - 1
            Dim groupStr As String
            groupStr = Mid(result, searchRes.startOffset(i)+1, searchRes.endOffset(i) - searchRes.startOffset(i))
            tmpReplace = Replace(tmpReplace, "$" & CStr(i), groupStr)
        Next i
        ' 执行替换
        result = Left(result, searchRes.startOffset(0)) & tmpReplace & Mid(result, searchRes.endOffset(0)+1)
        
        If Not globalReplace Then Exit Do
        findPos = searchRes.startOffset(0) + Len(tmpReplace)
    Loop While findPos <= Len(result)
    
    REGEX = result
End Function

第二步：直接调用即可，用法和预期完全一致 #

示例1：去掉首尾的西班牙文问号 #

Sub TestRegex
    Dim x As String, y As String
    x = "¿LibreOffice正则测试?"
    ' 匹配开头¿和结尾?，提取中间内容
    y = REGEX(x, "^¿(.*)\?$", "$1")
    MsgBox y ' 输出：LibreOffice正则测试
End Sub

示例2：全局替换所有数字为* #

Sub TestGlobalRegex
    Dim x As String, y As String
    x = "订单号：12345，金额：6789元"
    y = REGEX(x, "\d", "*", "g")
    MsgBox y ' 输出：订单号：*****，金额：****元
End Sub

方案说明 #

• 全程操作内存变量，无需操作工作表单元格，没有临时方案的各种弊端
• 支持所有LibreOffice标准正则语法、分组引用、全局替换、忽略大小写等常用特性
• 调用语法和工作表REGEX函数完全兼容，无需修改现有代码逻辑

之前直接调用REGEX报错的原因：工作表函数并未直接暴露到Basic的全局作用域，需要通过Uno服务间接调用，上述封装函数已经屏蔽了底层实现差异，可直接使用。

内容的提问来源于stack exchange，提问作者PJTraill