如何在 Interop.Excel 中使用 excel.range 时避免 OutOfMemory 错误-解网

问：

我正在用 vb.net （.Net framework >= 4.5）编写一个应用程序，该应用程序通过 Web 服务（作为数据表对象）从数据库中检索数据，并通过互操作库将其解析为具有特定格式的 Excel 文件（不幸的是）。

该应用程序运行良好，但最近我开始出现此 OutOfMemory 错误：

HRESULT 异常：0x8007000E （E_OUTOFMEMORY）

当我使用以下方法将数据从数据表解析为 excel.range 对象（rng）时，会发生错误：

rng.value = ConvertDataTableTo2DArray(DT)

函数 ConvertDataTableTo2DArray 接受一个数据表对象并返回一个包含信息的二维数组。我已经这样做了一段时间，因为它似乎是进行这种操作的最快方法（从我在网上研究的内容来看）。

        Public Shared Function ConvertDataTableTo2DArray(dataTable As DataTable) As Object(,)
            Dim rows As Integer = dataTable.Rows.Count
            Dim columns As Integer = dataTable.Columns.Count
            Dim dataArray(rows - 1, columns - 1) As Object

            For i As Integer = 0 To rows - 1
                For j As Integer = 0 To columns - 1
                    dataArray(i, j) = dataTable.Rows(i)(j)
                Next
            Next

            Return dataArray
        End Function

通过咨询这里和那里发布的其他问题，我发现 Excel.Range 对象的大小有限，因此我解决这个问题的最初方法是将数据表除以 x 行的批次，并再次为每个批次声明一个新范围。这根本无法解决问题，因此要么我没有清除以前正确使用的数据，要么错误的原因与我想象的不完全一样。

一些附加信息：

我正在处理 22 列的行。
该错误似乎发生在第 120 行左右。
我尝试过 100、50 和 20 行的批次，在同一组行中出现相同的错误（即，对于 20 行/批，它发生在第 6 批）。
我使用的是配备 I7 和 32GB RAM 的戴尔笔记本电脑。我已经检查过，该过程失败，未使用的 RAM 超过 10 GB。

我的代码（不幸的是名字是西班牙语）：

        Public Shared Sub CopiarDataTableAExcel(dataTable As DataTable, excelHoja As Excel.Worksheet, inicioCelda As String)
            ' Obtener el rango de inicio en base a la celda especificada
            Dim rangoInicio As Excel.Range = excelHoja.Range(inicioCelda)

            ' Obtener el rango final en base al tamaño de la DataTable
            Dim rangoFin As Excel.Range = excelHoja.Cells(rangoInicio.Row + dataTable.Rows.Count - 1, rangoInicio.Column + dataTable.Columns.Count - 1)

            ' Obtener el rango completo que abarca desde el inicio hasta el fin
            Dim rangoCompleto As Excel.Range = excelHoja.Range(rangoInicio, rangoFin)
            Try
                ' Copiar los datos de la DataTable al rango completo en Excel
                rangoCompleto.Value = ConvertDataTableTo2DArray(dataTable)
            Catch ex As Exception
                If ex.Message.Contains("E_OUTOFMEMORY") Then
                    Dim filas_por_particion As Int16 = 20
                    Dim particiones As Int16 = Math.Ceiling(dataTable.Rows.Count / filas_por_particion)
                    For prt = 1 To particiones
                        Dim num_filas As Integer = filas_por_particion
                        If prt = particiones Then
                            num_filas = dataTable.Rows.Count - (prt * filas_por_particion) ' numero de filas menos las filas ya escritas
                        End If
                        'Dim nuevo_rango_inicio As Excel.Range = excelHoja.Range("A" & ((prt - 1) * num_filas + 2).ToString)
                        Dim nuevo_rango_inicio As Excel.Range = excelHoja.Cells((prt - 1) * num_filas + 2, 1)
                        Dim nuevo_rango_fin As Excel.Range = excelHoja.Cells(nuevo_rango_inicio.Row + num_filas, nuevo_rango_inicio.Column + dataTable.Columns.Count - 1)

                        Dim nuevo_rango As Excel.Range = excelHoja.Range(nuevo_rango_inicio, nuevo_rango_fin)
                        Dim auxDt As DataTable = dataTable.Clone

                        For fila = (prt - 1) * num_filas To prt * num_filas - 1
                            auxDt.ImportRow(dataTable.Rows(fila))
                        Next
                        nuevo_rango.Value = ConvertDataTableTo2DArray(auxDt)
                    Next
                Else
                    MsgBox("Se ha producido el siguiente error: " & Chr(13) & ex.Message)
                End If
            End Try
        End Sub

在代码的第一部分，你可以找到我的初始方法。在 catch 语句中，我介绍了这个仍然失败的“批处理”副本。根据我的理解，nuevo_rango* 变量的每个声明都应该清除以前使用的内存，因此新的 excel.range nuevo_rango应该使用一个只有 x （num_filas）行和 22 列的新范围。在调试时，我检查了我的批处理数据是否具有预期的大小，这就是我不理解 OutOfMemory 错误的原因。rangoCompleto.Value = ConvertDataTableTo2DArray(dataTable)

更新：

按照@djv的建议和他提供的链接中的信息，我尝试了这个：

        Public Shared Sub CopiarDataTableAExcel(dataTable As DataTable, excelHoja As Excel.Worksheet, inicioCelda As String)
            ' Obtener el rango de inicio en base a la celda especificada
            Dim rangoInicio As Excel.Range = excelHoja.Range(inicioCelda)

            ' Obtener el rango final en base al tamaño de la DataTable
            Dim rangoFin As Excel.Range = excelHoja.Cells(rangoInicio.Row + dataTable.Rows.Count - 1, rangoInicio.Column + dataTable.Columns.Count - 1)

            ' Obtener el rango completo que abarca desde el inicio hasta el fin
            Dim rangoCompleto As Excel.Range = excelHoja.Range(rangoInicio, rangoFin)
            Try
                ' Copiar los datos de la DataTable al rango completo en Excel
                rangoCompleto.Value = ConvertDataTableTo2DArray(dataTable)
            Catch ex As Exception
                If ex.Message.Contains("E_OUTOFMEMORY") Then
                    Dim filas_por_particion As Int16 = 100
                    Dim particiones As Int16 = Math.Ceiling(dataTable.Rows.Count / filas_por_particion)
                    For prt = 1 To particiones
                        Dim num_filas As Integer = filas_por_particion
                        If prt = particiones Then
                            num_filas = (prt * filas_por_particion) - dataTable.Rows.Count ' numero de filas menos las filas ya escritas
                        End If
                        DT2Excel(excelHoja, num_filas, prt, dataTable)
                        GC.Collect()
                        GC.WaitForPendingFinalizers()
                    Next
                Else
                    MsgBox("Se ha producido el siguiente error: " & Chr(13) & ex.Message)
                End If
            End Try
        End Sub


        Public Shared Function DT2Excel(exl As Excel.Worksheet, num_filas As Int16, num_iter As Int16, dt As DataTable) As Boolean

            Try
                Dim rng_inicio As New Tuple(Of Int16, Int16)((num_iter - 1) * num_filas + 2, 1)
                Dim nuevo_rango_inicio As Excel.Range = exl.Cells(rng_inicio.Item1, rng_inicio.Item2)
                Dim rng_fin As New Tuple(Of Int16, Int16)(nuevo_rango_inicio.Row + num_filas, nuevo_rango_inicio.Column + dt.Columns.Count - 1)
                Dim nuevo_rango_fin As Excel.Range = exl.Cells(rng_fin.Item1, rng_fin.Item2)

                Dim nuevo_rango As Excel.Range = exl.Range(nuevo_rango_inicio, nuevo_rango_fin)
                Dim auxDt As DataTable = dt.Clone

                For fila = (num_iter - 1) * num_filas To num_iter * num_filas - 1
                    auxDt.ImportRow(dt.Rows(fila))
                Next
                nuevo_rango.Value = ConvertDataTableTo2DArray(auxDt)
                nuevo_rango = Nothing
                Return True
            Catch ex As Exception
                Return False
            End Try

        End Function

它不再抛出错误，但在第 104 行之后仍然没有复制。我什至使用另一个函数中的范围封装了该部分，以避免注意到的调试错误，但我觉得这只有在我封装所有 Excel 对象时才有效，这将破坏函数的目的。

似乎 Excel.range 对象仍然没有释放内存......我想我可以实例化工作簿并在每次迭代中打开文件，但这违背了快速执行操作的目的。

.NET vb.net Excel-Interop

我按照答案中给出的提示进行操作，但它无法正常工作。它不再抛出错误，但在第 104 行之后仍然没有复制。我什至使用另一个函数中的范围封装了该部分，以避免注意到的调试错误。似乎 Excel.range 对象仍然没有释放内存......我想我可以实例化工作簿并在每次迭代中打开文件，但这违背了快速执行操作的目的。

0赞 djv 10/20/2023

更新您的问题以显示您如何释放对象。

0赞 Roc 10/20/2023

@djv刚刚更新了它！

0赞 Panagiotis Kanavos 10/20/2023

@Roc根本不使用 Interop。使用 EPPlus 或 ClosedXML 等库将数据直接加载到真正的 Excel 文件中，而无需使用 Excel 本身。您的整个代码可以简化为对 LoadFromDataTable 的单个调用，其中包含标头和样式，例如sheet.Cells.LoadFromDataTable(dt,true,TableStyles.Dark1)

答：

0赞 djv 10/20/2023 #1

尝试释放函数内部的范围

Public Shared Function DT2Excel(exl As Excel.Worksheet, num_filas As Int16, num_iter As Int16, dt As DataTable) As Boolean
    Dim nuevo_rango_inicio As Excel.Range
    Dim nuevo_rango_fin As Excel.Range
    Dim nuevo_rango As Excel.Range
    Try
        Dim rng_inicio As New Tuple(Of Int16, Int16)((num_iter - 1) * num_filas + 2, 1)
        nuevo_rango_inicio = exl.Cells(rng_inicio.Item1, rng_inicio.Item2)

        Dim rng_fin As New Tuple(Of Int16, Int16)(nuevo_rango_inicio.Row + num_filas, nuevo_rango_inicio.Column + dt.Columns.Count - 1)
        nuevo_rango_fin = exl.Cells(rng_fin.Item1, rng_fin.Item2)

        nuevo_rango = exl.Range(nuevo_rango_inicio, nuevo_rango_fin)

        Dim auxDt As DataTable = dt.Clone

        For fila = (num_iter - 1) * num_filas To num_iter * num_filas - 1
            auxDt.ImportRow(dt.Rows(fila))
        Next
        nuevo_rango.Value = ConvertDataTableTo2DArray(auxDt)
        
        Return True
    Catch ex As Exception
        Return False
    Finally
        nuevo_rango = Nothing
        nuevo_rango_inicio = Nothing
        nuevo_rango_fin = Nothing
        GC.Collect()
        GC.WaitForPendingFinalizers()
    End Try

End Function

这可能无关紧要。退出函数应该释放引用，GC 应该正确处理它，但值得一试。

同样在 For 循环的方法中，执行相同的操作。使用块清除引用和 GC。不过，有点奇怪的是，在内存不足异常之后，您似乎在异常处理程序中执行实际工作。CopiarDataTableAExcelFinally

因为我不知道我可以用于范围的最大大小，我宁愿迭代处理它，也不愿确定范围的大小。我不知道如何为每个单元格分配内存，它是否随数据类型而变化等等，所以我设计了这个解决方案，将所有范围切入其他较小的范围，并使函数非常灵活。我的目的是让它与任何 excel 工作表一起使用。

上一个：vb.net 如何从数据库加载当前月份或任何特定月份

下一个：.NET 独立存储

如何在 Interop.Excel 中使用 excel.range 时避免 OutOfMemory 错误

How to avoid OutOfMemory errors while using excel.range in Interop.Excel

评论

评论