Datatable导出为Excel

首先在程序集添加引用-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表格中，希望能对大家有所帮助。