Dec 20

Written by: Wil Dobson
12/20/2007 2:29 PM

Here is how I export my gridview's to excel!

First of all, we need to ensure that our Event Validation isn't taking place:

In your Source Code - avoid EnableEventValidation
---------------------------------------------------------------------------------
<%@ Page Language="C#"
EnableEventValidation="false"
... />


Your EXPORT button:
---------------------------------------------------------------------------------
AlternateText="Click to export this to Excel"
ImageUrl="images/goog_spreadsheets.gif"
OnClick="imgbtnExport2Excel_Click" />


In your code-behind - react to your [Export] button click
---------------------------------------------------------------------------------

#region Export 2 Excel Code

private string fileName = "NewHires.xls";

protected void imgbtnExport2Excel_Click(object sender, ImageClickEventArgs e)
{
string style = @"< style> .text { mso-number-format:\@; } < /script> ";

if (GridView1.AllowPaging == true)
{
DisableControls(GridView1);
}

WriteXLS(style);
}

private void WriteXLS(string style)
{
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename="
+ fileName);
Response.ContentType = "application/excel";

StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);

GridView1.RenderControl(htw);
// Style is added dynamically
Response.Write(style);
Response.Write(sw.ToString());
Response.End();
}

// disables the paging so no data is lost in conversion
private void DisableControls(Control gv)
{
LinkButton lb = new LinkButton();
Literal l = new Literal();
string name = String.Empty;

for (int i = 0; i < gv.Controls.Count; i++)
{
if (gv.Controls[i].GetType() == typeof(LinkButton))
{
((LinkButton)gv.Controls[i]).ForeColor = Color.Black;
l.Text = (gv.Controls[i] as LinkButton).Text;

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

if (gv.Controls[i].HasControls())
{
DisableControls(gv.Controls[i]);
}
}
}

// override to prevent errors when writing excel document
public override void VerifyRenderingInServerForm(Control control)
{

}

// set up some fake classes for formatting cell colors in excel
// your gridview will need to use this for its RowDataBound event
protected void gv_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
e.Row.Cells[1].Attributes.Add("class", "text");
}
}

#endregion

Copyright ©2007 Wil Dobson

Tags: