使用Interop.Excel的Excel.Range时如何避免OutOfMemory错误

问题背景 #

基于VB.NET（.NET Framework ≥4.5）开发的程序，通过Web服务获取DataTable，使用Interop.Excel将数据写入Excel文件。原批量写入方式（rng.value = ConvertDataTableTo2DArray(DT)）近期触发Exception from HRESULT: 0x8007000E (E_OUTOFMEMORY)，拆分批次（100/50/20行）无效；添加GC.Collect()和GC.WaitForPendingFinalizers()后不再报错，但写入至104行后无法继续，疑似Range内存未释放。

核心原因 #

Interop.Excel依赖COM对象，.NET的GC无法自动及时回收这些非托管资源，哪怕系统剩余内存充足，Excel进程自身的内存占用也会触发溢出；手动GC仅能触发托管层回收，未彻底清理COM对象导致后续写入阻塞。

解决方案 #

1. 强制手动释放所有COM对象 #

不要依赖GC自动回收，每次使用完Excel对象（尤其是Range）后立即释放：

Imports System.Runtime.InteropServices

' 批次写入示例
For Each batchDT As DataTable In SplitDataTable(originalDT, 50)
    Dim arr As Object(,) = ConvertDataTableTo2DArray(batchDT)
    Dim startRow As Integer = ' 根据批次计算起始行
    Dim rng As Excel.Range = ws.Cells(startRow, 1).Resize(batchDT.Rows.Count, 22)
    
    rng.Value = arr
    
    ' 立即释放Range对象
    Marshal.ReleaseComObject(rng)
    rng = Nothing
    
    ' 清理二维数组
    Array.Clear(arr, 0, arr.Length)
    arr = Nothing
    
    ' 触发GC并清理未使用的COM对象
    GC.Collect()
    GC.WaitForPendingFinalizers()
    Marshal.CleanupUnusedObjectsInCurrentContext()
Next

• 注意：所有创建的Excel对象（Application、Workbook、Worksheet等）都要执行Marshal.ReleaseComObject()并设为Nothing。

2. 优化二维数组与Range的匹配度 #

检查ConvertDataTableTo2DArray函数，确保生成的二维数组维度与Range严格一致：

• 数组第二维度长度必须等于22列，避免生成多余空值或超出Range范围的元素
• 若DataTable存在空行/空列，提前过滤，减少不必要的内存占用

3. 改用低内存占用的写入方式 #

替换Range.Value为Range.CopyFromRecordset，该方法内存效率更高：

' 需引用Microsoft ActiveX Data Objects x.x Library
Imports ADODB

Dim rs As New Recordset()
rs.Open(originalDT, New Connection(), CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockReadOnly)
ws.Cells(1, 1).CopyFromRecordset(rs)

' 释放Recordset对象
Marshal.ReleaseComObject(rs)
rs = Nothing

4. 排查残留的Excel进程 #

每次测试后打开任务管理器，确认Excel.exe进程是否自动关闭：

• 若进程残留，说明仍有未释放的COM对象，逐一排查所有Excel相关对象的释放逻辑

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