vb.net 使用 IDisposable 清理 Excel 互操作对象

Clean up Excel Interop Objects with IDisposable

提问by ruedi

In my company the common way to release Excel Interop Objects is to use IDisposablethe following way:

在我公司，发布 Excel Interop Objects 的常用方法是使用IDisposable以下方式：

Public Sub Dispose() Implements IDisposable.Dispose
    If Not bolDisposed Then
        Finalize()
        System.GC.SuppressFinalize(Me)
    End If
End Sub

Protected Overrides Sub Finalize()
    _xlApp = Nothing
    bolDisposed = True
    MyBase.Finalize()
End Sub

where _xlAppwas created in the constructor the following way:

where_xlApp是在构造函数中通过以下方式创建的：

Try
    _xlApp = CType(GetObject(, "Excel.Application"), Excel.Application)
Catch e As Exception
    _xlApp = CType(CreateObject("Excel.Application"), Excel.Application) 
End Try

And the client uses the using-statementto execute code concerning excel interop objects.

客户端使用using-statement来执行有关 excel 互操作对象的代码。

We completely avoid to use the two dot rule. Now I started researching how to release (Excel) Interop Objects and almost all discussions I found about it like How to properly clean up excel interop objectsor Release Excel Objectsare using mostly Marshal.ReleaseComObject(), none of them using the IDisposableinterface.

我们完全避免使用两点规则。现在我开始研究如何发布 (Excel) 互操作对象以及我发现的几乎所有关于它的讨论，比如如何正确清理 excel 互操作对象或发布 Excel 对象主要使用Marshal.ReleaseComObject()，但没有一个使用IDisposable界面。

My questions is: Are there any disadvantages using the IDisposableinterace for releasing excel interop objects? If so, what are these disadvantages.

我的问题是：使用IDisposableinterace发布excel互操作对象有什么缺点吗？如果是这样，这些缺点是什么。

回答by Hans Passant

Are there any disadvantages using the IDisposable Interace

使用 IDisposable Interace 有什么缺点吗

Sure, it accomplishes absolutely nothing. Using Usingor calling Dispose() is never an appropriate way to set a variable to Nothing. Which is all that your code does.

当然，它绝对没有完成任何事情。使用Using或调用 Dispose() 从来都不是将变量设置为 Nothing 的合适方法。这就是您的代码所做的一切。

We completely avoid to use the two dot rule.

我们完全避免使用两点规则。

Feel free to continue to ignore it, it is nonsense and causes nothing but grief. The blog author's implied assertion is that doing so would force the programmer to use a variable to store the value of xlApp.Workbooks. So he'd have a fighting chance, later, to not forget to call releaseObject(). But there are many more statements that produce an interface reference that don't use dots. Something like Range(x,y), there's a hidden Range object reference there that you'll never see. Having to store them as well just produces incredibly convoluted code.

随意继续忽略它，这是无稽之谈，只会引起悲伤。博客作者的隐含断言是，这样做会迫使程序员使用一个变量来存储 xlApp.Workbooks 的值。所以他以后会有机会，不要忘记调用 releaseObject()。但是还有更多的语句可以生成不使用点的接口引用。像 Range(x,y) 这样的东西，那里有一个你永远看不到的隐藏 Range 对象引用。不得不存储它们只会产生令人难以置信的错综复杂的代码。

And overlooking just oneis enough to completely fail to get the job done. Utterly impossible to debug. This is the kind of code that C programmers have to write. And often failed at miserably, large C programs often leak memory and their programmers spend a great deal of time finding those leaks. Not the .NET way of course, it has a garbage collector to do this automatically. It never gets it wrong.

仅仅忽略一个就足以完全无法完成工作。完全无法调试。这是 C 程序员必须编写的那种代码。并且经常惨遭失败，大型 C 程序经常泄漏内存，他们的程序员花费大量时间来查找这些泄漏。当然不是 .NET 方式，它有一个垃圾收集器来自动执行此操作。它永远不会出错。

Trouble is, it is a bit slow at taking care of the job. Very much by design. Nobody ever notices this, except in this kind of code. You can seethat the garbage collector didn't run, you still see the Office program running. It didn't quit when you wrote xlapp.Quit(), it is still present in the Processes tab of Task Manager. What they want to happen is for it to quit when they say so.

问题是，它在处理工作方面有点慢。非常设计。除了在这种代码中，没有人注意到这一点。您可以看到垃圾收集器没有运行，您仍然可以看到 Office 程序在运行。当您编写 xlapp.Quit() 时它并没有退出，它仍然存在于任务管理器的“进程”选项卡中。他们想要发生的是，当他们这么说时它就退出了。

That's very possible in .NET, you can certainly force the GC to get the job done:

这在 .NET 中很有可能，您当然可以强制 GC 完成工作：

GC.Collect()
GC.WaitForPendingFinalizers()

Boom, everyExcel object reference gets released automatically. There is no need to store these object references yourself and explicitly call Marshal.ReleaseComObject(), the CLR does it for you. And it never gets it wrong, it doesn't use or need a "two dot rule" and it has no trouble finding those hidden interface references back.

繁荣，每个Excel 对象引用都会自动释放。无需自己存储这些对象引用并显式调用 Marshal.ReleaseComObject()，CLR 会为您完成。它永远不会出错，它不使用或不需要“两点规则”，并且可以轻松找到那些隐藏的接口引用。

---

---

What matters a great deal however is exactlywhere you put this code. And most programmers put it in the wrong place, in the same method that used those Excel interfaces. Which is fine, but does not work when you debug the code, a quirk that's explained in this answer. The proper way to do it in the blog author's code is to move the code into a little helper method, let's call it DoExcelThing(). Like this:

然而什么重要的一个很大的正是你把这个代码。大多数程序员把它放在了错误的地方，使用的方法与使用那些 Excel 界面的方法相同。这很好，但在调试代码时不起作用，这个答案中解释了一个怪癖。在博客作者的代码中正确的做法是将代码移动到一个小助手方法中，我们称之为DoExcelThing()。像这样：

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    DoExcelThing()
    GC.Collect()
    GC.WaitForPendingFinalizers()
    '' Excel.exe no longer running anymore at this point
End Sub

And do keep in mind that this is truly all just a debugging artifact. Programmers just hate to have to use Task Manager to kill the zombie Excel.exe instances. Zombified when they stopped the debugger, preventing the program from exiting normally and collect garbage. This is normal. It will also happen when your program dies in production for any kind of reason. Put your energy where it belongs, getting the bugs out of your code so your program won't die. The GC doesn't need more help than that.

并且请记住，这真的只是一个调试工件。程序员只是讨厌不得不使用任务管理器来杀死僵尸 Excel.exe 实例。当他们停止调试器时被僵尸化，阻止程序正常退出并收集垃圾。这是正常的。当您的程序因任何原因在生产中死亡时，也会发生这种情况。把你的精力放在它所属的地方，从你的代码中清除错误，这样你的程序就不会死。GC 不需要更多帮助。

回答by toinetoine

If you're looking for a cleaner way, you can use Koogra. It's not much extra overhead (just two dll's your have to include in you're refrences) and you don't have to deal with implicit garbage collection.

如果您正在寻找更清洁的方式，您可以使用Koogra。它没有太多额外的开销（只有两个 dll 必须包含在您的引用中）并且您不必处理隐式垃圾收集。

The code bellow is all that's needed to read 10 rows of 10 columns from an excel file with no EXCEL32or Excel.exeprocesses left behind. I had this issue a month ago and wrote instructions on how to do it.So much easier and cleaner than dealing with Excel Interop directly.

下面的代码是从 Excel 文件中读取 10 行 10 列所需的全部代码，没有留下 EXCEL32或Excel.exe进程。一个月前我遇到了这个问题，并写了如何做的说明。比直接处理 Excel Interop 更容易、更干净。

Koogra.IWorkbook workbook = Koogra.WorkbookFactory.GetExcel2007Reader("MyExcelFile.xlsx");
Net.SourceForge.Koogra.IWorksheet worksheet = workbook.Worksheets.GetWorksheetByName("Sheet1");

//This will invididually print out to the Console the columns A-J (10 columns) for rows 1-10.
for (uint rowIndex = 1; rowIndex <= 10; rowIndex++)
{
    for (uint columnIndex = 1; columnIndex <= 10; columnIndex++)
    {
        Console.WriteLine(worksheet.Rows.GetRow(rowIndex).GetCell(columnIndex).GetFormattedValue());
    }
}