ASP.NET DataSet 導出 Excel 兩大方案：OLEDB 寫入 vs HTML Table 渲染

方案對比速覽

方案一：HTML Table 流式導出（推薦通用）

public void ExportDataSetToExcel(DataSet ds, string fileName, string sheetTitle = "Sheet1")
{
    var res = HttpContext.Current.Response;
    res.Clear();
    res.Buffer = false; // 關鍵：流式輸出
    res.Charset = "UTF-8";
    res.ContentEncoding = Encoding.UTF8;
    res.ContentType = "application/vnd.ms-excel"; // .xls MIME
    string encoded = HttpUtility.UrlEncode(fileName, Encoding.UTF8).Replace("+", "%20");
    res.AddHeader("Content-Disposition", $"attachment; filename=\"{encoded}\"; filename*=UTF-8''{encoded}");

    // UTF-8 BOM 確保 Excel 識別中文
    res.OutputStream.Write(new byte[] { 0xEF, 0xBB, 0xBF }, 0, 3);

    using (var writer = new StreamWriter(res.OutputStream, Encoding.UTF8, 1024, true))
    {
        // 1. 樣式定義 (內聯 CSS)
        writer.Write(@"
");

        // 2. 遍歷 DataSet 所有表（或僅第一表）
        foreach (DataTable dt in ds.Tables)
        {
            if (dt.Rows.Count == 0) continue;

            writer.Write($"");
            foreach (DataColumn col in dt.Columns)
                writer.Write($"");
            writer.Write("");

            foreach (DataRow row in dt.Rows)
            {
                writer.Write("");
                foreach (DataColumn col in dt.Columns)
                {
                    var val = row[col];
                    string css = "";
                    if (val is DateTime) css = "date";
                    else if (val is decimal || val is double || val is float) css = "num";
                    writer.Write($"");
                }
                writer.Write("");
            }
            writer.Write("
{HttpUtility.HtmlEncode(col.ColumnName)}
{HttpUtility.HtmlEncode(val?.ToString() ?? "")}

"); // 多表換頁分隔
        }
    }
    HttpContext.Current.ApplicationInstance.CompleteRequest();
}

方案二：OLEDB 寫入真實 .xls（舊系統兼容）

public void ExportDataSetOleDb(DataSet ds, string filePath, string tableName = "Sheet1")
{
    // 1. 確保目錄存在
    Directory.CreateDirectory(Path.GetDirectoryName(filePath));
    if (File.Exists(filePath)) File.Delete(filePath);

    // 2. 創建空 .xls 文件 (ACE 引擎需文件預存在)
    string connStr = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={filePath};Extended Properties='Excel 12.0 Xml;HDR=YES;'";
    using (var conn = new OleDbConnection(connStr))
    {
        conn.Open();
        // 3. 建表 - 根據 DataTable 結構動態生成
        var dt = ds.Tables[0];
        var colsSql = string.Join(", ", dt.Columns.Cast()
            .Select(c => $"[{c.ColumnName}] {GetOleDbType(c.DataType)}"));
        using (var cmd = new OleDbCommand($"CREATE TABLE [{tableName}] ({colsSql})", conn))
            cmd.ExecuteNonQuery();

        // 4. 參數化批量插入
        string insertSql = $"INSERT INTO [{tableName}] ({string.Join(",", dt.Columns.Cast().Select(c => $"[{c.ColumnName}]"))}) VALUES ({string.Join(",", dt.Columns.Cast().Select((c,i) => $"@p{i}"))})";
        using (var cmd = new OleDbCommand(insertSql, conn))
        {
            // 添加參數
            for (int i = 0; i < dt.Columns.Count; i++)
                cmd.Parameters.Add($"@p{i}", GetOleDbType(dt.Columns[i].DataType));

            foreach (DataRow row in dt.Rows)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                    cmd.Parameters[i].Value = row[i] ?? DBNull.Value;
                cmd.ExecuteNonQuery();
            }
        }
    }

    // 5. 下載文件
    DownloadFile(filePath, Path.GetFileName(filePath));
}

private string GetOleDbType(Type t)
{
    if (t == typeof(string)) return "VARCHAR(255)";
    if (t == typeof(int) || t == typeof(long)) return "INTEGER";
    if (t == typeof(decimal) || t == typeof(double)) return "DOUBLE";
    if (t == typeof(DateTime)) return "DATETIME";
    if (t == typeof(bool)) return "BIT";
    return "VARCHAR(255)";
}

private void DownloadFile(string physicalPath, string fileName)
{
    var res = HttpContext.Current.Response;
    var fi = new FileInfo(physicalPath);
    res.Clear();
    res.ContentType = "application/vnd.ms-excel";
    res.AddHeader("Content-Length", fi.Length.ToString());
    string encoded = HttpUtility.UrlEncode(fileName, Encoding.UTF8).Replace("+", "%20");
    res.AddHeader("Content-Disposition", $"attachment; filename=\"{encoded}\"; filename*=UTF-8''{encoded}");
    res.TransmitFile(physicalPath);
    res.Flush();
    HttpContext.Current.ApplicationInstance.CompleteRequest();
    // 可選：延遲刪除臨時文件
    Task.Delay(5000).ContinueWith(_ => { try { File.Delete(physicalPath); } catch { } });
}

現代最佳方案：ClosedXML / EPPlus 生成 .xlsx（強烈推薦新項目）

// NuGet: ClosedXML
public void ExportWithClosedXML(DataSet ds, string fileName)
{
    using var wb = new XLWorkbook();
    foreach (DataTable dt in ds.Tables)
    {
        var ws = wb.Worksheets.Add(dt.TableName);
        ws.Cell(1, 1).InsertTable(dt.AsEnumerable()); // 一鍵導入 DataTable
        ws.Columns().AdjustToContents();
        // 樣式
        ws.Row(1).Style.Font.Bold = true;
        ws.Row(1).Style.Fill.BackgroundColor = XLColor.FromHtml("#4472C4");
        ws.Row(1).Style.Font.FontColor = XLColor.White;
    }
    using var ms = new MemoryStream();
    wb.SaveAs(ms);
    ms.Position = 0;

    var res = HttpContext.Current.Response;
    res.Clear();
    res.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    string encoded = HttpUtility.UrlEncode(fileName, Encoding.UTF8).Replace("+", "%20");
    res.AddHeader("Content-Disposition", $"attachment; filename=\"{encoded}\"; filename*=UTF-8''{encoded}");
    res.BinaryWrite(ms.ToArray());
    res.Flush();
    HttpContext.Current.ApplicationInstance.CompleteRequest();
}

關鍵最佳實踐總結

1. 編碼鐵律：UTF-8 + BOM + `filename*` 雙重編碼，徹底解決中文亂碼。
2. 流式輸出：`Response.Buffer = false` + `StreamWriter(Response.OutputStream, ..., leaveOpen:true)`，大數據不佔內存。
3. CompleteRequest：替代 `Response.End()`，避免 `ThreadAbortException`。
4. 類型映射：HTML 方案用 `mso-number-format` 控制 Excel 顯示格式；OLEDB/ClosedXML 保持原生類型。
5. 臨時文件清理：OLEDB 方案需異步延遲刪除，防止下載未完成文件被刪。
6. 新項目首選 ClosedXML/EPPlus：生成真實 .xlsx，支持樣式、公式、圖表、篩選、凍結窗格，無驅動依賴，跨平台。

遷移建議

從「偽裝 .xls」到「真實 .xlsx」，從「驅動依賴」到「純托管」，DataSet 導出 Excel 的技術棧已完成現代化升級。根據項目約束選擇：舊系統維護用 HTML Table 方案；新項目、重構項目**直接上 ClosedXML**。