Create Excel file from Gridview
This program will generate Excel file
from database data. You can also Generate Word document also. Here I have taken
a gridview and my table name is tablename.
Step 1) Open a ASP.Net page
Step 2) Drag and drop one gridview and
configure the data source from your database.
Step 3) Add the following Code in your
ASP.Net page to get Gridview and configure Datasource option of your GridView
to set proper database Connection.
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Creating Excel file
from Gridview</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<td align="right">
<asp:ImageButton ID="btnExcel" runat="server" onclick="btnExcel_Click" text=”Excel”/>
<asp:ImageButton ID="btnWord" runat="server" text=”Word” onclick="btnWord_Click"
/>
</td>
</tr>
<tr>
<td>
<asp:GridView runat="server" ID="gvdetails" DataSourceID="dsdetails" AllowPaging="true" AllowSorting="true" AutoGenerateColumns="false">
<RowStyle BackColor="#EFF3FB" />
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:BoundField DataField="UserId" HeaderText="UserId" />
<asp:BoundField DataField="UserName" HeaderText="UserName" />
<asp:BoundField DataField="LastName" HeaderText="LastName" />
<asp:BoundField DataField="Location" HeaderText="Location" />
</Columns>
</asp:GridView>
</td>
</tr>
</table>
<asp:SqlDataSource ID="dsdetails" runat="server" ConnectionString="<%$ConnectionStrings:dbconnection
%>"
SelectCommand="select * from tablename"/>
</div>
</form>
</body>
</html>
Step 4) Add those coding to your web.config file for get
proper database connection I have used database configuration setting in my
web.config file.
<connectionStrings>
<add name="dbconnection" connectionString="Data Source=SureshDasari;Integrated Security=true;Initial
Catalog=MySampleDB"/>
</connectionStrings>
Step 5)Add these code in code behind
of your page
public override
void VerifyRenderingInServerForm(Control control)
{
/*
Verifies that the control is rendered */
}
/// <summary>
/// This event is used to export gridview data to word
document
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void
btnWord_Click(object sender, ImageClickEventArgs e)
{
gvdetails.AllowPaging
= false;
gvdetails.DataBind();
Response.ClearContent();
Response.AddHeader("content-disposition", string.Format("attachment;
filename={0}", "Customers.doc"));
Response.Charset
= "";
Response.ContentType
= "application/ms-word";
StringWriter sw = new
StringWriter();
HtmlTextWriter htw = new
HtmlTextWriter(sw);
gvdetails.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}
/// <summary>
/// This Event is used to export gridview data to Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void
btnExcel_Click(object sender, ImageClickEventArgs e)
{
Response.ClearContent();
Response.Buffer =
true;
Response.AddHeader("content-disposition", string.Format("attachment;
filename={0}", "Customers.xls"));
Response.ContentType
= "application/ms-excel";
StringWriter sw = new
StringWriter();
HtmlTextWriter htw = new
HtmlTextWriter(sw);
gvdetails.AllowPaging
= false;
gvdetails.DataBind();
//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;
//This
loop is used to apply stlye to cells based on particular row
foreach (GridViewRow
gvrow in gvdetails.Rows)
{
gvrow.BackColor =
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();
}
