从DataTable导出Excel

    首先，导入命名空间

 

Imports Microsoft.Office.Interop

 

    其次，定义变量来访问Excel中的从属对象及其属性和方法。   

        '-------------------------------------
        '*********定义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")
        '-------------------------------------
        '**********获取DataTable数据**********
        Dim Table As New DataTable()
        Dim BllLineInfo As New BLL.b_LineInfo
        Table = BllLineInfo.GetRow(txtCardNum.Text)
        '-------------------------------------
        '***将所得到的表的列名,赋值给单元格***
        Dim dtCol As DataColumn
        Dim dtRow As DataRow
        For Each dtCol In Table.Columns
            IntColIndex = IntColIndex + 1
            ExcelApp.Cells(1, IntColIndex) = dtCol.ColumnName
        Next
        '-------------------------------------
        '****将得到的表所有行,赋值给单元格*****
        For Each dtRow In Table.Rows
            IntRowIndex = IntRowIndex + 1
            IntColIndex = 0
            For Each dtCol In Table.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
            '设表格边框样式
        End With
        ExcelApp.Visible = True
        '--------------------------------------
    End Sub