Export gridview to excel in asp.net using c#

In this asp.net tutorial I will teach you how to export gridView to excel.

Let’s have a look over code of our aspx page

Gridview_to_excel.aspx

<form id="form1" runat="server">

<div>

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false"
onrowdatabound="GridView1_RowDataBound" Width="100%" GridLines="None" HeaderStyle-HorizontalAlign="Left" HeaderStyle-VerticalAlign="Top">

<Columns>

<asp:TemplateField HeaderText="Roll No">

<ItemTemplate>

<asp:Label ID="lbl_Rollno" runat="server" Text='<%# Bind("RollNo")%>'>
</asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="First Name">

<ItemTemplate>

<asp:Label ID="lbl_Firstname" runat="server" Text='<%# Bind("FirstName")%>'>
</asp:Label>

</ItemTemplate>

</asp:TemplateField>


<asp:TemplateField HeaderText="Last Name">

<ItemTemplate>

<asp:Label ID="lbl_Lastname" runat="server" Text='<%# Bind("LastName")%>'>
</asp:Label>

</ItemTemplate>

</asp:TemplateField>


<asp:TemplateField HeaderText="City">

<ItemTemplate>

<asp:Label ID="lbl_City" runat="server" Text='<%# Bind("City")%>'>
</asp:Label>

</ItemTemplate>


</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
<br />
<asp:Button ID="btn_excel" runat="server" onclick="btn_excel_Click" Text="Export to Excel"> </asp:Button>

</form>

The above mentioned code is very clear, We are using GridView Control in our web page and assigning data to gridview from database. After GridView control we have an asp button and its onClick event we are calling our custom function that will export data from gridview to excel.

Let’s Have a look over its .cs page

Gridview_to_excel.aspx.cs

Please add these namespaces in the list of namespaces so that we can successfully export data from grid view to excel.

using System.Text;
using System.IO;

protected void Page_Load(object sender, EventArgs e)

{

if (!Page.IsPostBack)

{

BindData();

}

}

private void BindData()

{

string query = "SELECT * FROM Students";

SqlConnection myConnection = new SqlConnection(ConnectionString);

SqlDataAdapter ad = new SqlDataAdapter(query, myConnection);

DataSet ds = new DataSet(); ad.Fill(ds, "Students");

GridView1.DataSource = ds;

GridView1.DataBind();

}

private string ConnectionString

{

get

{

return @"Server=localhost;Database=YourDatabase;Trusted_Connection=true";

}

}



public override void VerifyRenderingInServerForm(Control control)
{
/* Its Confirms that an HtmlForm control is rendered for the specified .net server control at run time. */
}

public static void Export(string fileName, GridView gv)
{
string style = @"<style> .text { mso-number-format:\@; } </style> ";
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader(
"content-disposition", string.Format("attachment; filename={0}", fileName));
HttpContext.Current.Response.ContentType = "application/ms-excel";

using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
// Create a form to contain the grid
Table table = new Table();

// add the header row to the table
if (gv.HeaderRow != null)
{
PrepareControlForExport(gv.HeaderRow);
table.Rows.Add(gv.HeaderRow);
}

// add each of the data rows to the table
foreach (GridViewRow row in gv.Rows)
{
PrepareControlForExport(row);
table.Rows.Add(row);
}

// add the footer row to the table
if (gv.FooterRow != null)
{
PrepareControlForExport(gv.FooterRow);
table.Rows.Add(gv.FooterRow);
}

// render the table into the htmlwriter
table.RenderControl(htw);
HttpContext.Current.Response.Write(style);
HttpContext.Current.Response.Write(sw.ToString());
HttpContext.Current.Response.End();
}
}
}
private static void PrepareControlForExport(Control control)
{
for (int i = 0; i < control.Controls.Count; i++)
{
Control current = control.Controls[i];
if (current is Label)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as Label).Text));
}
if (current is LinkButton)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
}
else if (current is ImageButton)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
}
else if (current is HyperLink)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
}
else if (current is DropDownList)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
}
else if (current is CheckBox)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
}

if (current.HasControls())
{
PrepareControlForExport(current);
}
}
}
protected void btn_excel_Click(object sender, EventArgs e)
{
//Export the grid data to excel sheet
Export("MyExcelfile.xls", this.GridView1);
}


PrepareControlForExport() function will restrict controls to export themselves to excel, mean if you have textbox control in gridview then this function will not export textbox from gridview to excel, actually it will export textbox data from gridview to excel.

If you will not use function VerifyRenderingInServerForm(Control control) in your C# code then you will get following error depends upon the controls you are using in the .aspx page

Control 'your_control' of type 'control_type' must be placed inside a form tag with runat=server.

This is quite confusing, since your GridView and all its controls already inside the form tags containing attribute runat=”server”, So please use this VerifyRenderingInServerForm(Control control) function in your C# Code to remove this error.

In export function the first line of code is

string style = @"<style> .text { mso-number-format:\@; } </style> ";

Actually I use this text style to accurately export data from gridview to excel because in some cases users have numeric or floting type data in gridview such as 109876,54 or 1.987654. by default the numeric or floting point data type style in gridview is mso-number-format:\@ so we have to convert this style to text that’s why I use this line of code and also assign text style to those numeric or floting point data containing fields in Row Bound event of GridView

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
//If the first template field of gridview contains
//numeric or floting point data then use this code
e.Row.Cells[0].Attributes.Add("class", "text");

}
}

So now feel free to export grid view data to excel and it will show you exact data without converting it into such as +E.01

If you want to export gridview data to excel within an UpdatePanel then please read this post Export gridview to excel within an UpdatePanel

This is the way to export grid view data to excel