Generating Ms Excel file from GridView with ASP.NET and C#

Sheonarayan
Posted by in ASP.NET category on for Intermediate level | Views : 33761 red flag
Rating: 5 out of 5  
 1 vote(s)

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.


 Download source code for Generating Ms Excel file from GridView with ASP.NET and C#

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:GridView>
<br />
<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" ?>

<GridData>
<Details>
<FirstName>Sheo</FirstName>
<LastName>Narayan</LastName>
<Address>Aurangabad, Bihar</Address>
<Profession>Job</Profession>
</Details>
<Details>
<FirstName>Vijay</FirstName>
<LastName>Bandaru</LastName>
<Address>Hyderabad, AP</Address>
<Profession>Software Professoinal</Profession>
</Details>
<Details>
<FirstName>Sannat</FirstName>
<LastName>Digar</LastName>
<Address>Sanat Nagar, Orrisa</Address>
<Profession>Job</Profession>
</Details>
<Details>
<FirstName>Suraj</FirstName>
<LastName>Singh</LastName>
<Address>Suraj Singh Nagar, Delhi</Address>
<Profession>Businessman</Profession>
</Details>
</GridData>



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");
dSet.ReadXml(fileName);
GridView1.DataSource = dSet.Tables[0].DefaultView;
GridView1.DataBind();
dSet.Dispose();


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.

using System.IO;
using System.Text;

Here is the code for the button click event

string attachment = "attachment; filename=GridViewExport.xls";

Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/ms-excel";
StringWriter sWriter = new StringWriter();

HtmlTextWriter htwWriter = new HtmlTextWriter(sWriter);

GridView1.RenderControl(htwWriter);
Response.Write(sWriter.ToString());
Response.End();

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.

Thats it!
Page copy protected against web site content infringement by Copyscape

About the Author

Sheonarayan
Full Name: Sheo Narayan
Member Level: HonoraryPlatinum
Member Status: Administrator
Member Since: 7/8/2008 6:32:14 PM
Country: India
Regards, Sheo Narayan http://www.dotnetfunda.com
http://www.snarayan.com
Ex-Microsoft MVP, Author, Writer, Mentor & architecting applications since year 2001. Connect me on http://www.facebook.com/sheo.narayan | https://twitter.com/sheonarayan | http://www.linkedin.com/in/sheonarayan

Login to vote for this post.

Comments or Responses

Posted by: Akiii on: 3/21/2011 | Points: 25
very good explanation sir.....

Thanks
Akiii
Posted by: Hareesh on: 1/11/2012 | Points: 25
hi this is very helpful article
thanks.
Posted by: Hareesh on: 3/28/2012 | Points: 25
By using this code we can bind data to gridview


protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack) {
DataSet ds = new DataSet();
ds.ReadXml(Server.MapPath("~/GridData.xml"));
GridView1.DataSource = ds;
GridView1.DataBind();
}
}
Posted by: Sam111 on: 12/31/2012 | Points: 25
i am getting InvalidOperationException at Gridview.RenderControl(htmlwriter) line....what mite be the problem???
the exact error is as follows...
RegisterForEventValidation can only be called during Render();
Posted by: Sharmagoldi781 on: 11/27/2013 | Points: 25
'ASP.grid2excel_aspx' does not contain a definition for 'GenerateExcelFile'
error occured while running...is there any reference required??
plz do reply
Posted by: Sheonarayan on: 11/27/2013 | Points: 25
@Sharma....

No, GenerateExcelFile is the method name and the code for that method is 2nd from end of the article.

Hope this helps.

Thanks

Login to post response

Comment using Facebook(Author doesn't get notification)