ASP.NET - Export GridView to Excel
Exporting GridView Data to Excel Sheet in ASP.NET is a common task for many Applications. In this article I am explaining problem and solution for this task.
In this article, I am going to explain steps for exporting GridView Data to Excel Sheet and what are the problems we face in this task.
There are two cases when you export GridView Data-
1. GridView without control like (Button, LinkButton, ImageButton etc.)
2. GridView with control like (Button, LinkButton, ImageButton etc.)
Generally we face problem with second option and the common error is-
Error- RegisterForEventValidation can only be called during Render();
Here I am going to explain both cases-
1. GridView without control like (Button, LinkButton, ImageButton etc.)
For this task, I have designed GridView like this-
Here is .aspx code-
<asp:GridView ID="GrdEmpData" runat="server" AutoGenerateColumns="false" Width ="50%">
<Columns>
<asp:TemplateField HeaderText="EmpId" ItemStyle-HorizontalAlign ="Left" ItemStyle-Width ="30%">
<ItemTemplate >
<asp:Label ID="lblEmpId" runat="server" Text='<% # Eval("EmpId") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="EmpName" ItemStyle-HorizontalAlign ="Left" ItemStyle-Width ="30%">
<ItemTemplate>
<asp:Label ID="lblEmpName" runat="server" Text='<% # Eval("EmpName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="DeptId" ItemStyle-HorizontalAlign ="Left" ItemStyle-Width ="30%">
<ItemTemplate>
<asp:Label ID="lblDeptName" runat="server" Text='<% # Eval("DeptName")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<br />
<asp:Button ID="btnExport" runat="server" Text="Export to Excel " onclick="btnExport_Click"/>
I have bound this GridView with DataTable, Here is .cs code for getting data from Database to bind GridView.
This is connection string in web.config file-
<connectionStrings>
<add name="ConString" connectionString="Data Source=ServerName;Initial Catalog=DataBaseName;Persist Security Info=True;User ID=UserName;Password=YourPassword"/>
</connectionStrings>
Now .cs code to get data from database-
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
//Binding Data to GridView
GrdEmpData.DataSource = GetData();
GrdEmpData.DataBind();
}
}
//Function to get data from database
private DataTable GetData()
{
DataTable dt = null;
try
{
string conString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
SqlConnection con = new SqlConnection(conString);
SqlDataAdapter da = new SqlDataAdapter("select * from tab_EmpMaster,tab_DeptMaster where tab_EmpMaster.DeptId=tab_DeptMaster.DeptId", con);
dt = new DataTable();
da.Fill(dt);
}
catch (Exception ex)
{
Response.Write("<script>alert('" + ex.Message + "')</script>");
}
return dt;
}
After Executing this code your will get data in GridView control. Now the main task is to export this data to Excel Sheet, Here is code for this-
protected void btnExport_Click(object sender, EventArgs e)
{
try
{
//Calling function to Export GridView Data to Excel Sheet
FunExportToExcel(GrdEmpData);
}
catch (Exception ex)
{
Response.Write("<script>alert('" + ex.Message + "')</script>");
}
}
//Function to export GridView data to Excel sheet
private void FunExportToExcel(GridView GrdView)
{
try
{
Response.Clear();
Response.AddHeader("content-disposition", "attachment; filename=FileName.xls");
Response.Charset = "";
// If you want the option to open the Excel file without saving than
// comment out the line below
// Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
GrdView.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();
}
catch (Exception ex)
{
Response.Write("<script>alert('" + ex.Message + "')</script>");
}
}
If you will use this code then after executing code you will get following error-
Control 'GrdEmpData' of type 'GridView' must be placed inside a form tag with runat=server
To solve this error include this code in your .cs file-
public override void VerifyRenderingInServerForm(Control control)
{
// Do nothing
}
After adding this code your GridView Data will be exported to Excel Sheet.
2. GridView with control like (Button, LinkButton, ImageButton)
For showing functionality of this case-2 , I have added one LinkButton inside GridView Like this-
<asp:TemplateField HeaderText="Delete" ItemStyle-HorizontalAlign ="Left" ItemStyle-Width ="20%">
<ItemTemplate>
<asp:LinkButton ID="lnkDelete" runat="server" Text="Delete" CommandName ="delete"></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
Now if you if use same code for this case , which we have used for case-1, then it will generate following error-
Error- RegisterForEventValidation can only be called during Render();
Note - This error comes because of rendering problem, when you export GridView data to Excel Sheet then controls of GridView first converted to Literal controls. But control like Button, LisnkButton , ImageButton etc. can't be converted to Literal Controls, so it generates error.
To solve this problem I have used following solution-
Before exporting GridView Data to Excel Sheet , hide all the controls like Button, LinkButton , ImageButton etc.
and after exporting data again show controls.
Here is .cs code for that-
protected void btnExport_Click(object sender, EventArgs e)
{
try
{
//Calling function to hide controls like Button, LinkButton, ImageButton inside GridView
HideControls();
//Calling function to Export GridView Data to Excel Sheet
FunExportToExcel(GrdEmpData2);
//Calling function to show controls like Button, LinkButton, ImageButton inside GridView
ShowControls();
}
catch (Exception ex)
{
Response.Write("<script>alert('" + ex.Message + "')</script>");
}
}
//Function to hide control like button,linkbutton of GridView
//Here I am hiding LinkButton, you can hide other controls also
private void HideControls()
{
try
{
foreach (GridViewRow row in GrdEmpData2.Rows)
{
LinkButton lnk = (LinkButton)row.FindControl("lnkDelete");
if (lnk != null)
{
lnk.Visible = false;
}
}
}
catch (Exception ex)
{
Response.Write("<script>alert('" + ex.Message + "')</script>");
}
}
//Function to show control like button,linkbutton of GridView
private void ShowControls()
{
try
{
foreach (GridViewRow row in GrdEmpData2.Rows)
{
LinkButton lnk = (LinkButton)row.FindControl("lnkDelete");
if (lnk != null)
{
lnk.Visible = true;
}
}
}
catch (Exception ex)
{
Response.Write("<script>alert('" + ex.Message + "')</script>");
}
}
Now execute this code, you will get result without any error.
If you want to download complete code then , download from here-
Download code- Here
Thanks
