-1

I'm transferring my datagridview data to an excel file using EPPlus, my problem is the process is eating memory, what am I missing to free up the used memory? on the outside it seems fine with a few thousand rows but the memory used by the program rises and doesn't go back down after exporting and saving to excel. Now when I try to export a million rows I run out of memory.

Here's my code, this process runs on a background worker.

Using p = New ExcelPackage
        Dim sheetnum As Integer = 2
        Dim ws As ExcelWorksheet = CreateSheet(p, "report")
        For Each dgcol As DataGridViewColumn In dg.Columns
            ws.Cells(1, col).Value = dgcol.HeaderText
            col += 1
        Next

        For Each rowx As DataGridViewRow In dg.Rows
            For Each colx As DataGridViewColumn In dg.Columns
                ws.Cells(row, colx.Index + 1).Value = dg.Rows(rowx.Index).Cells(colx.Index).Value
            Next
            row += 1
            BackgroundWorker1.ReportProgress(CInt(100 * Integer.Parse(rowx.Index + 1) / dg.Rows.Count), CInt(100 * Integer.Parse(rowx.Index + 1) / dg.Rows.Count))
            If row = 1048577 Then 'Check if max rows have been reached and create a new sheet
                ws = CreateSheet(p, "report" & sheetnum)
                sheetnum += 1
                row = 2
                col = 1
                For Each dgcol As DataGridViewColumn In dg.Columns
                    ws.Cells(1, col).Value = dgcol.HeaderText
                    col += 1
                Next
            End If
        Next

        Dim bin() As Byte = p.GetAsByteArray()
        File.WriteAllBytes(Environment.GetFolderPath(Environment.SpecialFolder.Desktop) & "\" & "report.xlsx", bin)
    End Using
crimson589
  • 1,238
  • 1
  • 20
  • 36
  • You need to dispose [`ExcelWorksheet`](https://epplus.codeplex.com/SourceControl/latest#EPPlus/ExcelWorksheet.cs) after you finished working with it. – Reza Aghaei Feb 22 '17 at 23:48
  • @RezaAghaei Thanks for the suggestion but that didn't work. – crimson589 Feb 22 '17 at 23:53
  • I'm not sure if it solve the whole problem, but if you take a look at implementation of the `Dispose` method for the `ExcelWorksheet` class, you will see the code releases many resources and probably the author of code knows better than us which that some resources needs to be released. – Reza Aghaei Feb 22 '17 at 23:56
  • @RezaAghaei I don't know how much it should have helped but it did something but not much as I was expecting, I tried exporting around 13000 rows, my program was using around 40mb of memory, when I started the export process it jumped to 75mb, without the dispose command it stayed there, with the dispose command it went down to 68-70mb, I was expecting it to just go back to around 40mb. Anyway this still won't give me a solution to running out of memory if I export a million rows. Thanks. – crimson589 Feb 23 '17 at 00:00
  • Take a look at some links about EPPlus memory usage like [this](http://stackoverflow.com/questions/26787443/epplus-large-dataset-issue-with-out-of-memory-exception). – Reza Aghaei Feb 23 '17 at 00:07

1 Answers1

0

Since it's encased in a using block, the ExcelPackage object is properly disposed of. You said that you have also disposed of the ExcelWorksheet object in the comments and that did not work out.

However, i think that you are not disposing the byte array that you write to file in the last line of code (before the End Using)

Try that. Hope it helps.

Mihai Ovidiu Drăgoi
  • 1,307
  • 1
  • 10
  • 16