Export GridView with TemplateField Column to Excel after removing controls in asp.net

Description:-

in this article we will see about how to export gridview control to excel and if we used templatefield in gridview control than how to export it in excel. Here i have given sample example to export gridview control with checkbox control and radio button control in excel file. using the template field we can also export gridview data to excel like below i have given example to follow steps.

Here in this example i have explain how to export template fields with getting type dynamically in asp.net. if we have used different controls in gridview like radio button, text box, label, checkbox, image controls etc... then we can export also that control in gridview like below.

Create Table:-

CREATE TABLE [dbo].[Customers](
 [ContactName] [varchar](20) NOT NULL,
 [City] [varchar](20) NOT NULL,
 [PostalCode] [int] NOT NULL,
 [Country] [varchar](20) NOT NULL,
 [Phone] [bigint] NOT NULL,
 [Fax] [bigint] NOT NULL,
 [CustomerId] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
 [CustomerId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Default.aspx:-

    <div>
        <asp:GridView ID="GridView1" HeaderStyle-BackColor="#3AC0F2" HeaderStyle-ForeColor="White"
            RowStyle-BackColor="#A1DCF2" AlternatingRowStyle-BackColor="White" AlternatingRowStyle-ForeColor="#000"
            runat="server" AutoGenerateColumns="false" AllowPaging="true" OnPageIndexChanging="OnPageIndexChanging">
            <Columns>
                <asp:BoundField DataField="ContactName" HeaderText="Contact Name" ItemStyle-Width="150px" />
                <asp:TemplateField HeaderText="City">
                    <ItemTemplate>
                        <asp:HyperLink ID="lnkCity" runat="server" NavigateUrl="#" Text='<%# Eval("City") %>'></asp:HyperLink>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Country">
                    <ItemTemplate>
                        <asp:TextBox ID="txtCountry" runat="server" Text='<%# Eval("Country") %>'></asp:TextBox>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Checkbox">
                    <ItemTemplate>
                        <asp:CheckBox ID="CheckBox1" runat="server" Text="CheckBox Control" />
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="RadioButton">
                    <ItemTemplate>
                        <asp:RadioButton ID="RadioButton1" runat="server" Text="RadioButton Control" />
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
        <br />
        <asp:Button ID="btnExport" runat="server" Text="Export To Excel" OnClick="ExportToExcel" />
    </div>

Default.aspx.cs:-

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        this.BindGrid();
    }
}

private void BindGrid()
{
    string strConnString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
    using (SqlConnection con = new SqlConnection(strConnString))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT * FROM Customers"))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    GridView1.DataSource = dt;
                    GridView1.DataBind();
                }
            }
        }
    }
}

protected void OnPageIndexChanging(object sender, GridViewPageEventArgs e)
{
    GridView1.PageIndex = e.NewPageIndex;
    this.BindGrid();
}

protected void ExportToExcel(object sender, EventArgs e)
{
    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
    Response.Charset = "";
    Response.ContentType = "application/vnd.ms-excel";
    using (StringWriter sw = new StringWriter())
    {
        HtmlTextWriter hw = new HtmlTextWriter(sw);
        //To Export all pages
        GridView1.AllowPaging = false;
        this.BindGrid();
        GridView1.HeaderRow.BackColor = Color.White;
        foreach (TableCell cell in GridView1.HeaderRow.Cells)
        {
            cell.BackColor = GridView1.HeaderStyle.BackColor;
        }
        foreach (GridViewRow row in GridView1.Rows)
        {
            row.BackColor = Color.White;
            foreach (TableCell cell in row.Cells)
            {
                if (row.RowIndex % 2 == 0)
                {
                    cell.BackColor = GridView1.AlternatingRowStyle.BackColor;
                }
                else
                {
                    cell.BackColor = GridView1.RowStyle.BackColor;
                }
                cell.CssClass = "textmode";
                List<Control> controls = new List<Control>();
                //Add controls to be removed to Generic List
                foreach (Control control in cell.Controls)
                {
                    controls.Add(control);
                }
                //Loop through the controls to be removed and replace then with Literal
                foreach (Control control in controls)
                {
                    switch (control.GetType().Name)
                    {
                        case "HyperLink":
                            cell.Controls.Add(new Literal { Text = (control as HyperLink).Text });
                            break;
                        case "TextBox":
                            cell.Controls.Add(new Literal { Text = (control as TextBox).Text });
                            break;
                        case "LinkButton":
                            cell.Controls.Add(new Literal { Text = (control as LinkButton).Text });
                            break;
                        case "CheckBox":
                            cell.Controls.Add(new Literal { Text = (control as CheckBox).Text });
                            break;
                        case "RadioButton":
                            cell.Controls.Add(new Literal { Text = (control as RadioButton).Text });
                            break;
                    }
                    cell.Controls.Remove(control);
                }
            }
        }
        GridView1.RenderControl(hw);
        //style to format numbers to string
        string style = @"<style> .textmode { } </style>";
        Response.Write(style);
        Response.Output.Write(sw.ToString());
        Response.Flush();
        Response.End();
    }
}

public override void VerifyRenderingInServerForm(Control control)
{
    /* Verifies that the control is rendered */
}

Author : Umesh Patel

Related Posts