首先在程序集添加引用-Microsoft Excel 14.0 Object Library。
然后在程序中添加引用-Imports Microsoft.Office.Interop。
最后是导出到Excel的函数
‘导出到Excel方法
Public Sub ExportExcel(ByVal dt As DataTable)
‘*********定义Excel对象变量***********
Dim ExcelApp As New Excel.Application()
Dim ExcelBook As Excel.Workbook
Dim ExcelSheet As Excel.Worksheet
Dim IntRowIndex As Integer = 1
Dim IntColIndex As Integer = 0
‘-------------------------------------
‘*************新建工作簿**************
ExcelBook = ExcelApp.Workbooks().Add
‘*************选定工作表*************
ExcelSheet = ExcelBook.Worksheets("sheet1")
‘-------------------------------------
‘***将所得到的表的列名,赋值给单元格***
Dim dtCol As DataColumn
Dim dtRow As DataRow
For Each dtCol In dt.Columns
IntColIndex = IntColIndex + 1
ExcelApp.Cells(1, IntColIndex) = dtCol.ColumnName
Next
‘-------------------------------------
‘****将得到的表所有行,赋值给单元格*****
For Each dtRow In dt.Rows
IntRowIndex = IntRowIndex + 1
IntColIndex = 0
For Each dtCol In dt.Columns
IntColIndex = IntColIndex + 1
ExcelApp.Cells(IntRowIndex, IntColIndex) = dtRow(dtCol.ColumnName)
Next
Next
‘-------------------------------------
‘**************设置表格外观**********
With ExcelSheet
‘设标题为黑体字
.Range(.Cells(1, 1), .Cells(1, IntColIndex)).Font.Name = "黑体"
‘标题字体加粗
.Range(.Cells(1, 1), .Cells(1, IntColIndex)).Font.Bold = True
‘设表格边框样式
.Range(.Cells(1, 1), .Cells(IntRowIndex, IntColIndex)).Borders.LineStyle = 1
‘使Excel单元格宽度自动适应
.Cells.EntireColumn.AutoFit()
End With
ExcelApp.Visible = True
End Sub 通过这三步，我们就能把获得的Datatable对象中的数据导出到Excel表格中，希望能对大家有所帮助。