Create Excel File From DataBase Using ASP.Net C#

Posted on by Agnes Berry
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>

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.

Agnes Berry

More Posts