我一直遵循以下模式使用Excel InterOp创建多个工作表和多个工作簿。现在,我需要创建一个称为主工作簿的文件,该工作簿将包含我在原始工作簿中创建的工作表中的一些工作表。仅举一个例子,假设WorkBook1具有Sheet1和Sheet2,而Workbook2具有sheet3和sheet4。我希望主工作簿具有Sheet1和Sheet3。我正在寻找一种方法来创建我一直在为WorkBook1和WorkBook2创建的工作表(sheet1,sheet2,sheet3,sheet4),但是还要以最少的代码重复次数将sheet1和sheet3添加到Master工作簿中。任何帮助将不胜感激。
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
所以现在,我的问题是,如果我有我的主工作簿:
Dim MasterWorkBook As Workbook
MasterWorkBook = xlApp.Workbooks.Add
在重复10次以创建10个不同WorkBook的循环之前定义。如何以最少的代码重复量将选择的工作表添加到MasterWorkBook。
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
该代码创建3个工作簿,并在每个工作簿的末尾添加2个工作表。并将第一个添加的工作表从每个工作簿复制到主工作簿。并记住使用后释放对象引用。希望能帮助到你:)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句