Libreoffice Calc Basic 宏，用于合并具有不同列数的工作表

问：

我需要这个 LibreOffice Basic 代码的帮助，该代码旨在将所有工作表合并并合并到“组合”工作表中。 列应该合并为所有工作表中的列的并集，即将相同的列合并为一列。行应从所有工作表中附加。 但是代码无法正常工作：

1. 缺少包含列名称的标题行
2. 并非所有工作表中的所有行都会附加
3. 复制的值似乎不行

Sub CombineSheetsWithDifferentHeaders()
    Dim oDoc As Object
    Dim consolidatedData() As Variant
    Dim firstIteration As Boolean
    firstIteration = True

    oDoc = ThisComponent ' Get the current document

    ' Check if the "Combined" sheet exists; if not, create it
    Dim combinedSheet As Object
    On Error Resume Next
    combinedSheet = oDoc.Sheets.getByName("Combined")
    On Error GoTo 0

    If combinedSheet Is Nothing Then
        combinedSheet = oDoc.createInstance("com.sun.star.sheet.Spreadsheet")
        combinedSheet.setName("Combined")
        oDoc.Sheets.insertByName("Combined", combinedSheet)
    End If

    ' Iterate through all sheets in the document
    For Each srcSheet In oDoc.Sheets
        If srcSheet.Name <> "Combined" Then ' Skip the Combined sheet
            ' Read the data from the source sheet into an array
            Dim srcData() As Variant
            srcData = ReadSheetData(srcSheet)

            ' Debug: Print the sheet name
            MsgBox "Sheet Name: " & srcSheet.Name

            ' Debug: Print the dimensions of srcData
            Dim numRowsSrc As Integer
            Dim numColsSrc As Integer
            numRowsSrc = UBound(srcData, 1) + 1
            numColsSrc = UBound(srcData, 2) + 1
            MsgBox "srcData Dimensions: " & numRowsSrc & " rows, " & numColsSrc & " columns"

            ' Consolidate the data
            If firstIteration Then
                ' Initialize consolidatedData with the first data
                consolidatedData = srcData
                firstIteration = False
            Else
                ' Merge the data from the current sheet with consolidatedData
                consolidatedData = MergeData(consolidatedData, srcData)
            End If
        End If
    Next srcSheet

    ' Debug: Check if consolidatedData is empty
    If IsEmpty(consolidatedData) Then
        MsgBox "consolidatedData is empty"
    Else
        ' Debug: Print the dimensions of consolidatedData
        Dim numRowsConsolidated As Integer
        Dim numColsConsolidated As Integer
        numRowsConsolidated = UBound(consolidatedData, 1) + 1
        numColsConsolidated = UBound(consolidatedData, 2) + 1
        MsgBox "consolidatedData Dimensions: " & numRowsConsolidated & " rows, " & numColsConsolidated & " columns"
    End If

    ' Write the consolidated data to the "Combined" sheet
    WriteConsolidatedData(consolidatedData, combinedSheet)
End Sub

' Helper function to write the consolidated data to the "Combined" sheet
Sub WriteConsolidatedData(consolidatedData() As Variant, combinedSheet As Object)
    ' Resize the "Combined" sheet to accommodate the consolidated data
    Dim numRows As Integer
    Dim numCols As Integer
    numRows = UBound(consolidatedData, 1) + 1
    numCols = UBound(consolidatedData, 2) + 1
    combinedSheet.getRows().insertByIndex(0, numRows)
    combinedSheet.getColumns().insertByIndex(0, numCols)

    ' Write the consolidated data to the "Combined" sheet, including the header row
    For i = 0 To numRows - 1
        For j = 0 To numCols - 1
            combinedSheet.getCellByPosition(j, i).setValue(consolidatedData(i, j))
        Next j
    Next i
End Sub

' Helper function to merge data from different sheets
Function MergeData(data1() As Variant, data2() As Variant) As Variant
    ' Determine the number of rows in each dataset
    Dim numRows1 As Integer
    Dim numRows2 As Integer
    numRows1 = UBound(data1, 1) + 1
    numRows2 = UBound(data2, 1) + 1

    ' Determine the number of columns in each dataset
    Dim numCols1 As Integer
    Dim numCols2 As Integer
    numCols1 = UBound(data1, 2) + 1
    numCols2 = UBound(data2, 2) + 1

    ' Create an array to store column names and their indices from the first dataset
    Dim columnArray1() As Variant
    ReDim columnArray1(0 To numCols1 - 1)
    For j = 0 To numCols1 - 1
        columnArray1(j) = data1(0, j)
    Next j

    ' Merge columns from the second dataset
    Dim numMergedCols As Integer
    numMergedCols = numCols1

    For j = 0 To numCols2 - 1
        Dim colName As String
        colName = data2(0, j)

        ' Check if the column name from the second dataset exists in the first dataset
        Dim colIndex2 As Integer
        colIndex2 = -1
        For k = 0 To UBound(columnArray1)
            If columnArray1(k) = colName Then
                colIndex2 = k
                Exit For
            End If
        Next k

        If colIndex2 = -1 Then
            ' Add the new column name to the array
            ReDim Preserve columnArray1(0 To numMergedCols)
            columnArray1(numMergedCols) = colName
            numMergedCols = numMergedCols + 1
            colIndex2 = numMergedCols - 1
        End If
    Next j

    ' Calculate the maximum number of rows
    Dim maxRows As Integer
    maxRows = IIf(numRows1 > numRows2, numRows1, numRows2)

    ' Create a result array with the maximum dimensions
    Dim result() As Variant
    ReDim result(0 To maxRows, 0 To numMergedCols - 1)

    ' Initialize the result array with headers
    For j = 0 To UBound(columnArray1)
        result(0, j) = columnArray1(j)
    Next j

    ' Copy data from the first dataset
    For i = 1 To numRows1 - 1
        For j = 0 To numCols1 - 1
            result(i, j) = data1(i, j)
        Next j
    Next i

    ' Copy data from the second dataset
    For i = 1 To numRows2 - 1
        For j = 0 To numCols2 - 1
            result(i, colIndex2) = data2(i, j)
        Next j
    Next i

    MergeData = result
End Function

Function ReadSheetData(sheet As Object) As Variant
    Dim numRows As Integer
    Dim numCols As Integer
    Dim cellValue As Variant
    Dim data() As Variant

    numRows = RowsCount(UsedRange(sheet))
    numCols = ColumnsCount(UsedRange(sheet))
    
    ReDim data(0 To numRows - 1, 0 To numCols - 1)

    For i = 0 To numRows - 1
        For j = 0 To numCols - 1
            cellValue = sheet.getCellByPosition(j, i).getValue()
            data(i, j) = cellValue
        Next j
    Next i

    ReadSheetData = data
End Function

Function UsedRange(oSheet As Variant) As Variant
    Dim oCursor As Variant
    oCursor = oSheet.createCursor()
    oCursor.gotoEndOfUsedArea(False)
    oCursor.gotoStartOfUsedArea(True)
    UsedRange = oCursor
End Function

Function RowsCount(oRange As Variant) As Long 
    RowsCount = oRange.getRows().getCount()
End Function

Function ColumnsCount(oRange As Variant) As Long 
    ColumnsCount = oRange.getColumns().getCount()
End Function

Function LastRow(oRange As Variant) As Long 
    LastRow = oRange.getRangeAddress().EndRow
End Function

Function IsInArray(arr() As Variant, value As Variant) As Boolean
    Dim element As Variant
    For Each element In arr
        If element = value Then
            IsInArray = True
            Exit Function
        End If
    Next element
    IsInArray = False
End Function

Function GetColumnIndex(headerRow() As Variant, columnName As String) As Integer
    Dim i As Integer
    For i = 0 To UBound(headerRow)
        If headerRow(i) = columnName Then
            GetColumnIndex = i
            Exit Function
        End If
    Next i
    GetColumnIndex = -1
End Function