我在C#(ApplicationClass)中使用Excel互操作,并在finally子句中放置了以下代码:
while (System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheet) != 0) { }
excelSheet = null;
GC.Collect();
GC.WaitForPendingFinalizers();
尽管这种方法有效,但即使在我关闭Excel之后,Excel.exe进程仍处于后台。它只在我的应用程序被手动关闭后发布。
我做错了什么,或者是否有其他方法可以确保正确处理互操作对象?
我找到了一个有用的通用模板,它可以帮助实现COM对象的正确处置模式,这些对象在超出范围时需要调用Marshal.ReleaseComObject:
用法:
using (AutoReleaseComObject<Application> excelApplicationWrapper = new AutoReleaseComObject<Application>(new Application()))
{
try
{
using (AutoReleaseComObject<Workbook> workbookWrapper = new AutoReleaseComObject<Workbook>(excelApplicationWrapper.ComObject.Workbooks.Open(namedRangeBase.FullName, false, false, missing, missing, missing, true, missing, missing, true, missing, missing, missing, missing, missing)))
{
// do something with your workbook....
}
}
finally
{
excelApplicationWrapper.ComObject.Quit();
}
}
模板:
public class AutoReleaseComObject<T> : IDisposable
{
private T m_comObject;
private bool m_armed = true;
private bool m_disposed = false;
public AutoReleaseComObject(T comObject)
{
Debug.Assert(comObject != null);
m_comObject = comObject;
}
#if DEBUG
~AutoReleaseComObject()
{
// We should have been disposed using Dispose().
Debug.WriteLine("Finalize being called, should have been disposed");
if (this.ComObject != null)
{
Debug.WriteLine(string.Format("ComObject was not null:{0}, name:{1}.", this.ComObject, this.ComObjectName));
}
//Debug.Assert(false);
}
#endif
public T ComObject
{
get
{
Debug.Assert(!m_disposed);
return m_comObject;
}
}
private string ComObjectName
{
get
{
if(this.ComObject is Microsoft.Office.Interop.Excel.Workbook)
{
return ((Microsoft.Office.Interop.Excel.Workbook)this.ComObject).Name;
}
return null;
}
}
public void Disarm()
{
Debug.Assert(!m_disposed);
m_armed = false;
}
#region IDisposable Members
public void Dispose()
{
Dispose(true);
#if DEBUG
GC.SuppressFinalize(this);
#endif
}
#endregion
protected virtual void Dispose(bool disposing)
{
if (!m_disposed)
{
if (m_armed)
{
int refcnt = 0;
do
{
refcnt = System.Runtime.InteropServices.Marshal.ReleaseComObject(m_comObject);
} while (refcnt > 0);
m_comObject = default(T);
}
m_disposed = true;
}
}
}
参考:
http://www.deez.info/sengelha/2005/02/11/useful-idisposable-class-3-autoreleasecomobject/
正如其他人所指出的,您需要为使用的每个Excel对象创建一个显式引用,并对该引用调用Marshal.ReleaseComObject,如本知识库文章所述。您还需要使用try/finally来确保始终调用ReleaseComObject,即使抛出异常也是如此。即,代替:
Worksheet sheet = excelApp.Worksheets(1)
... do something with sheet
你需要做一些事情,比如:
Worksheets sheets = null;
Worksheet sheet = null
try
{
sheets = excelApp.Worksheets;
sheet = sheets(1);
...
}
finally
{
if (sheets != null) Marshal.ReleaseComObject(sheets);
if (sheet != null) Marshal.ReleaseComObject(sheet);
}
如果要关闭Excel,还需要在释放Application对象之前调用Application.Quit。
正如您所看到的,只要您尝试做任何稍微复杂的事情,这很快就会变得非常笨拙。我用一个简单的包装类成功地开发了.NET应用程序,该类包装了Excel对象模型的一些简单操作(打开工作簿、写入范围、保存/关闭工作簿等)。包装器类实现IDisposable,在它使用的每个对象上仔细地实现Marshal.ReleaseComObject,并且不向应用程序的其他部分公开任何Excel对象。
但这种方法不能很好地适应更复杂的需求。
这是.NETCOM互操作的一大缺陷。对于更复杂的场景,我会认真考虑用VB6或其他非托管语言编写ActiveX DLL,您可以将与进程外COM对象(如Office)的所有交互委托给它。然后,您可以从.NET应用程序中引用此ActiveX DLL,因为您只需要发布这一个引用,所以事情会变得更加简单。
尝试之后
按相反顺序释放COM对象在末尾添加两次GC.Collect()和GC.WaitForPendingFinalizers()不超过两个点关闭工作簿并退出应用程序以释放模式运行
对我有效的最终解决方案是移动一组
GC.Collect();
GC.WaitForPendingFinalizers();
我们将其添加到包装器的函数末尾,如下所示:
private void FunctionWrapper(string sourcePath, string targetPath)
{
try
{
FunctionThatCallsExcel(sourcePath, targetPath);
}
finally
{
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
为了说明Excel不关闭的原因,即使在读取和创建时为每个对象创建直接引用,也需要使用“For”循环。
For Each objWorkBook As WorkBook in objWorkBooks 'local ref, created from ExcelApp.WorkBooks to avoid the double-dot
objWorkBook.Close 'or whatever
FinalReleaseComObject(objWorkBook)
objWorkBook = Nothing
Next
'The above does not work, and this is the workaround:
For intCounter As Integer = 1 To mobjExcel_WorkBooks.Count
Dim objTempWorkBook As Workbook = mobjExcel_WorkBooks.Item(intCounter)
objTempWorkBook.Saved = True
objTempWorkBook.Close(False, Type.Missing, Type.Missing)
FinalReleaseComObject(objTempWorkBook)
objTempWorkBook = Nothing
Next