萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> ASP.NET 程式設計

ASP.NET 導出 Excel 類別庫封裝:Microsoft.Office.Interop.Excel 完整包裝與最佳實踐

前言

雖然 不建議在 Web 伺服器使用 COM Interop (非執行緒安全、需安裝 Office、記憶體洩漏風險),但在「受控桌面環境」、「需完整 Excel 計算引擎/圖表/巨集」的特定場景仍有需求。本文提供一個完整封裝類別 ExcelClass,統一管理 Application/Workbook/Worksheet 生命週期、提供儲存格寫入/合併/邊框/樣式/插入列等高階 API,並示範正確釋放 COM 物件模式。

一、ExcelClass 完整程式碼

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

public class ExcelClass : IDisposable
{
    private readonly Excel.Application _app;
    private Excel.Workbook _book;
    private Excel.Worksheet _sheet;
    private readonly object _missing = Missing.Value;

    // 欄位字母對照 (支援超過 26 欄擴充)
    private static readonly string[] ColLetters = GenerateColumnLetters(1000);

    public ExcelClass(bool visible = false)
    {
        _app = new Excel.Application { Visible = visible, DisplayAlerts = false, ScreenUpdating = false };
    }

    public ExcelClass(Excel.Application existingApp)
    {
        _app = existingApp ?? throw new ArgumentNullException(nameof(existingApp));
    }

    // === 核心輔助 ===
    private static string[] GenerateColumnLetters(int max)
    {
        var list = new string[max];
        for (int i = 1; i <= max; i++) list[i - 1] = ColumnIndexToLetter(i);
        return list;
    }

    public static string ColumnIndexToLetter(int colIndex) // 1-based
    {
        string s = "";
        while (colIndex > 0)
        {
            int rem = (colIndex - 1) % 26;
            s = (char)('A' + rem) + s;
            colIndex = (colIndex - 1) / 26;
        }
        return s;
    }

    private string GetAddress(int col, int row) // col, row 1-based
    => $"{ColLetters[col - 1]}{row}";

    private Excel.Range Range(int col1, int row1, int col2, int row2)
    => _sheet.get_Range(GetAddress(col1, row1), GetAddress(col2, row2));

    // === 生命週期管理 ===
    public void CreateNewWorkbook(int sheetsCount = 1)
    {
        _book = _app.Workbooks.Add(_missing);
        while (_book.Worksheets.Count < sheetsCount)
            _book.Worksheets.Add(_missing, _missing, _missing, _missing);
        _sheet = (Excel.Worksheet)_book.Worksheets[1];
    }

    public void OpenWorkbook(string path, bool readOnly = false)
    {
        _book = _app.Workbooks.Open(path, _missing, readOnly, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing);
        _sheet = (Excel.Worksheet)_book.Worksheets[1];
    }

    public void SelectSheet(int indexOrName)
    {
        _sheet = (Excel.Worksheet)_book.Worksheets[indexOrName];
    }

    public void SaveAs(string fullPath, Excel.XlFileFormat format = Excel.XlFileFormat.xlOpenXMLWorkbook)
    {
        _book.SaveAs(fullPath, format, _missing, _missing, _missing, _missing, Excel.XlSaveAsAccessMode.xlNoChange, _missing, _missing, _missing, _missing, _missing);
    }

    public void CloseWorkbook(bool saveChanges = false)
    {
        _book?.Close(saveChanges, _missing, _missing);
        _book = null;
        _sheet = null;
    }

    public void Quit()
    {
        CloseWorkbook(false);
        _app.Quit();
        ReleaseCom(_app);
    }

    // === 儲存格操作 API ===
    public void SetValue(int row, int col, object value, string align = null, Font font = null, Color? color = null)
    {
        Excel.Range rng = Range(col, row, col, row);
        rng.Value2 = value;

        if (!string.IsNullOrEmpty(align))
        {
            rng.HorizontalAlignment = align.ToUpper() switch
            {
                "CENTER" => Excel.XlHAlign.xlHAlignCenter,
                "LEFT" => Excel.XlHAlign.xlHAlignLeft,
                "RIGHT" => Excel.XlHAlign.xlHAlignRight,
                _ => rng.HorizontalAlignment
            };
        }

        if (font != null)
        {
            rng.Font.Name = font.Name;
            rng.Font.Size = font.Size;
            rng.Font.Bold = font.Bold;
            rng.Font.Italic = font.Italic;
            rng.Font.Underline = font.Underline ? Excel.XlUnderlineStyle.xlUnderlineStyleSingle : Excel.XlUnderlineStyle.xlUnderlineStyleNone;
            if (color.HasValue) rng.Font.Color = ColorTranslator.ToOle(color.Value);
        }
    }

    public void MergeCells(int row1, int col1, int row2, int col2)
    {
        Range(col1, row1, col2, row2).Merge();
    }

    public void SetBorder(int row1, int col1, int row2, int col2, Excel.XlLineStyle lineStyle = Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight weight = Excel.XlBorderWeight.xlThin)
    {
        Excel.Range rng = Range(col1, row1, col2, row2);
        foreach (Excel.XlBordersIndex idx in new[] { Excel.XlBordersIndex.xlEdgeLeft, Excel.XlBordersIndex.xlEdgeTop, Excel.XlBordersIndex.xlEdgeBottom, Excel.XlBordersIndex.xlEdgeRight, Excel.XlBordersIndex.xlInsideHorizontal, Excel.XlBordersIndex.xlInsideVertical })
        {
            rng.Borders[idx].LineStyle = lineStyle;
            rng.Borders[idx].Weight = weight;
        }
    }

    public void SetColumnWidth(int col, double width)
    {
        Range(col, 1, col, 1).EntireColumn.ColumnWidth = width;
    }

    public void InsertRow(int rowIndex, int copyFromRow = 1)
    {
        Excel.Range src = Range(1, copyFromRow, ColLetters.Length, copyFromRow);
        Excel.Range dest = Range(1, rowIndex, ColLetters.Length, rowIndex);
        src.Copy(dest);
        dest.Insert(Excel.XlInsertShiftDirection.xlShiftDown, _missing);
    }

    // === COM 物件釋放 ===
    private void ReleaseCom(object obj)
    {
        if (obj != null && System.Runtime.InteropServices.Marshal.IsComObject(obj))
        {
            try { System.Runtime.InteropServices.Marshal.ReleaseComObject(obj); } catch { }
        }
    }

    public void Dispose()
    {
        ReleaseCom(_sheet);
        ReleaseCom(_book);
        ReleaseCom(_app);
        GC.Collect();
        GC.WaitForPendingFinalizers();
    }

    // 公開內部物件供進階操作
    public Excel.Application App => _app;
    public Excel.Workbook Book => _book;
    public Excel.Worksheet Sheet => _sheet;
}

二、使用範例:產出格式化報表

public byte[] GenerateReport(DataTable dt)
{
    string tempPath = Path.Combine(Path.GetTempPath(), $"Report_{Guid.NewGuid():N}.xlsx");
    using (var excel = new ExcelClass())
    {
        excel.CreateNewWorkbook();
        // 標題列
        for (int c = 0; c < dt.Columns.Count; c++)
            excel.SetValue(1, c + 1, dt.Columns[c].ColumnName, "CENTER", new Font("Calibri", 11, FontStyle.Bold), Color.Navy);

        // 資料
        for (int r = 0; r < dt.Rows.Count; r++)
            for (int c = 0; c < dt.Columns.Count; c++)
                excel.SetValue(r + 2, c + 1, dt.Rows[r][c]);

        excel.SetColumnWidth(1, 30); // 第一欄寬度
        excel.MergeCells(1, 1, 1, dt.Columns.Count); // 標題跨欄合併 (視需求)
        excel.SetBorder(1, 1, dt.Rows.Count + 1, dt.Columns.Count);

        excel.SaveAs(tempPath);
    }
    return File.ReadAllBytes(tempPath);
}

三、Web 環境部署警示與替代方案

⚠️ 重要警告:Microsoft.Office.Interop.Excel 在 ASP.NET/IIS 環境中 極不推薦 使用,原因:
  • Excel.exe 為單執行緒 STA 元件,ASP.NET 多執行緒會導致死鎖/當機。
  • 伺服器需安裝完整 Office,授權成本高、安全風險大。
  • 若程式異常未正確 Quit,殘留 Excel.exe 進程會耗盡記憶體。
  • Microsoft 官方明確聲明 不支援伺服器端自動化。
替代方案:請改用 NPOI / EPPlus / ClosedXML / Open XML SDK (純 managed code、無 Office 相依、高效能、支援雲端部署)。

四、適用場景 (僅限)

  • WinForms / WPF 桌面應用程式匯出報表。
  • 受控的單一使用者終端機,已安裝 Office,需使用 Excel 內建函數計算、圖表、VBA 巨集。
  • 批次離線轉檔服務 (Windows Service),非 Web 即時請求。
copyright © 萬盛學電腦網 all rights reserved