In an asp.net application while using grid view to display a data there will be a requirement of exporting Grid view data to excel file. The following code will be used to export the grid view data to excel.
Grid view Design
The following code will design a form with grid view and “Export to Excel” Button.
<%@ Page Language="C#" EnableEventValidation = "false" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>
<br />
<asp:Button ID="nExport" runat="server" onclick="Export_Click"
Text="Export To Excel" />
</form>
</body>
</html>
In the Page Directive section include EnableEventValidation = "false".
Grid view Code
The following code will export the grid view data into excel when the "Export to Excel" Button is clicked.
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
LoadData();
}
}
private void LoadData()
{
SqlConnection con;
con = new SqlConnection("server=;uid=sa;password=;database=AdventureWorksDW");
con.Open();
SqlDataAdapter oAdapter = new SqlDataAdapter("SalesReason_S", con);
oAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
DataSet oDataSet = new DataSet();
oAdapter.Fill(oDataSet);
GridView1.DataSource = oDataSet.Tables[0];
GridView1.DataBind();
}
protected void Export_Click(object sender, EventArgs e)
{
ExportGridView();
}
private void ExportGridView()
{
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");
Response.Charset = "";
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);
GridView1.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();
}
public override void VerifyRenderingInServerForm(Control control)
{
}
}
When a user clicks the Export to Excel button, the excel file “FileName.xls” is downloaded.
The user can save or open the file by clicking “OK” button.