MENU

Export Gridview to Excel In ASP.Net

Filed Under: , on
There often is a need in a project's reporting module to show records of a GridView in an Excel sheet, so to do that I decided to write this article, especially focusing on beginners and those who want to learn how to export a GridView to Excel Using ASP.Net C#.
Now before creating the application, let us create a table named employee in a database with records for the GridView, 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 "ExportGridToExcel" or another as you wish and specify the location.
  4. Then right-click on the Solution Explorer and select "Add New Item" - "Default.aspx" page.
  5. Then add one button, one label and a GridView to the page.
Now let us create a function to bind the records to the GridView from the database.Now create the following function in the default.aspx.cs page to bind the GridView:
private void Bindgrid()  
   {  
       connection();  
//not recommended this i have written for example,write stored procedure for security  
       query = "select *from Employee";
       com = new SqlCommand(query, con);  
       SqlDataReader dr = com.ExecuteReader();  
       GridView1.DataSource = dr;  
       GridView1.DataBind();  
       con.Close();     
                  
   }
Now, call this function on page load as in the following:
protected void Page_Load(object sender, EventArgs e)  
  {  
      if (!IsPostBack)  
      {  
          Bindgrid();  
        
      }  
  }
Now run the application, we then can see the following records in the GridView as in the following:

Now, we have a record to export to Excel, let us start coding for our actual requirements. Add the VerifyRenderingInServerForm event after the page load that is required while exporting the GridView to Excel,Word and PDF formt to avoid the runtime error that occurrs, such as "GridView' must be placed inside a form tag with runat=server.".
public override void VerifyRenderingInServerForm(Control control)  
   {  
       //required to avoid the run time error "  
       //Control 'GridView1' of type 'Grid View' must be placed inside a form tag with runat=server."  
   }
Now create the following function to Export the GridView to Excel as in the following:
private void ExportGridToExcel()  
   {  
       Response.Clear();  
       Response.Buffer = true;  
       Response.ClearContent();  
       Response.ClearHeaders();  
       Response.Charset = "";  
       string FileName ="Vithal"+DateTime.Now+".xls";  
       StringWriter strwritter = new StringWriter();  
       HtmlTextWriter htmltextwrtter = new HtmlTextWriter(strwritter);        
       Response.Cache.SetCacheability(HttpCacheability.NoCache);  
       Response.ContentType ="application/vnd.ms-excel";    
       Response.AddHeader("Content-Disposition","attachment;filename=" + FileName);  
       GridView1.GridLines = GridLines.Both;  
       GridView1.HeaderStyle.Font.Bold = true;  
       GridView1.RenderControl(htmltextwrtter);  
       Response.Write(strwritter.ToString());  
       Response.End();      
  
   }
Now double-click on the "Export to Excel" button and call the preceding function in "onclick" as in the following:
protected void Button1_Click(object sender, EventArgs e)  
  {  
      ExportGridToExcel();  
  }
Now the entire code of the Default.aspx.cs page will be as follows:
using System;  
using System.Web.UI;  
using System.Web.UI.WebControls;  
using System.Configuration;  
using System.Data.SqlClient;  
using System.IO;  
using System.Web;  
  
public partial class _Default : System.Web.UI.Page  
{  
    private SqlConnection con;  
    private SqlCommand com;  
    private string constr,query;  
    private void connection()  
    {  
        constr = ConfigurationManager.ConnectionStrings["getconn"].ToString();  
        con = new SqlConnection(constr);  
        con.Open();  
      
    }  
    protected void Page_Load(object sender, EventArgs e)  
    {  
        if (!IsPostBack)  
        {  
            Bindgrid();  
          
        }  
    }  
  
    public override void VerifyRenderingInServerForm(Control control)  
    {  
        //required to avoid the runtime error "  
        //Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server."  
    }  
  
    private void Bindgrid()  
    {  
        connection();  
        query = "select *from Employee";//not recommended this i have wrtten just for example,write stored procedure for security  
        com = new SqlCommand(query, con);  
        SqlDataReader dr = com.ExecuteReader();  
        GridView1.DataSource = dr;  
        GridView1.DataBind();  
        con.Close();     
                   
    }  
    protected void Button1_Click(object sender, EventArgs e)  
    {  
        ExportGridToExcel();  
    }  
    private void ExportGridToExcel()  
    {  
        Response.Clear();  
        Response.Buffer = true;  
        Response.ClearContent();  
        Response.ClearHeaders();  
        Response.Charset = "";  
        string FileName ="Vithal"+DateTime.Now+".xls";  
        StringWriter strwritter = new StringWriter();  
        HtmlTextWriter htmltextwrtter = new HtmlTextWriter(strwritter);        
        Response.Cache.SetCacheability(HttpCacheability.NoCache);  
        Response.ContentType ="application/vnd.ms-excel";    
        Response.AddHeader("Content-Disposition","attachment;filename=" + FileName);  
        GridView1.GridLines = GridLines.Both;  
        GridView1.HeaderStyle.Font.Bold = true;  
        GridView1.RenderControl(htmltextwrtter);  
        Response.Write(strwritter.ToString());  
        Response.End();      
  
    }  
  
}
Now run the application and click on the "Export to Excel" button, the following popup is shown:

Now click on the "Open with" option, all the GridView records are exported into Excel as in the following:

Notes
  • 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 suggestion then please contact me, including beginners.

Post a Comment

www.CodeNirvana.in

Protected by Copyscape
Copyright © Compilemode