无法努力关闭内存中运行的Excel进程 [英] Unable to diligently close the excel process running in memory

问题描述

我开发了一个VB.Net代码，用于从excel文件中检索数据.我以一种形式加载此数据，并在对数据进行必要的修改后将其更新为excel.完整的流程运行良好，但是大多数时候我观察到即使关闭表单也是如此.已经加载的excel进程无法正确关闭.

I have developed a VB.Net code for retrieving data from excel file .I load this data in one form and update it back in excel after making necessary modifications in data. This complete flow works fine but most of the times I have observed that even if I close the form; the already loaded excel process does not get closed properly.

我尝试了所有可能的方法来关闭它，但无法解决该问题.在下面找到我用于连接到excel的代码，并告诉我是否需要其他任何方法来解决此问题.

I tried all possible ways to close it but could not be able to resolve the issue. Find below code which I am using for connecting to excel and let me know if any other approach I may need to follow to resolve this issue.

注意:我不想终止excel进程，因为它将终止excel的其他实例

Note: I do not want to kill the excel process as it will kill other instances of the excel

将connectionString设置为字符串

Dim connectionString As String

connectionString ="Provider = Microsoft.Jet.OLEDB.4.0;数据源="& ExcelFilePath& ; 扩展属性= excel 8.0;坚持安全信息=假"

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & ExcelFilePath & "; Extended Properties=excel 8.0; Persist Security Info=False"

    excelSheetConnection = New ADODB.Connection

    If excelSheetConnection.State = 1 Then excelSheetConnection.Close()
    excelSheetConnection.Open(connectionString)
    objRsExcelSheet = New ADODB.Recordset

    If objRsExcelSheet.State = 1 Then objRsExcelSheet.Close()
    Try
        If TestID = "" Then
            objRsExcelSheet.Open("Select * from [" & ActiveSheet & "$]", excelSheetConnection, 1, 1)
        Else
            objRsExcelSheet.Open("Select Test_ID,Test_Description,Expected_Result,Type,UI_Element,Action,Data,Risk from [" & ActiveSheet & "$] WHERE TEST_Id LIKE '" & TestID & ".%'", excelSheetConnection, 1, 1)
        End If
        getExcelData = objRsExcelSheet
    Catch errObj As System.Runtime.InteropServices.COMException
        MsgBox(errObj.Message, , errObj.Source)
        Return Nothing
    End Try

    excelSheetConnection = Nothing
    objRsExcelSheet = Nothing

推荐答案

您正在使用旧的VB6 COM接口.您的代码段中没有任何证据表明您曾经在RecordSet上调用Close().因为将其设置为Nothing，所以无法在Connection上调用Close().

You are using the old VB6 COM interfaces. There isn't any evidence in your code snippet of you ever calling Close() on the RecordSet. You can't call Close() on the Connection since you set it to Nothing.

按照书面规定，直到垃圾收集器运行并且终结器线程释放COM接口上的引用计数，Excel才会退出.如果您的程序在处理数据后没有退出或进入休眠状态，则可能要花费很长时间.您确实应该考虑提升此代码以使用System.Data.Oledb中的类，以便在处理完对象后可以正确地Dispose()所有内容.

As written, Excel won't exit until the garbage collector runs and the finalizer thread releases the reference counts on the COM interfaces. Which can take a long time if your program doesn't exit or goes dormant after processing the data. You really should consider uplifting this code to use the classes in System.Data.Oledb so you can properly Dispose() everything when you're done with the objects.

Q& D解决方案是强制终结器线程运行:

The Q&D solution is to force the finalizer thread to run:

    GC.Collect()
    GC.WaitForPendingFinalizers()
    GC.Collect()

使用RecordSet完成后，运行此命令.否则不建议这样做.

Run this after you're done using the RecordSet. It isn't otherwise recommended.

这篇关于无法努力关闭内存中运行的Excel进程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！