4

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) //to remove any special characters to avoid format exception(the datatable has xml content)
        {
            foreach (DataRow row in dtdiv.Rows)
            {
                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++;

        }


        FileStream fs = new FileStream(Server.MapPath("Test.xlsx"), FileMode.Create);
        wb.SaveAs(fs);
        fs.Close();

i have also tried using the following

            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();
user3067170
  • 193
  • 4
  • 14
  • i think there is a 64K limit but I could be mistaken .. do you have a documentation site for the closedxml provider..? also I am wondering if you are having a ComInterop issue where do you dispose of `wb` – MethodMan Aug 19 '15 at 15:38
  • Thanks for the reply. Here is the link of documentation https://closedxml.codeplex.com/documentation?referringTitle=Home seems like there is no limit specified. No am not getting any interop issue. – user3067170 Aug 19 '15 at 15:45
  • have you considered changing the FileStream to use MemoryStream also since you are doing this via the web you need to flush() the data as well here is a link that can lend you some refactor ideas http://stackoverflow.com/questions/19091202/how-to-convert-created-excel-file-using-closed-xml-into-bytes-format – MethodMan Aug 19 '15 at 15:48
  • yes i did tried that using Response and memory stream having the same issue at wb.saveas – user3067170 Aug 19 '15 at 15:54
  • you tried it with flushing the data..? can you show what you have tired in that regard.. – MethodMan Aug 19 '15 at 15:57
  • remove the code from the comments section and put it in your original question just edit your original and add it to the bottom of what you have posted stating something like `I've also tried the following` – MethodMan Aug 19 '15 at 16:01
  • also what if you change your `Response.ContentType` to the following `application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"` another example found here as well http://stackoverflow.com/questions/2519026/how-do-you-stream-an-excel-2007-or-word-2007-file-using-asp-net-and-c-sharp – MethodMan Aug 19 '15 at 16:06
  • i am using the content type you have mentioned – user3067170 Aug 19 '15 at 17:09
  • I've tried FileStream and MemoryStream and they both have the same issue. – Justin Jul 28 '16 at 23:27

0 Answers0