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

问题描述

我有以下代码（从在线教程获得）。代码正在工作，但我怀疑处理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）
 
'根据名称选择WorkSheet 
 Dim oWS As Excel.Worksheet = CType （oBook.Sheets（Sheet1），Excel.Worksheet）
尝试
 
 oExcel.Visible = False 
'现在显示单元格值
 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）
尝试
 System.Runtime.InteropServices.Marshal.ReleaseComObject（obj）
 obj =没有
 Catch ex As Exception 
 obj = Nothing 
最后
 GC.Collect（）
 End尝试
 End Sub 
  

解决方案

@PanPizza C＃和VB.NET非常相似，删除; 从行尾，工作表Sheet = ... 成为 Dim sheet Worksheets = ... 。如果您有兴趣更好地编程，您应该真正学习如何在两者之间进行转换，因为许多.NET示例只能在一个或另一个中提供，而您真的限制自己。

正如本答案所述：如何正确地清理Excel互操作对象？永远不要使用两个点，这意味着总是放下一个子对象，从不这样做 Dim oWS AS Excel.Worksheet = oExcel。 Worksheets.Open（...）总是放下工作簿，然后放下工作表，从不直接从 Excel.Application 。

作为一般规则，您需要做的是以与其创建的顺序相反的顺序发布您的项目。否则你将从你的其他参考文献中的脚下脱离出来，他们将无法正确地解除分配。

注意你如何创建Excel应用程序（ oExcel ），然后Excel工作簿（ oBook ），然后最终Excel Worksheet（ oWS ），您需要以相反的顺序发布它们。

因此，您的代码变为：

  oBook.Close（）
 oExcel.Quit（）
 
 releaseObject（oWS）
 releaseObject（oBook）
 releaseObject（oExcel）
 Catch ex As Exception 
  

只需从 Sub releaseObject（ ByVal obj As Object）

 最后
 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 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.

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屋！