LibreOffice Calc中按行列索引获取单元格名称及公式引用改造

我有一段宏代码，点击按钮时会从名为REPARTITION PIECES的工作表列中依次读取数值，创建名为FICHE INVENTAIRE的新工作表，并以值传递的方式写入内容。现在需要修改为传递单元格引用，让REPARTITION PIECES工作表内容变更时，FICHE INVENTAIRE的对应单元格自动更新（比如目标单元格显示=REPARTITION PIECES$A$2这类公式引用）。

旧版本代码 #

For n = 1 To ligs + 1
    y = Mid(celu(n), 6)
    y = y - 1
    nombre = thisComponent.Sheets.getByName("REPARTITION PIECES").getCellByPosition(col, y).String
    If nombre <> "" Then
        li = li + 1
        code = thisComponent.Sheets.getByName("REPARTITION PIECES").getCellByPosition(1, y).String
        clair = thisComponent.Sheets.getByName("REPARTITION PIECES").getCellByPosition(2, y).String

        thisComponent.Sheets.getByName(onglet).getCellByPosition(D, li-l).String = Date()
        thisComponent.Sheets.getByName(onglet).getCellByPosition(D+1, li-l).String = code
        thisComponent.Sheets.getByName(onglet).getCellByPosition(D+2, li-l).String = clair
        thisComponent.Sheets.getByName(onglet).getCellByPosition(D+5, li-l).String = nombre
        ' If li > 22 And D = 0 Then
        '     D = 6
        '     l = 22
        ' End If
        If strcomp(onglet, "FICHE INVENTAIRE") = 0 Then
             borne = 30
         Else
             borne = 19
         End If

        If (li - l) >= borne Then
             If strcomp(onglet, "FICHE INVENTAIRE") = 0 Then
                 li = 9
             Else
                 li = 3
             End If
             If D = 0 Then
                 D = D + 6
             Else
                 onglet = "VERSO FICHE INVENTAIRE"
                 li = 3
                 D = 0
             End If
         End If

    End If
Next n

修改后的代码 #

For n = 1 To ligs + 1
    y = Mid(celu(n), 6)
    y = y - 1
    ' 提前获取源工作表对象，减少重复调用提升效率
    Dim srcSheet As Object
    srcSheet = thisComponent.Sheets.getByName("REPARTITION PIECES")
    ' 直接判断单元格是否为空，比读取String更准确
    If Not srcSheet.getCellByPosition(col, y).IsEmpty Then
        li = li + 1
        Dim targetSheet As Object
        targetSheet = thisComponent.Sheets.getByName(onglet)
        Dim targetRow As Integer
        targetRow = li - l

        ' 保留原逻辑写入当前日期，若需动态日期可改为=TODAY()
        targetSheet.getCellByPosition(D, targetRow).String = Date()
        
        ' 构造绝对引用公式，通过Formula属性写入实现自动更新
        ' Code字段：引用REPARTITION PIECES第2列（索引1）的对应行
        targetSheet.getCellByPosition(D+1, targetRow).Formula = _
            "=REPARTITION PIECES!$" & Chr(65 + 1) & "$" & (y + 1)
        ' Clair字段：引用REPARTITION PIECES第3列（索引2）的对应行
        targetSheet.getCellByPosition(D+2, targetRow).Formula = _
            "=REPARTITION PIECES!$" & Chr(65 + 2) & "$" & (y + 1)
        ' Nombre字段：引用REPARTITION PIECES对应col列的对应行
        targetSheet.getCellByPosition(D+5, targetRow).Formula = _
            "=REPARTITION PIECES!$" & Chr(65 + col) & "$" & (y + 1)

        ' 保留原有的分页逻辑
        If strcomp(onglet, "FICHE INVENTAIRE") = 0 Then
             borne = 30
         Else
             borne = 19
         End If

        If (li - l) >= borne Then
             If strcomp(onglet, "FICHE INVENTAIRE") = 0 Then
                 li = 9
             Else
                 li = 3
             End If
             If D = 0 Then
                 D = D + 6
             Else
                 onglet = "VERSO FICHE INVENTAIRE"
                 li = 3
                 D = 0
             End If
         End If

    End If
Next n

关键修改说明 #

• 替换值传递为公式引用：不再读取源单元格的.String值，而是构造=工作表名!$列$行格式的绝对引用公式，通过.Formula属性写入目标单元格，实现源数据变更时目标自动更新
• 优化对象调用：提前获取源工作表和目标工作表对象，避免循环中重复调用getByName，提升代码运行效率
• 空值判断优化：直接使用单元格的.IsEmpty属性判断是否为空，比读取.String后判断更精准
• 列名转换：通过Chr(65 + 列索引)将从0开始的列索引转换为Excel风格的列字母（A对应65），配合实际行号（y+1，因代码中y为从0开始的索引）生成标准绝对引用

内容的提问来源于stack exchange，提问作者Revolucion for Monica