vb.net导出数据到Excel代码

'首先添加引用，在COM组件里选择 Microsoft Excel 11.0 Object Library

'在代码中导入命名空间

Imports Excel = Microsoft.Office.Interop.Excel

 

Public Sub ExportExcel()

Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim rowIndex, colIndex As Integer
Dim TDS As DataSet
Dim TSQL As String
TSQL = "select * from transfers"
TDS = GetDataFromDB(TSQL)
rowIndex = 1
colIndex = 0
xlBook = xlApp.Workbooks().Add
xlSheet = xlBook.Worksheets("sheet1")
Dim Table As New DataTable
Table = TDS.Tables(0)
'将所得到的表的列名,赋值给单元格
Dim Col As DataColumn
Dim col1 As DataColumn
Dim i As Integer
Dim Row As DataRow
For Each Col In Table.Columns
colIndex = colIndex + 1
xlApp.Cells(1, colIndex) = Col.ColumnName
Next
'得到的表所有行(, 赋值给单元格)
For Each Row In Table.Rows
rowIndex = rowIndex + 1
colIndex = 0
'For i = 0 To Table.Columns.Count - 1
For Each col1 In Table.Columns
colIndex = colIndex + 1
xlApp.Cells(rowIndex, colIndex) = Row(col1.ColumnName)
Next
Next
With xlSheet
.Range(.Cells(1, 1), .Cells(1, colIndex)).Font.Name = "黑体"
'设标题为黑体字
.Range(.Cells(1, 1), .Cells(1, colIndex)).Font.Bold = True
'标题字体加粗
.Range(.Cells(1, 1), .Cells(rowIndex, colIndex)).Borders.LineStyle = 1
'设表格边框样式
End With
With xlSheet.PageSetup

.LeftHeader = "" & Chr(10) & "&""楷体_GB2312,常规""&10公司名称：" '& Gsmc
.CenterHeader = "&""楷体_GB2312,常规""公司人员情况表&""宋体,常规""" & Chr(10) & "&""楷体_GB2312,常规""&10日 期："
.RightHeader = "" & Chr(10) & "&""楷体_GB2312,常规""&10单位："
.LeftFooter = "&""楷体_GB2312,常规""&10制表人："
.CenterFooter = "&""楷体_GB2312,常规""&10制表日期："
.RightFooter = "&""楷体_GB2312,常规""&10第&P页 共&N页"
End With

xlApp.Visible = True

End Sub