Programer達

匯出Excel網路上能查到很多種方式，像是NPOI、EPPlus、ClosedXML等

但偶爾會遇到不能使用這些套件的環境，所以這邊以 Interop.Excel 操作為例

將DataTable匯出為Excel，匯出格式為xlsx檔

匯出xlsx檔，需要加入參考 Interop.Excel.dll, 版本為12以上，也就是要安裝Office2007以上版本

若是匯出Excel為xls檔，則Mime類型須改成 application/vnd.ms-excel

也就是 Response.ContentType 的地方改為Response.ContentType = "application/vnd.ms-excel"

值得注意的事，匯出Excel後，若未釋放資源，Server上的工作管理員就會看到Excel在執行，不會自動關閉

所以在try-finally釋放Excel開啟的所有資源

    'tempFilePath路徑包含xlsx檔名
    Public Sub ExportDataTableToExcel(dt As DataTable, tempFilePath as String)
        Dim xlApp As New Excel.Application
        Dim xlBook As Excel.Workbook = xlApp.Workbooks.Add()
        Dim xlSheet As Excel.Worksheet = xlBook.ActiveSheet
        Try
            Dim tRowCount As Integer = dt.Rows.Count
            Dim tColumnCount As Integer = dt.Columns.Count
            '塞入head
            For i As Integer = 0 To tColumnCount - 1
                xlSheet.Cells(1, i + 1).value = dt.Columns(i).ColumnName
            Next
            '塞入每一筆資料
            For j As Integer = 0 To tRowCount - 1
                For k As Integer = 0 To tColumnCount - 1
                    xlSheet.Cells(j + 2, k + 1).value = dt.Rows(j)(k).ToString()
                Next
            Next
            xlBook.SaveAs(tempFilePath)
        Finally
            '釋放所有Excel資源，若未處理釋放資源，執行10次匯出，Server上的工作管理員就會看到10個Excel在執行
            xlBook.Close()
            xlApp.Workbooks.Close()
            xlApp.Application.Quit()
            xlApp.Quit()
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlSheet)
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlBook)
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp)
            GC.Collect()
        End Try
        Dim buf() As Byte = IO.File.ReadAllBytes(tempFilePath)
        If IO.File.Exists(tempFilePath) Then
            IO.File.Delete(tempFilePath)
        End If
        Response.Expires = 0
        Response.Clear()
        Response.Buffer = True
        Response.Charset = "utf-8"
        Response.ContentEncoding = System.Text.Encoding.UTF8
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(Path.GetFileName(tempFilePath), System.Text.Encoding.UTF8))
        Response.BinaryWrite(buf)
        Response.Flush()
        Response.End()
    End Sub