vb.net 将工作表添加到 Excel 工作簿

Adding a Sheet to an Excel Workbook

提问by Bob

I'm trying to create a Workbookwith multiple sheets in Excel but I can't figure out how to create the multiple sheets. I can create one just fine, but when I try to create a second one to write to I get an error.

我正在尝试Workbook在 Excel 中创建多张工作表，但我不知道如何创建多张工作表。我可以创建一个就好了，但是当我尝试创建第二个要写入的文件时，出现错误。

Dim app As Application = New Application
Dim xlApp As New Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim newXlApp As Excel.Application = New Microsoft.Office.Interop.Excel.Application
Dim newXlWorkbook As Excel.Workbook
Dim newXlSheet As Excel.Worksheet
Dim newXlSheet2 As Excel.Worksheet

Public Sub createWorkBook()
    newXlWorkbook = newXlApp.Workbooks.Add()

    newXlSheet = newXlWorkbook.Sheets("Sheet1")
    newXlSheet2 = newXlWorkbook.Sheets.Add("Sheet2")

    newXlSheet.Cells(1, 1) = "Task ID"
    newXlSheet.Cells(1, 2) = "Collective Tasks"
    newXlSheet.Cells(1, 3) = "Supported Task"

    newXlSheet2.Cells(1, 1) = "Parent Collective Task"
    newXlSheet2.Cells(1, 2) = "Individual Task"
End Sub

I'm not sure if it matters or not, but I also have a separate Excel Workbookopen that I'm querying.

我不确定这是否重要，但我也有一个单独的 ExcelWorkbook打开，我正在查询。

回答by Bugs

From what I can see the error your code is giving will be:

从我可以看到你的代码给出的错误是：

A first chance exception of type 'System.Runtime.InteropServices.COMException'

“System.Runtime.InteropServices.COMException”类型的第一次机会异常

If you want to add multiple Sheets to your Excel Workbookthis is the code to do that:

如果您想向 Excel 添加多个工作表，Workbook这是执行此操作的代码：

Dim app As New Excel.Application
Dim wb As Excel.Workbook = app.Workbooks.Add()
Dim ws As Excel.Worksheet

ws = CType(wb.Sheets.Add(Count:=10), Excel.Worksheet)

By default a Workbookcomes with one Sheet. If you want to add more than one set the Count:= parameter. As you can see in my example I have used 10. This will leave me with 11 Sheets to work with.

默认情况下 aWorkbook带有 one Sheet。如果要添加多个设置，则Count:= parameter. 正如您在我的示例中看到的，我使用了10。这将给我留下 11 张工作表。

Note that wswill be the last sheet in the Workbook. In my example this would be Sheet11.

请注意，这ws将是Workbook. 在我的示例中，这将是Sheet11。

If you want to work with each Worksheetthen you would need to look at the following code:

如果您想使用每个，Worksheet那么您需要查看以下代码：

Dim ws1 As Excel.Worksheet = CType(wb.Sheets(1), Excel.Worksheet)
Dim ws2 As Excel.Worksheet = CType(wb.Sheets.Add(), Excel.Worksheet)

ws1.Cells(1, 1) = "Task ID"
ws1.Cells(1, 2) = "Collective Tasks"
ws1.Cells(1, 3) = "Supported Task"

ws2.Cells(1, 1) = "Parent Collective Task"
ws2.Cells(1, 2) = "Individual Task"

Note that ws1references to the first sheet. As said above a Workbookby default comes with one sheet.

注意ws1对第一张表的引用。如上所述Workbook，默认情况下带有一张纸。