Export to excel in c# results in System.OutOfMemoryException

Question

I have a DataTable with 200,000 rows.

I want to export DataTable in excel file. But it gives System.OutOfMemoryException error.

using (ExcelPackage objExcelPackage = new ExcelPackage()) 
{ 
    ExcelWorksheet objWorksheet = 
        objExcelPackage.Workbook.Worksheets.Add("Conversion Data");  
    objWorksheet.Cells["A1"].LoadFromDataTable(FinalResult, true); 
}

using (ExcelPackage objExcelPackage = new ExcelPackage()) { ExcelWorksheet objWorksheet = objExcelPackage.Workbook.Worksheets.Add("Conversion Data"); objWorksheet.Cells["A1"].LoadFromDataTable(FinalResult, true); — Vimal Sharma, Sep 22 '17 at 07:27
ExcelPackage appears to be a third party library which hasn't been updated in at least 2 years. Why aren't you using the proper OpenXML SDK? — Equalsk, Sep 22 '17 at 07:46
Better yet, why not use EPPlus? It can load data from datatables and collections just fine and makes creating xlsx files a lot easier. — Panagiotis Kanavos, Sep 22 '17 at 07:48
*Where* does this error appear though? Post the full exception, including the call stack. How *do* you create that data table? You can get an OOM if you start with a list or datatable without rows and keep adding them one at a time. The collection will double its internal buffer each time it runs out. With 200K rows, that's a lot of discarded buffers. Memory may get so fragmented that the runtime can't allocate memory for the latest buffer — Panagiotis Kanavos, Sep 22 '17 at 07:49
You can avoid an OOM if you specify the capacity when you create a table or list. — Panagiotis Kanavos, Sep 22 '17 at 07:50
@PanagiotisKanavos is right in his comment to my answer, I jumped to the conclusion that the problem was due to the size of your datatable. As such, you may want to try [this solution](https://stackoverflow.com/a/29912563/6741868) first, or [this one](https://stackoverflow.com/a/34831519/6741868). — Keyur PATEL, Sep 22 '17 at 07:57

score 0 · Answer 1 · answered Sep 22 '17 at 07:48

This answer gives a method to split datatables into smaller chunks (its generic but will work with datatables), and since the issue you need to fix is the size of the datatable, something like this should work:

public static IEnumerable<IEnumerable<T>> ToChunks<T>(this IEnumerable<T> enumerable, int chunkSize)
{
    int itemsReturned = 0;
    var list = enumerable.ToList(); // Prevent multiple execution of IEnumerable.
    int count = list.Count;
    while (itemsReturned < count)
    {
        int currentChunkSize = Math.Min(chunkSize, count - itemsReturned);
        yield return list.GetRange(itemsReturned, currentChunkSize);
        itemsReturned += currentChunkSize;
    }
}

After that, use it as follows:

int rowCount = 1;
int chunkSize = 50000;
using (ExcelPackage objExcelPackage = new ExcelPackage()) 
{ 
    ExcelWorksheet objWorksheet = objExcelPackage.Workbook.Worksheets.Add("Conversion Data"); 
    //objWorksheet.Cells["A1"].LoadFromDataTable(FinalResult, true);
    foreach (var smallerTable in FinalResult.AsEnumerable().ToChunks(chunkSize))
    {
        objWorksheet.Cells["A" + rowCount].LoadFromDataTable(smallerTable, true);
        rowCount += chunkSize;
    }

Basically, rowCount is to keep track of where on the Excel sheet to add the rows, after each iteration it will be 1, 50001, 100001, etc to insert to A1, A50001, A100001, etc.

If there is still an error for memory, try smaller and smaller chunkSize until it works.

Why do you assume the issue is the size of the datatable? – Panagiotis Kanavos Sep 22 '17 at 07:51 — Panagiotis Kanavos, Sep 22 '17 at 07:51

score 0 · Answer 2 · answered Apr 27 '22 at 06:26

Try do this....

I have large data 2 lakhs and above. First i am using excel package. Local system is working fine , but iis(static ip address) hosting after (worksheet.cells/worksheet.range) OutOfMemoryeException error thorw. So i am using StreamWriter and write to .xls file. After xls convert to xlsx file. Its working for me. My english is not good . please understand it.

private void ExportExcelFile(DataTable dt)
    {
        try
        {
            //open file
            //non english not support(ex: Bangla Language)"সালাউদ্দিন স্টোর";
            // StreamWriter wr = new StreamWriter(@"D:\TestBook.xls");

            //non english support(ex: Bangla Language)
            StreamWriter wr = new StreamWriter(@"D:\TestBook.xls", true, Encoding.Unicode); // Encoding.Unicode or Encoding.UTF32

            for (int i = 0; i < dt.Columns.Count; i++)
            {
                wr.Write(dt.Columns[i].ToString().ToUpper() + "\t");
            }

            wr.WriteLine();

            //write rows to excel file
            for (int i = 0; i < (dt.Rows.Count); i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (dt.Rows[i][j] != null)
                    {
                        wr.Write(Convert.ToString(dt.Rows[i][j]) + "\t");
                    }
                    else
                    {
                        wr.Write("\t");
                    }
                }
                //go to next line
                wr.WriteLine();
            }
            //close file
            wr.Close();

            //xls to xlsx convertion
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Open(@"D:\TestBook.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            wb.SaveAs(@"D:\TestBook.xlsx", Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            wb.Close();
            app.Quit();
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

Export to excel in c# results in System.OutOfMemoryException

2 Answers2