将工作表添加到Excel工作簿 [英] Adding a Sheet to an Excel Workbook
问题描述
我正在尝试在Excel中创建具有多个工作表的Workbook
,但是我不知道如何创建多个工作表.我可以创建一个就好了,但是当我尝试创建第二个要写的文件时,却出现了错误.
I'm trying to create a Workbook
with 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.
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
我不确定这是否重要,但我还有一个要查询的单独的Excel Workbook
打开.
I'm not sure if it matters or not, but I also have a separate Excel Workbook
open that I'm querying.
推荐答案
从我可以看到您的代码给出的错误将是:
From what I can see the error your code is giving will be:
类型为'System.Runtime.InteropServices.COMException'的第一次机会异常
A first chance exception of type 'System.Runtime.InteropServices.COMException'
如果要将多个工作表添加到Excel Workbook
,请执行以下操作:
If you want to add multiple Sheets to your Excel Workbook
this is the code to do that:
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)
默认情况下,Workbook
带有一个Sheet
.如果要添加多个,请设置Count:= parameter
.如您在我的示例中看到的,我使用了 10 .这将给我11张工作表.
By default a Workbook
comes 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.
请注意,
ws
将是Workbook
中的最后一张纸.在我的示例中,该名称为 Sheet11 .
Note that
ws
will be the last sheet in theWorkbook
. In my example this would be Sheet11.
如果要使用每个Worksheet
,则需要查看以下代码:
If you want to work with each Worksheet
then you would need to look at the following code:
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"
请注意,
ws1
是指第一张纸.如上所述,默认情况下,Workbook
随附一张纸.
Note that
ws1
references to the first sheet. As said above aWorkbook
by default comes with one sheet.
这篇关于将工作表添加到Excel工作簿的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!