Export to Excel from Data Table in Asp.Net
In this blog you will learn how to Export to Excel from Data Table in Asp.Net.
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);
- }
- }