在c#中安全地配置Excel互操作对象?

i am working on a winforms c# visual studio 2008 application. the app talks to excel files and i am using Microsoft.Office.Interop.Excel; to do this.

我正在开发winforms c# visual studio 2008应用程序。这个应用程序是针对excel文件的，我使用的是Microsoft.Office.Interop.Excel;来做到这一点。

i would like to know how can i make sure that the objects are released even when there is an error?

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

here's my code:

这是我的代码:

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();
    }
}

how can i make sure that even if i get an error after the workbook opened, that i make sure to dispose of the objects:

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

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

In other words no matter what i need the following lines to run

换句话说，无论我需要什么，我都要运行以下代码行

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

releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);

please note that i have tried this as well, resulting in the same issue

请注意，我也尝试过，导致了同样的问题

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);  

and i did this as well:

我也这么做了:

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); 

at this point i do not think it's possible to close excel from visual studio 2008. it must be a bug or something, but i've tried the top 20 websites on this and getting the same result: excel is opening two instances for some reason and when i do the garbage collection etc.. (or not) it closes just ONE instance.

此时，我不认为可以从visual studio 2008中关闭excel。它一定是一个bug或者什么东西，但是我尝试了这上面的前20个网站并得到了相同的结果:excel由于某种原因打开了两个实例，当我做垃圾收集时……它只关闭一个实例。

when i try to open the file, it says there's an error or it's corrupt.

当我试图打开文件时，它会说有错误或它损坏了。

when i go to task manager and kill the excel process, the file will open without problems.]

当我去任务管理器并杀死excel进程时，文件将会打开，没有问题。

is there a way to close excel with visual studio 2008? if so, can you please provide me with guidance or a solution to this

是否有办法关闭visual studio 2008中的excel ?如果是这样的话，你能给我提供指导或者解决这个问题吗?

2 个解决方案

#1

First I will present a modified releaseObject, and then I will provide a pattern to use it.

首先，我将展示一个经过修改的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;
}

Now, a pattern to use:

现在，使用一种模式:

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);    
    }
}

This simple approach can be extended/nested over most situations. I use a custom wrapper class that implements IDisposable to make this task easier.

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

#2

Verify that there are two problems you're seeing in your code:

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

• That when the program closes Excel remains as a running process
• 当程序关闭Excel时，它仍然是一个正在运行的进程
• That when you open the Excel file your program creates you see an error in Excel saying the file is corrupted or some such
• 当你打开Excel文件时你的程序会在Excel中创建一个错误说文件被损坏了或者类似的

I copied the button1 click handler and pst's releaseObject method in your edited question into a clean VS2008, C#3.5 Winform application and made a couple minor changes to eliminate both the problems I listed above.

在您编辑的问题中，我将button1 click处理程序和pst的releaseObject方法复制到一个干净的VS2008、c# 3.5 Winform应用程序中，并做了一些小小的修改，以消除上面列出的两个问题。

To fix Excel not unloading from memory, call releaseObject on the range object you created. Do this before your call to releaseObject(xlWorkSheet); Remembering all these references is what makes COM Interop programming so much fun.

要修复Excel不从内存中卸载，请在创建的范围对象上调用releaseObject。在调用releaseObject(xlWorkSheet)之前执行此操作;记住所有这些引用是COM Interop编程如此有趣的原因。

To fix the corrupt Excel file problem update your WorkBook.SaveAs method call to replace the second parameter (Excel.XlFileFormat.xlXMLSpreadsheet) with Type.Missing. The SaveAs method will handle this correctly by default.

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

I'm sure the code you posted in your question is simplified to help debug the problems you're having. You should use the try..finally block pst demonstrates.

我确信您在问题中所发布的代码已经被简化以帮助调试您所遇到的问题。你应该试试。finally块pst演示。