Memory problem with ClosedXML

May 11, 2013 at 5:04 PM
I am doing a test where I create a workbook, then add two spreadsheets on it and 500,000 rows with one test value in the first cell. Every object is initialised with the using keyword.

Memory consumption shoots up to 1GB and stays there, even after I have saved the workbook and exited the using bracket (but not the application).

I have profiled the app using ANTS. It looks as if the bulk of the memory is held in Generation 2 and consists of XLAddress, XLRow, XLCell and string objects (these must be the values)

I am not sure if this is standard behaviour? Ideally I would like ClosedXML to release those resources when it's finished because the service on which I am using it runs many such reports.

Is there any way of marshalling back that memory, other than using GC.Collect() ?
May 13, 2013 at 10:06 PM
I was having a similar problem.
The quick version is that I was doing a loop in the loop.
My spreadsheet have about 250,000 rows.
The first pass of the 2nd loop took my 8g machine into an out of memory crash.

I solved by doing a .select to "find" the row for which I was looking.

If you want to post the most relevant code,. I will take a look at.

Jun 10, 2013 at 6:31 PM
Thanks for the reply, Mitch. This was only a test to assess ClosedXML's memory usage. Looking at XLAddress, XLRow and XLCell I can see that they do not implement IDisposable, and I cannot see how resources are released. From my tests it seems like they keep holding on to a lot of memory even after the using block is exited. I would be keen to read other members' opinions on this.
Aug 5, 2013 at 3:07 PM
I am also finding memory consumption to be an issue. I am using the library within a website, and w3p.exe memory consumption keeps going up.
Aug 6, 2013 at 2:57 AM
I'm just getting back to the project so be patient with me :)

As Mitch said you usually run into these kinds of problems if you're creating objects inside a loop. In general the "using" keyword is not necessary unless you're creating workbooks/worksheets inside a loop. That is because the objects are released when the method exits.

I'd be happy to look at the issue if you guys can give me a block of code I can use to reproduce the issue.

Jan 9, 2014 at 2:02 PM
I'm having a similar problem with memory not being de-allocated when the workbook/worksheet are disposed.
I have created a datatable and am creating a workbook/worksheet directly from it:
            Dim WorkBook As XLWorkbook = New XLWorkbook()
            WorkBook.CalculateMode = XLCalculateMode.Manual
            Dim WorkSheet As IXLWorksheet = WorkBook.Worksheets.Add(SheetName)
            WorkSheet.Cell(1, 1).InsertTable(Datatbl.AsEnumerable())            'insert the entire datatable
        Catch ex As Exception
            LOG("CreateXLS error: " & ex.Message)
        End Try
After this has completed, the workbook/worksheet look great but it leaves me with over 150 MB of allocated memory when the application usually has about 15MB prior to workbook creation. I also dispose the datatable right afterwards as well.
Any advice on how I can release this memory?