Background
There is often a need in a project's reporting module to create an Excel file from a database, so by considering that requirement I decided to write this article especially focusing on beginners and those who want to learn how to create an Excel file from a database using ASP.NET C#.

Now before creating the application, let us create a table named employee in a database from where we show the records in a Grid View, the table has the following fields (shown in the following image):


I hope you have created the same type of table.

Now create the project as:
  1. "Start" - "All Programs" - "Microsoft Visual Studio 2010".

  2. "File" - "New Project" - "C#" - "Empty Project" (to avoid adding a master page).

  3. Provide the Project name such as CreateExcelFile or another as you wish and specify the location.

  4. Then right-click on Solution Explorer and select "Add New Item" then select Default.aspx page.

  5. One Button and a grid view.
Now the Default.aspx source code will be as follows:
  1. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
  3. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  5. <html xmlns="http://www.w3.org/1999/xhtml">
  6. <head runat="server">
  7. <title></title>
  8. </head>
  9. <body bgcolor="Silver">
  10. <form id="form1" runat="server">
  11. <br />
  12. <h2 style="color: #808000; font-size: x-large; font-weight: bolder;">
  13. Article by Vithal Wadje</h2>
  14. <br />
  15. <div>
  16. <asp:GridView ID="GridView1" AutoGenerateColumns="false" runat="server" CellPadding="6"
  17. ForeColor="#333333" GridLines="None">
  18. <AlternatingRowStyle BackColor="White" />
  19. <EditRowStyle BackColor="#7C6F57" />
  20. <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
  21. <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
  22. <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
  23. <RowStyle BackColor="#E3EAEB" />
  24. <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
  25. <SortedAscendingCellStyle BackColor="#F8FAFA" />
  26. <SortedAscendingHeaderStyle BackColor="#246B61" />
  27. <SortedDescendingCellStyle BackColor="#D4DFE1" />
  28. <SortedDescendingHeaderStyle BackColor="#15524A" />
  29. <Columns>
  30. <asp:BoundField DataField="id" HeaderText="id" />
  31. <asp:BoundField DataField="Name" HeaderText="Name" />
  32. <asp:BoundField DataField="City" HeaderText="City" />
  33. <asp:BoundField DataField="Address" HeaderText="Address" />
  34. <asp:BoundField DataField="Designation" HeaderText="Designation" />
  35. </Columns>
  36. </asp:GridView>
  37. <br />
  38. <asp:Button ID="Button1" runat="server"
  39. Text="Create Excel File" onclick="Button1_Click"/>
  40. </div>
  41. </form>
  42. </body>
  43. </html>

If you are a beginner and don't understand in detail how to bind a Grid View from a database then refer to the following article of mine.

Now, for this article create the following function in the default.aspx.cs page to bind the Grid View:

  1. private void Bindgrid()
  2. {
  3. connection();
  4. query = "select *from Employee";//not recommended this i have written just for example,write stored procedure for security
  5. com = new SqlCommand(query, con);
  6. SqlDataAdapter da = new SqlDataAdapter(query, con);
  7. DataSet ds = new DataSet();
  8. da.Fill(ds);
  9. GridView1.DataSource = ds;
  10. GridView1.DataBind();
  11. con.Close();
  12. ViewState["DataTable"] = ds.Tables[0];
  13. }
Now, call the preceding function on page load as in the following:
  1. protected void Page_Load(object sender, EventArgs e)
  2. {
  3. if (!IsPostBack)
  4. {
  5. Bindgrid();
  7. }
  8. }
Now create the following function to create an Excel File from the database as in the following:
  1. public void CreateExcelFile(DataTable Excel)
  2. {
  3. //Clears all content output from the buffer stream.
  4. Response.ClearContent();
  5. //Adds HTTP header to the output stream
  6. Response.AddHeader("content-disposition", string.Format("attachment; filename=C#cornerVithalWadje.xls"));
  7. // Gets or sets the HTTP MIME type of the output stream
  8. Response.ContentType = "application/vnd.ms-excel";
  9. string space = "";
  10. foreach (DataColumn dcolumn in Excel.Columns)
  11. {
  12. Response.Write(space + dcolumn.ColumnName);
  13. space = "\t";
  14. }
  15. Response.Write("\n");
  16. int countcolumn;
  17. foreach (DataRow dr in Excel.Rows)
  18. {
  19. space = "";
  20. for (countcolumn = 0; countcolumn < Excel.Columns.Count; countcolumn++)
  21. {
  22. Response.Write(space+dr[countcolumn].ToString());
  23. space="\t";
  24. }
  25. Response.Write("\n");
  26. }
  27. Response.End();
  28. }
We have created the preceding function to create an Excel File from the database. Now call the preceding function on the Create Excel File button click as in the following:
  1. protected void Button1_Click(object sender, EventArgs e)
  2. {
  3. //getting datatable from viewstate
  4. DataTable dt =(DataTable)ViewState["DataTable"];
  6. //calling create Excel File Method and ing dataTable
  7. CreateExcelFile(dt);
  8. }
The entire code of the default.aspx page will look as follows.
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Web;
  5. using System.Web.UI;
  6. using System.Web.UI.WebControls;
  7. using System.Data;
  8. using System.IO;
  9. using System.Configuration;
  10. using System.Data.SqlClient;
  11. public partial class _Default : System.Web.UI.Page
  12. {

  14. private SqlConnection con;
  15. private SqlCommand com;
  16. private string constr, query;
  17. private void connection()
  18. {
  19. constr = ConfigurationManager.ConnectionStrings["getconn"].ToString();
  20. con = new SqlConnection(constr);
  21. con.Open();
  23. }
  24. protected void Page_Load(object sender, EventArgs e)
  25. {
  26. if (!IsPostBack)
  27. {
  28. Bindgrid();
  30. }
  31. }
  32. private void Bindgrid()
  33. {
  34. connection();
  35. query = "select *from Employee";//not recommended this i have written just for example,write stored procedure for security
  36. com = new SqlCommand(query, con);
  37. SqlDataAdapter da = new SqlDataAdapter(query, con);
  38. DataSet ds = new DataSet();
  39. da.Fill(ds);
  40. GridView1.DataSource = ds;
  41. GridView1.DataBind();
  42. con.Close();
  43. ViewState["DataTable"] = ds.Tables[0];
  44. }
  45. public void CreateExcelFile(DataTable Excel)
  46. {
  48. //Clears all content output from the buffer stream.
  49. Response.ClearContent();
  50. //Adds HTTP header to the output stream
  51. Response.AddHeader("content-disposition", string.Format("attachment; filename=C#cornerVithalWadje.xls"));
  53. // Gets or sets the HTTP MIME type of the output stream
  54. Response.ContentType = "application/vnd.ms-excel";
  55. string space = "";
  57. foreach (DataColumn dcolumn in Excel.Columns)
  58. {
  60. Response.Write(space + dcolumn.ColumnName);
  61. space = "\t";
  62. }
  63. Response.Write("\n");
  64. int countcolumn;
  65. foreach (DataRow dr in Excel.Rows)
  66. {
  67. space = "";
  68. for (countcolumn = 0; countcolumn < Excel.Columns.Count; countcolumn++)
  69. {
  71. Response.Write(space+dr[countcolumn].ToString());
  72. space="\t";
  74. }
  76. Response.Write("\n");
  77. }
  78. Response.End();
  79. }
  80. protected void Button1_Click(object sender, EventArgs e)
  81. {
  82. //getting datatable from viewstate
  83. DataTable dt =(DataTable)ViewState["DataTable"];
  85. //calling create Excel File Method and ing dataTable
  86. CreateExcelFile(dt);
  87. }
  88. }
Now run the application and then we can see the following records in the Grid view:
Now click on the Create Excel File button, the following dialog will appear:
Now select the option to open or save and click on the OK button, then the Created Excel file records will look such as follows:
Now you can see that the Excel file is created from the database and the records will look as above.

Notes
  • Download the Zip file from the attachment for the full source code of the application.
  • Change the connection string in the web.config file to specify your server location.
Summary

I hope this article is useful for all readers. If you have any suggestions then please contact me including beginners also.