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