Calling LoadFromDataTable gives System.OutOfMemoryException

Jan 3, 2011 at 1:33 PM

Hi

I am using following code to export dataTable to excel.

 using (ExcelPackage pck = new ExcelPackage(newFile))
                {
                    //Create the worksheet
                    ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Accounts");
                    //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
                    ws.Cells["A1"].LoadFromDataTable(dataTable, true);
                    pck.Save();
                                       
                } 



I have around 45000 Rows and 71 Columns in the table. When it is excecuting LoadFromDataTable i get System.OutOfMemory exception.I have 4 GB Ram installed in my machine.

Please help.

Coordinator
Jan 3, 2011 at 4:07 PM

Hi,

I have loaded about 25,000,000 cells on my 8GB RAM dev machine. Then the taskmanager shows 0GB physical memory and everything starts to go really slow. Of course the memory usage depends on many things, like the what kind of data you load, how much memory other process use and a lot of other things as well.

I suggest you you try to load the datatable manually, setting the values yourself (something like ... ws.Cells[row,col].value). Have a look at the taskmanager. If it goes down to 0 than you are obviously out of memory. Report back how many cells you are able to load and the amount of memory the process takes.

If you have the hardware, try it on a machine with more memory.

I should try to memory optimize the ExcelCell class some time in the future.

Jan

Jan 4, 2011 at 7:20 AM

Hi

I tried to iterate through datatable as you suggested but still i am facing same problem.

I tried to export 1 million records with 14 columns , I got system.outofmemory exception around 44K rows. Even after operation is completed memory is not released.

 

Heare is the code snippet i am using

 

                using (ExcelPackage pck = new ExcelPackage(newFile))
                {
                    //Create the worksheet
                    ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Accounts");
                    for (int row = 1; row <= exportStateData.ExportDataTable.Rows.Count; row++)
                    {
                        for (int column = 1; column <= exportStateData.ExportDataTable.Columns.Count; column++)
                        {
                            ws.Cells[row, column].Value = exportStateData.ExportDataTable.Rows[row - 1][column - 1];
                        }
                    }

                    pck.Save();


                }

 

Coordinator
Jan 4, 2011 at 8:46 AM

Just to make sure, are you using version 2.8?

Jan 4, 2011 at 11:09 AM

Yes..I am using version 2.8.1

Coordinator
Jan 4, 2011 at 12:15 PM

OK

How much avalible physical memory do you have when you start to generate the sheet?

I'm not sure what the problem can be, but make sure you dont run any other memory consuming process on the same computer at the same time (like sql server)