I was having one requirement of giving an option to the user to generate the Ms Excel sheet from the report that was given to the user in GridView.
Initially it seems difficult but when I worked around, I found really easy way to do that.
First you need to create a .aspx page where GridView will be placed and data will be rendered.
Here is the code for .aspx page
<asp:GridView ID="GridView1" runat="Server" AutoGenerateColumns="True">
<asp:Button ID="btn" runat="server" OnClick="GenerateExcelFile" Text="Generate Excel File from GridView" />
Now, We have to write code to populate the GridView, in this example i have used one .xml file to populate the data into the GridView.
.xml file as the datasource of the GridView
<?xml version="1.0" encoding="utf-8" ?>
<Address>Sanat Nagar, Orrisa</Address>
<Address>Suraj Singh Nagar, Delhi</Address>
Now, I am going to write the code to populate the GridView in !IsPostBack of Page_Load event.
DataSet dSet = new DataSet();
string fileName = Server.MapPath("~/GridData.xml");
GridView1.DataSource = dSet.Tables.DefaultView;
Now, I have my GridView populated with the data.
The main part here is to export this GridView as the Ms Excel file, that will be done in the click event of the [Generate Excel File from GridView] button.
To work with following code you may need to add following code at the top of the .cs page.
Here is the code for the button click event
string attachment = "attachment; filename=GridViewExport.xls";
Response.ContentType = "application/ms-excel";
StringWriter sWriter = new StringWriter();
HtmlTextWriter htwWriter = new HtmlTextWriter(sWriter);
Now you have everything ready to test, but wait. When you click the button you will get error "Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server.". To avoid this error, Just add the following method into your .cs file.
public override void VerifyRenderingInServerForm(Control control)
Now you have everything into your page, go ahead and click the button. It will ask to open or save the Ms Excel file, do whatever you have to do.