Excel 在 VB.NET 中关闭后在任务管理器中保持打开状态

Question

我创建了一个在 VB.NET 中创建几个 Excel 电子表格的应用程序

我遇到的问题是我无法让 Excel 完全退出。

我在隐藏 Excel 的 VB.NET 中创建和填充工作簿，进程(Microsoft Excel)显示在任务管理器的后台进程中

完成后，我使 Excel 可见，并且该过程移至应用程序。

然后，当我关闭 Excel 时，该进程又回到后台进程。

知道我做错了什么吗？

代码:

    Dim oExcel As Excel.Application = Nothing
    Dim oWorkbook As Excel.Workbook = Nothing
    Dim oWorksheet As Excel.Worksheet = Nothing
    Dim oRange As Excel.Range = Nothing

    oExcel = CreateObject("Excel.Application")
    oExcel.DisplayAlerts = False
    oExcel.Visible = False

    oWorkbook = oExcel.Workbooks.Add
    oWorksheet = oWorkbook.ActiveSheet
    'Populate, format, etc.
    oWorkbook.SaveAs(Me.txtExportLocation.Text & "\sales.xlsx")

    oExcel.Visible = True

    oRange = Nothing
    oWorksheet = Nothing
    oWorkbook = Nothing

    ReleaseObject(oExcel)

    Public Sub ReleaseObject(ByVal obj As Object)

         Dim iValue As Integer = 0

         Try
             Do
                 iValue = System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
             Loop While iValue > 0
         Catch ex As Exception
             RaiseError("", "modGeneral." & "." & System.Reflection.MethodBase.GetCurrentMethod().Name, Err.Number, Err.Description)
             obj = Nothing
         Finally
             GC.Collect()
         End Try

     End Sub

2016 年 10 月 31 日更新:

好吧，现在我真的很困惑。

使用此代码采取以下建议，我可以让 Excel 完全退出，但需要注意:

        GC.Collect()
        GC.WaitForPendingFinalizers()
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oWorksheet) : oWorksheet = Nothing
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oWorkbook) : oWorkbook = Nothing
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel) : oExcel = Nothing

但是，我的代码正在创建两个工作簿。除了 SQL 之外，创建每个工作簿的代码都是相同的。如果用户勾选 chkA，则清理代码不起作用。如果他们检查 chkB 它确实有效。如果他们同时检查两者，它就不起作用。我在下面包含了完整的代码:

    Private Sub btnExport_Click(sender As Object, e As EventArgs) Handles btnExport.Click

    Dim oExcel As Excel.Application = Nothing
    Dim oWorkbook As Excel.Workbook = Nothing
    Dim oWorksheet As Excel.Worksheet = Nothing
    Dim drSystem As SqlClient.SqlDataReader = Nothing
    Dim sSQL As String = ""
    Dim iRowCount As Integer = 2

    Try
        If Not Me.chkA.Checked And Not Me.chkB.Checked Then
            MsgBox("Select A, B or both before continuing.", vbInformation)
            Exit Try
        End If

        Me.Cursor = Cursors.WaitCursor
        Me.lblStatus.Text = "Exporting sales..."

        oExcel = CreateObject("Excel.Application")
        oExcel.DisplayAlerts = False
        oExcel.Visible = False

        If Me.chkA.Checked Then
            oWorkbook = oExcel.Workbooks.Add
            oWorksheet = oWorkbook.ActiveSheet
            oWorksheet.Cells(1, 1).Value = "Ship date"
            oWorksheet.Cells(1, 2).Value = "Customer"
            oWorksheet.Cells(1, 3).Value = "Invoice"
            oWorksheet.Cells(1, 4).Value = "Purchase order"
            oWorksheet.Cells(1, 5).Value = "Railcar"
            oWorksheet.Cells(1, 6).Value = "Weight"
            oWorksheet.Cells(1, 7).Value = "Total"
            oWorksheet.Cells(1, 8).Value = "Member purchase order"

            sSQL = "SELECT FORMAT(i.ship_date, N'MM/dd/yyyy') AS ship_date, "
            sSQL += "i.customer_no, "
            sSQL += "i.invoice_number, "
            sSQL += "i.customer_purchase_order_no, "
            sSQL += "r.railcar_number, "
            sSQL += "r.weight, "
            sSQL += "r.total, "
            sSQL += "i.member + N'-' + i.member_purchase_order_no AS member_purchase_order_no "
            sSQL += "FROM Invoices i "
            sSQL += "JOIN Railcars r "
            sSQL += "ON i.invoice_number = r.invoice_number "
            sSQL += "WHERE i.ship_date BETWEEN N'" & Format(Me.dtpStartDate.Value, "MM/dd/yyyy") & "' AND N'" & Format(Me.dtpEndDate.Value, "MM/dd/yyyy") & "' AND "
            sSQL += "invoice_type = N'A' "
            sSQL += "ORDER BY i.customer_no, "
            sSQL += "i.ship_date, "
            sSQL += "r.railcar_number"
            drSystem = modGeneral.drRunSQL(sSQL, CommandType.Text)
            Do While drSystem.Read
                oWorksheet.Cells(iRowCount, 1).Value = drSystem("ship_date")
                oWorksheet.Cells(iRowCount, 2).Value = drSystem("customer_no")
                oWorksheet.Cells(iRowCount, 3).Value = drSystem("invoice_number")
                oWorksheet.Cells(iRowCount, 4).Value = drSystem("customer_purchase_order_no")
                oWorksheet.Cells(iRowCount, 5).Value = drSystem("railcar_number")
                oWorksheet.Cells(iRowCount, 6).Value = drSystem("weight")
                oWorksheet.Cells(iRowCount, 7).Value = drSystem("total")
                oWorksheet.Cells(iRowCount, 8).Value = drSystem("member_purchase_order_no")
                iRowCount += 1
            Loop
            drSystem.Close()
            With oWorksheet.Range("A1", "J1")
                .Font.Bold = True
                .EntireColumn.AutoFit()
            End With
            oWorksheet.Range("D1").EntireColumn.HorizontalAlignment = Excel.Constants.xlLeft
            With oWorksheet.Range("F1")
                .EntireColumn.HorizontalAlignment = Excel.Constants.xlRight
                .EntireColumn.NumberFormat = "#,##0.00_);(#,##0.00)"
            End With
            With oWorksheet.Range("G1")
                .EntireColumn.HorizontalAlignment = Excel.Constants.xlRight
                .EntireColumn.NumberFormat = "#,##0.00_);(#,##0.00)"
            End With
            oWorkbook.SaveAs(Me.txtExportLocation.Text & "\sales-a.xlsx")
        End If
        If Me.chkB.Checked Then
            iRowCount = 2
            oWorkbook = oExcel.Workbooks.Add
            oWorksheet = oWorkbook.ActiveSheet
            oWorksheet.Cells(1, 1).Value = "Ship date"
            oWorksheet.Cells(1, 2).Value = "Customer"
            oWorksheet.Cells(1, 3).Value = "Invoice"
            oWorksheet.Cells(1, 4).Value = "Purchase order"
            oWorksheet.Cells(1, 5).Value = "Railcar"
            oWorksheet.Cells(1, 6).Value = "Weight"
            oWorksheet.Cells(1, 7).Value = "Total"
            oWorksheet.Cells(1, 8).Value = "Member purchase order"

            sSQL = "SELECT FORMAT(i.ship_date, N'MM/dd/yyyy') AS ship_date, "
            sSQL += "i.customer_no, "
            sSQL += "i.invoice_number, "
            sSQL += "i.customer_purchase_order_no, "
            sSQL += "r.railcar_number, "
            sSQL += "r.weight, "
            sSQL += "r.total, "
            sSQL += "i.member + N'-' + i.member_purchase_order_no AS member_purchase_order_no "
            sSQL += "FROM mxInvoices i "
            sSQL += "JOIN mxRailcars r "
            sSQL += "ON i.invoice_number = r.invoice_number "
            sSQL += "WHERE i.ship_date BETWEEN N'" & Format(Me.dtpStartDate.Value, "MM/dd/yyyy") & "' AND N'" & Format(Me.dtpEndDate.Value, "MM/dd/yyyy") & "' AND "
            sSQL += "invoice_type = N'B' "
            sSQL += "ORDER BY i.customer_no, "
            sSQL += "i.ship_date, "
            sSQL += "r.railcar_number"
            drSystem = modGeneral.drRunSQL(sSQL, CommandType.Text)
            Do While drSystem.Read
                oWorksheet.Cells(iRowCount, 1).Value = drSystem("ship_date")
                oWorksheet.Cells(iRowCount, 2).Value = drSystem("customer_no")
                oWorksheet.Cells(iRowCount, 3).Value = drSystem("invoice_number")
                oWorksheet.Cells(iRowCount, 4).Value = drSystem("customer_purchase_order_no")
                oWorksheet.Cells(iRowCount, 5).Value = drSystem("railcar_number")
                oWorksheet.Cells(iRowCount, 6).Value = drSystem("weight")
                oWorksheet.Cells(iRowCount, 7).Value = drSystem("total")
                oWorksheet.Cells(iRowCount, 8).Value = drSystem("member_purchase_order_no")
                iRowCount += 1
            Loop
            drSystem.Close()
            With oWorksheet.Range("A1", "J1")
                .Font.Bold = True
                .EntireColumn.AutoFit()
            End With
            oWorksheet.Range("D1").EntireColumn.HorizontalAlignment = Excel.Constants.xlLeft
            With oWorksheet.Range("F1")
                .EntireColumn.HorizontalAlignment = Excel.Constants.xlRight
                .EntireColumn.NumberFormat = "#,##0.00_);(#,##0.00)"
            End With
            With oWorksheet.Range("G1")
                .EntireColumn.HorizontalAlignment = Excel.Constants.xlRight
                .EntireColumn.NumberFormat = "#,##0.00_);(#,##0.00)"
            End With
            oWorkbook.SaveAs(Me.txtExportLocation.Text & "\sales-b.xlsx")
        End If
        oExcel.Visible = True

        GC.Collect()
        GC.WaitForPendingFinalizers()
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oWorksheet) : oWorksheet = Nothing
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oWorkbook) : oWorkbook = Nothing
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel) : oExcel = Nothing
    Catch ex As Exception
        RaiseError("", Me.Name & "." & System.Reflection.MethodBase.GetCurrentMethod().Name, Err.Number, Err.Description)
    Finally
        If Not drSystem Is Nothing Then
            If Not drSystem.IsClosed Then drSystem.Close()
        End If
    End Try

    Me.lblStatus.Text = ""
    Me.Cursor = Cursors.Default

Answer 1

使用后需要对 excel 对象进行处理和释放。

即使在任务管理器中，我也使用了一个简单的代码来关闭 excel:
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
它适用于我的应用程序