Introduction:
Here I will explain how to export gridviewdata to Word and Excel using asp.net.
Here don’t forgot to set the connection string in web.config file here I am getting database connection from web.config file for that reason you need to set the connectionstring in web.config file like this
Here I will explain how to export gridviewdata to Word and Excel using asp.net.
Description:
I have one gridview that has filled with user details now I need to export gridview data to word or excel document based on selection. To implement this functionality first we need to design aspx page like this
<html xmlns="http://www.w3.org/1999/xhtml"> <head id="Head1" runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <table> <tr> <td align="right"> <asp:ImageButton ID="btnExcel" runat="server" ImageUrl="~/ExcelImage.jpg" onclick="btnExcel_Click" /> <asp:ImageButton ID="btnWord" runat="server" ImageUrl="~/WordImage.jpg" 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 UserInformation"/> </div> </form> </body> </html> |
<connectionStrings> <add name="dbconnection" connectionString="Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB"/> </connectionStrings> |
After set the connection string in web.config file now we are able to get the data from database and we can bind that data to gridview by using sqldatasource. Now if you run application gridview appears like this
 |
Now in code behind add this reference
using System.IO; |
After that write the following code in code behind
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(); } | |
Demo for Word document
 |
If you observe above code I added one function that is VerifyRenderingInServerForm this function is used to avoid the error like “control must be placed in inside of form tag”. If we set VerifyRenderingInServerForm function then compiler will think that controls rendered before exporting and our functionality will work perfectly.
Here I used basic code to export gridview data to word document and for excel code is different but we can use the same code (Export to Word) for excel also to import gridview data just by replacing Customers.doc to Customers.xls and application/ms-word to application/ms-excel but here we have problem that is row background color is applied throughout excel for that reason I made some small code modification and applied color only to the particular columns based on rows.
Demo for Excel document
Posts
2 comments:
Hi
Great job.It works pretty fine for me,BTW I got some useful page from google when I was trying to Export data form listview to Excel using c#.Hope it can contribute.
Thank Q alex
Post a Comment