This project has moved. For the latest updates, please go here.

Out of Memory Exception when exporting more than 50k

Aug 19, 2015 at 8:57 PM
I am using closedxml to export datatable to excel in asp.net. it works fine if i export less than 50k rows but throwing exception when i export more rows. It has 31 columns. I am Spliting the datatable to 10000 rows then adding the datatable to a seperate sheet. Would appreciate if some one help me. Below is the code.
   XLWorkbook wb = new XLWorkbook();

    foreach (DataTable dtdiv in splitdt) 
    {
        foreach (DataRow row in dtdiv.Rows)//to remove any special characters to avoid format                   exception(the datatable has xml content)
        {
            for (int i = 0; i < dtdiv.Columns.Count; i++)
            {
                if (dtdiv.Columns[i].DataType == typeof(string))
                {
                    if (row[i] != System.DBNull.Value)
                    {
                        row[i] = ReplaceHexadecimalSymbols((string)row[i]);
                    }
                }
            }
        }
        string newString = "report_" + k;

        wb.AddWorksheet(dtdiv, newString);
        k++;

    }


        Response.Clear();
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    Response.AddHeader("content-disposition", "attachment;filename=" + (String)Session["MatrixOutputFileName"]);

    using (MemoryStream memoryStream = new MemoryStream())
    {
        wb.SaveAs(memoryStream);
        memoryStream.WriteTo(Response.OutputStream);
        memoryStream.Close();
    }
    Response.End();
Aug 20, 2015 at 3:34 PM
I had the same issue with Closed XML running out of memory with 100K+ rows and 65 columns running in a 32bit environment. It does not run out of memory on a 64bit bit server with IIS but is very slow.

My solution was to generate a very basic spreadsheet using the Open XML dll. What took over 25 minutes to generate with Closed XML generated in less than 5 minutes using the basic Open XML approach. But be advised that there was no formatting or any other processing whatsoever.
Aug 24, 2015 at 1:10 PM
Thank you for the response. Will Try using OpenXML. Is there any limitation for OpenXML.Can you please point to any place where i can check an example.