How to bind and Export GridView data to Ms Excel file using asp.net C#,VB.Net

Introduction: In previous articles i explained How to bind and Export GridView data to Ms Word file and and Bind and Export GridView data to PDF file in asp.net and Bind and Export GridView data to CSV file in asp.net and How to Get city, state and country based on zip code using Google map API and How to enable JavaScript in asp.net  and JavaScript validation in asp.net website and Ajax AutoCompleteExtender control example without using web service and Get Title,Description and Keywords Meta tags from URL and How to use RangeValidator validation control with example.
 In this article I will explain with example How to Bind GridView and Export Gridview data to Ms Excel file using asp.net with C# and VB.Net.
 
Bind and Export GridView data to Ms Excel in asp.net
Click on image to enlarge
Bind and Export GridView data to Ms Excel in asp.net
click on image to enlarge
Implementation: Let's create an asp.net website demo to see it in action.
  • Create a Database e.g. "MyDataBase" and a table under that DataBase in Sql Server and name it "EMPLOYEE" as shown in figure: 
Note: EMP_ID column is set to Primary key and Identity specification is set to yes with Identity increment and Identity seed equal to 1. Insert some data in this table that you  want to show in the Gridview.
  • Now in web.config file add the connection string under <configuration> tag
<connectionStrings>
    <add name="conStr" connectionString="Data Source=LocalServer;Initial Catalog=MyDataBase;Integrated Security=True"/>
  </connectionStrings>

Note: Replace the Data Source and Initial Catalog(i.e. Database Name) as per your application.
    Source Code:

  • In the design page (.aspx) place a GridView control to bind data and a Button control to Export the GridView data to MS Excel file.
<fieldset style="width:360px;">
            <legend>Bind and Export GridView data to Ms Excel in asp.net</legend>
            <table>
                <tr>
                    <td>
                        <asp:GridView ID="grEmp" runat="server" AllowPaging="True" AutoGenerateColumns="False"
                   GridLines="None" Width="100%" CellPadding="4" ForeColor="#333333">
                   
                    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />                   
                    <Columns>
                        <asp:BoundField DataField="EMP_NAME" HeaderText="Emp Name"  />
                        <asp:BoundField DataField="DEPT" HeaderText="Department"  />
                        <asp:BoundField DataField="SALARY" HeaderText="salary"  />
                        <asp:BoundField DataField="EMAIL_ID" HeaderText="Email Id" />
                    </Columns>                  
                    <EditRowStyle BackColor="#999999" />
                    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
                    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
                    <SortedAscendingCellStyle BackColor="#E9E7E2" />
                    <SortedAscendingHeaderStyle BackColor="#506C8C" />
                    <SortedDescendingCellStyle BackColor="#FFFDF8" />
                    <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
                </asp:GridView>
                    </td>               
                </tr>
                <tr>
                    <td>
<asp:Button ID="btnExportToExcel" runat="server" Text="Export To MS Excel File" OnClick="btnExportToExcel_Click" />
                    </td>
                </tr>
            </table>
        </fieldset>

C#.Net Code to Bind and Export GridView data to Ms Excel file

First include the following namespaces

using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Net;
using System.Net.Mail;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Text;

Then write the code as:

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            BindEmpGrid();
        }
    }
    public override void VerifyRenderingInServerForm(Control control)
    {
        //It solves the error "Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server."
    }
    protected void BindEmpGrid()
    {
        SqlCommand cmd = new SqlCommand("select * from EMPLOYEE", con);
        DataTable dt = new DataTable();
        SqlDataAdapter adp = new SqlDataAdapter(cmd);
        adp.Fill(dt);
        grEmp.DataSource = dt;
        grEmp.DataBind();
    }

protected void btnExportToExcel_Click(object sender, EventArgs e)
    {
        try
        {
            Response.ClearContent();
            string attachment = "attachment; filename=MyExelFile.xls";
            Response.ClearContent();
            Response.AddHeader("content-disposition", attachment);
            Response.ContentType = "application/ms-excel";
            StringWriter strWrite = new StringWriter();
            HtmlTextWriter htmWrite = new HtmlTextWriter(strWrite);             
            HtmlForm htmfrm = new HtmlForm();
            grEmp.Parent.Controls.Add(htmfrm);
            htmfrm.Attributes["runat"] = "server";
            htmfrm.Controls.Add(grEmp);
            htmfrm.RenderControl(htmWrite);
            Response.Write(strWrite.ToString());
            Response.Flush();
            Response.End();
        }
        catch (Exception ex){}
    }

VB.Net Code to Bind and Export GridView data to Ms Excel file

Design the form as shown above but just replace the line <asp:Button ID="btnExportToExcel" runat="server" Text="Export To MS Excel File" OnClick="btnExportToExcel_Click" /> with the <asp:Button ID="btnExportToExcel" runat="server" Text="Export To MS Excel File" />

Then in the code behind file(.aspx.vb) wrtie the code as: 

First include the following namespaces

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Net
Imports System.Net.Mail
Imports System.Web.UI.HtmlControls
Imports System.IO
Imports System.Text

  Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("conStr").ConnectionString)

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            BindEmpGrid()
        End If
    End Sub
    Public Overrides Sub VerifyRenderingInServerForm(control As Control)
        'It solves the error "Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server."
    End Sub

    Protected Sub BindEmpGrid()
        Dim cmd As New SqlCommand("select * from EMPLOYEE", con)
        Dim dt As New DataTable()
        Dim adp As New SqlDataAdapter(cmd)
        adp.Fill(dt)
        grEmp.DataSource = dt
        grEmp.DataBind()
    End Sub


Protected Sub btnExportTExcel_Click(sender As Object, e As EventArgs) Handles btnExportTExcel.Click
        Try
            Response.ClearContent()
            Dim attachment As String = "attachment; filename=MyExelFile.xls"
            Response.ClearContent()
            Response.AddHeader("content-disposition", attachment)
            Response.ContentType = "application/ms-excel"
            Dim strWrite As New StringWriter()
            Dim htmWrite As New HtmlTextWriter(strWrite)
            Dim htmfrm As New HtmlForm()
            grEmp.Parent.Controls.Add(htmfrm)
            htmfrm.Attributes("runat") = "server"
            htmfrm.Controls.Add(grEmp)
            htmfrm.RenderControl(htmWrite)
            Response.Write(strWrite.ToString())
            Response.Flush()
            Response.[End]()
        Catch ex As Exception
        End Try

  • Notice that I have added an overriding function VerifyRenderingInServerForm in the code behind. This is to resolve the error “Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server” that may occur while exporting GridView data to MS Excel file or MS Word file or PDF or CSV (Comma separated value) file.
Note: To view complete article on why this error occur and how to resolve that error, read my article “How to Solve Error Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server

  • After exporting, gridview data to Ms Excel file, will look as shown in figure:
Bind and Export GridView data to Ms Excel in asp.net
Click on image to enlarge
Now over to you:
"If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."
Previous
Next Post »

8 comments

Click here for comments
Anonymous
admin
September 30, 2013 ×

Hello Sir ,

I need a registration form with capcha image.How to check capcha text is valid or not,then proceed to login page..
Could u please help me.
Thanks , Saroj Panda

Reply
avatar
September 30, 2013 ×

Hi, i was actually thinking to create an article for that..so keep reading as i will create and post the article as soon as possible..:)

Reply
avatar
Anonymous
admin
October 28, 2013 ×

Hello Sir, Again Saroj Here,
How Can i create a page where i play mp3 songs in gridview selected Id.
(I store my mp3 songs in Mp3 folder i access it but unable to play songs in flash player)..
Could u please help me.
Thanks , Saroj Panda
www.mercedesgroups.com

Reply
avatar
Anonymous
admin
October 28, 2013 ×

Thanks sir....

Reply
avatar
October 28, 2013 ×

Your welcome...keep reading for more useful updates like this..:)

Reply
avatar
Anonymous
admin
November 26, 2013 ×

hi sir
ive got an error in this line

public override void VerifyRenderingInServerForm(Control control)

{
//It solves the error "Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server."
}

and when i have many details in my gridview its get an error

"A page can have only one server-side Form tag. "

pls. help me in this sir .
thanks in advance;

Reply
avatar
Unknown
admin
December 13, 2013 ×

Nice article lalit.. but how to download huge data from DB and display in Excel with multuple sheets..like first 1000 records in sheet 1,next 1000 records in sheet 2..and so on.

Reply
avatar
Unknown
admin
December 13, 2013 ×

helo lalit..again ..i want to download 1000's of records from DB into 2 excel files instead of 1..can you please post a snippet on these aspects also..

Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..