C# 將資料匯出至 Excel 總覽（三）：伺服器端儲存與下載

編輯：關於C語言

前言

前幾篇文章介紹了直接將 Excel 檔案流輸出至瀏覽器的方法。本文將說明另一種策略：先將匯出的檔案儲存在伺服器指定資料夾，再透過 Response 將檔案傳送給客戶端下載。這樣做的優點在於能持久保存匯出檔案，方便後續實現其他功能（如歷史記錄、批次處理等）。

方法一：將 CSV 檔案存至伺服器再下載

此範例示範從資料庫讀取資料，寫入 CSV 檔案（以日期加亂數命名），存放於 web.config 設定的 downloadurl 路徑，最後透過 Response.WriteFile 提供下載。

SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["conn"]);
SqlDataAdapter da = new SqlDataAdapter("select * from tb1", conn);
DataSet ds = new DataSet();
da.Fill(ds, "table1");
DataTable dt = ds.Tables["table1"];

string name = System.Configuration.ConfigurationSettings.AppSettings["downloadurl"].ToString() 
    + DateTime.Today.ToString("yyyyMMdd") 
    + new Random(DateTime.Now.Millisecond).Next(10000).ToString() + ".csv";

FileStream fs = new FileStream(name, FileMode.Create, FileAccess.Write);
StreamWriter sw = new StreamWriter(fs, System.Text.Encoding.GetEncoding("gb2312"));
sw.WriteLine("自動編號,姓名,年齡");
foreach (DataRow dr in dt.Rows)
{
    sw.WriteLine(dr["ID"] + "," + dr["vName"] + "," + dr["iAge"]);
}
sw.Close();

Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(name));
Response.ContentType = "application/ms-Excel";
Response.WriteFile(name);
Response.End();

方法二：使用 Excel COM 元件操作範本產生報表

此方法利用 Microsoft.Office.Interop.Excel 開啟預先設計好的範本檔（MyTemplate.xls），將資料填入指定儲存格，另存新檔（myExcel.xls），最後關閉 Excel 並釋放 COM 物件。範例中包含 KillProcess 方法強制結束殘留的 Excel 進程。

public void Out2Excel(string sTableName, string url)
{
    Excel.Application oExcel = new Excel.Application();
    Workbooks oBooks;
    Workbook oBook;
    Sheets oSheets;
    Worksheet oSheet;
    Range oCells;
    string sFile = "", sTemplate = "";
    // System.Data.DataTable dt = TableOut(sTableName).Tables[0];
    sFile = url + "myExcel.xls";
    sTemplate = url + "MyTemplate.xls";

    oExcel.Visible = false;
    oExcel.DisplayAlerts = false;
    oBooks = oExcel.Workbooks;
    oBooks.Open(sTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    oBook = oBooks.get_Item(1);
    oSheets = oBook.Worksheets;
    oSheet = (Worksheet)oSheets.get_Item(1);
    oSheet.Name = "Sheet1";
    oCells = oSheet.Cells;
    // DumpData(dt, oCells); // 將資料寫入 Excel 的自訂方法
    oSheet.SaveAs(sFile, Excel.XlFileFormat.xlTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing);
    oBook.Close(false, Type.Missing, Type.Missing);
    oExcel.Quit();
    GC.Collect();
    KillProcess("Excel");
}

private void KillProcess(string processName)
{
    try
    {
        foreach (Process thisproc in Process.GetProcessesByName(processName))
        {
            if (!thisproc.CloseMainWindow())
            {
                thisproc.Kill();
            }
        }
    }
    catch (Exception Exc)
    {
        throw new Exception("", Exc);
    }
}