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

Answer 1

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.

Answer 2

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