Tuesday, August 9, 2011

.NET and ASP.NET interview questions: - How to Export GridViewData to Excel in .NET?

This is one of the most important topics while dealing with GridView data so I
thought to share this information.

Let’s do a small demonstration to see how exactly we can export data of GridView
to Excel in .NET.

In order to Export Data of GridView to Excel you need to follow the following
steps.

Step1: - create a new web application for that go to File > New > Project
> Select ASP.NET Empty Web Application.







Now, add a WebForm in your application for that justgo to Solution Explorer
>Right click on the Project > Add > Add New Item > Select WebForm.





Now, on the WebForm.aspx page just drag and drop GridView control and do the same as I have done in below code snippet.

<asp:GridViewID="GridView1"runat="server"AutoGenerateColumns="False">

<Columns>

<asp:BoundFieldDataField="FirstName"HeaderText="FirstName"/>

<asp:BoundFieldDataField="LastName"HeaderText="LastName"/>

</Columns>

</asp:GridView>

Step2: -In this step we will bound the data to the GridView control.

In order to bound the data go to WebForm.aspx.cs and add the below code snippet.

publicvoidLoadGrid()

{

SqlConnection connection = newSqlConnection(ConnectionString);

connection.Open();

SqlCommandcommmand = newSqlCommand();

com.CommandText = "select FirstName,LastName from Customers";

com.Connection = connnection;

com.ExecuteNonQuery();

SqlDataAdapter adapter = newSqlDataAdapter(commmand);

DataSet dataset = newDataSet();

adapter.Fill(dataset);

GridView1.DataSource = dataset;

GridView1.DataBind();

}
Later just call the method LoadGrid() on form load event like below code snippet.

protectedvoidPage_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

LoadGrid();

}

}

Step3
: - Now again go to WebForm.aspx and add a Button control on the Form and name it as ExportToExcel.

<asp:ButtonID="Button1"runat="server"onclick="Button1_Click"Text="ExportToExcel"/>

Now on the Button click event just add the below code snippet.

protectedvoid Button1_Click(object sender, EventArgs e)

{

Response.Clear();

Response.AddHeader("content-disposition", "attachment;filename=Customer.xls");

Response.Charset = "";

Response.Cache.SetCacheability(HttpCacheability.NoCache);

Response.ContentType = "application/vnd.xls";

System.IO.StringWriterstringWrite = newSystem.IO.StringWriter();

System.Web.UI.HtmlTextWriterhtmlWrite = newHtmlTextWriter(stringWrite);

StringWriterswriter = newStringWriter();

HtmlTextWriterhwriter = newHtmlTextWriter(swriter);

HtmlFormfrm = newHtmlForm();

this.GridView1.Parent.Controls.Add(frm);

frm.Attributes["runat"] = "server";

frm.Controls.Add(GridView1);

frm.RenderControl(hwriter);

Response.Write(swriter.ToString());

Response.End();

}

Once you have completed the above steps now just run your application.
As soon as you click on Export to Excel Button a window will pop up like below
diagram.


Just click on open a new window will pop up like below diagram.




Now just click yes your data of GridView will be nowbeing exported to the Excel
and will appear like below diagram.




View the following video on ASP.NET Web.config transformation: -





Click for more learning interview tutorials on .NET and ASP.NET interview questions
Regards,
Visit author’s blog on .NET and ASP.NET interview questions











No comments: