C# 向 Excel 報表插入圖片：兩種方法完整比較與封裝元件

前置需求

• 引用 COM：Microsoft Office 11.0 Object Library (Excel 2003)
• 或瀏覽加入：C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE
• 專案屬性 → 內嵌 Interop 類型：True (避免部署 PIA)

方法一：Pictures.Insert (舊版相容、簡單)

public void InsertPicture(string rangeName, string picturePath)
{
    var range = _worksheet.get_Range(rangeName, Missing.Value);
    range.Select();
    var pics = (Excel.Pictures)_worksheet.Pictures(Missing.Value);
    pics.Insert(picturePath, Missing.Value);
}

• 優點：程式碼簡單、相容舊版 Excel
• 缺點：無法精確控制圖片大小、位置；插入後圖片隨儲存格移動調整

方法二：Shapes.AddPicture (精確控制、現代推薦)

public void InsertPicture(string rangeName, string picturePath, float width, float height)
{
    var range = _worksheet.get_Range(rangeName, Missing.Value);
    float left = (float)range.Left;
    float top = (float)range.Top;

    _worksheet.Shapes.AddPicture(
        picturePath,
        Microsoft.Office.Core.MsoTriState.msoFalse, // 不連結檔案
        Microsoft.Office.Core.MsoTriState.msoTrue,  // 隨文件儲存
        left, top, width, height); // 座標單位：Points (1/72 inch)
}

• 優點：精確指定寬高、位置；支援不連結檔案（內嵌）；不隨儲存格調整
• 缺點：需計算 Points 座標；參數較多

完整封裝元件：ExcelReportHelper

using System;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

namespace MengXianhui.Utility.ExcelReport
{
    public class ExcelReportHelper : IDisposable
    {
        private Excel.Application _app;
        private Excel.Workbook _book;
        private Excel.Worksheet _sheet;
        private readonly object _missing = Missing.Value;

        public void Open(string templatePath = "")
        {
            _app = new Excel.Application { Visible = false, DisplayAlerts = false };
            _book = string.IsNullOrEmpty(templatePath)
                ? _app.Workbooks.Add(_missing)
                : _app.Workbooks.Open(templatePath, _missing, _missing, _missing, _missing,
                    _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing);
            _sheet = (Excel.Worksheet)_book.Worksheets[1];
        }

        public void InsertPicture(string cell, string imagePath, float? width = null, float? height = null)
        {
            var range = _sheet.get_Range(cell, _missing);
            if (width.HasValue && height.HasValue)
            {
                _sheet.Shapes.AddPicture(imagePath,
                    Microsoft.Office.Core.MsoTriState.msoFalse,
                    Microsoft.Office.Core.MsoTriState.msoTrue,
                    (float)range.Left, (float)range.Top, width.Value, height.Value);
            }
            else
            {
                range.Select();
                ((Excel.Pictures)_sheet.Pictures(_missing)).Insert(imagePath, _missing);
            }
        }

        public void Save(string filePath)
        {
            _book.SaveAs(filePath, _missing, _missing, _missing, _missing, _missing,
                Excel.XlSaveAsAccessMode.xlNoChange, _missing, _missing, _missing, _missing, _missing);
            Close();
        }

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

        private void Close()
        {
            _book?.Close(false, _missing, _missing);
            _app?.Quit();
        }

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

使用範例

using (var excel = new ExcelReportHelper())
{
    excel.Open();
    excel.InsertPicture("B2", @"C:\logo.gif");           // 方法一
    excel.InsertPicture("B8", @"C:\logo.gif", 120, 80);  // 方法二：寬 120pt、高 80pt
    excel.Save(@"C:\Report.xls");
}

執行結果示意

⚠️ 重要避坑指南

1. 務必釋放 COM 物件：Marshal.ReleaseComObject + GC.Collect，否則 Excel 進程殘留。
2. 絕對路徑：圖片路徑必須為完整實體路徑。
3. 版本相容：Interop 組件綁定特定 Office 版本，跨版本部署建議改用 OpenXML SDK 或 EPPlus (無需安裝 Office)。
4. 非 UI 執行緒：ASP.NET / 服務中使用需設定 Thread.SetApartmentState(ApartmentState.STA)。