excel - 如何在没有 'Out of Memory' 异常的情况下将大型 DataGridView 导出到 Excel？

Question

我的问题是我需要将 90.000+ 行/143 列从 DataGridView(从 MySQL 数据库填充)导出到 Excel。无论我做什么，我总是在 45k-60k 行之后出现“System.Out.Of.Memory”异常，具体取决于解决方案。我知道可能会有诸如“为什么需要这么多行”之类的问题，我会回答“不幸的是，这是需要的”。我搜索了有关我的问题的论坛，但没有找到任何可行的解决方案。我尝试将 StreamWriter 转换为 CSV，分 block 处理数据(下面的解决方案)，还使用多个 Excel 或 CSV 文件，但没有任何帮助。每次执行期间，当我尝试使用较少的行数时，RAM 使用量都会增长，并且在成功导出后不会释放。我不知道何时以及是否在成功执行后释放 RAM。
测试机器有 8 GB 的 RAM 并且使用的是 Windows 10。不幸的是，我无法使用 MySQL 服务器的资源在那里处理 Excel 导出，然后输出文件与用户共享，所以我需要使用客户端机器。
下面是我最新的不起作用的解决方案，其中数据从 DGV 读取并以 block 的形式写入 Excel。更改 block 的大小不会减少内存消耗，如果我将其缩小(如 500 到 2000)，唯一的影响是导出速度越来越慢。

Imports Excel = Microsoft.Office.Interop.Excel

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

        If DataGridView1.Rows.Count > 0 Then
            Dim filename As String = ""
            Dim SV As SaveFileDialog = New SaveFileDialog()
            SV.FileName = "Worst_cells"

            SV.Filter = "xlsx files (*.xlsx)|*.xlsx|All files (*.*)|*.*"
            SV.FilterIndex = 1
            SV.RestoreDirectory = True

            Dim result As DialogResult = SV.ShowDialog()

            If result = DialogResult.OK Then

                filename = SV.FileName

                Dim XCELAPP As Microsoft.Office.Interop.Excel.Application = Nothing
                Dim XWORKBOOK As Microsoft.Office.Interop.Excel.Workbook = Nothing
                Dim XSHEET As Microsoft.Office.Interop.Excel.Worksheet = Nothing
                Dim misValue As Object = System.Reflection.Missing.Value
                XCELAPP = New Excel.Application()
                XWORKBOOK = XCELAPP.Workbooks.Add(misValue)
                XCELAPP.DisplayAlerts = False
                XCELAPP.Visible = False
                XSHEET = XWORKBOOK.ActiveSheet

                XSHEET.Range("B1").ColumnWidth = 11

                For Each column As DataGridViewColumn In DataGridView1.Columns
                    XSHEET.Cells(1, column.Index + 1) = column.HeaderText
                Next

                Dim rowCnt As Integer = DataGridView1.Rows.Count
                Dim colCnt As Integer = DataGridView1.Columns.Count

                Dim batchSize As Integer = 10000
                Dim currentRow As Integer = 0
                Dim valueObjArray As Object(,) = New Object(batchSize - 1, colCnt - 1) {}

                While currentRow < rowCnt
                    Dim rowIndex As Integer = 0

                    While rowIndex < batchSize AndAlso currentRow + rowIndex < rowCnt

                        For colIndex As Integer = 0 To colCnt - 1
                            valueObjArray(rowIndex, colIndex) = DataGridView1(colIndex, currentRow + rowIndex).Value
                        Next

                        rowIndex += 1
                    End While
                    Dim colName As String = ColumnLetter(colCnt)

                    If (currentRow + batchSize + 1) < rowCnt Then
                        XSHEET.Range("A" + (currentRow + 2).ToString(), colName + (currentRow + batchSize + 1).ToString()).Value2 = valueObjArray
                    Else
                        XSHEET.Range("A" + (currentRow + 2).ToString(), colName + (rowCnt + 1).ToString()).Value2 = valueObjArray
                    End If
                    XWORKBOOK.SaveAs(filename)
                    currentRow += batchSize
                End While

                XCELAPP.DisplayAlerts = True

                XWORKBOOK.Close(False)
                XCELAPP.Quit()

                Try
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(XSHEET)
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(XWORKBOOK)
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(XCELAPP)
                Catch
                End Try

                GC.Collect()
                GC.WaitForPendingFinalizers()
                GC.Collect()
                GC.WaitForPendingFinalizers()
            
            End If
        End If

    End Sub

Answer 1

确认使用Marshal.ReleaseComObject(...);在 Range对象修复 OutOfMemory异常(exception)。下面是用于测试的代码。您将不得不用自己的代码替换几行代码。代码的第一部分是生成大量随机数据。第二部分写出DataTable成 block 的行。通过设置 xls.Visible = true;您可以通过 Excel 窗口底部的进度条看到 Excel 处理每个 block 。

public static void TestExcel(String filename, int maxRows) {
    int numCols = 100;
    Type[] availTypes = new Type[] { typeof(bool), typeof(int), typeof(double), typeof(String), typeof(DateTime) };
    Type[] types = new Type[numCols];
    Random r = new Random();
    DataTable table = new DataTable();
    for (int i = 0; i < numCols; i++) {
        Type ty = availTypes[r.Next(availTypes.Length)];
        types[i] = ty;
        table.Columns.Add("Col" + i, ty);
    }
    DateTime minDate = new DateTime(1901,01,01);
    for (int i = 0; i < maxRows; i++) {
        Object[] arr2 = new Object[numCols];
        for (int j = 0; j < numCols; j++) {
            Object o = null;
            Type ty = types[j];
            if (ty == typeof(bool))
                o = (r.Next(2) == 0 ? false : true);
            else if (ty == typeof(int))
                o = r.Next(int.MinValue, int.MaxValue);
            else if (ty == typeof(double))
                o = r.NextDouble();
            else if (ty == typeof(String)) {
                int len = r.Next(0, 256);
                char c = ExcelUtils.ToLetters(r.Next(26))[0];
                o = new String(c, len);
            }
            else if (ty == typeof(DateTime))
                o = minDate.AddSeconds(r.Next(int.MaxValue));

            arr2[j] = o;
        }
        table.Rows.Add(arr2);   
    }

    XlFileFormat format = XlFileFormat.xlWorkbookDefault;
    if (File.Exists(filename))
        File.Delete(filename);

    DateTime utcNow = DateTime.UtcNow;
    Workbook wb = null;
    Worksheet ws = null;

    Excel xls = new Excel(); // replace with Application.Excel
    xls.Visible = true;
    xls.DisplayAlerts = false;
    if (xls.Workbooks.Count == 0)
        wb = xls.Workbooks.Add();
    else
        wb = xls.Workbooks[1];

    if (wb.Worksheets.Count == 0)
        ws = wb.Worksheets.Add();
    else
        ws = wb.Worksheets[1];

    int maxCellsPerInsert = 1000000; // inserting too much data at once results in an out of memory exception
    int batchSize = maxCellsPerInsert / table.Columns.Count; 
    int fromIndex = 0;
    int n = table.Rows.Count;
    while (fromIndex < n) {
        int toIndex = Math.Min(fromIndex + batchSize, n);
        Range r0 = ws.get_Range("A" + (fromIndex + 1));
        Object[,] arr = DataTableUtils.ToObjectArray(table, false, true, null, fromIndex, toIndex); // replace with your own arr[,] code
        Range r00 = r0.Resize(arr.GetLength(0), arr.GetLength(1));
        r00.Value = arr;
        r00.Dispose(); // replace with Marshal.Release
        r0.Dispose(); // replace with Marshal.Release
        fromIndex = toIndex;
    }

    wb.SaveAs(filename, format, AccessMode: XlSaveAsAccessMode.xlNoChange);
    wb.Close(false, filename, null);
    xls.Quit(false, false);

    long length = FileEx.GetFileLengthFast(filename);
    double totalSeconds = (DateTime.UtcNow - utcNow).TotalSeconds;
    String message = "NumRows: " + maxRows + " duration: " + Math.Round(totalSeconds, 1) + " seconds. File length: " + length + "  rows/sec: " + Math.Round(1.0* maxRows / totalSeconds);
}