vb.net中从datatable读取数据到Excel

原文地址:http://hi.baidu.com/hiochou/item/5aedd2f0418056cea835a2dc

最近用这个比较多，所以到网上找了些相关的资料，自己做了个简单的示例，记录下来以防以后又忘记了。。。下面是完整代码(用的是Northwind数据库中的Products表)：

Imports System.Data

Imports System.Data.SqlClient

Imports Microsoft.Office

Public Class Form1

 

    Private Sub btnExpert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExpert.Click

        Dim connStr As String = "Data Source=PC-201104071256\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"

        Dim conn As SqlConnection = New SqlConnection(connStr)

        Dim sqlstr As String = "select top 10 * from dbo.Products"

 

        Dim adapter As SqlDataAdapter = New SqlDataAdapter(sqlstr, conn)

        Dim ds As DataSet = New DataSet

        Dim myTable As DataTable

        adapter.Fill(ds, "productsTable")

        myTable = ds.Tables("productsTable")

 

        Dim xlApp As Excel.Application

        Dim xlWorkBook As Excel.Workbook

        Dim xlWorkSheet As Excel.Worksheet

        Dim misValue As Object = System.Reflection.Missing.Value

        Dim chartRange As Excel.Range

        Dim rcount, ccount As Integer

        rcount = myTable.Rows.Count()

        ccount = myTable.Columns.Count()

 

        xlApp = New Excel.Application()

        xlWorkBook = xlApp.Workbooks.Add(misValue)

        xlWorkSheet = xlWorkBook.Worksheets("sheet1")

       '表头

        xlWorkSheet.Cells(1, 1) = "产品ID"

        xlWorkSheet.Cells(1, 2) = "产品名"

        xlWorkSheet.Cells(1, 3) = "供应商ID"

        xlWorkSheet.Cells(1, 4) = "分类ID"

        xlWorkSheet.Cells(1, 5) = "单元数量"

        xlWorkSheet.Cells(1, 6) = "单价"

        xlWorkSheet.Cells(1, 7) = "单位库存"

        xlWorkSheet.Cells(1, 8) = "订购单位"

        xlWorkSheet.Cells(1, 9) = "再订购库存量"

        xlWorkSheet.Cells(1, 10) = "停止使用" 

 

        chartRange = xlWorkSheet.UsedRange

        For rCnt = 2 To rcount + 1

            For cCnt = 1 To ccount

                xlWorkSheet.Cells(rCnt, cCnt) = CStr(myTable.Rows(rCnt - 2)(cCnt - 1).ToString)

            Next

        Next

 

        '格式化单元格

        chartRange.HorizontalAlignment = 3

        chartRange.VerticalAlignment = 3

        chartRange = xlWorkSheet.Range("A1", "J1")

        chartRange.Font.Bold = True

        chartRange = xlWorkSheet.Range("A2", "J11")

        chartRange.Font.ColorIndex = 5

 

        xlWorkSheet.SaveAs("E:\Test\products.xls")

        xlWorkBook.Close()

        xlApp.Quit()

 

        releaseObject(xlApp)

        releaseObject(xlWorkBook)

        releaseObject(xlWorkSheet)

 

        MsgBox("成功保存文件products.xls在E:\Test中...")

    End Sub

 

    Private Sub releaseObject(ByVal obj As Object)

        Try

            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)

            obj = Nothing

        Catch ex As Exception

            obj = Nothing

        Finally

            GC.Collect()

        End Try

    End Sub

End Class