VB.NET把DataTable里的内容导出到EXCEL

在程序的引用中添加对Excel的引用.

添加引用Microsoft Excel 9.0 Object Library后

Function SendToExcel(ByVal Table As DataTable, ByVal SheetName As String)

    Dim oExcel As New Excel.Application

    Dim obook As Excel.Workbook

    Dim oSheet As Excel.Worksheet

    Try

        obook = oExcel.Workbooks.Add '.Open(file)

        oSheet = obook.Worksheets(1)

        oSheet.Name = SheetName

        Dim r As Integer, c As Integer

        Dim rCount As Integer

        Dim cCount As Integer

        rCount = Table.Rows.Count

        cCount = Table.Columns.Count()

        For c = 1 To cCount

            oSheet.Cells(1, c) = Table.Columns(c - 1).Caption '设置列标题

        Next

        c = 0 : r = 0

        For r = 1 To rCount

            For c = 1 To cCount

                oSheet.Cells(r + 1, c) = CStr(Table.Rows(r - 1)(c - 1).ToString)

            Next

        Next

        oExcel.Visible = True

    Catch ex As Exception

        MsgBox(ex.Message)

    End Try

    oExcel = Nothing

    obook = Nothing

    oSheet = Nothing

End Function

第二种方法：

'把DataTable里的内容导出到EXCEL,执行代码就行,什么都不用改,什么都不用设置

Private Sub toExcel(ByVal tb As DataTable)

            Dim   dgrid   As   System.Web.UI.WebControls.DataGrid   =   Nothing

            Dim   context   As   System.Web.HttpContext   =   System.Web.HttpContext.Current

            Dim   strOur   As   System.IO.StringWriter   =   Nothing

            Dim   htmlWriter   As   System.Web.UI.HtmlTextWriter   =   Nothing

            If   Not   IsNothing(tb)   Then

                    if tb.rows.count=0 then exit sub

                    context.Response.ContentType   =   "application/vnd.ms-excel "

                    context.Response.ContentEncoding   =   System.Text.Encoding.UTF7

                    context.Response.Charset   =   " "

                    strOur   =   New   IO.StringWriter

                    htmlWriter   =   New   System.Web.UI.HtmlTextWriter(strOur)

                    dgrid   =   New   DataGrid

                    dgrid.DataSource   =   tb.DefaultView

                    dgrid.AllowPaging   =   False

                    dgrid.DataBind()

                    dgrid.RenderControl(htmlWriter)

                    context.Response.Write(strOur.ToString)

                    context.Response.End()

            End   If 

End Sub