Post
Ask Question

Create Excel File From DataBase Using ASP.Net C#

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" %>  
  2.   
  3. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
  4.   
  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();    
  6.   
  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"];    
  5.   
  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();  
  22.   
  23.     }  
  24.     protected void Page_Load(object sender, EventArgs e)  
  25.     {  
  26.         if (!IsPostBack)  
  27.         {  
  28.             Bindgrid();  
  29.   
  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.     {  
  47.   
  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"));  
  52.   
  53.        // Gets or sets the HTTP MIME type of the output stream  
  54.         Response.ContentType = "application/vnd.ms-excel";  
  55.         string space = "";  
  56.   
  57.         foreach (DataColumn dcolumn in Excel.Columns)  
  58.         {  
  59.   
  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.             {   
  70.               
  71.             Response.Write(space+dr[countcolumn].ToString());  
  72.                 space="\t";  
  73.               
  74.             }  
  75.           
  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"];  
  84.   
  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. 


Similar Articles