public static void ExportToExcel(DataTable dtExcel, string fileName)
{
string attachment = "attachment; filename=" + fileName + ".xlsx";
System.Web.HttpContext.Current.Response.ClearContent();
System.Web.HttpContext.Current.Response.AddHeader("content-disposition", attachment);
System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
//System.Web.HttpContext.Current.Response.ContentType = "application/excel";
//System.Web.HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//System.Web.HttpContext.Current.Response.ContentType = string.Empty;
string tab = "";
foreach (DataColumn dc in dtExcel.Columns)
{
System.Web.HttpContext.Current.Response.Write(tab + dc.ColumnName);
tab = "\t";
}
System.Web.HttpContext.Current.Response.Write("\n");
int i;
foreach (DataRow dr in dtExcel.Rows)
{
tab = "";
for (i = 0; i < dtExcel.Columns.Count; i++)
{
System.Web.HttpContext.Current.Response.Write(tab + dr[i].ToString());
tab = "\t";
}
System.Web.HttpContext.Current.Response.Write("\n");
}
System.Web.HttpContext.Current.Response.Flush();
System.Web.HttpContext.Current.Response.Close();
}
Above code snippet generate only .xls format .If i change response time and File-name extension to .xlsx it does not work .Is there any other approach to do the same.
As per suggestion i have changed approach and generating .xlsx using openxml library. But it throws error Unable to determine the identity of domain while generating large xlsx file.Please help
public static void ExportToexcel(DataTable dtExcel, string fileName)
{
System.Web.HttpContext.Current.Response.ClearContent();
OpenXMLOffice openxmloffice = new OpenXMLOffice();
MemoryStream msXML = openxmloffice.DataTableToMemoryStream(dtExcel);
msXML.Seek(0, SeekOrigin.Begin);
msXML.WriteTo(System.Web.HttpContext.Current.Response.OutputStream);
System.Web.HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=DataTable.xlsx");
System.Web.HttpContext.Current.Response.StatusCode = 200;
System.Web.HttpContext.Current.Response.Flush();
System.Web.HttpContext.Current.Response.Close();
}
