I want to export DB table to excel. DB table has approx 5 804 806 records which I want to export to an excel.
I have tried different ways but getting
Insufficent memory to continue the execution of program
When I'm exporting less data then below function is working fine but as try to pull report with 5 million records it is throwing insufficient memory error.
How can I achieve it?
Code I have used but not working:
public static void GetExcelFromDataTable(DataTable dt, string fileName)
{
string attachment = "attachment; filename=" + fileName;
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache);
HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.xls", fileName));
//HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; //Excel 2003
HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; //Excel 2007
string tab = "";
foreach (DataColumn dc in dt.Columns)
{
HttpContext.Current.Response.Write(tab + dc.ColumnName);
tab = "\t";
}
HttpContext.Current.Response.Write("\n");
int i;
foreach (DataRow dr in dt.Rows)
{
tab = "";
for (i = 0; i < dt.Columns.Count; i++)
{
if (dr[i].ToString().Trim() == "\"")
{
dr[i] = " \" ";
}
// HttpContext.Current.Response.Write(tab + dr[i].ToString());
if (dr[i].ToString().StartsWith("0"))
{
HttpContext.Current.Response.Write(tab + @"=""" + dr[i].ToString().Trim() + @"""");
}
else
{
HttpContext.Current.Response.Write(tab + dr[i].ToString().Trim());
}
tab = "\t";
}
HttpContext.Current.Response.Write("\n");
}
HttpContext.Current.Response.End();
}
ADO code:
if (ReportName == "SalesStatusVsMRPStatus")
{
query = "select Material,Material_Description,Eng_Resp,PLDv,SalesOrg,DC,Valid_from,SS,ItemCG,DelPlant,DelPlantMS,DelPlantValid_from,Grp From " + ReportName;
//query = "SELECT TOP 10 * FROM " + ReportName;
}
else
{
query = "select * From " + ReportName;
}
SqlCommand cmd = new SqlCommand(query, cn);
SqlDataAdapter adap = new SqlDataAdapter(cmd);
DataTable dtUserData = new DataTable();
cmd.CommandTimeout = 999999;
cmd.CommandType = CommandType.Text;
try
{
cn.Open();
adap.Fill(dtUserData);
foreach (DataColumn dc in dtUserData.Columns)
{
if (dc.ColumnName == "Grp")
{
dc.ColumnName = "AuthGrp";
}
}
}
catch (Exception ex)
{
}
finally
{
cmd.Connection.Close();
cn.Close();
GridViewExportUtil.GetExcelFromDataTable(dtUserData, fileName);
}
}
how can I achieve it?
