How to export large DataGridView to Excel without 'Out of Memory' exception?

Question

My problem is that I need to export 90.000+ rows / 143 cols from a DataGridView (populated from MySQL database) to Excel. Whatever I do I always end up with 'System.Out.Of.Memory' exception after 45k-60k rows depending of the solution. I know that there could be questions like 'Why do you need so much rows' and I would answer that 'Unfortunately this is needed.' I have searched forums about my problem but haven't found any working solution. I tried StreamWriter to CSV, processing data in chunks (the solution below), also using multiple Excel or CSV files, but nothing helped. Every time during execution RAM usage is growing and not released after a successful export, when I try with less amount of rows. I don't know when and if after a successful execution RAM is released.

Test machines have 8 GB of RAM and are using Windows 10. Unfortunately I am not able to use the resources of MySQL server for processing of Excel export there and then output file to be shared with user, so I need to use the client machines.

Below is my latest not-working solution, where data is read from DGV and wrote to Excel in chunks. Changing the size of chunks is not reducing memory consumption and if I make it smaller (like 500 to 2000) the only effect is that exporting is getting slower.

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

After a lot of testing and help from other users (especially Loathing) I found out that to export large DataGridView to Excel without throwing an exception Out of Memory is not possible with standard approach (I haven't tested Oledb or Xml). A working solution for me was to export the very datatable, which was the datasource for DGV. Please note that such a solution is suitable when you do the export in the same process just after DGV population with data. Otherwise if you want to export the data afterwards, for example after clicking a button, then you will need to declare the datatable as Public, which I would not do. It seems that memory outage that happens when exporting directly from DGV is that after reading chunks of data from DGV and then copy them to Excel range, these chunks remain in memory (I don't know why is that). One key thing in this solution is that reading from datatable and then writing to Excel is done in batches. My case is that I need to export 90.000+ rows to Excel. For 90.000 rows I used a batch size of 25.000 rows, which worked fine for 90k cells. But for larger number of rows like 270k or 360k, which I tested, I used a smaller batch value of 10.000 rows. This is because my WinForm is already burdened memory-wise by displaying a large DGV. So if in DGV there 270k rows then exporting with a batch of 25.000 runs into exception. But with 10.000 this was fine, although exporting time is larger. Regarding exporting times: 90k rows with batch of 25k took in my environment 1 min 05 sec; 270k rows with batch of 10k took 9 min and 360k rows with batch of 10k took 15 min.

        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

The function for transformation of column index to Excel column name is below.

    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) & s
            n = (n - c) \ 26
        Loop While n > 0
        ColumnLetter = s
    End Function

Answer 2

Confirmed that using Marshal.ReleaseComObject(...); on the Range objects fixes the OutOfMemory exception. Below is the code used for testing. You will have to replace a couple of lines of code with your own. The first part of the code is to generate a large amount of random data. The second part writes out the DataTable rows in chunks. By setting xls.Visible = true; you can see Excel processing each chunk with the progress bar at the bottom of the Excel window.

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