vb.net 如何在 Excel InterOp 中创建 Excel 工作表并将其添加到多个工作簿

How to create an Excel Sheet and Add it to Multiple WorkBooks in Excel InterOp

提问by Dogahe

I've been following the following pattern to create multiple sheets and multiple workbooks using Excel InterOp. Now, I would need to create a let's call a master workbook which would contain some of the sheets out of the sheets that I've been creating in the original workbooks. Just to give an example, let say WorkBook1 had Sheet1 and Sheet2, and Workbook2 had sheet3 and sheet4. I want the Master workbook to have Sheet1 and Sheet3. I'm looking for a way to create the sheets (sheet1, sheet2, sheet3, sheet4) that I've been creating for WorkBook1 and WorkBook2, but also with minimal amount of code repetition add sheet1 and sheet3 to the Master workbook. Any help will be appreciated.

我一直按照以下模式使用 Excel InterOp 创建多个工作表和多个工作簿。现在，我需要创建一个让我们称之为主工作簿的工作簿，其中将包含我在原始工作簿中创建的工作表中的一些工作表。举个例子，假设WorkBook1有Sheet1和Sheet2，Workbook2有sheet3和sheet4。我希望主工作簿具有 Sheet1 和 Sheet3。我正在寻找一种方法来创建我一直为 WorkBook1 和 WorkBook2 创建的工作表（sheet1、sheet2、sheet3、sheet4），而且还以最少的代码重复将 sheet1 和 sheet3 添加到主工作簿。任何帮助将不胜感激。

For i = 1 To 10
   Dim xlApp As Application = New Application
   Dim xlWorkBook As Workbook
   xlWorkBook = xlApp.Workbooks.Add
   Dim xlWorkSheet As Worksheet
   Dim xlSheets As Sheets = xlWorkBook.Sheets
   Dim xlNewSheet As Worksheet
   Dim sheetCount As Integer = 1

   ' So I repeat the following block to add multiple sheets with different content to a WorkBook
   xlNewSheet = xlSheets.Add(xlSheets(sheetCount), Type.Missing, Type.Missing, Type.Missing)
   sheetCount += 1
   xlNewSheet.Name = SomeName
   xlWorkSheet = xlWorkBook.Sheets(SomeName)
   AddContentToSheet(xlNewSheet) ' A Sub that adds real content to the sheet
   .
   .
   .
   .

   xlWorkBook.SaveAs(...)
   xlWorkBook.Close()
   xlApp.Quit()
Next i

So now, my question is, if I have my Master Workbook:

所以现在，我的问题是，如果我有我的主工作簿：

Dim MasterWorkBook As Workbook
MasterWorkBook = xlApp.Workbooks.Add

defined before the loop that repeats 10 times to create 10 different WorkBooks. How can I add select Sheets to MasterWorkBook with minimal amount of code repetition.

在重复 10 次以创建 10 个不同的工作簿的循环之前定义。如何以最少的代码重复将选择的工作表添加到 MasterWorkBook。

采纳答案by 2342G456DI8

    Dim xlApp As Application = New Application
    Dim masterWb As Workbook
    masterWb = xlApp.Workbooks.Add

    For i = 1 To 3
        Dim xlWorkBook As Workbook
        xlWorkBook = xlApp.Workbooks.Add
        Dim ws As Worksheet
        'Dim xlSheets As Sheets = xlWorkBook.Sheets
        'Dim xlNewSheet As Worksheet
        For j = 1 To 2
            Try
                ws = xlWorkBook.Sheets.Add(, xlWorkBook.Sheets(xlWorkBook.Sheets.Count))
                ws.Name = i + j
                AddContentToSheet(ws)
                If j = 1 Then
                    ws.Copy(, masterWb.Sheets(masterWb.Sheets.Count))
                End If
            Catch
                ws = Nothing
                xlWorkBook.Close()
                xlWorkBook = Nothing
                xlApp.Quit()
                xlApp = Nothing
                Console.WriteLine("error")
                Exit Sub
            End Try
        Next j

        'AddContentToSheet(xlNewSheet) ' A Sub that adds real content to the sheet
        ws = Nothing
        xlWorkBook.SaveAs("C:\Users\Documents\Visual Studio 2008\Project\" + Str(i) + ".xlsx")
        xlWorkBook.Close()
        xlWorkBook = Nothing
    Next i

    masterWb.Sheets("Sheet1").Delete()
    masterWb.Sheets("Sheet2").Delete()
    masterWb.Sheets("Sheet3").Delete()
    masterWb.SaveAs("C:\Users\Documents\Visual Studio 2008\Project\master.xlsx")
    masterWb.Close()
    masterWb = Nothing
    xlApp.Quit()
    xlApp = Nothing
End Sub

Sub AddContentToSheet(ByVal a As Worksheet)
    a.Cells(1, 1) = "abc"
    a.Cells(1, 2) = "abc"
End Sub

The code creates 3 workbooks, and adds 2 worksheets in the end of each workbook. And it copies the first added worksheets from each workbook to the master workbook. And remember release the object reference after using. Hope it helps:)

该代码创建了 3 个工作簿，并在每个工作簿的末尾添加了 2 个工作表。并将第一个添加的工作表从每个工作簿复制到主工作簿。并记住在使用后释放对象引用。希望能帮助到你：）