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

问题描述

我有以下code（从网上教程获得）。在code的工作，但我怀疑处置Excel的COM对象的方式是有点不正确。我们需要真正需要调用GC.Collect？或者有什么要处理此Excel的COM对象的最佳方式？

 公用Sub T1（）
    昏暗oExcel作为新Excel.Application
    昏暗oBook的作为Excel.Workbook = oExcel.Workbooks.Open（TextBox2.Text）

    基于名称选择工作表
    昏暗的OWS作为Excel.Worksheet = CTYPE（oBook.Sheets（工作表Sheet1），Excel.Worksheet）
    尝试

        oExcel.Visible = FALSE
        现在显示单元格的值
        的MessageBox.show（oWS.Range（TextBox6.Text）。文）

        oBook.Close（）
        oExcel.Quit（）

        releaseObject（oExcel）
        releaseObject（oBook的）
        releaseObject（OWS）
    抓住EX为例外
        MSGBOX（错误：＆放大器; ex.ToString，MsgBoxStyle.Critical，错误！）
    结束尝试
结束小组

私人小组releaseObject（OBJ BYVAL作为对象）
    尝试
        System.Runtime.InteropServices.Marshal.ReleaseComObject（OBJ）
        OBJ =无
    抓住EX为例外
        OBJ =无
    最后
        所以GC.Collect（）
    结束尝试
结束小组
 

解决方案

@PanPizza C＃和VB.NET都非常相似，删除; 从行的末尾，工作表表= ... 变成暗淡张工作表= ... 。如果你有兴趣在获取更好的节目，你真该学学如何既尽可能多的.NET的例子仅提供了一个或另一个，你真的限制自己的转变。<​​/ P>

正如这个答案：<一href="http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects-in-c-sharp">How妥善清理在C＃中不要用两个点这意味着永远下台成一个单一的子对象的Excel互操作的对象，从来没有做到这一点暗淡OWS AS Excel.Worksheet = oExcel.Worksheets 。开（...）总是下台从 Excel.Application 工作簿，然后下台工作表，从不直接。

正如你需要做一个一般的规则是释放你的项目以相反的顺序来，他们创造了它。否则，你正在做的脚从您的其他参考下，他们将无法正常释放。

请注意您创建将Excel应用程序（ oExcel ），则Excel工作簿（ oBook的），然后终于Excel工作表（ OWS ），你需要释放它们以相反的顺序。

因此​​，你的code就变成了：

  oBook.Close（）
    oExcel.Quit（）

    releaseObject（OWS）
    releaseObject（oBook的）
    releaseObject（oExcel）
抓住EX为例外
 

和刚刚完全由删除此code中的子releaseObject（OBJ BYVAL作为对象）

 最后
    所以GC.Collect（）
 

这是没有必要的，GC自然发生，不要指望你的应用程序可以立即释放内存，.NET池未分配的内存，以便它可以很容易地在该存储器实例对象，而不必要求操作系统更多的内存。

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

As mentioned in this answer: How to properly clean up Excel interop objects in C# "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.

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

and just remove this code entirely from the 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.

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