真·VB.NET彻底释放Interop.Excel对象

使用 Microsoft.Office.Interop.Excel 虽然有速度慢的缺点;但是作为自带引用,兼容性最好,而且是COM对象模型也很熟悉(Excel里直接录个宏,很方便把VBA代码转成VB.NET)。所以处理几百上千条的小数据时还是很方便的。

Microsoft.Office.Interop.Excel 用得不多的最大问题其实就是拿简单例子可以正确释放Excel,做了大量操作后却发现在任务管理器中依然有多余Excel进程存在。

问题原因当然是COM对象映射到Interop交互对象之后,.NET下的交互对象释放次序不符合COM对象预期,导致不能正确释放。比如

Dim xlApp New Excel.Application() With {.Visible = False}
Dim xlWorkbooks As Excel.Workbooks = xlApp.Workbooks
Dim xlWorkbook As Excel.Workbook = xlWorkbooks.Open("...")

Dim value As Object = xlWorkbook.Sheets(1).Cells(1,1).Value

xlWorkbook.Close()
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook)
xlWorkbook = Nothing
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbooks)
xlWorkbooks = Nothing
xlApp.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
xlApp = Nothing
System.GC.Collect()

中间取value这行代码看起来很正常,没有保留任何交互对象。其实在整个对象访问路径上隐式使用了以下交互对象,要靠GC来释放(通常是延后的————即调用Close()时交互对象未释放、工作簿关闭不了,之后的Quit()Excel不会退出),

xlWorkbook.Sheets 'Excel.Sheets
xlWorkbook.Sheets(1) 'Excel.Worksheet
xlWorkbook.Sheets(1).Cells 'Excel.Range
xlWorkbook.Sheets(1).Cells(1,1) 'Excel.Range

要做到正确释放,要把这些交互对象全部在Close()前释放。为了方便使用,把 Excel.ApplicationExcel.Workbook 封装在类中,用 IDisposable 接口确保释放。用类似下面的属性封装访问

    Public Property CellValue(sheetIndex As Object, rowNo As Integer, colNo As Integer) As Object
        Get
            Dim xlSheets As Excel.Sheets = m_xlWorkbook.Sheets
            Dim xlSheet As Excel.Worksheet = xlSheets.Item(sheetIndex)
            Dim xlCells As Excel.Range = xlSheet.Cells
            Dim xlCell As Excel.Range = xlCells.Item(rowNo, colNo)
            Dim value As Object = xlCell.Value
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlCell)
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlCells)
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet)
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheets)
            System.GC.Collect(0)
            Return value
        End Get
        Set(value As Object)
            ' 同理所有交互对象保留变量、释放
        End Set
    End Property

上面的属性是通过行号、列号访问单元value;如果需要通过A1格式访问单元又要定义属性;如果需要访问单元text也要单独定义属性。
总之全部封装好后,读写完Excel文件后就能正确释放,不再有多余Excel进程残留。