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