In
this article I’m going to explain how to export GridView data to excel using
ASP.NET and C#.
I got a
requirement that I have to export Grid View records to Excel sheet. While
exporting GridView records to Excel sheet I’ve to concentrate Excel sheet
formatting, which includes GridView header row background color and alternate
row color.
If you want to export GridView records to PDF document you could refer this article.
Export GridView data to PDF document using iTextSharp
Table Design:
|
Column
Name
|
Data
Type
|
|
empid
|
varchar(50)
|
|
name
|
varchar(100)
|
|
designation
|
varchar(100)
|
|
city
|
varchar(50)
|
|
country
|
varchar(50)
|
Create table script:
CREATE TABLE [dbo].[EmployeeDetails](
[empid] [varchar](50) NULL,
[name] [varchar](100) NULL,
[designation] [varchar](100) NULL,
[city] [varchar](50) NULL,
[country] [varchar](50) NULL
) ON [PRIMARY]
Designer Source Code:
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Export
Grid View to Excel</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ImageButton ID="imgbtnExcel" runat="server" ImageUrl="~/Images/excel_icon.png"
OnClick="imgbtnExcel_Click" />
<asp:GridView ID="gvDetails" runat="server" AutoGenerateColumns="false" ShowFooter="true"
Width="100%">
<AlternatingRowStyle BackColor="#AED6FF" />
<Columns>
<asp:BoundField DataField="empid" HeaderText="Employee-ID" />
<asp:BoundField DataField="name" HeaderText="Name" />
<asp:BoundField DataField="designation" HeaderText="Designation" />
<asp:BoundField DataField="city" HeaderText="City" />
<asp:BoundField DataField="country" HeaderText="Country" />
</Columns>
<HeaderStyle BackColor="#0063A6" ForeColor="White" />
</asp:GridView>
</div>
</form>
</body>
</html>
Here we have to use
following method to avoid exception something like
Control 'gvDetails' of type 'GridView' must be placed inside a form
tag with runat=server.
public override void VerifyRenderingInServerForm(Control control)
{
/* Verifies
that the control is rendered */
}
Note:
You have to use following namespace
using System.IO;
C# Code :
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.IO;
public partial class _Default :
System.Web.UI.Page
{
//get connection
string from web.config
SqlConnection
conn = new SqlConnection("Data Source=SPIDER;Initial Catalog=Demo; Integrated
Security=True");
protected void Page_Load(object
sender, EventArgs e)
{
if
(!IsPostBack)
{
BindGrid();
}
}
public override void
VerifyRenderingInServerForm(Control control)
{
/* Verifies
that the control is rendered */
}
protected void BindGrid()
{
DataSet ds = new DataSet();
conn.Open();
string
cmdstr = "Select * from EmployeeDetails";
SqlCommand
cmd = new SqlCommand(cmdstr,
conn);
SqlDataAdapter
adp = new SqlDataAdapter(cmd);
adp.Fill(ds);
gvDetails.DataSource = ds;
gvDetails.DataBind();
conn.Close();
}
protected void imgbtnExcel_Click(object
sender, ImageClickEventArgs e)
{
Response.ClearContent();
Response.Buffer = true;
Response.AddHeader("content-disposition", string.Format("attachment;
filename={0}", "Employees.xls"));
Response.ContentType = "application/ms-excel";
StringWriter
sw = new StringWriter();
HtmlTextWriter
htw = new HtmlTextWriter(sw);
gvDetails.AllowPaging = false;
//Change the
Header Row back to white color
gvDetails.HeaderRow.Style.Add("background-color", "#FFFFFF");
//Applying
stlye to gridview header cells
for (int i = 0; i < gvDetails.HeaderRow.Cells.Count;
i++)
{
gvDetails.HeaderRow.Cells[i].Style.Add("background-color",
"#507CD1");
}
int j =
1;
//Set
alternate row color
foreach
(GridViewRow gvrow in
gvDetails.Rows)
{
gvrow.BackColor = System.Drawing.Color.White;
if
(j <= gvDetails.Rows.Count)
{
if
(j % 2 != 0)
{
for
(int k = 0; k < gvrow.Cells.Count; k++)
{
gvrow.Cells[k].Style.Add("background-color",
"#EFF3FB");
}
}
}
j++;
}
gvDetails.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}
}
Export GridView to word document:
If you want to export GridView records to word
document you have to do very simple changes
in above code
1.First change file extension to .doc instead of .xls
2.Change
content type "application/ms-word" instead of "application/ms-excel"
Sample
code:
Response.AddHeader("content-disposition", string.Format("attachment;
filename={0}", "Employees.doc"));
Response.ContentType
= "application/ms-word";