使用 VB.NET 处理 Excel com 对象的正确方法? [英] The proper way to dispose Excel com object using VB.NET?

问题描述

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

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?

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

推荐答案

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

@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.

如本答案所述:如何我应该正确清理 Excel 互操作对象吗?永远不要使用两个点"这意味着始终进入单个子对象并且永远不要这样做 Dim oWS AS Excel.Worksheet = oExcel.Worksheets.Open(...) 总是下到工作簿，然后下到工作表，从不直接从 Excel.Application.

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.

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

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.

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

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.

这样你的代码就变成了:

Thus your code becomes:

    oBook.Close()
    oExcel.Quit()

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

然后从 Sub releaseObject(ByVal obj As Object)

Finally
    GC.Collect()

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

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.

这篇关于使用 VB.NET 处理 Excel com 对象的正确方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！