Exporting grid data to Word and Excel using asp.net

Introduction: 
Here I will explain how to export gridviewdata to Word and Excel using asp.net.

Description:

I have one gridview that has filled with user details now I need to export gridview data to word or excel document based on selection. To implement this functionality first we need to design aspx page like this

<html xmlns=”http://www.w3.org/1999/xhtml”&gt;

<head id=”Head1″ runat=”server”>

<title></title>

</head>

<body>

<form id=”form1″ runat=”server”>

<div>

<table>

<tr>

<td align=”right”>

<asp:ImageButton ID=”btnExcel” runat=”server” 

ImageUrl=”~/ExcelImage.jpg”

onclick=”btnExcel_Click” />

<asp:ImageButton ID=”btnWord” runat=”server” 

ImageUrl=”~/WordImage.jpg”

onclick=”btnWord_Click” />

</td>

</tr>

<tr>

<td>

<asp:GridView runat=”server” ID=”gvdetails” DataSourceID=”dsdetails”  

AllowPaging=”true”AllowSorting=”true” AutoGenerateColumns=”false”>

<RowStyle BackColor=”#EFF3FB” />

<FooterStyle BackColor=”#507CD1″ Font-Bold=”True” 

ForeColor=”White” />

<PagerStyle BackColor=”#2461BF” ForeColor=”White” 

HorizontalAlign=”Center” />

<HeaderStyle BackColor=”#507CD1″ Font-Bold=”True” 

ForeColor=”White” />

<AlternatingRowStyle BackColor=”White” />

<Columns>

<asp:BoundField DataField=”UserId” HeaderText=”UserId” />

<asp:BoundField DataField=”UserName” HeaderText=”UserName” />

<asp:BoundField DataField=”LastName” HeaderText=”LastName” />

<asp:BoundField DataField=”Location” HeaderText=”Location” />

</Columns>

</asp:GridView>

</td>

</tr>

</table>

<asp:SqlDataSource ID=”dsdetails” runat=”server”ConnectionString=”<%$ConnectionStrings:dbconnection %>

SelectCommand=”select * from UserInformation”/>

</div>

</form>

</body>

</html>

Here don’t forgot to set the connection string in web.config file here I am getting database connection from web.config file for that reason you need to set the connectionstring in web.config file like this 

<connectionStrings>

<add name=dbconnection connectionString=Data Source=SD;Integrated Security=true;Initial Catalog=MySampleDB/>

</connectionStrings>

After set the connection string in web.config file now we are able to get the data from database and we can bind that data to gridview by using sqldatasource. Now if you run application gridview appears like this

 

Now in code behind add this reference

using System.IO;

After that write the following code in code behind


public override void VerifyRenderingInServerForm(Control control)

{

/* Verifies that the control is rendered */

}

/// <summary>

/// This event is used to export gridview data to word document

/// </summary>

/// <param name=”sender”></param>

/// <param name=”e”></param>

protected void btnWord_Click(object sender, ImageClickEventArgs e)

{

gvdetails.AllowPaging = false;

gvdetails.DataBind();

Response.ClearContent();

Response.AddHeader(“content-disposition”string.Format(“attachment; filename={0}”,“Customers.doc”));

Response.Charset = “”;

Response.ContentType = “application/ms-word”;

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

gvdetails.RenderControl(htw);

Response.Write(sw.ToString());

Response.End();

}

/// <summary>

/// This Event is used to export gridview data to Excel

/// </summary>

/// <param name=”sender”></param>

/// <param name=”e”></param>

protected void btnExcel_Click(object sender, ImageClickEventArgs e)

{

Response.ClearContent();

Response.Buffer = true;

Response.AddHeader(“content-disposition”string.Format(“attachment; filename={0}”“Customers.xls”));

Response.ContentType = “application/ms-excel”;

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

gvdetails.AllowPaging = false;

gvdetails.DataBind();

//Change the Header Row back to white color

gvdetails.HeaderRow.Style.Add(“background-color”“#FFFFFF”);

//Applying stlye to gridview header cells

for (int i = 0; i < gvdetails.HeaderRow.Cells.Count; i++)

{

gvdetails.HeaderRow.Cells[i].Style.Add(“background-color”“#507CD1”);

}

int j = 1;

//This loop is used to apply stlye to cells based on particular row

foreach (GridViewRow gvrow in gvdetails.Rows)

{

gvrow.BackColor = Color.White;

if (j <= gvdetails.Rows.Count)

{

if (j % 2 != 0)

{

for (int k = 0; k < gvrow.Cells.Count; k++)

{

gvrow.Cells[k].Style.Add(“background-color”“#EFF3FB”);

}

}

}

j++;

}

gvdetails.RenderControl(htw);

Response.Write(sw.ToString());

Response.End();

}

Demo for Word document

 

If you observe above code I added one function that is VerifyRenderingInServerForm this function is used to avoid the error like “control must be placed in inside of form tag”. If we setVerifyRenderingInServerForm function then compiler will think that controls rendered before exporting and our functionality will work perfectly.

Here I used basic code to export gridview data to word document and for excel code is different but we can use the same code (Export to Word) for excel also to import gridview data just by replacingCustomers.doc to Customers.xls and application/ms-word to application/ms-excel but here we have problem that is row background color is applied throughout excel for that reason I made some small code modification and applied color only to the particular columns based on rows.

Demo for Excel document

 

Download : sample code attached

Now if you’re getting any error message please feel free to let me know ?

Advertisements

42 responses to this post.

  1. Good website! I really love how it is easy on my eyes and the data are well written. I am wondering how I might be notified when a new post has been made. I have subscribed to your RSS which must do the trick! Have a great day!

  2. Hello Web Admin, I noticed that your On-Page SEO is is missing a few factors, for one you do not use all three H tags in your post, also I notice that you are not using bold or italics properly in your SEO optimization. On-Page SEO means more now than ever since the new Google update: Panda. No longer are backlinks and simply pinging or sending out a RSS feed the key to getting Google PageRank or Alexa Rankings, You now NEED On-Page SEO. So what is good On-Page SEO?First your keyword must appear in the title.Then it must appear in the URL.You have to optimize your keyword and make sure that it has a nice keyword density of 3-5% in your article with relevant LSI (Latent Semantic Indexing). Then you should spread all H1,H2,H3 tags in your article.Your Keyword should appear in your first paragraph and in the last sentence of the page. You should have relevant usage of Bold and italics of your keyword.There should be one internal link to a page on your blog and you should have one image with an alt tag that has your keyword….wait there’s even more Now what if i told you there was a simple WordPress plugin that does all the On-Page SEO, and automatically for you? That’s right AUTOMATICALLY, just watch this 4minute video for more information at. WordPress Seo Plugin

  3. Heya i’m for the first time here. I found this board and I find It really helpful & it helped me out a lot. I’m hoping to give something again and help others such as you helped me.

  4. I need google adword coupons please send me an individual or 2.

  5. Hi Karen. I truly have a lens on Squidoo after i uncovered out it was good for creating backlinks to your blog or site. I had my account for awhile before I in fact created my first lens but it surely is worth putting in time and effort into it. It has brought me some traffic on days when i am active in the forums or commenting on other lenses, but you are not able to beat getting a backlink from a PR8 web page just from doing something you already do. I love it and recommend it to anyone for that reason alone.
    Karen Cruz recently posted..Not the One particular

  6. I’m unable to access your “Rolling Coils” tutorial. Please advise. Much thanks.

  7. This is such a great idea. A friend of mine sent me your website link. It’s such a fantastic way to engage readers offline and get physical mail which everyone loves. I may must do this after my blog is up and running a bit more.

  8. Weird , your post turns up with a dark color to it, what color is the primary color on your site?

  9. I tried looking at your site in my iphone and the page layout does not seem to be right. Might want to check it out on WAP as well as it seems most cell phone layouts are not really working with your website.

  10. Re: Whomever produced the comment that this was a good internet site really needs to get their brain evaluated.

  11. I like this website layout . How was it made? It is so cool!

  12. I Am Going To have to come back again whenever my course load lets up – however I am getting your Rss feed so i could read your web blog offline. Cheers.

  13. Do youve a spam problem on this internet site; I also am a blogger, and I was wondering your situation; weve got made some good methods and were looking to trade options with other individuals, be positive to fire me an e-mail if planning to pursue.

  14. It seems to me that this site doesnt download in a Motorola Droid. Are other people having the exact same problem? I enjoy this blog and dont want to have to skip it when Im away from my computer.

  15. Nearly all of the responses on this web site dont make sense.

  16. Jesus Christ theres plenty of spammy comments on this web site. Have you actually believed about attempting to get rid of them or installing a extension?

  17. How did you make your blog look this awesome! Email me if you want and share your wisdom. !

  18. Hi, I just hopped over to your site through StumbleUpon. Not somthing I might typically read, but I enjoyed your thoughts none the less. Thanks for making some thing worth browsing.

  19. Have you considered including several social bookmarking buttons to these blogs. At the very least for twitter.

  20. Have you considered including a few social bookmarking buttons to these sites. At least for facebook.

  21. Is it alright to place part of this in my personal website if perhaps I post a reference point to this web site?

  22. How come you do not have your site viewable in mobile format? cant see anything in my phone.

  23. It appears to me that this website doesnt load up on a Motorola Droid. Are other people getting the exact same issue? I enjoy this blog and dont want to have to skip it whenever Im away from my computer.

  24. Im getting a teeny issue. I cant get my reader to pickup your rss feed, Im using bing reader by the way.

  25. This really solved my problem, thank you!

  26. A insightful blog post right there mate . Cheers for the post !

  27. Is it fine to place part of this in my blog if I submit a reference point to this website?

  28. When I at first left a comment I clicked the Notify me whenever new comments are added checkbox and now each time a remark is added I receive 4 emails with the identical comment.

  29. If you could message me with any pointers on how you made this website look this cool, Id appreciate it!

  30. Could you email me with some tips on how you made your blog site look this awesome , Id be thankful!

  31. Wanted to drop a comment and let you know your Rss feed isnt functioning today. I tried including it to my Google reader account but got absolutely nothing.

  32. If you could message me with any hints about how you made your website look this good , Id appreciate it.

  33. wedding photographers philippines 2010

  34. I can’t subscribe to your channel

  35. Internet is a source of information and knowledge. But there are so many sites in net which are nothing but gibberish. But when I visit your site – I felt that – in net there are lots of sites (like this) from where I can get lot of information too. And if I say truly – your site is fantastic. I’ve bookmarked your site in my browser; I hope in future days I’ll get more valuable information from your site.

  36. below you will obtain the link to some web pages that we assume you must visit

  37. Articles of your respective web site are so very good, i can keep coming back no doubt as well as listing an individual’s paper for you to my very own prefering web

  38. Between me and my husband we’ve owned more MP3 players over the years than I can count, including Sansas, iRivers, iPods (classic & touch), the Ibiza Rhapsody, etc. But, the last few years I’ve settled down to one line of players. Why? Because I was happy to discover how well-designed and fun to use the underappreciated (and widely mocked) Zunes are.

  39. When I first saw this title Exporting grid data to Word and Excel using asp.net | Denno Secqtinstien Foundation on google I just whent and bookmark it. About Dress Shirts … Would this possesses anything to use the weather or maybe Forex Signal Providers ? Fantastic place though!

  40. Check beneath, are some totally unrelated web-sites to ours, even so, they may be most trustworthy sources that we use.

  41. Wow, superb blog layout! How long have you been blogging for? you made blogging look easy. The overall look of your site is fantastic, let alone the content!. Thanks For Your article about Exporting grid data to Word and Excel using asp.net | Denno Secqtinstien Foundation .

Comments are closed.

%d bloggers like this: