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.

Export Gridview To Excel Asp.Net
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
Response.ClearContent();

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

        Response.ContentType = "application/excel";

        StringWriter sWriter = new StringWriter();

        HtmlTextWriter hTextWriter = new HtmlTextWriter(sWriter);

        GridView1.RenderControl(hTextWriter);

        Response.Write(sWriter.ToString());

        Response.End();


httpexception error
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.








public override void VerifyRenderingInServerForm(Control control)
{
}

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

RegisterForEventValidation error
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.




<%@ Page Language="C#" AutoEventWireup="true"  EnableEventValidation="false" 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.

private void ChangeControlsToValue(Control gridView)
    {
        Literal literal = new Literal();
        
        for (int i = 0; i < gridView.Controls.Count; i++)
        {
            if (gridView.Controls[i].GetType() == typeof(LinkButton))
            {

                literal.Text = (gridView.Controls[i] as LinkButton).Text;
                gridView.Controls.Remove(gridView.Controls[i]);
                gridView.Controls.AddAt(i,literal);
            }
            else if (gridView.Controls[i].GetType() == typeof(DropDownList))
            {
                literal.Text = (gridView.Controls[i] as DropDownList).SelectedItem.Text;

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

                gridView.Controls.AddAt(i,literal);

            }
            else if (gridView.Controls[i].GetType() == typeof(CheckBox))
            {
                literal.Text = (gridView.Controls[i] as CheckBox).Checked ? "True" : "False";
                gridView.Controls.Remove(gridView.Controls[i]);
                gridView.Controls.AddAt(i,literal);
            }
            if (gridView.Controls[i].HasControls())
            {

                ChangeControlsToValue(gridView.Controls[i]);

            }

        }

    }


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
protected void btnExportToExcel_Click(object sender, EventArgs e)
    {
        if (RadioButtonList1.SelectedIndex == 0)
        {
            GridView1.ShowHeader = true;
            GridView1.GridLines = GridLines.Both;
            GridView1.AllowPaging = false;
            GridView1.DataBind();
        }
        else
        {
            GridView1.ShowHeader = true;
            GridView1.GridLines = GridLines.Both;
            GridView1.PagerSettings.Visible = false;
            GridView1.DataBind();
        }

        ChangeControlsToValue(GridView1);
        Response.ClearContent();

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

        Response.ContentType = "application/excel";

        StringWriter sWriter = new StringWriter();

        HtmlTextWriter hTextWriter = new HtmlTextWriter(sWriter);

        HtmlForm hForm = new HtmlForm();

        GridView1.Parent.Controls.Add(hForm);

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

        hForm.Controls.Add(GridView1);

        hForm.RenderControl(hTextWriter);

        // Write below code to add cell border to empty cells in Excel file
        // If we don't add this line then empty cells will be shown as blank white space

         StringBuilder sBuilder = new StringBuilder();
        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>");
        sBuilder.Append(sWriter + "</body></html>");
        Response.Write(sBuilder.ToString());
        Response.End();
    }

    private void ChangeControlsToValue(Control gridView)
    {
        Literal literal = new Literal();
        
        for (int i = 0; i < gridView.Controls.Count; i++)
        {
            if (gridView.Controls[i].GetType() == typeof(LinkButton))
            {

                literal.Text = (gridView.Controls[i] as LinkButton).Text;
                gridView.Controls.Remove(gridView.Controls[i]);
                gridView.Controls.AddAt(i,literal);
            }
            else if (gridView.Controls[i].GetType() == typeof(DropDownList))
            {
                literal.Text = (gridView.Controls[i] as DropDownList).SelectedItem.Text;

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

                gridView.Controls.AddAt(i,literal);

            }
            else if (gridView.Controls[i].GetType() == typeof(CheckBox))
            {
                literal.Text = (gridView.Controls[i] as CheckBox).Checked ? "True" : "False";
                gridView.Controls.Remove(gridView.Controls[i]);
                gridView.Controls.AddAt(i,literal);
            }
            if (gridView.Controls[i].HasControls())
            {

                ChangeControlsToValue(gridView.Controls[i]);

            }

        }

    }

VB.NET
Protected Sub btnExportToExcel_Click(sender As Object, e As EventArgs)
 If RadioButtonList1.SelectedIndex = 0 Then
  GridView1.ShowHeader = True
  GridView1.GridLines = GridLines.Both
  GridView1.AllowPaging = False
  GridView1.DataBind()
 Else
  GridView1.ShowHeader = True
  GridView1.GridLines = GridLines.Both
  GridView1.PagerSettings.Visible = False
  GridView1.DataBind()
 End If

 ChangeControlsToValue(GridView1)
 Response.ClearContent()

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

 Response.ContentType = "application/excel"

 Dim sWriter As New StringWriter()

 Dim hTextWriter As New HtmlTextWriter(sWriter)

 Dim hForm As New HtmlForm()

 GridView1.Parent.Controls.Add(hForm)

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

 hForm.Controls.Add(GridView1)

 hForm.RenderControl(hTextWriter)

 Dim sBuilder As New StringBuilder()
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>")
sBuilder.Append(sWriter & "</body></html>")
Response.Write(sBuilder.ToString())

 Response.[End]()
End Sub

Private Sub ChangeControlsToValue(gridView As Control)
 Dim literal As New Literal()

 For i As Integer = 0 To gridView.Controls.Count - 1
  If gridView.Controls(i).[GetType]() = GetType(LinkButton) Then

   literal.Text = TryCast(gridView.Controls(i), LinkButton).Text
   gridView.Controls.Remove(gridView.Controls(i))
   gridView.Controls.AddAt(i, literal)
  ElseIf gridView.Controls(i).[GetType]() = GetType(DropDownList) Then
   literal.Text = TryCast(gridView.Controls(i), DropDownList).SelectedItem.Text

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


   gridView.Controls.AddAt(i, literal)
  ElseIf gridView.Controls(i).[GetType]() = GetType(CheckBox) Then
   literal.Text = If(TryCast(gridView.Controls(i), CheckBox).Checked, "True", "False")
   gridView.Controls.Remove(gridView.Controls(i))
   gridView.Controls.AddAt(i, literal)
  End If
  If gridView.Controls(i).HasControls() Then


   ChangeControlsToValue(gridView.Controls(i))

  End If
 Next

End Sub

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

Download Sample Code


If you like this post than join us or share

16 comments:

Anonymous said...

Great.
Thanks for sharing.

By the way, I have a question about exporting GridView to Excel: if the GridView has the selection and sort options enabled, can I remove them using above method?

I am trying now to remove these two options from the exported Excel sheet but I did not get any successful results.


amiT jaiN said...

@Above: Plz write below mentioned code in click event of export button, i am assuming the select button to be the first [ 0 th column ]

GridView1.AllowSorting = false;
GridView1.Columns[0].Visible = false;
GridView1.DataBind();


sonya said...

How can I save the data in a form format and not in gridview into a excel spreadsheet?


Mike Gledhilll said...

It's amazing how many "How do I export from a GridView / DataSet to Excel" articles are out there, and how many of them recommend just sending out a load of values to the "HttpContext.Current.Response".

Here's full source code to create *real* Office 2007 files, using the OpenXML libraries.
You just need to include this class in your application, and call one function.

DataSet ds = CreateSampleData();
CreateExcelFile.CreateExcelDocument(ds, "C:\\Sample.xlsx");

Full source code, and a demo application are provided, free of charge.
http://www.mikesknowledgebase.com/pages/CSharp/ExportToExcel.htm

Good luck !


Anonymous said...

This comment has been removed by a blog administrator.


Mike said...

Hi amiT jaiN

Great post :-)

I have a similar code to export. But, it doesn't work properly for the nested Gridview. I'm using C#

I tried to put code you have where you wrote some xml tags and have the following comment:
// Write below code to add cell border to empty cells in Excel file
// If we don't add this line then empty cells will be shown as blank white space

The compiler says that a ; is expected and higlight the whole string in red. I guess because it constains the characther \ in a few parts.
I removed them, but not at the moment to export to excel an error comes up saying:

Problem during load.
Strict Parse Error
This file cannot be opened besause of error. Errors are listed in ....

Did that xml string work for you?
If so, could you share how you formatted the string?

Regards!


amiT jaiN said...

@Mike: Thanks for pointing out the issue, i have updated the source code and post
complete string was hidden because of it containing HTML tags


Vasudev Amodkar said...

we paste this code.but problem of opening the excelsheet.so what to do


amiT jaiN said...

@vasudev: i didn't get what do you want to do ?


Anonymous said...

Hello,

I have implemented Export to Excel functionality to a gridview that supports sorting and the header row is always invisible when exported to excel. I have tried using the statement gv.ShowHeader = true; before calling the ChangeControlsToValue method like you have done. I also tried gv.Rows[0].Visible = true; and gv.HeaderRow.Visible = true; but the problem persists.

Can you please help?


amiT jaiN said...

@Above: i checked my code and it's showing headers in excel file, you please download the source code and run it to see whether it's working for you, then you can compare your code with this one


Anonymous said...

Hi,

Thanks, I found some silly mistakes in mine after comparing with yours, and it is working fine now. Thanks!


kanak said...

can we export to ms access in similar way


Unknown said...

Charming solution. Kudos to the post. Saved my time a lot.


amiT jaiN said...

@kanak: ms access is database and saving gridview data to database is altogether different approach


Anonymous said...

really thankful to you brother.. keep it up..


Find More Articles