如何在Excel中将数据导出到不同的工作表中

我认为这段代码可以帮助您...

I think this code may help you...

Dim _FileName As String = ""
               Dim _xl As New Excel.Application
               Dim _xlBook As Excel.Workbook
               Dim _xlSheet1 As Excel.Worksheet
               _xl.ScreenUpdating = False
               '' '' Adding Workbook tio Excel application
               '' ''
               _xlBook = _xl.Workbooks.Add()
               '' '' Adding Worksheet tio Excel Workbook
               '' ''
               _xlSheet1 = _xlBook.Worksheets.Add
               _xlSheet1.Name = "My Sheet"
               '' '' Deleting extra Worksheet
               '' ''
               Dim i As Integer = 0
               For i = _xl.Worksheets.Count To 2 Step -1
                   _xl.Worksheets(i).Select()
                   _xl.Worksheets(i).Delete()
               Next
               '' '' Writing in Worksheet
               '' ''
               i = 1
               For Each Tbl As DataTable In _Ds.Tables
                   If Tbl.Rows.Count = 0 Then Continue For
                   Dim _dt As DataTable = Tbl
                   Dim _xlPgSetup As Excel.PageSetup
                   Dim _dc As DataColumn
                   Dim _Cols As Integer = 0
                   Dim _Rows As Integer

                   '' '' Adding new Worksheet
                   '' ''
                   _xlSheet1 = New Excel.Worksheet
                   _xlSheet1 = _xlBook.Worksheets(i)
                   _xlSheet1 = _xlBook.Worksheets.Add
                   _xlSheet1.Name = "My Sheet" & i
                   i += 1

                   Try
                       'Add the column headings for the Customers
                       For Each _dc In _dt.Columns
                              _xlSheet1.Range("B1").Offset(0, _Cols).Value = GetHederValue(_dc.ColumnName.ToString.Trim)
                             _Cols += 1
                       Next
                       'Add the data
                       For _Rows = 0 To _dt.Rows.Count - 1
                           _xlSheet1.Range("B3").Offset(_Rows).Resize(1, _Cols).Value = _dt.Rows(_Rows).ItemArray()
                       Next
                       With _xlSheet1.Cells
                           '.WrapText = True
                           .EntireColumn.AutoFit()
                           .EntireRow.AutoFit()
                       End With
                       For _Counter = 1 To _dt.Columns.Count + 1
                           _xlSheet1.Cells.Range(_xlSheet1.Cells(1, _Counter), _xlSheet1.Cells(2, _Counter)).Merge()
                       Next
                   Catch ex As Exception

                   Finally

                   End Try
                   '' '' Setting Printing properties
                   _xlPgSetup = _xl.ActiveSheet.PageSetup
                   _xlPgSetup.PaperSize = Excel.XlPaperSize.xlPaperA3
                   _xlPgSetup.Orientation = Excel.XlPageOrientation.xlPortrait
               Next

               '' '' Deleting My Sheet
               _xl.Worksheets(_xl.Worksheets.Count).Select()
               _xl.Worksheets(_xl.Worksheets.Count).Delete()
               _xl.ScreenUpdating = True
               '' ''Saving Excel File
               _ComponentNo = DataID & "_" & _ReportType '' ''_dt.Rows(0)("ProductNo")
               _xl.DisplayAlerts = False
               _FileName = "C:\Filename.xls"
               '' ''_xl.ActiveSheet.SaveAs(FileName:=_FileName, FileFormat:=Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8)
               _xl.ActiveSheet.SaveAs(FileName:=_FileName)
               _xl.DisplayAlerts = True
               _xl.Quit()