使用 VB.NET 处理 Excel com 对象的正确方法？

The proper way to dispose Excel com object using VB.NET?

提问by Pan Pizza

I have following code (obtained from online tutorial). The code is working but I suspect the way to dispose the Excel com object is somewhat not proper. Do we need really need to call GC.Collect? Or what is the best way to dispose this Excel com object?

我有以下代码（从在线教程中获得）。该代码正在运行，但我怀疑处理 Excel com 对象的方式有些不正确。我们真的需要调用 GC.Collect 吗？或者处理这个 Excel com 对象的最佳方法是什么？

Public Sub t1()
    Dim oExcel As New Excel.Application
    Dim oBook As Excel.Workbook = oExcel.Workbooks.Open(TextBox2.Text)

    'select WorkSheet based on name
    Dim oWS As Excel.Worksheet = CType(oBook.Sheets("Sheet1"), Excel.Worksheet)
    Try

        oExcel.Visible = False
        'now showing the cell value
        MessageBox.Show(oWS.Range(TextBox6.Text).Text)

        oBook.Close()
        oExcel.Quit()

        releaseObject(oExcel)
        releaseObject(oBook)
        releaseObject(oWS)
    Catch ex As Exception
        MsgBox("Error: " & ex.ToString, MsgBoxStyle.Critical, "Error!")
    End Try
End Sub

Private Sub releaseObject(ByVal obj As Object)
    Try
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
        obj = Nothing
    Catch ex As Exception
        obj = Nothing
    Finally
        GC.Collect()
    End Try
End Sub

回答by Govert

First - you neverhave to call Marshal.ReleaseComObject(...)or Marshal.FinalReleaseComObject(...)when doing Excel interop. It is a confusing anti-pattern, but any information about this, including from Microsoft, that indicates you have to manually release COM references from .NET is incorrect. The fact is that the .NET runtime and garbage collector correctly keep track of and clean up COM references. For your code, this means you can remove the whole releaseObject(...)Sub and calls to it.

首先 - 您永远不必打电话Marshal.ReleaseComObject(...)或Marshal.FinalReleaseComObject(...)在执行 Excel 互操作时。这是一个令人困惑的反模式，但是任何关于此的信息（包括来自 Microsoft 的信息）表明您必须从 .NET 手动释放 COM 引用都是不正确的。事实是 .NET 运行时和垃圾收集器正确地跟踪和清理 COM 引用。对于您的代码，这意味着您可以删除整个releaseObject(...)Sub 并调用它。

Second, if you want to ensure that the COM references to an out-of-process COM object is cleaned up when your process ends (so that the Excel process will close), you need to ensure that the Garbage Collector runs. You do this correctly with calls to GC.Collect()and GC.WaitForPendingFinalizers(). Calling twice is safe, end ensures that cycles are definitely cleaned up too.

其次，如果要确保在进程结束时清除对进程外 COM 对象的 COM 引用（以便 Excel 进程将关闭），则需要确保垃圾收集器运行。您可以通过调用GC.Collect()和正确执行此操作GC.WaitForPendingFinalizers()。调用两次是安全的， end 确保循环也被清理干净。

Third, when running under the debugger, local references will be artificially kept alive until the end of the method (so that local variable inspection works). So a GC.Collect()calls are not effective for cleaning object like rng.Cellsfrom the same method. You should split the code doing the COM interop from the GC cleanup into separate methods.

第三，在调试器下运行时，局部引用将人为地保持活动状态直到方法结束（以便局部变量检查工作）。因此，GC.Collect()调用对于rng.Cells从同一方法中清除对象无效。您应该将执行 COM 互操作的代码从 GC 清理拆分为单独的方法。

The general pattern would be:

一般模式是：

Sub WrapperThatCleansUp()

    ' NOTE: Don't call Excel objects in here... 
    '       Debugger would keep alive until end, preventing GC cleanup

    ' Call a separate function that talks to Excel
    DoTheWork()

    ' Now Let the GC clean up (twice, to clean up cycles too)
    GC.Collect()    
    GC.WaitForPendingFinalizers()
    GC.Collect()    
    GC.WaitForPendingFinalizers()

End Sub

Sub DoTheWork()
    Dim app As New Microsoft.Office.Interop.Excel.Application
    Dim book As Microsoft.Office.Interop.Excel.Workbook = app.Workbooks.Add()
    Dim worksheet As Microsoft.Office.Interop.Excel.Worksheet = book.Worksheets("Sheet1")
    app.Visible = True
    For i As Integer = 1 To 10
        worksheet.Cells.Range("A" & i).Value = "Hello"
    Next
    book.Save()
    book.Close()
    app.Quit()

    ' NOTE: No calls the Marshal.ReleaseComObject() are ever needed
End Sub

There is a lot of false information and confusion about this issue, including many posts on MSDN and on StackOverflow.

关于这个问题有很多虚假信息和混淆，包括 MSDN 和 StackOverflow 上的许多帖子。

What finally convinced me to have a closer look and figure out the right advice was this post https://blogs.msdn.microsoft.com/visualstudio/2010/03/01/marshal-releasecomobject-considered-dangerous/together with finding the issue with references kept alive under the debugger on some StackOverflow answer.

最终说服我仔细研究并找出正确建议的是这篇文章https://blogs.msdn.microsoft.com/visualstudio/2010/03/01/marshal-releasecomobject-thinked-dangerous/以及找到在某些 StackOverflow 答案上的调试器下引用保持活动的问题。

回答by Seph

@PanPizza C# and VB.NET are very similar, remove the ;from the end of the line, Worksheets sheets = ...becomes Dim sheets Worksheets = .... If you're interested in getting better at programming you should really learn how to transition between both as many .NET examples are only provided in one or the other and you are really limiting yourself.

@PanPizza C# 和 VB.NET 非常相似，;从行尾去掉，Worksheets sheets = ...变成Dim sheets Worksheets = .... 如果您有兴趣提高编程水平，您应该真正学习如何在两者之间转换，因为许多 .NET 示例仅在其中一个或另一个中提供，而您确实在限制自己。

As mentioned in this answer: How do I properly clean up Excel interop objects?"Never use two dots" this means always step down into a single sub-object and never do this Dim oWS AS Excel.Worksheet = oExcel.Worksheets.Open(...)always step down to workbook and then step down to the worksheet, never directly from the Excel.Application.

如此答案中所述：如何正确清理 Excel 互操作对象？“永远不要使用两个点”这意味着总是下级到单个子对象，永远不要这样做Dim oWS AS Excel.Worksheet = oExcel.Worksheets.Open(...)总是下级到工作簿，然后下级到工作表，永远不要直接从Excel.Application.

As a general rule what you need to do is release your items in the reverse order to that which they were created. Otherwise you're taking the feet out from underneath your other references and they won't correctly deallocate.

作为一般规则，您需要按照与创建项目相反的顺序发布项目。否则，您将从其他引用下方取出脚，它们将无法正确解除分配。

Notice how you create Excel Application (oExcel), then Excel Workbook (oBook) and then finally Excel Worksheet (oWS), you need to release them in the reverse order.

请注意如何创建 Excel 应用程序 ( oExcel)，然后是 Excel 工作簿 ( oBook)，最后是 Excel 工作表 ( oWS)，您需要以相反的顺序释放它们。

Thus your code becomes:

因此你的代码变成：

    oBook.Close()
    oExcel.Quit()

    releaseObject(oWS)
    releaseObject(oBook)
    releaseObject(oExcel)
Catch ex As Exception

and just remove this code entirely from the Sub releaseObject(ByVal obj As Object)

并完全从 Sub releaseObject(ByVal obj As Object)

Finally
    GC.Collect()

It's not needed, GC occurs naturally and don't expect your applications to instantly free up memory, .NET pools unallocated memory so that it can readily instance objects in this memory rather than having to ask the OS for more memory.

它不是必需的，GC 自然发生并且不要指望您的应用程序立即释放内存，.NET 将未分配的内存池化，以便它可以轻松地在此内存中实例化对象，而不必向操作系统请求更多内存。

回答by ataraxia

I've searched and searched for this and even Microsoft's own solution doesn't work (Hereif you want to have a look). I have a vb.net application that exports data to an Excel template. Ideally when the user closes the Excel window it would kill the process but it doesn't because, as stated in the Microsoft article, vb.net is still referencing it.

我找啊找，这和（连微软自己的解决方案不是没有工作在这里，如果你想看看）。我有一个将数据导出到 Excel 模板的 vb.net 应用程序。理想情况下，当用户关闭 Excel 窗口时，它会终止进程，但事实并非如此，因为正如 Microsoft 文章中所述，vb.net 仍在引用它。

You need to kill the process yourself, there is a procedure to do this as below:

您需要自己终止该进程，有一个程序可以执行此操作，如下所示：

For Each p As Process In Process.GetProcesses
     If p.ProcessName = "EXCEL.EXE" Then p.Kill
Next

However, this would kill all instances of Excel and the user may have other Excel windows open that would get shutdown without saving, so I've come up with this (the workbook I'm using is called "Top 5 Issues Template"):

但是，这会杀死 Excel 的所有实例，并且用户可能打开了其他 Excel 窗口，这些窗口会在不保存的情况下关闭，所以我想出了这个（我正在使用的工作簿称为“前 5 个问题模板”）：

For Each p As Process In Process.GetProcesses
     If InStr(p.MainWindowTitle, "Top 5 Issues Template") <> 0 Then p.Kill
Next

This looks by the window name, not process name, and kills only the process that is related to it. This is the only way I could get Excel to close properly without messing anything up.

这通过窗口名称而不是进程名称查找，并且仅杀死与其相关的进程。这是我可以让 Excel 正确关闭而不会弄乱任何东西的唯一方法。

回答by dustinrwh

The key for me was to let the GarbageCollector (GC) know I wanted something cleaned up. I realize this usually isn't necessary, but when working with COM objects, it is sometimes necessary. See this link for more information https://www.add-in-express.com/creating-addins-blog/2013/11/05/release-excel-com-objects/

对我来说，关键是让 GarbageCollector (GC) 知道我想要清理一些东西。我意识到这通常不是必需的，但是在使用 COM 对象时，有时是必需的。有关更多信息，请参阅此链接https://www.add-in-express.com/creating-addins-blog/2013/11/05/release-excel-com-objects/

After releasing the objects, ask the GCto clean up by calling Collect()and WaitForPendingFinalizers(). The link above states it is necessary to call these mehtods twice in order to completely remove COM objects from memory. In my case, calling these methods once worked, but it may be worth calling it twice.

释放对象后，GC通过调用Collect()和 要求进行清理WaitForPendingFinalizers()。上面的链接指出必须调用这些方法两次才能从内存中完全删除 COM 对象。就我而言，调用这些方法曾经有效，但可能值得调用两次。

oBook.Close()
oExcel.Quit()

releaseObject(oExcel)
releaseObject(oBook)
releaseObject(oWS)

GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()