Post
Ask Question

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:
  1. public DataTable CreateTable()      
  2. {      
  3.     DataTable dt = new DataTable();      
  4.     dt.Columns.Add("EmployeeCode", typeof(string));      
  5.     dt.Columns.Add("EmployeeName", typeof(string));      
  6.     dt.Columns.Add("Address", typeof(string));      
  7.     dt.Columns.Add("City", typeof(string));      
  8.     dt.Columns.Add("PinCode", typeof(Int32));      
  9.     dt.Columns.Add("PhoneNo", typeof(string));      
  10.     return dt;      
  11. }  
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:
  1. private void ExportToExcel(DataTable dtExcel)      
  2. {      
  3.     try      
  4.     {      
  5.         HttpContext.Current.Response.Clear();      
  6.         HttpContext.Current.Response.ClearContent();      
  7.         HttpContext.Current.Response.ClearHeaders();      
  8.         HttpContext.Current.Response.Buffer = true;      
  9.         HttpContext.Current.Response.ContentType = "application/ms-excel";      
  10.         HttpContext.Current.Response.Write(@"<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">");      
  11.         HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=Salary_Cert.xls");      
  12.   
  13.         HttpContext.Current.Response.Charset = "utf-8";      
  14.         HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250");      
  15.   
  16.         HttpContext.Current.Response.Write("<font style='font-size:11.0pt; font-family:Calibri;'>");      
  17.         HttpContext.Current.Response.Write("<BR><BR>");      
  18.   
  19.         HttpContext.Current.Response.Write("<Table border='0' bgColor='#ffffff' " +      
  20.                       "borderColor='#000000' cellSpacing='0' cellPadding='0' " +      
  21.                       "style='font-size:11.0pt; font-family:Calibri; background:white;'>");      
  22.  
  23.  
  24.         #region Report Header      
  25.         HttpContext.Current.Response.Write("<TR valign='top'>");      
  26.         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>");      
  27.         HttpContext.Current.Response.Write("</U></B></TR>");      
  28.   
  29.         HttpContext.Current.Response.Write("<TR valign='top'><TD align='left' colspan='9'> Employee Personal Details </TD></TR>");      
  30.         HttpContext.Current.Response.Write("<TR valign='top'><TD align='left' colspan='9'> </TD></TR>");      
  31.   
  32.         HttpContext.Current.Response.Write("<TR valign='top'><TD align='left' colspan='9' rowspan='3' style='whitespace:normal;'>");      
  33.         HttpContext.Current.Response.Write("</TD></TR>");      
  34.  
  35.         #endregion      
  36.  
  37.         #region Header Row      
  38.   
  39.         HttpContext.Current.Response.Write("<TR valign='top'><td colspan='10'");      
  40.         HttpContext.Current.Response.Write("<Table border='1' bgColor='#FFFFFF' " +      
  41.           "borderColor='#000000' cellSpacing='0' cellPadding='0' " +      
  42.           "style='font-size:10.0pt; font-family:Calibri; background:white;'>");      
  43.         HttpContext.Current.Response.Write("<TR  valign='top' style='background:#D8D8D8;'>");      
  44.         HttpContext.Current.Response.Write("<TD align='left' style='width:20%;'>Employee Code</TD>");      
  45.         HttpContext.Current.Response.Write("<TD align='center' style='width:10%;'>Employee Name</TD>");      
  46.         HttpContext.Current.Response.Write("<TD align='center' style='width:10%;'>Address</TD>");      
  47.         HttpContext.Current.Response.Write("<TD align='center' style='width:10%;'>City</TD>");      
  48.   
  49.         HttpContext.Current.Response.Write("<TD align='center' style='width:10%;'>Pin Code</TD>");      
  50.         HttpContext.Current.Response.Write("<TD align='center' style='width:10%;'>Phone No</TD>");      
  51.         HttpContext.Current.Response.Write("</TR>");      
  52.         #endregion      
  53.  
  54.         #region Detail Row      
  55.         for (int iRow = 0; iRow < dtExcel.Rows.Count; iRow++)      
  56.         {      
  57.             HttpContext.Current.Response.Write("<TR valign='top'>");      
  58.             HttpContext.Current.Response.Write("<TD align='left'>" + dtExcel.Rows[iRow]["EmployeeCode"].ToString() + "</TD>");      
  59.             HttpContext.Current.Response.Write("<TD align='left'>" + dtExcel.Rows[iRow]["EmployeeName"].ToString() + "</TD>");      
  60.             HttpContext.Current.Response.Write("<TD align='left'>" + dtExcel.Rows[iRow]["Address"].ToString() + "</TD>");      
  61.             HttpContext.Current.Response.Write("<TD align='left'>" + dtExcel.Rows[iRow]["City"].ToString() + "</TD>");      
  62.             HttpContext.Current.Response.Write("<TD align='left'>" + dtExcel.Rows[iRow]["PinCode"].ToString() + "</TD>");      
  63.             HttpContext.Current.Response.Write("<TD align='left'>" + dtExcel.Rows[iRow]["PhoneNo"].ToString() + "</TD>");      
  64.             HttpContext.Current.Response.Write("</TR>");      
  65.         }                      
  66.         HttpContext.Current.Response.Write("</Table>");      
  67.         #endregion      
  68.   
  69.         HttpContext.Current.Response.Write("</Table>");      
  70.         HttpContext.Current.Response.Write("</font>");      
  71.         HttpContext.Current.Response.Flush();      
  72.         HttpContext.Current.Response.End();      
  73.     }      
  74.     catch (Exception ex)      
  75.     {      
  76.         throw (ex);      
  77.     }      
  78. }