0

I stuck in the issue of exporting excel it gives error System.OutOfMemoryException in excel 2007 we cam export 1000000 rows but while exporting more then 250000 rows its gives the error the logic to upload the excel as i am using is Response.Clear(); Response.Charset = "";

    Response.Buffer = true;
    Response.ContentType = "application/vnd.ms-excel";
    string FileName = "PoliciesDetailsForBranch";
    Response.AppendHeader("content-disposition", "attachment; filename=" + FileName + ".xls");
    System.IO.StringWriter sw = new System.IO.StringWriter();
    System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(sw);
    GridView gv = new GridView();
     this.EnableViewState = false;
    gv.DataSource = (DataTable)dt;
    gv.DataBind();
    this.ClearControls(gv);
    gv.RenderControl(hw);
    Response.Write(sw.ToString());
    Response.End();

Can any one give me the solution to cope up with this issue.

icedwater
  • 4,701
  • 3
  • 35
  • 50
Ritesh Ranjan
  • 1
  • 2
  • 2
  • 2
    Check this answer, is almost the same. http://stackoverflow.com/questions/3736040/outofmemoryexception-when-creating-huge-string-in-asp-net/3736080 – Aristos Sep 20 '10 at 07:42
  • @Aristos yes, very similar, also the solution in both is use Response.Output, but it had a slight difference i.e. the other had to write lines, while @Ritesh had to RenderControl to it. – eglasius Sep 20 '10 at 08:52

1 Answers1

1

Instead of using a StringBuilder and causing the whole thing to be converted to a string in memory, send it directly to the Response.Output.

Response.Buffer = true;//left unmodified, but why were you buffering?
Response.ContentType = "application/vnd.ms-excel";
string FileName = "PoliciesDetailsForBranch";
Response.AppendHeader("content-disposition", "attachment; filename=" + FileName + ".xls");
GridView gv = new GridView();
this.EnableViewState = false;
gv.DataSource = (DataTable)dt;
gv.DataBind();
this.ClearControls(gv);
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(Response.Output);
gv.RenderControl(hw);
Response.End();
eglasius
  • 35,831
  • 5
  • 65
  • 110
  • Dear Eglasius, Thsanks for your kind response but it still gives compile time error cannot convert from System.IO.TextWriter to System.Web.UI.HtmlTextWriter. while convering response.output to HtmlTextWriter its gives run time error Unable to cast object of type 'System.Web.HttpWriter' to type 'System.Web.UI.HtmlTextWriter. kindly do the needful. – Ritesh Ranjan Sep 20 '10 at 10:16
  • @Ritesh instantiate a HtmlTextWriter passing Response.Output / updated the code in the answer. – eglasius Sep 20 '10 at 16:44
  • Hi Eglasius,there is issue when 5.5 lacks record is binding in the gridview it gives the error System.OutOfMemoryException. kindly help me to cope up with issue. because i have to export 1000000 records in excel and when i tryied for 5 lacks record its fail on gridview binding.please do the needful. – Ritesh Ranjan Sep 27 '10 at 08:13
  • what do you mean with: "5.5 lacks record"? anyway, I just re-read it much less focused on the specific issue. Why are you using an asp.net control to render its html as excel data being sent to the client? Even if was working, you'll run into a limit because its loading All the data into that control. Even if what's rendered is streamed, it still create all the children collection. Also note you already have that data in a DataTable that is as well all held in memory. If you want the real thing you need to: use a reader processing by row, and stream transformed rows to excel data tothe browser – eglasius Sep 27 '10 at 08:50
  • Dear Eglasius, Can you provide me the sample code for the suggested solutions. it will help me to overcome with the excel export issue. – Ritesh Ranjan Sep 27 '10 at 10:01
  • You should do some searches, this is bordering doing it. See http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx, and look out there for alternatives to generate a excel file in .net. – eglasius Sep 27 '10 at 15:58