Блог

Моя проблема в том, что мне нужно экспортировать более 90 000 строк / 143 cols из DataGridView (заполненного из базы данных MySQL) в Excel. Что бы я ни делал, я всегда получаю исключение «System.Out.Of.Memory» после 45-60 тыс. строк в зависимости от решения. Я знаю, что могут возникнуть вопросы типа «Зачем вам столько строк», и я бы ответил, что «К сожалению, это необходимо.’ Я искал форумы о своей проблеме, но не нашел никакого рабочего решения. Я попробовал StreamWriter в CSV, обрабатывая данные порциями (решение ниже), также используя несколько файлов Excel или CSV, но ничего не помогло. Каждый раз во время выполнения использование оперативной памяти растет и не освобождается после успешного экспорта, когда я пытаюсь использовать меньшее количество строк. Я не знаю, когда и будет ли освобождена оперативная память после успешного выполнения.

Тестовые машины имеют 8 ГБ оперативной памяти и используют Windows 10. К сожалению, я не могу использовать ресурсы сервера MySQL для обработки экспорта Excel там, а затем вывода файла для совместного использования с пользователем, поэтому мне нужно использовать клиентские компьютеры.

Ниже приведено мое последнее нерабочее решение, в котором данные считываются из DGV и записываются в Excel порциями. Изменение размера блоков не уменьшает потребление памяти, и если я сделаю его меньше (например, от 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
 

Подтверждено, что использование Marshal.ReleaseComObject(...); на Range объектах устраняет OutOfMemory исключение. Ниже приведен код, используемый для тестирования. Вам придется заменить пару строк кода на свои собственные. Первая часть кода заключается в генерации большого количества случайных данных. Вторая часть записывает DataTable строки по частям. Установив xls.Visible = true; , вы можете видеть, как Excel обрабатывает каждый фрагмент с индикатором выполнения в нижней части окна Excel.

 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.xlWorkbookDefau<
    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);
}
 

После долгих испытаний и помощи других пользователей (особенно ненавистных) Я обнаружил, что экспортировать большой DataGridView в Excel без исключения исключения из памяти невозможно при стандартном подходе (я не тестировал Oledb или Xml). Рабочим решением для меня было экспортировать саму datatable, которая была источником данных для DGV. Пожалуйста, обратите внимание, что такое решение подходит, когда вы выполняете экспорт в том же процессе сразу после заполнения DGV данными. В противном случае, если вы захотите экспортировать данные впоследствии, например, после нажатия кнопки, вам нужно будет объявить datatable как общедоступный, чего я бы не стал делать. Похоже, что сбой памяти, который происходит при экспорте непосредственно из DGV, заключается в том, что после считывания фрагментов данных из DGV и последующего копирования их в Excel range, эти фрагменты остаются в памяти (я не знаю, почему это так). Одним из ключевых моментов в этом решении является то, что чтение из datatable и последующая запись в Excel выполняются пакетно. Мой случай заключается в том, что мне нужно экспортировать более 90 000 строк в Excel. Для 90 000 строк я использовал размер пакета в 25 000 строк, который отлично работал для 90 тыс. ячеек. Но для большего количества строк, таких как 270k или 360k, которые я тестировал, я использовал меньшее значение пакета в 10000 строк. Это потому, что моя WinForm уже перегружена памятью, отображая большой DGV. Итак, если в DGV есть 270 тыс. строк, то экспорт с пакетом из 25 000 запускает исключение. Но с 10.000 это было нормально, хотя время экспорта больше. Что касается времени экспорта: 90 тыс. строк с пакетом из 25 тыс. заняли в моей среде 1 мин 05 сек; 270 тыс. строк с пакетом из 10 тыс. заняли 9 минут, а 360 тыс. строк с пакетом из 10 тыс. заняли 15 минут.

         Dim filename As String = ""
        Dim SV As SaveFileDialog = New SaveFileDialog()
        SV.FileName = "Excel export"

        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

            'export column headers to Excel is shown below
            Dim i As Integer = 1
            For Each column As DataColumn In dataTab.Columns
                xSheet.Cells(1, i) = column.ColumnName
                i = i   1
            Next

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

            Dim batchSize As Integer = 10000 'export will de done in batches
            Dim startRow As Integer = 0 'starting row for each batch
            Dim valueObjArray As Object(,) = New Object(batchSize - 1, colCnt - 1) {}
            'object array with a size of the batch x number of columns

            While startRow < rowCnt 'iterate until max row number is exceeded
                Dim rowIndex As Integer = 0

                'iterate each until row index reaches batch size
                While rowIndex < batchSize AndAlso startRow   rowIndex < rowCnt

                    'iterate each cell in the row until last column is reached
                    'and assign the value of the cell in datatable to the object array
                    For colIndex As Integer = 0 To colCnt - 1
                        valueObjArray(rowIndex, colIndex) =
                            dataTab.Rows(startRow   rowIndex).Item(colIndex)
                    Next

                    rowIndex  = 1 'go to new row
                End While

                Dim colName As String = ColumnLetter(colCnt) 'transform column index to Excel column name

                '("if" below) assign object array to Excel range if batch range   starting row is less than total rows
                If (startRow   batchSize   1) < rowCnt Then
                    Dim r As Excel.Range = xSheet.Range("A"   (startRow   2).ToString(),
                                                        colName   (startRow   batchSize   1).ToString())
                    r.Value2 = valueObjArray
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(r) 'this might be not needed
                Else 'if batch range   starting row is more than total rows assign to Excel range only the remaining rows
                    Dim r As Excel.Range = xSheet.Range("A"   (startRow   2).ToString(),
                                                        colName   (rowCnt   1).ToString())
                    r.Value2 = valueObjArray
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(r) 'this might be not needed
                End If
                xWorkbook.SaveAs(filename)
                startRow  = 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
 

Ниже приведена функция преобразования индекса столбца в имя столбца Excel.

     Function ColumnLetter(ColumnNumber As Long) As String
        Dim n As Long
        Dim c As Byte
        Dim s As String

        n = ColumnNumber
        Do
            c = ((n - 1) Mod 26)
            s = Chr(c   65) amp; s
            n = (n - c)  26
        Loop While n > 0
        ColumnLetter = s
    End Function