Exporting GridView to MS Excel using C#

Platform: ASP.NET-2.0
Database: MS Sql Server-2005
AdventureWork (download)

Main aim of this article is to export ASP.NET-2.0 GridView data into MS Excel. Additionally this article will focus on proper report formatting as well.

We have taken data from Customer table of default database Northwind.

Before going into export to excel functionality, first we have to fill the GridView.

We assume GridView is filled and its matter of exporting it into Excel. (Refer code attached with this article. AdventureWork).

There is button with ID btnExport on its Click event; we have to write the functionality of exporting. First set AllowPaging property of GridView to false so that whole data can be exported into single Excel file.

grdCustomerDetails.AllowPaging = false;

And then fill the GridView. At this time formatting GridView can be done so that it might reflect into Excel file.

grdCustomerDetails.ForeColor = Color.Black;

grdCustomerDetails.Style.Add("font-family", "Verdana");

grdCustomerDetails.Style.Add("font-size", "10px");

Here are few lines of codes that are self explanatory…

Response.Clear();

Response.AddHeader("content-disposition", "attachment; filename=Customer_Detailed_Report.xls");

Response.Charset = "";

Response.Cache.SetCacheability(HttpCacheability.NoCache);

Response.ContentType = "application/vnd-ms.xls";

StringWriter stringWrite = new StringWriter();

HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

While exporting we have to take care of report header as well. So for this purpose take a label control with visibility false. It will help in rendering the report header information with its text property.

lblExcelHeader.Visible = true;

string strReportHeader = "Employee Detailed Report
" +

"Report Generated on: " + DateTime.Now.ToString("dd-MMM-yyyy") + "
" +

"" +

"Note: This is a demo report";

lblExcelHeader.Text = strReportHeader;

lblExcelHeader.RenderControl(htmlWrite);

lblExcelHeader.Visible = false;

grdCustomerDetails.RenderControl(htmlWrite);

Response.Write(stringWrite.ToString());

Response.End();