安達利機車行

在VB.NET中有許多種匯出資料到Excel的方式
以下的方式是由我同事周大姐所提供的一個好方法
先把資料透過串流的方式寫到文字檔
再呼叫Execl去開啟這個檔。  

請將您的資料寫到Datable中，將DataTable和檔案路徑傳入到這個method。


    Private Sub ExportScoreToXls(ByVal dt As System.Data.DataTable, ByVal xlsPath As String)
        If xlsPath.Trim = "" Then Exit Sub
        Dim txtPath As String = Replace(xlsPath, ".xls", ".txt")
        If File.Exists(txtPath) = True Then File.Delete(txtPath)
        If File.Exists(xlsPath) = True Then File.Delete(xlsPath)

        Dim oExcel As Object = CreateObject("Excel.Application")
        GC.Collect() : GC.WaitForPendingFinalizers()
        Try
            '---------------------------------
            'create header
            '---------------------------------
            Dim sb As New StringBuilder
            sb.Append("欄位A,欄位B,欄位C,欄位D,欄位E,欄位F,欄位G,欄位H,欄位I,")
            sb.Append(Chr(13) + Chr(10))
            '---------------------------------
            'create data
            '---------------------------------
            If dt.Rows.Count > 0 Then
                For Each dr As DataRow In dt.Rows
                    Dim en_data As IEnumerator = dt.Columns.GetEnumerator
                    While en_data.MoveNext
                        sb.Append(dr.Item(en_data.Current).ToString).Append(",")
                    End While
                    sb.Append(Chr(13) + Chr(10))
               Next
            End If

            '寫文字檔
            Dim sw As StreamWriter = New StreamWriter(txtPath, _ 
                                                             False, System.Text.Encoding.Unicode)
            sw.WriteLine(sb.ToString)
            sw.Flush()
            sw.Close()

            '匯出excel檔
            oExcel.Visible = False : oExcel.DisplayAlerts = False
            'oExcel.Visible = True
            oExcel.Workbooks.OpenText(Filename:=txtPath, StartRow:=1, DataType:=1,  _
                                      TextQualifier:=1,ConsecutiveDelimiter:=False, TAB:=False, _
                                      Semicolon:=False, Comma:=True, _
                                      Space:=False, Other:=False, TrailingMinusNumbers:=True)
            oExcel.ActiveWorkbook.SaveAs(Filename:=xlsPath)
        Catch ex As Exception
            'Throw ex
        Finally
            If File.Exists(txtPath) = True Then File.Delete(txtPath)
            oExcel.ActiveWorkbook.Close()
            oExcel.Quit()
            ReleaseComObject(oExcel)
            oExcel = Nothing
            GC.Collect()
            Me.Refresh()
        End Try
    End Sub