Thursday, May 3, 2012

Exporting a GridView To Excel is simple
Well... but it soms come with some complications
If the gridview has TextBoxes or Checkboxes, you would have to take only the value and put it to a literal control, then export.
And while exporting; If you are using the UpdatePanel, it means you cannot have the export button within the panel,
as this works against the response object to create the excel file, you would have to add a postback trigger to the button.


the .aspx
<asp:UpdatePanel runat=server ID=updPnl>
<ContentTemplate>
<table>
<tr><td><asp:ImageButton ID="LinkButtonExcel"  runat="server" ImageUrl="~images/ex.jpg"
        ToolTip="Click to export contents of grid to Excel."
        onclick="LinkButtonExcel_Click" CausesValidation="false"></asp:ImageButton>
</td></tr>
<tr><td>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="True" ></asp:GridView>
<td><tr>

</table>
 </ContentTemplate></UpdatePanel>

the codebehind

.cs
protected void LinkButtonExcel_Click(object sender, ImageClickEventArgs e)
        {
            try
            {
               
GridView1.AllowPaging = false;
                bindRpt(); // this method binds the gridview
                GenerateExcel(
GridView1);
            }
            catch
            {
                Page.ClientScript.RegisterStartupScript(this.GetType(), "", "alert('Could not Export to excel, please try again later );", true);
            }
        }


public void GenerateExcel(GridView grdSource)
        {

            StringBuilder sbDocBody = new StringBuilder();
            try
            {
                // Declare Styles
                sbDocBody.Append("<style>");
                sbDocBody.Append(".Header {  background-color:Navy; color:#ffffff; font-weight:bold;font-family:Verdana; font-size:12px;}");
                sbDocBody.Append("</style>");
                //
                StringBuilder sbContent = new StringBuilder();

                sbDocBody.Append("<br><table align=\"center\" cellpadding=0 cellspacing=0 border=1>");

                if (grdSource.Rows.Count > 0)
                {
                    for (int i = 0; i < grdSource.Columns.Count - 1; i++)
                    {
                        sbDocBody.Append("<td class=\"Header\" width=\"120\">" + grdSource.Columns[i].HeaderText + "</td>");
                    }
                    sbDocBody.Append("</tr>");
                    // Add Data Rows
                    for (int i = 0; i < grdSource.Rows.Count; i++)
                    {
                        sbDocBody.Append("<tr>");
                        for (int j = 0; j < grdSource.Columns.Count - 1; j++)
                        {
                            if (grdSource.Rows[i].Cells[j].Controls.Count == 0)
                            {
                                sbDocBody.Append("<td class=\"Content\">" + grdSource.Rows[i].Cells[j].Text + "</td>");
                            }
                            else
                            {
                                if (grdSource.Rows[i].Cells[j].Controls[1].ToString() == "System.Web.UI.WebControls.LinkButton")
                                {
                                    LinkButton lnkBtn = (LinkButton)grdSource.Rows[i].Cells[j].Controls[1];
                                    sbDocBody.Append("<td class=\"Content\">" + lnkBtn.Text + "</td>");
                                }
                                else if (grdSource.Rows[i].Cells[j].Controls[1].ToString() == "System.Web.UI.WebControls.Label")
                                {
                                    Label lblData = (Label)grdSource.Rows[i].Cells[j].Controls[1];
                                    sbDocBody.Append("<td class=\"Content\">" + lblData.Text + "</td>");
                                }
                                else
                                if (grdSource.Rows[i].Cells[j].Controls[1].ToString() == "System.Web.UI.WebControls.TextBox")
                                {
                                    TextBox tb = (TextBox)grdSource.Rows[i].Cells[j].Controls[1];
                                    sbDocBody.Append("<td class=\"Content\">" + tb.Text + "</td>");
                                }
                            }
                        }
                        sbDocBody.Append("</tr>");
                    }
                    sbDocBody.Append("</table>");
                 }
                //
                HttpContext.Current.Response.Clear();
                HttpContext.Current.Response.Buffer = true;
                //
                HttpContext.Current.Response.AppendHeader("Content-Type", "application/ms-excel");
                HttpContext.Current.Response.AppendHeader("Content-disposition", "attachment; filename=BiasReport-" + DateTime.Now.ToLongDateString() + ".xls");
                HttpContext.Current.Response.Write(sbDocBody.ToString());
                HttpContext.Current.Response.End();
            }
            catch (Exception ex)
            {
                // Ignore
            }
        }