在运行时将多个表单添加到Excel工作簿 [英] Adding multiple Sheets to an excel workbook at run time

问题描述

我正在尝试使用vb.net在运行时创建一个excel工作簿，然后向其添加多个

工作表（工作表数量取决于变量值）并重命名工作表然后填充每个工作簿使用来自SQL Server表的数据表，当运行我的代码抛出一个异常时，我谷歌但尚未解决，使用vb.net的excel中的无效索引。（HRESULT异常：0x8002000B（DISP_E_BADINDEX）异常

它出现在句子中：xlWorkSheet = xlWorkBook.Sheets（xlWorkBook.Sheets（i）.Name（））

，任何有用的想法!!

I am trying to create an excel work book at run time using vb.net ,then adding multiple
sheets to it (sheet count depends on a variable Value ) and rename the sheets then fill each sheet with data from SQL server table,when running my code an exception thrown, i google it but not solved yet ,the exception "Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX)) in excel using vb.net"
it occurs at the sentence :xlWorkSheet = xlWorkBook.Sheets(xlWorkBook.Sheets(i).Name())
,any useful thoughts!!

Dim xlApp As Excel.Application = New Microsoft.Office.Interop.Excel.Application()
If xlApp Is Nothing Then
MessageBox.Show("Excel is not properly installed!!")
Exit Sub
End If

          Dim xlWorkBook As New Excel.Workbook
          xlWorkBook = xlApp.Workbooks.Add()
          Dim xlWorkSheet As New Excel.Worksheet

          Dim dtVacations As DataTable
         'here i fill the datatable with data

          If dtVacations IsNot Nothing AndAlso dtVacations.Rows.Count > 0 Then
              xlWorkSheet = CType(xlApp.Worksheets.Add(, , dtVacations.Rows.Count - 1), Excel.Worksheet)

' Dim Var As Int32 = xlWorkBook.Worksheets.Count
              Dim i As Int32 = 1
              For Each row In dtVacations.Rows
                  xlApp.Visible = True
                  xlWorkSheet = xlWorkBook.Worksheets(i)
                  xlWorkSheet.Activate()
                  '  xlApp.Worksheets("secondsheet").Move(After:=xlWorkBook.Worksheets(xlWorkBook.Worksheets.Count))
                  'xlWorkBook.Sheets(i).Select()
                  xlWorkBook.Sheets(i).Name() = NS(row("VacationNameA"))

                  xlWorkSheet.Cells(1, 1).Value = "EmpNo"
                  xlWorkSheet.Cells(1, 2).Value = "EmpName"
                  xlWorkSheet.Cells(1, 3).Value = "HireDate"
                  xlWorkSheet.Cells(1, 4).Value = "VacNo"
                  xlWorkSheet.Cells(1, 4).Value = "VacName"
                  xlWorkSheet.Cells(1, 5).Value = "VacDaysNo"
                  xlWorkSheet.Cells(1, 6).Value = "BalanceDate"
                  xlWorkSheet.Cells(1, 7).Value = "DueDate"
                  xlWorkSheet.Cells(1, 8).Value = "Bal"
                  xlWorkSheet.Cells(1, 9).Value = "ToDate"

                  'here i get the data from sql server and fill it into datatable
                  Dim dtEmpVac As DataTable = CreateDataTable(MySql)
                  If dtEmpVac IsNot Nothing AndAlso dtEmpVac.Rows.Count > 0 Then
                      For i = 0 To dtEmpVac.Rows.Count - 1
                          'Column
                          For j = 0 To dtEmpVac.Columns.Count - 1
                              ' this i change to header line cells >>>
                              xlWorkSheet.Cells(i + 3, j + 1) = _
                              dtEmpVac.Rows(i).Item(j)
                          Next
                      Next
                  End If

                  i += 1
              Next

          ElseIf dtVacations IsNot Nothing And dtVacations.Rows.Count = 0 Then
                              releaseObject(xlWorkBook)
              releaseObject(xlApp)
              Exit Sub
          End If

          ''Save the workbook

          xlWorkBook.SaveAs(My.Computer.FileSystem.SpecialDirectories.Desktop & "\Vacations.xls", Excel.XlFileFormat.xlWorkbookNormal, _
           Excel.XlSaveAsAccessMode.xlExclusive)
          xlWorkBook.Close(True)
          xlApp.Quit()

          releaseObject(xlWorkBook)
          releaseObject(xlApp)

问题出现在第一个循环后转到下一张表!!! />
[/ EDIT]

推荐答案

引用：

Microsoft Excel创建一个包含大量空白工作表的新工作簿（工作表数量由 SheetsInNewWorkbook属性 [ ^ ]

Microsoft Excel creates a new workbook with a number of blank sheets (the number of sheets is set by the SheetsInNewWorkbook property[^]

来源： Workbooks.Add方法 [ ^ ]





有关详细信息如何创建新的工作簿和工作表，请参阅：

如何：以编程方式创建新工作簿 [ ^ ]

如何：以编程方式将新工作表添加到工作簿 [ ^ ]

source: Workbooks.Add method[^]


For further information on how to create new workbook and worksheets, please see:
How to: Programmatically Create New Workbooks[^]
How to: Programmatically Add New Worksheets to Workbooks[^]

这篇关于在运行时将多个表单添加到Excel工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！