Excel 进程未在 VB.net 中关闭

Excel Process not closing in VB.net

提问by jmcsmith

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

我正在使用 interop.excel 创建一个 excel 文件并且该过程没有关闭。这是我正在尝试使用的代码。

 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

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

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

回答by Hans Passant

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.

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

It gets extra hard when you can't seethe memory being used. Which is certainly the case in your code, the xlWorkSheet.Cells(i + 1, j + 1)expression uses no less than threereferences. One for the range object returned by the Cells property, one for a sub-range object selected by i+1and 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.

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

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.

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

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 anyof the interface references. More about that behavior in this post.

调用 GC.Collect() 应该使其立即生效，但这在运行项目的 Debug 构建的特定情况下不起作用。然后将局部变量的生命周期延长到方法的末尾，帮助您在 Autos/Locals/Watch 窗口中查看它们。换句话说， GC.Collect() 实际上并不收集任何接口引用。在这篇文章中更多地了解这种行为。

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.

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

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

但无论如何，每个人都希望立即修复。您可以通过删除所有 releaseObject() 调用来获得它。而是这样做：

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

Or in other words, force a collection afterthe 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 引用。它现在在您调试时也可以工作，就像您在没有调试器的情况下运行 Release 版本时一样。

回答by Christian Sauer

Try System.Runtime.InteropServices.Marshal.FinalReleaseComObject, that should help... also you should call xlWorkBook.Close() and xlapp.quit, if I recall correctly. First call them and then set them to nothing.

试试 System.Runtime.InteropServices.Marshal.FinalReleaseComObject，这应该会有所帮助...如果我没记错的话，你也应该调用 xlWorkBook.Close() 和 xlapp.quit。首先调用它们，然后将它们设置为空。

回答by Zarat

The GC.Collect makes not much sense where you placed it, if anything you should call it after you returnfrom converToExcel. Also you may need to wait for finalizers to run. Personally I think Hans' answer is the way to go, but I know from personal experience writing office addins in C# that sometimes its necessary to do manual reference counting, in particular when you need to be compatible with older office versions. (There are many documented problems, in particular when handling events from office, which can only be reliably solved by manual reference counting. Also some COM libraries don't like at all when released in the wrong order by GC, but thats not the case with office.)

该GC.Collect的让没有太大意义，你把它，如果有的话，你应该把它返回后从converToExcel。此外，您可能需要等待终结器运行。就我个人而言，我认为 Hans 的答案是可行的方法，但我从用 C# 编写 Office 插件的个人经验中了解到，有时需要进行手动引用计数，特别是当您需要与旧的 Office 版本兼容时。（有很多记录在案的问题，特别是在处理办公室事件时，只能通过手动引用计数来可靠地解决。还有一些 COM 库在 GC 以错误的顺序发布时根本不喜欢，但事实并非如此带办公室。）

So on to the actual problem in your code: there are three intermediate COM objects not released here:

所以到你的代码中的实际问题：这里没有发布三个中间 COM 对象：

• xlWorkBook.Sheetsreturns a collection of type Excel.Sheets
• xlWorkSheet.Columnsreturns a COM object of type Excel.Range
• xlWorkSheet.Cellsalso returns an Excel.Rangeobject

• xlWorkBook.Sheets返回类型的集合 Excel.Sheets
• xlWorkSheet.Columns返回一个类型为 COM 的对象 Excel.Range
• xlWorkSheet.Cells也返回一个Excel.Range对象

Besides this, if Marshal.ReleaseComObject throws an exception you did something wrong in your manual reference counting, therefore I wouldn't wrap it in an exception handler. When doing manual reference counting you must release every COM object once for every time it crosses the COM->NET boundary, meaning the Excel.Rangeobjects need to be released in every iteration of the loop.

除此之外，如果 Marshal.ReleaseComObject 抛出异常，您在手动引用计数中做错了什么，因此我不会将它包装在异常处理程序中。在进行手动引用计数时，您必须在每次跨越 COM->NET 边界时释放每个 COM 对象一次，这意味着Excel.Range需要在循环的每次迭代中释放这些对象。

Here's code which properly terminates Excel for me:

这是为我正确终止 Excel 的代码：

Imports Microsoft.Office.Interop
Imports System.Runtime.InteropServices

Private Sub converToExcel(fileLoc As String, ds As DataSet)
    Dim xlApp As New Excel.Application
    Dim xlWorkBooks As Excel.Workbooks = xlApp.Workbooks
    Dim xlWorkBook As Excel.Workbook = xlWorkBooks.Add(System.Reflection.Missing.Value)
    Dim xlWorkSheets As Excel.Sheets = xlWorkBook.Sheets
    ' accessing the sheet by index because name is localized and your code will fail in non-english office versions
    Dim xlWorkSheet As Excel.Worksheet = xlWorkSheets(1)

    For i = 0 To ds.Tables(0).Rows.Count - 1
        For j = 0 To ds.Tables(0).Columns.Count - 1
            ' couldn't this be moved outside the loop?
            Dim xlColumns As Excel.Range = xlWorkSheet.Columns
            xlColumns.NumberFormat = "@"
            Marshal.ReleaseComObject(xlColumns)

            Dim xlCells As Excel.Range = xlWorkSheet.Cells
            xlCells(i + 1, j + 1) = ds.Tables(0).Rows(i).Item(j).ToString()
            Marshal.ReleaseComObject(xlCells)
        Next
    Next

    xlWorkSheet.SaveAs(fileLoc)
    'xlWorkBook.Close() -- not really necessary
    xlApp.Quit()

    Marshal.ReleaseComObject(xlWorkSheet)
    Marshal.ReleaseComObject(xlWorkSheets)
    Marshal.ReleaseComObject(xlWorkBook)
    Marshal.ReleaseComObject(xlWorkBooks)
    Marshal.ReleaseComObject(xlApp)
End Sub

If you want to be extra careful you'd want to handle exceptions from the office API and call ReleaseComObject inside finally-clauses. It can be helpful to define a generic wrapper and write using-clauses instead of try-finally (make the wrapper a structure not a class so you don't allocate the wrappers on the heap).

如果您想格外小心，您需要处理来自 office API 的异常并在 finally 子句中调用 ReleaseComObject。定义通用包装器并编写 using-clauses 而不是 try-finally 可能会有所帮助（使包装器成为结构而不是类，这样您就不会在堆上分配包装器）。

回答by Gretchen

'Get the PID from the wHnd and kill the process.
' open the spreadsheet
ImportFileName = OpenFileDialog1.FileName
excel = New Microsoft.Office.Interop.Excel.ApplicationClass
wBook = excel.Workbooks.Open(ImportFileName)
hWnd = excel.Hwnd
Dim id As Integer = GetWindowThreadProcessId(hWnd, ExcelPID)

---

---

Sub CloseExcelFile()
        Try
            ' first try this
            wBook.Saved = True
            wBook.Close()
            excel.Quit()

            ' then this.
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excel)
            excel = Nothing

            ' This appears to be the only way to close excel!
            Dim oProcess As Process
            oProcess = Process.GetProcessById(ExcelPID)
            If oProcess IsNot Nothing Then
                oProcess.Kill()
            End If

        Catch ex As Exception
            excel = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

回答by TinMan464

I did not see anyone properly address what was occuring and instead, tried to create work arounds for it.

我没有看到任何人正确解决正在发生的事情，而是试图为其创建变通方法。

What is happening here is that the workbook is prompting, in the background, to be saved. In your code, you're saving the worksheet and not the workbook. You can either trick it and set the saved state of the workbook to true or save the workbook before exiting the excel applicaiton.

这里发生的事情是工作簿在后台提示要保存。在您的代码中，您保存的是工作表而不是工作簿。您可以欺骗它并将工作簿的保存状态设置为 true 或在退出 excel 应用程序之前保存工作簿。

I was also having this issue. The Excel process would run the entire time the application was open. By adding the xlWorkBook.Saved = True line, the process would end after the call to xlApp.Quit(). In my case, I did not need to save the excel file, only reference it for values.

我也遇到了这个问题。Excel 进程将在应用程序打开的整个过程中运行。通过添加 xlWorkBook.Saved = True 行，该过程将在调用 xlApp.Quit() 后结束。就我而言，我不需要保存 excel 文件，只需要引用它的值。

Option #1 - Do not save the workbook:

选项 #1 - 不保存工作簿：

xlWorkSheet.SaveAs(fileLoc)
xlWorkBook.Saved = True       ' Add this line here.
'xlWorkBook.Close()           ' This line shouldn't be necessary anymore.
xlApp.Quit()

Option #2 - Save the workbook to a new file:

选项#2 - 将工作簿保存到一个新文件：

'xlWorkSheet.SaveAs(fileLoc)  ' Not needed
xlWorkBook.SaveAs(fileLoc)    ' If the file doesn't exist
'xlWorkBook.Close()           ' This line shouldn't be necessary anymore.
xlApp.Quit()

Option #3 - Save the workbook to an existing file:

选项 #3 - 将工作簿保存到现有文件：

'xlWorkSheet.SaveAs(fileLoc)  ' Not needed
xlWorkBook.Save(fileLoc)      ' If the file does exist
'xlWorkBook.Close()           ' This line shouldn't be necessary anymore.
xlApp.Quit()

.Quit() Method:
https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel._application.quit?view=excel-pia#Microsoft_Office_Interop_Excel__Application_Quit

.Quit() 方法：https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel._application.quit?view =excel-pia#Microsoft_Office_Interop_Excel__Application_Quit

.Saved() Method:
https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel._workbook.saved?view=excel-pia#Microsoft_Office_Interop_Excel__Workbook_Saved

.Saved() 方法：https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel._workbook.saved?view =excel-pia#Microsoft_Office_Interop_Excel__Workbook_Saved

回答by SuJo RiBu

It's just as simple as adding this line in your code, just after opening the Workbook:

就像在您的代码中添加这一行一样简单，就在打开工作簿之后：

oExcel.displayalerts = False

oExcel.displayalerts = False

回答by Xtian11

Finally solved :)

终于解决了:)

Private Function useSomeExcel(ByVal Excelfilename As String) 
  Dim objExcel As Excel.Application
  Dim objWorkBook As Excel.Workbook
  Dim objWorkSheets As Excel.Worksheet

  Dim datestart As Date = Date.Now
  objExcel = CreateObject("Excel.Application") 'This opens...  
  objWorkBook = objExcel.Workbooks.Open(Excelfilename) ' ... excel process
  Dim dateEnd As Date = Date.Now
  End_Excel_App(datestart, dateEnd) ' This closes excel proces
End Function

use this method

使用这个方法

  Private Sub End_Excel_App(datestart As Date, dateEnd As Date)
    Dim xlp() As Process = Process.GetProcessesByName("EXCEL")
    For Each Process As Process In xlp
     If Process.StartTime >= datestart And Process.StartTime <= dateEnd Then
       Process.Kill()
       Exit For
     End If
    Next
  End Sub

This method closes especific process opened.

此方法关闭打开的特定进程。

回答by Shivachandra

Dim xlp() As Process = Process.GetProcessesByName("EXCEL")

 For Each Process As Process In xlp
   Process.Kill()
     If Process.GetProcessesByName("EXCEL").Count = 0 Then
       Exit For
     End If
 Next