在 C# 中安全地处理 Excel 互操作对象？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9962157/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Safely disposing Excel interop objects in C#?
提问by l--''''''---------''''''''''''
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。它一定是一个错误或其他东西，但我已经尝试了前 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？如果是这样，您能否为我提供指导或解决方案
回答by l--''''''---------''''''''''''
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 的自定义包装类来简化此任务。
回答by Jay Riggs
Verify that there are two problems you're seeing in your code:
验证您在代码中看到的两个问题：
- That when the program closes Excel remains as a running process
- 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 文件时，您会在 Excel 中看到一个错误，指出该文件已损坏或诸如此类
I copied the button1 click handler and pst's releaseObjectmethod 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.
我将releaseObject您编辑的问题中的 button1 单击处理程序和 pst 的方法复制到一个干净的 VS2008、C#3.5 Winform 应用程序中，并进行了一些小的更改以消除我上面列出的两个问题。
To fix Excel not unloading from memory, call releaseObjecton the rangeobject 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的range对象。在您调用releaseObject(xlWorkSheet);记住所有这些引用之前执行此操作是使 COM Interop 编程如此有趣的原因。
To fix the corrupt Excel file problem update your WorkBook.SaveAsmethod call to replace the second parameter (Excel.XlFileFormat.xlXMLSpreadsheet) with Type.Missing. The SaveAsmethod will handle this correctly by default.
要修复损坏的 Excel 文件问题，请更新您的WorkBook.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..finallyblock pst demonstrates.
我确定您在问题中发布的代码已被简化，以帮助调试您遇到的问题。您应该使用try..finally块 pst 演示。
