Tuesday, February 7, 2012

Create Excel file from Database Gridview


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();
}





No comments:

Post a Comment