Export Gridview to Excel File.

By calling following functions, we get Formatted Excel File from GridView. Here gvTestData is Gridview & TestExcel.xls is output excel file.

ExportFormattedGridExcel(gvTestData,”TestExcel.xls”)

public static void ExportFormattedGridExcel(GridView gv,string FileName)
{
foreach (TableCell tc in gv.HeaderRow.Cells)
{
tc.BackColor = System.Drawing.ColorTranslator.FromHtml(“#87102C”); // maroon type
tc.Font.Name = “Trebuchet MS”;
tc.Font.Size = FontUnit.Point(10);
tc.ForeColor = System.Drawing.Color.White;
tc.BorderColor = System.Drawing.Color.White;
tc.BorderWidth = Unit.Point(1);
tc.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center;
tc.Wrap = false;
tc.Font.Bold = true;
}
foreach (GridViewRow tr in gv.Rows)
{
foreach (TableCell tc in tr.Cells)
{
tc.Font.Name = “Trebuchet MS”;
tc.Font.Size = FontUnit.Point(9);
tc.ForeColor = System.Drawing.ColorTranslator.FromHtml(“#424242″);
tc.BorderColor = System.Drawing.Color.LightGray;
tc.BorderWidth = Unit.Point(1);
tc.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center;
tc.Wrap = false;
tc.Font.Bold = true;
}
}

HtmlForm form1 = new HtmlForm();
form1.Controls.Clear();
form1.Controls.Add(gv);
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
PrepareControlForExport(gv);
gv.RenderControl(htmlWrite);
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader(“content-disposition”, “attachment;filename=”+FileName);
HttpContext.Current.Response.ContentType = “Application/x-msexcel”;
//HttpContext.Current.Response.ContentType = “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”;
HttpContext.Current.Response.Write(stringWrite.ToString());
HttpContext.Current.Response.End();

}

/// Prepare rendering of control before export
private static void PrepareControlForExport(Control control)
{

for (int i = 0; i < control.Controls.Count; i++)
{
Control current = control.Controls[i];
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 HiddenField)
{
control.Controls.Remove(current);
}
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);
}
}
}