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");
  13. HttpContext.Current.Response.Charset = "utf-8";
  14. HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250");
  16. HttpContext.Current.Response.Write("<font style='font-size:11.0pt; font-family:Calibri;'>");
  17. HttpContext.Current.Response.Write("<BR><BR>");
  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;'>");
  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>");
  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>");
  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>");
  35. #endregion
  37. #region Header Row
  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>");
  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
  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
  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. }