VB.NET导入Excel数据后如何释放Excel？解决内存残留问题

问题根源分析 #

你的代码存在以下几个导致Excel进程无法正常释放的问题：

• 未调用Excel应用的Quit()方法，Excel进程没有收到退出指令
• COM对象释放顺序错误，应该从最底层的Range开始，逐层向上释放到Application
• 未显式关闭打开的Workbook
• 遍历单元格时的链式调用（如range.Cells(i, 1).Value）会创建隐式COM对象，这些对象未被释放

修复后的代码 #

Private Sub Buttonimport_Click(sender As Object, e As EventArgs) Handles Buttonimport.Click
    ' Open het OpenFileDialog-component
    Dim openFileDialog1 As New OpenFileDialog()
    openFileDialog1.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*"
    openFileDialog1.FilterIndex = 1
    openFileDialog1.RestoreDirectory = True

    If openFileDialog1.ShowDialog() = DialogResult.OK Then
        ' Open het geselecteerde Excel-bestand
        Dim xlApp As New Excel.Application
        Dim xlWorkbook As Excel.Workbook
        Dim xlWorksheet As Excel.Worksheet
        Dim range As Excel.Range
        Dim cell As Excel.Range ' 用于显式释放单元格对象

        xlWorkbook = xlApp.Workbooks.Open(openFileDialog1.FileName)
        ' Selecteer het eerste werkblad
        xlWorksheet = xlWorkbook.Sheets(1)
        ' Selecteer de gehele werkbladgegevens
        range = xlWorksheet.UsedRange

        ' Lees de gegevens uit het Excel-bestand, rij per rij
        Dim Count As Integer = 0 ' 修正Count类型为Integer并初始化
        For i As Integer = 2 To range.Rows.Count
            ' 显式获取单元格对象，避免隐式COM引用
            cell = range.Cells(i, 1)
            Werk_order_nrTextBox.Text = cell.Value
            ReleaseObject(cell)

            cell = range.Cells(i, 2)
            Taak_nrTextBox.Text = cell.Value
            ReleaseObject(cell)

            cell = range.Cells(i, 3)
            cmbkleur.Text = cell.Value
            ReleaseObject(cell)

            cell = range.Cells(i, 4)
            ComboBoxAfdeling.Text = cell.Value
            JobTextBox.Text = cell.Value
            ReleaseObject(cell)

            cell = range.Cells(i, 6)
            Taak_omschrijvingTextBox.Text = cell.Value
            ReleaseObject(cell)

            cell = range.Cells(i, 7)
            Interne_opmerkingenTextBox.Text = cell.Value
            ReleaseObject(cell)

            cell = range.Cells(i, 12)
            Geplande_start_datumDateTimePicker.Value = cell.Value 'Start Datum
            ReleaseObject(cell)

            cell = range.Cells(i, 13)
            Geplande_eind_datumDateTimePicker.Text = cell.Value 'EIND  Datum
            ReleaseObject(cell)

            Plaat_nieuw_of_recupTextBox.Text = "Recup"
            Eigen_nrTextBox.Text = (CInt(Eigen_nrTextBox.Text) + 1).ToString() ' 修正数值运算逻辑

            ' Controleer de inhoud van de cel en stel de tekst van de Cmbcode-tekstbox in
            cell = range.Cells(i, 11)
            If cell.Value = "GB-BILS" Then
                Cmbcode.Text = "gb-"
            Else
                Cmbcode.Text = "gb+"
            End If
            ReleaseObject(cell)

            ' Controleer de inhoud van de cel en stel de tekst van de Cmbcode-tekstbox in
            Dim kleur As String
            cell = range.Cells(i, 14)
            kleur = cell.Value
            ReleaseObject(cell)
            Select Case kleur
                Case "1"
                    Me.cmbkleur.Text = "1-Rood"
                Case "2"
                    Me.cmbkleur.Text = "2-Geel"
                Case "3"
                    Me.cmbkleur.Text = "3-Groen"
                Case "4"
                    Me.cmbkleur.Text = "4-Blauw"
                Case "5"
                    Me.cmbkleur.Text = "5-Revisie"
                Case "7"
                    Me.cmbkleur.Text = "7-Turnaround"
            End Select

            Dim werkplek As String
            cell = range.Cells(i, 10)
            werkplek = cell.Value
            ReleaseObject(cell)
            Select Case werkplek
                Case "PIT"
                    Me.ComboBoxAfdeling.Text = "Cui"
                Case "SL"
                    Me.ComboBoxAfdeling.Text = "Stoomploeg"
                Case Else
                    Me.ComboBoxAfdeling.Text = "Onderhoud"
            End Select

            cell = range.Cells(i, 3)
            If Len(cell.Value) = 3 Then
                CenterTextBox.Text = Mid(cell.Value, 3, 1)
            Else
                CenterTextBox.Text = cell.Value
            End If
            ReleaseObject(cell)

            Count += 1
            ' Voer de Buttonappadd-functie uit
            Voegdatatoe()
        Next

        ' 先关闭工作簿
        xlWorkbook.Close(SaveChanges:=False)
        ' 退出Excel应用
        xlApp.Quit()

        ' 按正确顺序释放COM对象：先底层，后上层
        ReleaseObject(range)
        ReleaseObject(xlWorksheet)
        ReleaseObject(xlWorkbook)
        ReleaseObject(xlApp)

        Frmopvolglijst.Refreshdatagrid()
        MessageBox.Show(String.Format("Er zijn {0} bonnen geïmporteerd.", Count), "Importvoltooiing", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End If
End Sub

Private Sub ReleaseObject(ByVal obj As Object)
    Try
        If obj IsNot Nothing Then
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        End If
    Catch ex As Exception
        obj = Nothing
    Finally
        GC.Collect()
        GC.WaitForPendingFinalizers() ' 等待垃圾回收完成，确保COM对象被彻底释放
    End Try
End Sub

关键修复点说明 #

• 添加xlApp.Quit()：主动通知Excel进程退出
• 添加xlWorkbook.Close()：显式关闭打开的工作簿，避免资源占用
• 调整释放顺序：先释放Range，再Worksheet、Workbook，最后Application，符合COM对象的引用层级
• 显式处理单元格对象：将每个range.Cells(i, col)赋值给变量，使用后立即释放，避免隐式COM引用残留
• 优化垃圾回收：在ReleaseObject中添加GC.WaitForPendingFinalizers()，确保垃圾回收器彻底清理COM对象引用
• 修正Count变量：从String改为Integer并初始化，避免运算错误

内容的提问来源于stack exchange，提问作者Franky Thys