VB.Net 如何匯出Excel (將Excel 當成DB 來做匯出)

參考引用 
----

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            '建立暫存Table!!
            Dim dt As New DataTable
            dt.Columns.Add(New DataColumn("tItem", System.Type.GetType("System.String")))
            dt.Columns.Add(New DataColumn("tDescription", System.Type.GetType("System.String")))
            dt.Columns.Add(New DataColumn("tMemon", System.Type.GetType("System.String")))
            '建立暫存資料!!
            Dim dr As DataRow = dt.NewRow
            dr.Item("tItem") = "1"
            dr.Item("tDescription") = "你好嗎?"
            dr.Item("tMemon") = "...."
            dt.Rows.Add(dr)
            '建立暫存資料!!
            dr = dt.NewRow
            dr.Item("tItem") = "2"
            dr.Item("tDescription") = "你是誰?"
            dr.Item("tMemon") = ",,,,,"
            dt.Rows.Add(dr)
            '呼叫匯出Excel Function
            If Not ExportExcel(dt, "c:\temp\123.xls", "測試") Then
                MessageBox.Show("匯出Excel 時發生錯誤!!")
            Else
                MessageBox.Show("匯出Excel 成功!!")
            End If
        Catch ex As Exception
            '錯誤則秀出錯誤訊息!!
            MessageBox.Show(ex.Message)
        End Try
    End Sub
 
    'DT: Data Table
    'FullFileName: 匯出Excel 的完整路徑 Ex: C:\123.xls
    'TableName: 匯出Excel 的Sheet Name
    Private Function ExportExcel(ByVal DT As DataTable, ByVal FullFileName As String, ByVal TableName As String) As Boolean
        Dim connection As New Data.OleDb.OleDbConnection(String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0""", FullFileName))
        Try
            '建立Excel Connection
            connection.Open()
            '產生Table
            Dim strCreate As String
            For i As Int32 = 0 To DT.Columns.Count - 1
                strCreate += String.Format(",{0} Text(255)", DT.Columns(i).ColumnName)
            Next
            strCreate = String.Format("Create Table [{0}] ({1})", TableName, strCreate.Substring(1))
            Dim command As New Data.OleDb.OleDbCommand(strCreate, connection)
            command.ExecuteNonQuery()
 
            '讀取Excel Table (Sheet)
            Dim queryString As String = String.Format("Select * From [{0}]", TableName)
            '建立Excel 配接器
            Dim adapter As New Data.OleDb.OleDbDataAdapter()
            '宣告一個Dataset 
            Dim ds As New DataSet
            adapter.SelectCommand = New Data.OleDb.OleDbCommand(queryString, connection)
            Dim builder As Data.OleDb.OleDbCommandBuilder = New Data.OleDb.OleDbCommandBuilder(adapter)
            '產生新增語法
            adapter.Fill(ds, TableName)
            For i As Int32 = 0 To DT.Rows.Count - 1
                Dim dr As DataRow = ds.Tables(TableName).NewRow
                For j As Int32 = 0 To DT.Columns.Count - 1
                    dr.Item(DT.Columns(j).ColumnName) = DT.Rows(i).Item(j)
                Next
                ds.Tables(TableName).Rows.Add(dr)
            Next
 
            builder.GetInsertCommand(True)
            '執行新增語法
            adapter.Update(ds, TableName)
            Return True
        Catch ex As Exception
            '擲出錯誤!!
            Throw ex
        Finally
            '關閉連線
            connection.Close()
        End Try 

    End Function