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

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

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