C# 封裝 Excel 匯出元件：資料繫結、樣式、邊框、自動欄寬完整實作

編輯：關於C語言

設計目標

建立可重複使用的 ExcelExporter 類別，支援：

任意 DataTable/DataView/IEnumerable<T> 資料來源
自訂標題、欄位對應、資料格式化 (日期、數字、貨幣)
標題列樣式 (粗體、置中、底色)、資料列交替色、邊框、自動欄寬
彙總列 (小計/總計)、標題跨欄置中
可選擇立即顯示、儲存檔案、或回傳 byte[] 供下載

核心介面設計

public class ExcelExportOptions
{
    public string Title { get; set; }              // 報表主標題
    public string SheetName { get; set; } = "Sheet1";
    public List Columns { get; set; } = new(); // 欄位對應
    public bool AutoFitColumns { get; set; } = true;
    public bool ShowGridLines { get; set; } = true;
    public bool AddTotalRow { get; set; } = false;
    public Func TotalRowLabel { get; set; } = _ => "合計";
}

public class ColumnMap
{
    public string PropertyName { get; set; }   // 資料屬性/欄位名稱
    public string HeaderText { get; set; }     // 顯示標題
    public string Format { get; set; }         // 格式字串: "yyyy-MM-dd", "N2", "C", "P0"
    public ExcelHorizontalAlignment Alignment { get; set; } = ExcelHorizontalAlignment.General;
    public bool IsNumeric { get; set; }        // 用於彙總
    public int? Width { get; set; }            // 固定寬度 (字元數)
}

public enum ExcelHorizontalAlignment
{
    General, Left, Center, Right, Fill, Justify, CenterAcrossSelection, Distributed
}

泛型匯出實作 (支援 DataTable / List<T>)

using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;

public class ExcelExporter
{
    public void ExportToFile(IEnumerable data, ExcelExportOptions options, string filePath)
    {
        using var excel = new Excel.Application { Visible = false, DisplayAlerts = false };
        var wb = excel.Workbooks.Add();
        var ws = (Excel.Worksheet)wb.ActiveSheet;
        ws.Name = options.SheetName;

        try
        {
            int rowIdx = 1;

            // 1. 主標題
            if (!string.IsNullOrEmpty(options.Title))
            {
                ws.Cells[rowIdx, 1] = options.Title;
                var titleRange = ws.Range[ws.Cells[rowIdx, 1], ws.Cells[rowIdx, options.Columns.Count]];
                titleRange.Merge();
                titleRange.Font.Bold = true;
                titleRange.Font.Size = 18;
                titleRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
                rowIdx++;
            }

            // 2. 標題列
            int colIdx = 1;
            foreach (var col in options.Columns)
            {
                ws.Cells[rowIdx, colIdx] = col.HeaderText;
                var headerCell = (Excel.Range)ws.Cells[rowIdx, colIdx];
                headerCell.Font.Bold = true;
                headerCell.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                headerCell.Interior.Color = ColorTranslator.ToOle(Color.LightSteelBlue);
                headerCell.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
                headerCell.Borders.Weight = Excel.XlBorderWeight.xlThin;
                if (col.Width.HasValue) ws.Columns[colIdx].ColumnWidth = col.Width.Value;
                colIdx++;
            }
            int headerRow = rowIdx;
            rowIdx++;

            // 3. 資料列
            var props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance)
                .ToDictionary(p => p.Name, p => p, StringComparer.OrdinalIgnoreCase);

            foreach (var item in data)
            {
                colIdx = 1;
                foreach (var col in options.Columns)
                {
                    if (props.TryGetValue(col.PropertyName, out var prop))
                    {
                        object val = prop.GetValue(item);
                        string text = FormatValue(val, col.Format);
                        ws.Cells[rowIdx, colIdx] = text;

                        var cell = (Excel.Range)ws.Cells[rowIdx, colIdx];
                        cell.HorizontalAlignment = MapAlignment(col.Alignment);
                        cell.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
                        cell.Borders.Weight = Excel.XlBorderWeight.xlThin;
                        cell.NumberFormat = GetNumberFormat(col.Format); // 保留 Excel 原生格式
                    }
                    colIdx++;
                }
                // 交替列底色
                if ((rowIdx - headerRow) % 2 == 0)
                {
                    var rowRange = ws.Range[ws.Cells[rowIdx, 1], ws.Cells[rowIdx, options.Columns.Count]];
                    rowRange.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(245, 245, 245));
                }
                rowIdx++;
            }

            // 4. 彙總列
            if (options.AddTotalRow)
            {
                ws.Cells[rowIdx, 1] = options.TotalRowLabel(null);
                var totalRange = ws.Range[ws.Cells[rowIdx, 1], ws.Cells[rowIdx, options.Columns.Count]];
                totalRange.Font.Bold = true;
                totalRange.Interior.Color = ColorTranslator.ToOle(Color.LightYellow);
                totalRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
                totalRange.Borders.Weight = Excel.XlBorderWeight.xlThin;
                totalRange.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlMedium;
                totalRange.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlMedium;

                colIdx = 2;
                foreach (var col in options.Columns.Skip(1))
                {
                    if (col.IsNumeric)
                    {
                        string colLetter = GetColumnLetter(colIdx);
                        string formula = $"=SUBTOTAL(9,{colLetter}{headerRow+1}:{colLetter}{rowIdx-1})";
                        ws.Cells[rowIdx, colIdx].Formula = formula;
                        ws.Cells[rowIdx, colIdx].NumberFormat = GetNumberFormat(col.Format);
                    }
                    colIdx++;
                }
                rowIdx++;
            }

            // 5. 自動欄寬
            if (options.AutoFitColumns)
            {
                var usedRange = ws.UsedRange;
                usedRange.Columns.AutoFit();
                // 限制最大寬度避免過寬
                foreach (Excel.Range col in usedRange.Columns)
                    if (col.ColumnWidth > 60) col.ColumnWidth = 60;
            }

            // 6. 列印設定 (可選)
            ws.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
            ws.PageSetup.FitToPagesWide = 1;
            ws.PageSetup.FitToPagesTall = false;

            wb.SaveAs(filePath, Excel.XlFileFormat.xlOpenXMLWorkbook);
        }
        finally
        {
            ReleaseCom(ws);
            wb.Close(false);
            ReleaseCom(wb);
            excel.Quit();
            ReleaseCom(excel);
            GC.Collect(); GC.WaitForPendingFinalizers();
        }
    }

    private string FormatValue(object val, string format)
    {
        if (val == null || val == DBNull.Value) return "";
        return string.IsNullOrEmpty(format) ? val.ToString() : string.Format("{0:" + format + "}", val);
    }

    private string GetNumberFormat(string format)
    {
        // 將 .NET 格式對應至 Excel 格式代碼
        return format switch
        {
            "yyyy-MM-dd" => "yyyy-mm-dd",
            "N2" => "#,##0.00",
            "C" => "$#,##0.00",
            "P0" => "0%",
            _ => "@" // 文字格式
        };
    }

    private Excel.XlHAlign MapAlignment(ExcelHorizontalAlignment align)
    {
        return align switch
        {
            ExcelHorizontalAlignment.Left => Excel.XlHAlign.xlHAlignLeft,
            ExcelHorizontalAlignment.Center => Excel.XlHAlign.xlHAlignCenter,
            ExcelHorizontalAlignment.Right => Excel.XlHAlign.xlHAlignRight,
            ExcelHorizontalAlignment.CenterAcrossSelection => Excel.XlHAlign.xlHAlignCenterAcrossSelection,
            _ => Excel.XlHAlign.xlHAlignGeneral
        };
    }

    private string GetColumnLetter(int colIndex)
    {
        string letter = "";
        while (colIndex > 0)
        {
            int rem = (colIndex - 1) % 26;
            letter = (char)('A' + rem) + letter;
            colIndex = (colIndex - 1) / 26;
        }
        return letter;
    }

    private void ReleaseCom(object obj)
    {
        if (obj != null) try { Marshal.ReleaseComObject(obj); } catch { }
    }
}

使用範例

// 1. 定義資料模型
public class SalesRecord
{
    public string Product { get; set; }
    public DateTime Date { get; set; }
    public int Quantity { get; set; }
    public decimal UnitPrice { get; set; }
    public decimal Amount => Quantity * UnitPrice;
}

// 2. 準備資料
var data = new List
{
    new() { Product = "筆記型電腦", Date = new DateTime(2024,1,15), Quantity = 5, UnitPrice = 25000 },
    new() { Product = "滑鼠", Date = new DateTime(2024,1,16), Quantity = 20, UnitPrice = 350 },
    new() { Product = "鍵盤", Date = new DateTime(2024,1,17), Quantity = 15, UnitPrice = 800 },
};

// 3. 設定匯出選項
var options = new ExcelExportOptions
{
    Title = "2024 年 1 月銷售明細表",
    SheetName = "銷售明細",
    Columns = new List
    {
        new() { PropertyName = "Product", HeaderText = "產品名稱", Width = 20 },
        new() { PropertyName = "Date", HeaderText = "銷售日期", Format = "yyyy-MM-dd", Alignment = ExcelHorizontalAlignment.Center, Width = 12 },
        new() { PropertyName = "Quantity", HeaderText = "數量", Alignment = ExcelHorizontalAlignment.Right, IsNumeric = true, Width = 8 },
        new() { PropertyName = "UnitPrice", HeaderText = "單價", Format = "N0", Alignment = ExcelHorizontalAlignment.Right, IsNumeric = true, Width = 10 },
        new() { PropertyName = "Amount", HeaderText = "金額", Format = "N0", Alignment = ExcelHorizontalAlignment.Right, IsNumeric = true, Width = 12 },
    },
    AddTotalRow = true,
    AutoFitColumns = true
};

// 4. 執行匯出
var exporter = new ExcelExporter();
exporter.ExportToFile(data, options, @"C:\Exports\Sales_202401.xlsx");

進階：DataTable / DataView 直接匯出

public void ExportDataTable(DataTable dt, ExcelExportOptions options, string filePath)
{
    // 將 DataTable 轉為 List> 或直接迭代 Rows
    // 此處略，邏輯與泛型版相同，改用 row[col.PropertyName] 取值
}

效能優化建議

大量資料 (>5000 列)：改用陣列批次寫入 Range.Value2 = object[,]，而非逐 Cell 指派。
關閉 UI 更新：excel.ScreenUpdating = false; 已在建立 Application 時設定。
避免迴圈存取 COM：樣式設定 (字型、邊框、底色) 盡量對整個 Range 一次套用，如標題列、彙總列。
記憶體管理：大量資料分批寫入、分批釋放 COM 物件、定期 GC.Collect()。

無 Excel 環境版本 (EPPlus)

// 同樣介面，內部改用 EPPlus
public class EpplusExporter
{
    public byte[] ExportToByteArray(IEnumerable data, ExcelExportOptions options)
    {
        using var pkg = new ExcelPackage();
        var ws = pkg.Workbook.Worksheets.Add(options.SheetName);
        // ... 邏輯類似，使用 ws.Cells[row, col].Style 設定樣式
        return pkg.GetAsByteArray();
    }
}