Hi,

We are going to learn how to Export GridView data to Microsoft Excel at Runtime.

We are using

ASP.NET Framework 4.0, Visual Studio 2010, SQL Server 2008, C#, XHTML 4.01 Code for ASPX Page (Front-End).

Adding GridView and Columns:

  1. <asp:GridView ID="gvPros" runat="server" AutoGenerateColumns="false" Width="664px" Font-Names="Calibri"
  2. HeaderStyle-Font-Bold="true" AlternatingRowStyle-BackColor="LightGoldenrodYellow"
  3. EmptyDataText="No Records Found. Try again by Changing Filter Options.">
  4. <Columns>
  5. <asp:TemplateField HeaderText="Party Name">
  6. <ItemTemplate>
  7. <asp:Label ID="lblPartyName" runat="server" Text='<%#Eval("PartyName")%>'>
  8. </asp:Label>
  9. </ItemTemplate>
  10. </asp:TemplateField>
  11. </Columns>
  12. <Columns>
  13. <asp:TemplateField HeaderText="Mode">
  14. <ItemTemplate>
  15. <asp:Label ID="lblFreight" runat="server" Text='<%#Eval("Division")%>'>
  16. </asp:Label>
  17. </ItemTemplate>
  18. </asp:TemplateField>
  19. </Columns>
  20. <Columns>
  21. <asp:TemplateField HeaderText="Month">
  22. <ItemTemplate>
  23. <asp:Label ID="lblMonthName" runat="server" Text='<%#Eval("ProMonth")%>'>
  24. </asp:Label>
  25. </ItemTemplate>
  26. </asp:TemplateField>
  27. </Columns>
  28. <Columns>
  29. <asp:TemplateField HeaderText="Total Box">
  30. <ItemTemplate>
  31. <asp:Label ID="lblTotalBox" runat="server" Text='<%#Eval("ProPkg")%>'>
  32. </asp:Label>
  33. </ItemTemplate>
  34. </asp:TemplateField>
  35. </Columns>
  36. <Columns>
  37. <asp:TemplateField HeaderText="Actual Weight">
  38. <ItemTemplate>
  39. <asp:Label ID="lblPartyName" runat="server" Text='<%#Eval("ProActWt")%>'>
  40. </asp:Label>
  41. </ItemTemplate>
  42. </asp:TemplateField>
  43. </Columns>
  44. <Columns>
  45. <asp:TemplateField HeaderText="Charged Wt">
  46. <ItemTemplate>
  47. <asp:Label ID="lblChWt" runat="server" Text='<%#Eval("ProChWt")%>'>
  48. </asp:Label>
  49. </ItemTemplate>
  50. </asp:TemplateField>
  51. </Columns>
  52. <Columns>
  53. <asp:TemplateField HeaderText="Freight">
  54. <ItemTemplate>
  55. <asp:Label ID="lblFreight" runat="server" Text='<%#Eval("ProFreight")%>'>
  56. </asp:Label>
  57. </ItemTemplate>
  58. </asp:TemplateField>
  59. </Columns>
  60. </asp:GridView>
Adding Export Link
  1. <asp:LinkButton ID="lnkExport" runat="server" Text="Export to Excel" onclick="lnkExport_Click"></asp:LinkButton>


Now Code for ASPX.CS Page (BackEnd):

Namespaces:
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Web;
  5. using System.Web.UI;
  6. using System.Web.UI.WebControls;
  7. using System.Data;
  8. using System.Data.SqlClient;
  9. using System.Configuration;
  10. using System.Drawing;
  11. using System.IO;
  12. using System.Text;
  13. using System.Web.Security;
  14. using System.Web.UI.HtmlControls;
  15. using System.Web.UI.WebControls.WebParts;
Data Binding and Export LinkButton Click Event:
  1. void GVProsFill()
  2. {
  3. string Query = "Select * from Parties, Prosperity where Parties.PartyID=Prosperity.ProParty";
  4. SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConToSEPL"].ConnectionString);
  5. SqlDataAdapter adp = new SqlDataAdapter(Query, con);
  6. DataSet ds = new DataSet();
  7. adp.Fill(ds);
  8. gvPros.DataSource = ds.Tables[0];
  9. gvPros.DataBind();
  10. }
  11. //You have to add an another Event for Export to work properly:
  12. public override void VerifyRenderingInServerForm(Control control)
  13. {
  14. // Can Leave This Blank.
  15. }
  16. protected void lnkExport_Click(object sender, EventArgs e)
  17. {
  18. Response.ClearContent();
  19. Response.Buffer = true;
  20. Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Prosperity.xls"));
  21. Response.ContentType = "application/ms-excel";
  22. StringWriter sw = new StringWriter();
  23. HtmlTextWriter htw = new HtmlTextWriter(sw);
  24. gvPros.AllowPaging = false;
  25. GVProsFill();
  26. gvPros.HeaderRow.Style.Add("background-color", "#FFFFFF");
  27. for (int a = 0; a < gvPros.HeaderRow.Cells.Count; a++)
  28. {
  29. gvPros.HeaderRow.Cells[a].Style.Add("background-color", "#507CD1");
  30. }
  31. int j = 1;
  32. foreach(GridViewRow gvrow in gvPros.Rows)
  33. {
  34. gvrow.BackColor = Color.White;
  35. if (j <= gvPros.Rows.Count)
  36. {
  37. if (j % 2 != 0)
  38. {
  39. for (int k = 0; k < gvrow.Cells.Count; k++)
  40. {
  41. gvrow.Cells[k].Style.Add("background-color", "#EFF3FB");
  42. }
  43. }
  44. }
  45. j++;
  46. }
  47. gvPros.RenderControl(htw);
  48. Response.Write(sw.ToString());
  49. Response.End();
  50. }

I will be waiting to Listen from you….

John Bhatt