在C#中安全地处理Excel互操作对象

本文关键字:Excel 互操作 对象 处理 安全 | 更新日期: 2023-09-27 18:29:42

我正在开发一个winforms c#visualstudio2008应用程序。该应用程序可以与excel文件进行对话,我正在使用Microsoft.Office.Interop.Excel;来实现这一点。

我想知道,即使出现错误,我如何确保对象被释放?

这是我的代码:

private void button1_Click(object sender, EventArgs e)
{
    string myBigFile="";
    OpenFileDialog openFileDialog1 = new OpenFileDialog();
    DialogResult result = openFileDialog1.ShowDialog(); // Show the dialog.
    if (result == DialogResult.OK) // Test result.
        myBigFile=openFileDialog1.FileName;
    Excel.Application xlApp;
    Excel.Workbook xlWorkBook;
    Excel.Worksheet xlWorkSheet;
    Excel.Range range;
    string str;
    int rCnt = 0;
    int cCnt = 0;
    xlApp = new Excel.ApplicationClass();
    xlWorkBook = xlApp.Workbooks.Open(myBigFile, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "'t", true, false, 0, true, 1, 0);
    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
    range = xlWorkSheet.UsedRange;
    /*
    for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
    {
        for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
        {
            str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
            MessageBox.Show(str);
        }
    }
     */
    xlWorkSheet..EntireRow.Delete(Excel.XLDirection.xlUp)
    xlWorkBook.SaveAs(xlWorkBook.Path + @"'XMLCopy.xls",         Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing,
   false, false, Excel.XlSaveAsAccessMode.xlNoChange,
   Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    xlWorkBook.Close(true, null, null);
    xlApp.Quit();
    releaseObject(xlWorkSheet);
    releaseObject(xlWorkBook);
    releaseObject(xlApp);
}
private void releaseObject(object obj)
{
    try
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        obj = null;
    }
    catch (Exception ex)
    {
        obj = null;
        MessageBox.Show("Unable to release the Object " + ex.ToString());
    }
    finally
    {
        GC.Collect();
    }
}

我如何确保即使在工作簿打开后出现错误,我也能确保处理对象:

Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range range;

换句话说,无论我需要以下几行来运行

xlWorkBook.Close(true, null, null);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);

请注意,我也尝试过,导致了相同的问题

xlWorkBook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);

                xlApp.Quit();
                Marshal.ReleaseComObject(xlWorkSheet);
                Marshal.ReleaseComObject(xlWorkBook);
                Marshal.ReleaseComObject(xlApp);
                xlWorkSheet = null;
                xlWorkBook = null;
                xlApp = null;
                GC.GetTotalMemory(false);
                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();
                GC.GetTotalMemory(true);  

我也这样做了:

GC.Collect()                   ;
                GC.WaitForPendingFinalizers();
                GC.Collect()                  ; 
                GC.WaitForPendingFinalizers();
                Marshal.FinalReleaseComObject(xlWorkSheet);
                xlWorkBook.Close(Type.Missing, Type.Missing, Type.Missing);
                Marshal.FinalReleaseComObject(xlWorkBook); 
                xlApp.Quit();
                Marshal.FinalReleaseComObject(xlApp); 

在这一点上,我认为从visualstudio2008关闭excel是不可能的。这一定是一个bug或什么的,但我已经尝试了这方面的前20个网站,得到了相同的结果:excel由于某种原因打开了两个实例,当我进行垃圾收集等操作时。(或不)它只关闭了一个实例。

当我试图打开文件时,它会显示有错误或已损坏。

当我去任务管理器并终止excel进程时,文件将毫无问题地打开。]

有没有办法用visualstudio2008关闭excel?如果是的话,你能为我提供这个的指导或解决方案吗

在C#中安全地处理Excel互操作对象

首先我将呈现一个修改后的releaseObject,然后我将提供一个使用它的模式。

using Marshal = System.Runtime.InteropServices.Marshal;
private void releaseObject(ref object obj) // note ref!
{
    // Do not catch an exception from this.
    // You may want to remove these guards depending on
    // what you think the semantics should be.
    if (obj != null && Marshal.IsComObject(obj)) {
        Marshal.ReleaseComObject(obj);
    }
    // Since passed "by ref" this assingment will be useful
    // (It was not useful in the original, and neither was the
    //  GC.Collect.)
    obj = null;
}

现在,使用一种模式:

private void button1_Click(object sender, EventArgs e)
{
    // Declare. Assign a value to avoid a compiler error.
    Excel.Application xlApp = null;
    Excel.Workbook xlWorkBook = null;
    Excel.Worksheet xlWorkSheet = null;
    try {
        // Initialize
        xlApp = new Excel.ApplicationClass();
        xlWorkBook = xlApp.Workbooks.Open(myBigFile, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "'t", true, false, 0, true, 1, 0);
        // If the cast fails this like could "leak" a COM RCW
        // Since this "should never happen" I wouldn't worry about it.
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
        ...
    } finally {
        // Release all COM RCWs.
        // The "releaseObject" will just "do nothing" if null is passed,
        // so no need to check to find out which need to be released.
        // The "finally" is run in all cases, even if there was an exception
        // in the "try". 
        // Note: passing "by ref" so afterwords "xlWorkSheet" will
        // evaluate to null. See "releaseObject".
        releaseObject(ref xlWorkSheet);
        releaseObject(ref xlWorkBook);
        // The Quit is done in the finally because we always
        // want to quit. It is no different than releasing RCWs.
        if (xlApp != null) {
            xlApp.Quit();
        }
        releaseObject(ref xlApp);    
    }
}

这种简单的方法可以在大多数情况下进行扩展/嵌套。我使用了一个实现IDisposable的自定义包装器类来简化这项任务。

验证您在代码中看到的两个问题:

  • 当程序关闭时,Excel仍作为一个正在运行的进程
  • 当你打开程序创建的Excel文件时,你会看到Excel中的错误,称文件已损坏或类似

我将您编辑的问题中的button1点击处理程序和pst的releaseObject方法复制到一个干净的VS2008,C#3.5 Winform应用程序中,并进行了一些小的更改以消除我上面列出的两个问题。

要修复Excel未从内存中卸载的问题,请对您创建的range对象调用releaseObject。在调用releaseObject(xlWorkSheet);之前执行此操作记住所有这些引用是COM互操作编程如此有趣的原因。

若要修复损坏的Excel文件问题,请更新WorkBook.SaveAs方法调用,将第二个参数(Excel.XlFileFormat.xlXMLSpreadsheet)替换为Type.Missing。默认情况下,SaveAs方法将正确处理此问题。

我相信你在问题中发布的代码会被简化,以帮助调试你遇到的问题。您应该使用pst演示的try..finally块。