Export GridView To Excel ASP.NET

In this example i'm explaining how to Create Or Export GridView to Excel In Asp.Net 2.0,3.5 using C# and VB.NET. Place one button on the page for exporting data to ms excel file.

I have used Northwind database to populate GridView.

I have also explained how we can Create PDF From Gridview in one of my previous articles.

Write following code in Click Event of button

show source

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 try to execute this code on we get this httpexception error.

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

show source

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 paging is enabled or Gridview contains controls like linkbutton, DropDownLists or checkboxes etc then we get this error.

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

show source

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

Hyperlinks or other controls in gridview are not desireable in excel sheet, we should display their display text instead, for this write a method to remove controls and display their respective text property as mentioned below.

show source

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    }

HTML SOURCE

---

<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

---

show source

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        // Write below code to add cell border to empty cells in Excel file

40        // If we don't add this line then empty cells will be shown as blank white space

41 

42         StringBuilder sBuilder = new StringBuilder();

43        sBuilder.Append("<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"> <head><meta http-equiv="Content-Type" content="text/html;charset=windows-1252"><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>ExportToExcel</x:Name><x:WorksheetOptions><x:Panes></x:Panes></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head> <body>");

44        sBuilder.Append(sWriter + "</body></html>");

45        Response.Write(sBuilder.ToString());

46        Response.End();

47    }

48 

49    private void ChangeControlsToValue(Control gridView)

50    {

51        Literal literal = new Literal();

52 

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

54        {

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

56            {

57 

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

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

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

61            }

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

63            {

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

65 

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

67 

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

69 

70            }

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

72            {

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

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

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

76            }

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

78            {

79 

80                ChangeControlsToValue(gridView.Controls[i]);

81 

82            }

83 

84        }

85 

86    }

VB.NET

---

show source

01Protected Sub btnExportToExcel_Click(sender As Object, e As EventArgs)

02 If RadioButtonList1.SelectedIndex = 0 Then

03  GridView1.ShowHeader = True

04  GridView1.GridLines = GridLines.Both

05  GridView1.AllowPaging = False

06  GridView1.DataBind()

07 Else

08  GridView1.ShowHeader = True

09  GridView1.GridLines = GridLines.Both

10  GridView1.PagerSettings.Visible = False

11  GridView1.DataBind()

12 End If

13 

14 ChangeControlsToValue(GridView1)

15 Response.ClearContent()

16 

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

18 

19 Response.ContentType = "application/excel"

20 

21 Dim sWriter As New StringWriter()

22 

23 Dim hTextWriter As New HtmlTextWriter(sWriter)

24 

25 Dim hForm As New HtmlForm()

26 

27 GridView1.Parent.Controls.Add(hForm)

28 

29 hForm.Attributes("runat") = "server"

30 

31 hForm.Controls.Add(GridView1)

32 

33 hForm.RenderControl(hTextWriter)

34 

35 Dim sBuilder As New StringBuilder()

36sBuilder.Append("<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"> <head><meta http-equiv="Content-Type" content="text/html;charset=windows-1252"><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>ExportToExcel</x:Name><x:WorksheetOptions><x:Panes></x:Panes></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head> <body>")

37sBuilder.Append(sWriter & "</body></html>")

38Response.Write(sBuilder.ToString())

39 

40 Response.[End]()

41End Sub

42 

43Private Sub ChangeControlsToValue(gridView As Control)

44 Dim literal As New Literal()

45 

46 For i As Integer = 0 To gridView.Controls.Count - 1

47  If gridView.Controls(i).[GetType]() = GetType(LinkButton) Then

48 

49   literal.Text = TryCast(gridView.Controls(i), LinkButton).Text

50   gridView.Controls.Remove(gridView.Controls(i))

51   gridView.Controls.AddAt(i, literal)

52  ElseIf gridView.Controls(i).[GetType]() = GetType(DropDownList) Then

53   literal.Text = TryCast(gridView.Controls(i), DropDownList).SelectedItem.Text

54 

55   gridView.Controls.Remove(gridView.Controls(i))

56 

57 

58   gridView.Controls.AddAt(i, literal)

59  ElseIf gridView.Controls(i).[GetType]() = GetType(CheckBox) Then

60   literal.Text = If(TryCast(gridView.Controls(i), CheckBox).Checked, "True", "False")

61   gridView.Controls.Remove(gridView.Controls(i))

62   gridView.Controls.AddAt(i, literal)

63  End If

64  If gridView.Controls(i).HasControls() Then

65 

66 

67   ChangeControlsToValue(gridView.Controls(i))

68 

69  End If

70 Next

71 

72End Sub

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

Download Sample Code

Tweet

If you like this post than join us or share