Export to Excel from Data Table in Asp.Net

Debasis Saha
10y
46.7k
0
0

Blog

In Asp.Net some time we need to export datatable into Excel file format for reporting purpose. For full this requirement, we can convert the data into excel very easily. For that, we first create a Datatable as below:

public DataTable CreateTable()
{
DataTable dt = new DataTable();
dt.Columns.Add("EmployeeCode", typeof(string));
dt.Columns.Add("EmployeeName", typeof(string));
dt.Columns.Add("Address", typeof(string));
dt.Columns.Add("City", typeof(string));
dt.Columns.Add("PinCode", typeof(Int32));
dt.Columns.Add("PhoneNo", typeof(string));
return dt;
}

After it, we add some data into this data table. And After it, we want to export this data into on click of a Button. Against click event, we call the function ExportToExcel as below:

private void ExportToExcel(DataTable dtExcel)
{
try
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.Write(@"<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">");
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=Salary_Cert.xls");
HttpContext.Current.Response.Charset = "utf-8";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250");
HttpContext.Current.Response.Write("<font style='font-size:11.0pt; font-family:Calibri;'>");
HttpContext.Current.Response.Write("<BR><BR>");
HttpContext.Current.Response.Write("<Table border='0' bgColor='#ffffff' " +
"borderColor='#000000' cellSpacing='0' cellPadding='0' " +
"style='font-size:11.0pt; font-family:Calibri; background:white;'>");
#region Report Header
HttpContext.Current.Response.Write("<TR valign='top'>");
HttpContext.Current.Response.Write("<B><U><TD align='center' colspan='9' style='font-size:14.0pt;text-weight:bold;text-decoration:underline;'>TO WHOMSOEVER IT MAY CONCERN</TD>");
HttpContext.Current.Response.Write("</U></B></TR>");
HttpContext.Current.Response.Write("<TR valign='top'><TD align='left' colspan='9'> Employee Personal Details </TD></TR>");
HttpContext.Current.Response.Write("<TR valign='top'><TD align='left' colspan='9'> </TD></TR>");
HttpContext.Current.Response.Write("<TR valign='top'><TD align='left' colspan='9' rowspan='3' style='whitespace:normal;'>");
HttpContext.Current.Response.Write("</TD></TR>");
#endregion
#region Header Row
HttpContext.Current.Response.Write("<TR valign='top'><td colspan='10'");
HttpContext.Current.Response.Write("<Table border='1' bgColor='#FFFFFF' " +
"borderColor='#000000' cellSpacing='0' cellPadding='0' " +
"style='font-size:10.0pt; font-family:Calibri; background:white;'>");
HttpContext.Current.Response.Write("<TR valign='top' style='background:#D8D8D8;'>");
HttpContext.Current.Response.Write("<TD align='left' style='width:20%;'>Employee Code</TD>");
HttpContext.Current.Response.Write("<TD align='center' style='width:10%;'>Employee Name</TD>");
HttpContext.Current.Response.Write("<TD align='center' style='width:10%;'>Address</TD>");
HttpContext.Current.Response.Write("<TD align='center' style='width:10%;'>City</TD>");
HttpContext.Current.Response.Write("<TD align='center' style='width:10%;'>Pin Code</TD>");
HttpContext.Current.Response.Write("<TD align='center' style='width:10%;'>Phone No</TD>");
HttpContext.Current.Response.Write("</TR>");
#endregion
#region Detail Row
for (int iRow = 0; iRow < dtExcel.Rows.Count; iRow++)
{
HttpContext.Current.Response.Write("<TR valign='top'>");
HttpContext.Current.Response.Write("<TD align='left'>" + dtExcel.Rows[iRow]["EmployeeCode"].ToString() + "</TD>");
HttpContext.Current.Response.Write("<TD align='left'>" + dtExcel.Rows[iRow]["EmployeeName"].ToString() + "</TD>");
HttpContext.Current.Response.Write("<TD align='left'>" + dtExcel.Rows[iRow]["Address"].ToString() + "</TD>");
HttpContext.Current.Response.Write("<TD align='left'>" + dtExcel.Rows[iRow]["City"].ToString() + "</TD>");
HttpContext.Current.Response.Write("<TD align='left'>" + dtExcel.Rows[iRow]["PinCode"].ToString() + "</TD>");
HttpContext.Current.Response.Write("<TD align='left'>" + dtExcel.Rows[iRow]["PhoneNo"].ToString() + "</TD>");
HttpContext.Current.Response.Write("</TR>");
}
HttpContext.Current.Response.Write("</Table>");
#endregion
HttpContext.Current.Response.Write("</Table>");
HttpContext.Current.Response.Write("</font>");
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
catch (Exception ex)
{
throw (ex);
}
}