使用Microsoft.Office.Interop.Excel后Excel文档重复打开的问题解决

我的应用运行流程如下：

• 获取用户凭据
• 使用凭据将SharePoint站点的.xls文档下载至临时文件
• 通过Microsoft.Office.Interop.Excel在后台处理该临时文件，解析出日程信息后更新用户的Outlook日历（同样使用Interop）

解析Excel的核心方法代码如下：

private static List<AppointmentDetails> ParseXls(string localSchedulePath, string initials) {
    List<AppointmentDetails> list = new List<AppointmentDetails>();
    List<string> alreadyCounted = new List<string>();
    Excel.Application excel = new Excel.Application();
    Excel.Workbook workbook = excel.Workbooks.Open(localSchedulePath);
    Excel.Worksheet worksheet = workbook.Sheets["On Call"];
    Excel.Range schedule = worksheet.Range["A1", "P369"];
    foreach (Excel.Range row in schedule.Rows) {
        foreach (Excel.Range cell in row.Cells) {
            if (cell.Value == null || alreadyCounted.Contains(IndexToExcel(cell.Row, cell.Column))) continue;
            if (cell.Value.ToString().Contains(initials)) {
                List<int> rows = new List<int>();
                int rowNum = cell.Row;
                int colNum = cell.Column;
                rows.Add(rowNum);
                alreadyCounted.Add(IndexToExcel(rowNum, colNum));
                while (schedule.Range[IndexToExcel(++rowNum, colNum)].Value != null && schedule.Range[IndexToExcel(rowNum, colNum)].Value.ToString().Contains(initials)) {
                    alreadyCounted.Add(IndexToExcel(rowNum, colNum));
                    rows.Add(rowNum);
                }
                string regionCode = worksheet.Range[IndexToExcel(1, colNum)].Value.ToString();
                list.Add(new AppointmentDetails {
                    StartDate = DateTime.Parse(worksheet.Range[IndexToExcel(rows[0], 2)].Value.ToString()),
                    EndDate = DateTime.Parse(worksheet.Range[IndexToExcel(rows[rows.Count - 1], 2)].Value.ToString()),
                    Region = regionCode
                });
            }
        }
    }
    return list;
}

核心功能正常，日历可以成功更新，但存在一个异常：当用户从SharePoint站点手动打开该.xls文档时，之前下载的临时文件也会随之打开（二者处于独立的Excel窗口）。虽不影响核心功能，但希望解决这个问题，请问如何修复？

修复方案 #

我之前碰到过类似的Interop Excel关联问题，大概率是COM资源没清理干净，残留的Excel进程持有临时文件的引用，导致打开原文件时触发了关联。下面是具体的修复步骤：

1. 彻底清理Interop COM资源，避免残留Excel进程 #

.NET的垃圾回收器不会自动清理Interop的COM对象，如果你只创建对象不释放，会残留Excel进程在后台运行，它会一直持有临时文件的引用。修改你的ParseXls方法，按逆序释放所有COM对象：

// 记得添加这个引用
using System.Runtime.InteropServices;

private static List<AppointmentDetails> ParseXls(string localSchedulePath, string initials) {
    List<AppointmentDetails> list = new List<AppointmentDetails>();
    List<string> alreadyCounted = new List<string>();
    Excel.Application excel = null;
    Excel.Workbook workbook = null;
    Excel.Worksheet worksheet = null;
    Excel.Range schedule = null;

    try {
        excel = new Excel.Application();
        // 后台运行，不显示界面，避免干扰用户
        excel.Visible = false;
        excel.DisplayAlerts = false;

        // 以只读、无通知的方式打开临时文件，减少关联风险
        workbook = excel.Workbooks.Open(
            Filename: localSchedulePath,
            ReadOnly: true,
            Notify: false,
            IgnoreReadOnlyRecommended: true
        );
        worksheet = workbook.Sheets["On Call"];
        schedule = worksheet.Range["A1", "P369"];

        // 原有的解析逻辑保持不变
        foreach (Excel.Range row in schedule.Rows) {
            foreach (Excel.Range cell in row.Cells) {
                if (cell.Value == null || alreadyCounted.Contains(IndexToExcel(cell.Row, cell.Column))) continue;
                if (cell.Value.ToString().Contains(initials)) {
                    List<int> rows = new List<int>();
                    int rowNum = cell.Row;
                    int colNum = cell.Column;
                    rows.Add(rowNum);
                    alreadyCounted.Add(IndexToExcel(rowNum, colNum));
                    while (schedule.Range[IndexToExcel(++rowNum, colNum)].Value != null && schedule.Range[IndexToExcel(rowNum, colNum)].Value.ToString().Contains(initials)) {
                        alreadyCounted.Add(IndexToExcel(rowNum, colNum));
                        rows.Add(rowNum);
                    }
                    string regionCode = worksheet.Range[IndexToExcel(1, colNum)].Value.ToString();
                    list.Add(new AppointmentDetails {
                        StartDate = DateTime.Parse(worksheet.Range[IndexToExcel(rows[0], 2)].Value.ToString()),
                        EndDate = DateTime.Parse(worksheet.Range[IndexToExcel(rows[rows.Count - 1], 2)].Value.ToString()),
                        Region = regionCode
                    });
                }
            }
        }
    } finally {
        // 按创建的逆序释放COM对象
        if (schedule != null) {
            Marshal.ReleaseComObject(schedule);
        }
        if (worksheet != null) {
            Marshal.ReleaseComObject(worksheet);
        }
        if (workbook != null) {
            // 不保存临时文件的修改，直接关闭
            workbook.Close(false);
            Marshal.ReleaseComObject(workbook);
        }
        if (excel != null) {
            excel.Quit();
            Marshal.ReleaseComObject(excel);
        }
        // 强制触发垃圾回收，确保COM对象被彻底清理
        GC.Collect();
        GC.WaitForPendingFinalizers();
    }
    return list;
}

2. 处理完临时文件后立即删除 #

如果你的应用后续不需要这个临时文件，在ParseXls方法执行完成后，立即删除它：

// 调用ParseXls之后
var appointments = ParseXls(tempFilePath, userInitials);
// 确保文件没被占用（第一步的资源清理必须做对）
File.Delete(tempFilePath);

这样临时文件被彻底删除，就不会被后续的操作关联打开了。

3. 检查Excel的最近文件关联（可选） #

如果前两步还没解决问题，可以让用户检查Excel的“最近使用文件”列表，清除临时文件的记录；或者关闭SharePoint的离线同步功能，避免原文件和临时文件产生同步关联。

内容的提问来源于stack exchange，提问作者ScottishTapWater