Export GridView To Excel ASP.NET

Export GridView to Excel in asp.net 2.0,3.5 using C# and VB.NET

In this post i am going to explian how to export gridview to ms excel using C# and VB.NET.

For this i have used northwind database to populate gridview. To learn how to populate gridview .







After populating gridview we have to export gridview to excel on click of button placed in page.

For this we can simply write this code in click event of button

01Response.ClearContent();

02 

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

04 

05        Response.ContentType = "application/excel";

06 

07        StringWriter sWriter = new StringWriter();

08 

09        HtmlTextWriter hTextWriter = new HtmlTextWriter(sWriter);

10 

11        GridView1.RenderControl(hTextWriter);

12 

13        Response.Write(sWriter.ToString());

14 

15        Response.End();

But when we click on button to export gridview to excel we get this httpexception error.

to get past this either we can write this method in code behind.

1public override void VerifyRenderingInServerForm(Control control)

2{

3}

or we can add a html form and render it after adding gridview in it, i'll be using this.

If we have enabled paging in gridview or gridview contains controls like linkbutton, dropdowns or checkboxes etc then we get this error.

we can fix this error by setting event validation property to false in page directive.

1<%@ Page Language="C#" AutoEventWireup="true"  <b>EnableEventValidation="false" </b>CodeFile="Default.aspx.cs" Inherits="_Default" %>

When we export gridview containg controls then hyperlinks or other controls are not desireable in excel sheet, we need to display their display text insted for this we need to write a method to remove controls and display their respective text property as mentioned below.

01private void ChangeControlsToValue(Control gridView)

02    {

03        Literal literal = new Literal();

04 

05        for (int i = 0; i < gridView.Controls.Count; i++)

06        {

07            if (gridView.Controls[i].GetType() == typeof(LinkButton))

08            {

09 

10                literal.Text = (gridView.Controls[i] as LinkButton).Text;

11                gridView.Controls.Remove(gridView.Controls[i]);

12                gridView.Controls.AddAt(i,literal);

13            }

14            else if (gridView.Controls[i].GetType() == typeof(DropDownList))

15            {

16                literal.Text = (gridView.Controls[i] as DropDownList).SelectedItem.Text;

17 

18                gridView.Controls.Remove(gridView.Controls[i]);

19 

20                gridView.Controls.AddAt(i,literal);

21 

22            }

23            else if (gridView.Controls[i].GetType() == typeof(CheckBox))

24            {

25                literal.Text = (gridView.Controls[i] as CheckBox).Checked ? "True" : "False";

26                gridView.Controls.Remove(gridView.Controls[i]);

27                gridView.Controls.AddAt(i,literal);

28            }

29            if (gridView.Controls[i].HasControls())

30            {

31 

32                ChangeControlsToValue(gridView.Controls[i]);

33 

34            }

35 

36        }

37 

38    }

Complete HTML source of page look like

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
              DataSourceID="sqlDataSourceGridView" 
              AutoGenerateColumns="False"
              CssClass="GridViewStyle" 
              GridLines="None" Width="650px" 
              ShowHeader="False">
<Columns>
<asp:TemplateField HeaderText="Customer ID" ItemStyle-Width="75px">
<ItemTemplate>
<asp:LinkButton ID="lButton" runat="server" Text='<%#Eval("CustomerID") %>' 
                PostBackUrl="~/Default.aspx">
</asp:LinkButton>
</ItemTemplate>
<ItemStyle Width="75px"></ItemStyle>
</asp:TemplateField>
<asp:BoundField DataField="CompanyName" HeaderText="Company" 
                ItemStyle-Width="200px" >
<ItemStyle Width="200px"></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="ContactName" HeaderText="Name" 
                ItemStyle-Width="125px">
<ItemStyle Width="125px"></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="City" HeaderText="city" ItemStyle-Width="125px" >
<ItemStyle Width="125px"></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="Country" HeaderText="Country" 
                ItemStyle-Width="125px" >
<ItemStyle Width="125px"></ItemStyle>
</asp:BoundField>
</Columns>
<RowStyle CssClass="RowStyle" />
<PagerStyle CssClass="PagerStyle" />
<SelectedRowStyle CssClass="SelectedRowStyle" />
<HeaderStyle CssClass="HeaderStyle" />
<AlternatingRowStyle CssClass="AltRowStyle" />
</asp:GridView>

<asp:SqlDataSource ID="sqlDataSourceGridView" runat="server" 
ConnectionString="<%$ ConnectionStrings:northWindConnectionString %>" 
SelectCommand="SELECT [CustomerID], [CompanyName], [ContactName], 
               [City], [Country] FROM [Customers]">
</asp:SqlDataSource>

<table align="left" class="style1">
<tr>
<td class="style2">
<asp:RadioButtonList ID="RadioButtonList1" runat="server" AutoPostBack="True" 
                     RepeatDirection="Horizontal" RepeatLayout="Flow">
<asp:ListItem Value="0">All Pages</asp:ListItem>
</asp:RadioButtonList>
</td>
<td>
<asp:Button ID="btnExportToExcel" runat="server" Text="Export To Excel" 
            Width="215px" onclick="btnExportToExcel_Click"/>
</td>
</tr>
</table>

C# Code

01protected void btnExportToExcel_Click(object sender, EventArgs e)

02    {

03        if (RadioButtonList1.SelectedIndex == 0)

04        {

05            GridView1.ShowHeader = true;

06            GridView1.GridLines = GridLines.Both;

07            GridView1.AllowPaging = false;

08            GridView1.DataBind();

09        }

10        else

11        {

12            GridView1.ShowHeader = true;

13            GridView1.GridLines = GridLines.Both;

14            GridView1.PagerSettings.Visible = false;

15            GridView1.DataBind();

16        }

17 

18        ChangeControlsToValue(GridView1);

19        Response.ClearContent();

20 

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

22 

23        Response.ContentType = "application/excel";

24 

25        StringWriter sWriter = new StringWriter();

26 

27        HtmlTextWriter hTextWriter = new HtmlTextWriter(sWriter);

28 

29        HtmlForm hForm = new HtmlForm();

30 

31        GridView1.Parent.Controls.Add(hForm);

32 

33        hForm.Attributes["runat"] = "server";

34 

35        hForm.Controls.Add(GridView1);

36 

37        hForm.RenderControl(hTextWriter);

38 

39        Response.Write(sWriter.ToString());

40 

41        Response.End();

42    }

43 

44    private void ChangeControlsToValue(Control gridView)

45    {

46        Literal literal = new Literal();

47 

48        for (int i = 0; i < gridView.Controls.Count; i++)

49        {

50            if (gridView.Controls[i].GetType() == typeof(LinkButton))

51            {

52 

53                literal.Text = (gridView.Controls[i] as LinkButton).Text;

54                gridView.Controls.Remove(gridView.Controls[i]);

55                gridView.Controls.AddAt(i,literal);

56            }

57            else if (gridView.Controls[i].GetType() == typeof(DropDownList))

58            {

59                literal.Text = (gridView.Controls[i] as DropDownList).SelectedItem.Text;

60 

61                gridView.Controls.Remove(gridView.Controls[i]);

62 

63                gridView.Controls.AddAt(i,literal);

64 

65            }

66            else if (gridView.Controls[i].GetType() == typeof(CheckBox))

67            {

68                literal.Text = (gridView.Controls[i] as CheckBox).Checked ? "True" : "False";

69                gridView.Controls.Remove(gridView.Controls[i]);

70                gridView.Controls.AddAt(i,literal);

71            }

72            if (gridView.Controls[i].HasControls())

73            {

74 

75                ChangeControlsToValue(gridView.Controls[i]);

76 

77            }

78 

79        }

80 

81    }

This is how excel sheet will look like. Hope this helps.