Loading

Getting Exception when excel export More than 5 Lakhs Record ?

Getting Exception when excel export More than 5 Lakhs Record ?

Karthik K

Karthik K

6y2.1kLearn iOS Programming

Hi All,

I am facing issue when I try to export more than 5 Lakh Records in Asp.Net Excel Export. I am getting

Exception of type 'System.OutOfMemoryException' was thrown.

The above exception. If anyone knows for the solution, Suggest me with Sample Code.

Code which i used :

//Create a dummy GridView
GridView GridView1 = new GridView();
GridView1.ShowFooter = true;
GridView1.AllowPaging = false;
GridView1.DataSource = Dt2;
GridView1.DataBind();
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition",
"attachment;filename=MemberHistory.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
for (int i = 0; i < GridView1.Rows.Count; i++)
{
//Apply text style to each Row
GridView1.Rows[i].Attributes.Add("class", "textmode");
}
GridView1.RenderControl(hw);
//style to format numbers to string
string style = @"";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();

Thanks in advance

Karthik K

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Leon D6y ago
You can use a 3rd party library like free spire.xls(https://www.nuget.org/packages/FreeSpire.XLS/) to export data to excel, it's very easy to use and does not require MS Office to be installed.
//export datatable to excel
Workbook book = new Workbook();
Worksheet sheet = book.Worksheets[0];
sheet.InsertDataTable(datatable, true, 1, 1);
book.SaveToFile("insertTableToExcel.xlsx",ExcelVersion.Version2013);
0
Farhan Ahmed6y ago
here are some examples

https://www.codeproject.com/Tips/659666/Export-very-large-data-to-Excel-file
https://www.c-sharpcorner.com/UploadFile/4b0136/exporting-large-table-data-to-excel-in-Asp-Net/
https://stackoverflow.com/questions/52253129/export-large-data-to-excel-in-asp-net-c-sharp
https://forum.aspose.com/t/exporting-1-million-records-to-excel/92726
0