To get started, I am having a gridview filled with data from employee table. The gridview looks like this with its contents and styling,
Now to export the contents of the above gridview to excel sheet, write the code below in "Export to Excel" button's click event.
protected void Button1_Click(object sender, EventArgs e) { Response.Clear(); Response.Buffer = true; string filename="GridViewExport_"+DateTime.Now.ToString()+".xls"; Response.AddHeader("content-disposition", "attachment;filename="+filename); Response.Charset = ""; Response.ContentType = "application/vnd.ms-excel"; StringWriter sw = new StringWriter(); HtmlTextWriter hw = new HtmlTextWriter(sw); GridView1.AllowPaging = false; GridView1.DataBind(); GridView1.RenderControl(hw); //style to format numbers to string string style = @"<style> .textmode { mso-number-format:\@; } </style>"; Response.Write(style); Response.Output.Write(sw.ToString()); Response.Flush(); Response.End(); }
This error occurs whenever we try to render a control to response. This can be rectified in two ways.
1. Adding gridview to HtmlForm Object Programmatically and render the form. Use the below code to do that,
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
| protected void Button1_Click(object sender, EventArgs e){ HtmlForm form = new HtmlForm(); Response.Clear(); Response.Buffer = true; string filename="GridViewExport_"+DateTime.Now.ToString()+".xls"; Response.AddHeader("content-disposition", "attachment;filename="+filename); Response.Charset = ""; Response.ContentType = "application/vnd.ms-excel"; StringWriter sw = new StringWriter(); HtmlTextWriter hw = new HtmlTextWriter(sw); GridView1.AllowPaging = false; GridView1.DataBind(); form.Controls.Add(GridView1); this.Controls.Add(form); form.RenderControl(hw); //style to format numbers to string string style = @"<style> .textmode { mso-number-format:\@; } </style>"; Response.Write(style); Response.Output.Write(sw.ToString()); Response.Flush(); Response.End();} |
2. Overriding VerifyRenderingInServerForm Event in the code behind page. This ensures that HtmlForm Control is rendered for the specific ASP.NET control at runtime. Just add the below event handler code beneath the Page_Load event handler in the code behind page.
1
2
3
4
| public override void VerifyRenderingInServerForm(Control control) { } |
One last thing one should do is add EnableEventValidation="false" property to the page directive or to the web.config file. This avoids one more exception from arising that will say "RegisterEventValidation can only be called during Render()". See below code for reference,
1
| <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="DatabaseTestDemo.Default" EnableEventValidation="false"%> |
The output excel file will have contents that look like this. Note that the styling that is applied to gridview is also exported along with the content.
If you wants to add custom styling to the contents of the excel sheet, then use can add it dynamically by adding custom styles to the gridview and render it. For example,
//Change the Header Row back to white colorGridView1.HeaderRow.Style.Add("background-color", "#FFFFFF"); //Apply style to Individual CellsGridView1.HeaderRow.Cells[0].Style.Add("background-color", "white");GridView1.HeaderRow.Cells[1].Style.Add("background-color", "white");GridView1.HeaderRow.Cells[2].Style.Add("background-color", "white");GridView1.HeaderRow.Cells[3].Style.Add("background-color", "white"); for (int i = 0; i < GridView1.Rows.Count;i++ ){ GridViewRow row = GridView1.Rows[i]; //Change Color back to white row.BackColor = System.Drawing.Color.White; //Apply text style to each Row row.Attributes.Add("class", "textmode"); //Apply style to Individual Cells of Alternating Row if (i % 2 != 0) { row.Cells[0].Style.Add("background-color", "#C2D69B"); row.Cells[1].Style.Add("background-color", "#C2D69B"); row.Cells[2].Style.Add("background-color", "#C2D69B"); row.Cells[3].Style.Add("background-color", "#C2D69B"); }}GridView1.RenderControl(hw);
