Excel 进程未在 VB.net 中关闭 [英] Excel Process not closing in VB.net

问题描述

我正在使用 interop.excel 创建一个 excel 文件，但该过程并未关闭.这是我正在尝试使用的代码.

I am creating an excel file using interop.excel and the process is not closing. This is the code i am trying to use.

 Private Sub converToExcel(fileLoc As String, ds As DataSet)
    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkBooks As Excel.Workbooks
    Dim xlWorkSheet As Excel.Worksheet
    Dim misValue As Object = System.Reflection.Missing.Value
    Dim i As Integer
    Dim j As Integer

    xlApp = New Excel.Application
    xlWorkBooks = xlApp.Workbooks
    xlWorkBook = xlWorkBooks.Add(misValue)
    xlWorkSheet = xlWorkBook.Sheets("sheet1")

    For i = 0 To ds.Tables(0).Rows.Count - 1
        For j = 0 To ds.Tables(0).Columns.Count - 1
            xlWorkSheet.Columns.NumberFormat = "@"
            xlWorkSheet.Cells(i + 1, j + 1) = String.Format("{0}", ds.Tables(0).Rows(i).Item(j).ToString())
        Next
    Next

    xlWorkSheet.SaveAs(fileLoc)
    xlWorkBook.Close()
    xlApp.Quit()

    releaseObject(xlWorkSheet)
    releaseObject(xlWorkBook)
    releaseObject(xlWorkBooks)
    releaseObject(xlApp)

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

我想我缺少一个 COM 对象，但似乎找不到解决方案.另请注意，这是在 64 位 Windows 8 上运行的.任何帮助都会很棒！谢谢

I think i am missing a COM object but cant seem to find a solution. Also as a note, this is running on 64-bit Windows 8. Any help would be great! Thanks

推荐答案

像这样的手动内存管理永远行不通.这是一个众所周知的问题，也是发明垃圾收集器的核心原因.程序员永远忘记释放内存.

Manual memory management like this just never works. This is a problem that's been known for very a long time and the core reason that garbage collectors were invented. Programmers just forever forget to release memory.

当您看不到正在使用的内存时，这会变得更加困难.在您的代码中肯定是这种情况，xlWorkSheet.Cells(i + 1, j + 1) 表达式使用不少于 三个 引用.一个用于Cells属性返回的范围对象，一个用于i+1选择的子范围对象，另一个用于j+1选择的子范围对象.VB.NET 语言提供的非常好的语法糖，编写没有它的 COM 代码非常痛苦.但对让您看到参考文献没有帮助.您不仅无法在源代码中看到它，调试器也绝对无法帮助您看到它们.

It gets extra hard when you can't see the memory being used. Which is certainly the case in your code, the xlWorkSheet.Cells(i + 1, j + 1) expression uses no less than three references. One for the range object returned by the Cells property, one for a sub-range object selected by i+1 and another for the sub-range object selected by j+1. Very nice syntax sugar provided by the VB.NET language, writing COM code without it is pretty doggone painful. But not helpful to let you see the references. Not only can't you see it in your source code, there is absolutely nothing the debugger can do to help you see them either.

这在 .NET 中是一个非常已解决的问题，它有一个垃圾收集器并且可以看到一切.最基本的问题是你没有给它解决问题的机会.你犯的错误是你停止了.可能是在最后一条语句上设置断点，然后在任务管理器中查看 Excel.exe 仍在运行.是的，这很正常.垃圾收集不是即时.

This is very much a solved problem in .NET, it has a garbage collector and it can see everything. The most basic problem is that you don't give it a chance to solve your problem. The mistake you made is that you stopped. Probably by setting a breakpoint on the last statement and then looking in Task Manager and seeing Excel.exe still running. Yes, that's normal. Garbage collection is not instant.

调用 GC.Collect() 应该使其即时，但这在运行项目的调试版本的特定情况下不起作用.局部变量的生命周期随后被扩展到方法的末尾，帮助您在 Autos/Locals/Watch 窗口中看到它们.换句话说，GC.Collect() 实际上并不收集任何的接口引用.在这篇文章中详细了解这种行为.

Calling GC.Collect() is supposed to make it instant, but that doesn't work in the specific case of running the Debug build of your project. The lifetime of local variables gets then extended to the end of the method, help you see them in the Autos/Locals/Watch window. In other words, GC.Collect() doesn't actually collect any of the interface references. More about that behavior in this post.

简单的解决方法是不要停止.继续做有用的事情，让垃圾收集器有理由运行.或者让您的程序在完成后终止，Excel 会在终结器线程最后一次运行时终止.这是有效的，因为具有引用的局部变量不再在范围内.

The simple workaround is to not stop. Keep doing useful things to give the garbage collector a reason to run. Or letting your program terminate since it is done, Excel terminates when the finalizer thread runs for the last time. Which works because the local variables that had the references are not in scope anymore.

但无论如何，每个人都想要即时修复.您可以通过删除所有 releaseObject() 调用来获得它.而是这样做:

But everybody wants the instant fix anyway. You get it by deleting all the releaseObject() calls. And doing it like this instead:

converToExcel(path, dset)
GC.Collect()
GC.WaitForPendingFinalizers()

或者换句话说，在方法返回后强制一个集合.局部变量不再在范围内，因此它们无法保留 Excel 引用.它现在在您调试时也可以工作，就像您在没有调试器的情况下运行 Release 版本时一样.

Or in other words, force a collection after the method has returned. The local variables are no longer in scope so they can't hold on to an Excel reference. It will now also work when you debug it, like it already did when you ran the Release build without a debugger.

这篇关于Excel 进程未在 VB.net 中关闭的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！