In few previous article, we have explained how to export data to Excel in ASP.NET MVC and exporting datatable to excel using C# now in this article, I have explained how you can export to excel GridView in C# ASP.NET Web-Forms without using any external library and with formatting.
Step 1: Create new project in Visual Studio, navigate to File->New -> Project -> Select "Web" from left-pane and Select "ASP.NET Web-Application" from "right-pane", Name you project ("ExportGridViewToExcel") and Click "Ok".
In the Next Pop-Up, Select "Empty" and check "Web-Forms" to create Web-Forms Empty template, as shown below
Step 2: Connect your GridView to database to show data, before we connect GridView to database, I would like to mention we will be using AdventureWorks2012 database, if you don't have it, you can download and install AdventureWorks2012 in your SQL Server.
So, create a new "Default.aspx" Web-Form in your Solution and Add GridView
<form id="form1" runat="server">
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" AllowPaging="true"
HeaderStyle-BackColor="Black" HeaderStyle-ForeColor="White" OnPageIndexChanging="OnPageIndexChanging" PageSize="10">
<Columns>
<asp:BoundField DataField="PersonType" HeaderText="Person Type" />
<asp:BoundField DataField="FirstName" HeaderText="First Name" />
<asp:BoundField DataField="LastName" HeaderText="Last Name" />
</Columns>
</asp:GridView>
</form>In the above code, we have enabled paging using "AllowPaging= True" and calling method "OnPageIndexChanging="OnPageIndexChanging"" , each page will show 10 rows so "PageSize =10"
In the Default.aspx.cs code, use the below code to load data in GridView with paging
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
this.BindGrid();
}
}
private void BindGrid()
{
string constr = @"Data Source=VKS-PC-NEW\SQLEXPRESS;Initial Catalog='AdventureWorks2012';Integrated Security=True";
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("SELECT PersonType, FirstName, LastName FROM Person.Person"))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
}
}
}
protected void OnPageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
this.BindGrid();
}Once you are done, build and run your project in browser, you should see output as below
Step 3: Add New to button and call C# code in Codebehind to export GrdiView data into Excel, so complete form code after adding new button to export data to excel:
<form id="form1" runat="server">
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" AllowPaging="true"
HeaderStyle-BackColor="Black" HeaderStyle-ForeColor="White" OnPageIndexChanging="OnPageIndexChanging" PageSize="10">
<Columns>
<asp:BoundField DataField="PersonType" HeaderText="Person Type" />
<asp:BoundField DataField="FirstName" HeaderText="First Name" />
<asp:BoundField DataField="LastName" HeaderText="Last Name" />
</Columns>
</asp:GridView>
<div>
<input type="button" id="ExportToExcelBtn" value="Export GridView To Excel"
onserverclick="ExportToExcelClick" runat="server" />
</div>
</form>Now, we need to add new C# code in CodeBehind (Default.aspx.cs), which will be called on button click
protected void ExportToExcelClick(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
//set file name and add headers
Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
Response.Charset = "";
//set content type of file
Response.ContentType = "application/vnd.ms-excel";
using (StringWriter sw = new StringWriter())
{
HtmlTextWriter hw = new HtmlTextWriter(sw);
//set AllowPaging to false to export all pages at once.
GridView1.AllowPaging = false;
this.BindGrid();
GridView1.HeaderRow.BackColor = Color.White;
//header style
foreach (TableCell cell in GridView1.HeaderRow.Cells)
{
cell.BackColor = GridView1.HeaderStyle.BackColor;
}
// apply colors to rows if there is any in GridView rows
foreach (GridViewRow row in GridView1.Rows)
{
row.BackColor = Color.White;
foreach (TableCell cell in row.Cells)
{
if (row.RowIndex % 2 == 0)
{
cell.BackColor = GridView1.AlternatingRowStyle.BackColor;
}
else
{
cell.BackColor = GridView1.RowStyle.BackColor;
}
cell.CssClass = "textmode";
}
}
GridView1.RenderControl(hw);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
}
// need to Override metho to avoid this error "Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server."
public override void VerifyRenderingInServerForm(Control control)
{
/* Confirms that an HtmlForm control is rendered for the specified ASP.NET
server control at run time. */
}As mentioned in the comments, we have added below code
// need to Override metho to avoid this error "Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server."
public override void VerifyRenderingInServerForm(Control control)
{
/* Confirms that an HtmlForm control is rendered for the specified ASP.NET
server control at run time. */
}
To avoid error "Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server."
So complete Codebehind Code will be as below
using System;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace ExportGridViewToExcel
{
public partial class Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
this.BindGrid();
}
}
private void BindGrid()
{
string constr = @"Data Source=VKS-PC-NEW\SQLEXPRESS;Initial Catalog='AdventureWorks2012';Integrated Security=True";
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("SELECT PersonType, FirstName, LastName FROM Person.Person"))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
}
}
}
protected void OnPageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
this.BindGrid();
}
protected void ExportToExcelClick(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
//set file name and add headers
Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
Response.Charset = "";
//set content type of file
Response.ContentType = "application/vnd.ms-excel";
using (StringWriter sw = new StringWriter())
{
HtmlTextWriter hw = new HtmlTextWriter(sw);
//set AllowPaging to false to export all pages at once.
GridView1.AllowPaging = false;
this.BindGrid();
GridView1.HeaderRow.BackColor = Color.White;
//header style
foreach (TableCell cell in GridView1.HeaderRow.Cells)
{
cell.BackColor = GridView1.HeaderStyle.BackColor;
}
// apply colors to rows if there is any in GridView rows
foreach (GridViewRow row in GridView1.Rows)
{
row.BackColor = Color.White;
foreach (TableCell cell in row.Cells)
{
if (row.RowIndex % 2 == 0)
{
cell.BackColor = GridView1.AlternatingRowStyle.BackColor;
}
else
{
cell.BackColor = GridView1.RowStyle.BackColor;
}
cell.CssClass = "textmode";
}
}
GridView1.RenderControl(hw);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
}
// need to Override metho to avoid this error "Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server."
public override void VerifyRenderingInServerForm(Control control)
{
/* Confirms that an HtmlForm control is rendered for the specified ASP.NET
server control at run time. */
}
}
}Once you are done, you can build solution and run this in your browser, you will see output as below.
Warning which you saw in the above Gif, once the file is opened in MS Excel, is because this excel sheet is created using Custom C# code, if you want to avoid this error, I will insist you to use EPPlus Or Microsoft.Office.Interop to Export data to excel.
You may also like to read:
Buy us a coffee
Become a Patron