ASP.NET 匯出Excel檔案

以往的經驗，使用者常常會希望系統能依其查詢出來的結果匯出成一個Excel檔案，當然，不論從Datagrid或Gridview大概都有以下幾種方式：

1.使用third-party的元件
2.利用 Http 的方式轉換
3.直接將查詢結果寫入excel檔案中

第一種方式是使用third-party元件，大多都有提供匯出excel、pdf、wrod....等功能，有興趣的可以到google搜尋一下，還算蠻方便的，但缺點就是要$。

第二種方式，是利用GridView或DataGrid Render出與網頁上格式相同的HTML，並配合Response.ContentType = "application/vnd.xls"來轉出為Excel；相關做法可以參考TIPS-Export GridView To Excel 及 擴展 GridView 控制項 - 支援 Excel 及 Word 匯出 兩篇文章，雖然這種方式能很快速地寫完程式碼，但在一些小地方像css設定和不需匯出的欄位還得要加工處理一下；另外，使用這種方式別忘了要在Server端安裝OWC元件喔。

本文主要是介紹以第三種方式來匯出Excel檔案，這種方式是直接透過OleDB來存取，程式碼如下:

Public Function ExportExcel(ByRef sourceDS As DataSet, ByVal path As String) As Boolean

    Dim objConn As OleDbConnection
    Dim i As Integer = 0

    Dim Createstr As StringBuilder
    Dim j As Integer
    Dim CreateSheetcmd As OleDbCommand

    Dim Exceldr As DataRow
    Dim Insstr As StringBuilder
    Dim InsertDatacmd As OleDbCommand
    Dim k As Integer = 0    

    Try
        '依傳入的檔案 path 檢查檔案是否已存在
        Dim fi As FileInfo = New FileInfo(path)
        If fi.Exists Then fi.Delete()

        '使用OLEDB連接該檔案
        Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & path & "; Extended Properties=""Excel 8.0;"""
        objConn = New OleDbConnection(sConnectionString)

        '依 sourceDS 的 Table 個數產生對應的Excel頁籤
        For i = 0 To sourceDS.Tables.Count - 1
            Createstr = New StringBuilder("")
            Createstr.Append("Create Table " & sourceDS.Tables(i).TableName.Trim & "  ( ")     
            '依 sourceDS 的 DataTable 產生欄位
            For j = 0 To sourceDS.Tables(i).Columns.Count - 2
                If (sourceDS.Tables(i).Columns(j).DataType Is System.Type.GetType("System.Decimal") = False) Then
                    Createstr.Append(sourceDS.Tables(i).Columns(j).ColumnName.ToString & " Char(255) , ")
                Else
                    Createstr.Append(sourceDS.Tables(i).Columns(j).ColumnName.ToString & " Decimal , ")
                End If
            Next
            If (sourceDS.Tables(i).Columns(j).DataType Is System.Type.GetType("System.Decimal") = False) Then
                Createstr.Append(sourceDS.Tables(i).Columns(j).ColumnName.ToString & " Char(255) ")
            Else
                Createstr.Append(sourceDS.Tables(i).Columns(j).ColumnName.ToString & " Decimal ")
            End If
            Createstr.Append(" )")
            CreateSheetcmd = New OleDbCommand(Createstr.ToString, objConn)

            'Insert Data
            Insstr = New StringBuilder("")
            Insstr.Append("Insert Into " & sourceDS.Tables(i).TableName.Trim & " values (")      
            For j = 0 To sourceDS.Tables(i).Columns.Count - 2
                Insstr.Append("?,")
            Next
            Insstr.Append("?")
            Insstr.Append(")")
            InsertDatacmd = New OleDbCommand(Insstr.ToString, objConn)

            objConn.Open()
            CreateSheetcmd.ExecuteNonQuery()

            For k = 0 To sourceDS.Tables(i).Rows.Count - 1
                InsertDatacmd.Parameters.Clear()
                For j = 0 To sourceDS.Tables(i).Columns.Count - 1
                    InsertDatacmd.Parameters.Add("@" & sourceDS.Tables(i).Columns(j).ColumnName.ToString, OleDbType.Char, 255)
                    InsertDatacmd.Parameters("@" & sourceDS.Tables(i).Columns(j).ColumnName.ToString).Value = sourceDS.Tables(i).Rows(k).Item(j)
                Next
                InsertDatacmd.ExecuteNonQuery()
            Next
            objConn.Close()
        Next

    Catch ex As Exception
        'Exception 處理
        Return False
    End Try
    Return True
End Function

上面的 ExportExcel Function 傳入二個參數，一是 sourceDS 參數，為要匯出Excel檔的資料來源，其資料型態為DataSet；另一為path參數，表示Excel檔要產生在Server端的路徑。當Server端產生Excel檔後，可以直接Redirect到Excel檔案所在路徑即可，唯一要注意的是檔案目錄權限的問題，開放適當的權限，才不會造成Excel檔案無法寫入的問題。

利用這種方式的最大好處就是可以完完全全依程式設計師需要的資料來源來產生Excel檔，不會受到Web UI Control(DataGrid、GridView)的CSS設定影響，匯出Excel的資料也會與資料來源型態一致，怎麼說呢，透過下面這個例子大家應該就能瞭解了

當來源資料某欄資料為字串型態，如“012345”以第二種方式匯出Excel，可能就變成”12345”；而以第三種方式就不會有這種情形了；另外，如來源DataSet包含多個DataTable，也可以產生出分不同Sheet的Excel檔。

這種方式的缺點，大概也有幾個issue要考量：
1.檔案目錄權限--涉及安全性
2.Server端產出Excel檔的政策--檔案會不會持續增加？檔案如何管理？需不需要定期prug檔案？

筆者的經驗會覺得第三種方式比第二種方式提供了更多的彈性，利用第三種方式也可以適用在匯出Aceess檔案上，提供大家參考。