Export GridView data with TemplateField Column to Excel using C# in ASP.Net

Last Reply 9 days ago By ismail

Posted 12 days ago

i am exporting gridview data to excel i am not able to export and i am not receiving any error

                                        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BorderStyle="None" AllowSorting="true" CellPadding="0" CssClass="table" GridLines="Both" Width="100%" OnRowCommand="GridView1_RowCommand">
                                        <AlternatingRowStyle CssClass="odd" />
                                        <Columns>
                                            <asp:BoundField DataField="Department" HeaderText="مدیریت عمومی" SortExpression="Department" />
                                            <asp:BoundField DataField="GDepName" HeaderText="امریت" SortExpression="GDepName" />
                                            <asp:BoundField DataField="DName" HeaderText="ریاست" SortExpression="DName" />
                                            <asp:BoundField DataField="GDirectorateName" HeaderText="ریاست عمومی" SortExpression="GDirectorateName" />
                                            <asp:BoundField DataField="Moeenyat" HeaderText="معنیت" SortExpression="Moeenyat" />
                                            <asp:BoundField DataField="Comments" HeaderText="ملاحظات" SortExpression="Comments" />
                                            <asp:TemplateField HeaderText="">
                                                <ItemTemplate>
                                                    <asp:LinkButton ID="lbEdit" Style="text-decoration: none;" runat="server" CommandName="lbEdit" CommandArgument='<%# Eval("Id") %>'>
                                                        <asp:Image ID="imgEdit" runat="server" ImageUrl="~/images/edit.gif" ImageAlign="AbsMiddle" />
                                                        تغیر </asp:LinkButton>
                                                    |
                                                    <asp:LinkButton ID="lbDelete" Style="text-decoration: none;" runat="server" CommandName="lbDelete" CommandArgument='<%# Eval("Id") %>' OnClientClick='if(!confirm("ایا میخواهد این ریکارد را حذف کند؟")){ return false; };'>
                                                        <asp:Image ID="ImgDelete" runat="server" ImageUrl="~/images/del.gif" ImageAlign="AbsMiddle" />
                                                        حذف </asp:LinkButton>
                                                </ItemTemplate>
                                                <ItemStyle Width="110px" />
                                            </asp:TemplateField>
                                        </Columns>
                                        <PagerStyle CssClass="pagging" />
                                    </asp:GridView>

 

 private void ExportGridToExcel()
    {
        //Response.ClearContent();
        //Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
        //Response.ContentType = "application/vnd.ms-excel";
        //System.IO.StringWriter stringWriter = new StringWriter();
        //HtmlTextWriter htmlTextWriter = new HtmlTextWriter(stringWriter);
        //GridView1.RenderControl(htmlTextWriter);
        //Response.Write(stringWriter.ToString());
        //Response.End();


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

            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";
                }
            }

            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)
    {
        /* Confirms that an HtmlForm control is rendered for the specified ASP.NET
           server control at run time. */
    }

 

<%@ Page Title="" Language="C#" MasterPageFile="~/Forms/Main.master" AutoEventWireup="true" EnableEventValidation ="false"  CodeFile="DepartmentForm.aspx.cs" Inherits="Forms_DepartmentForm" %>

please help me

Posted 12 days ago

Hi ismail,

I have modified your code and test on my side it is working. Please compare modified code with your code.Below is the modified code with working sample.

HTML

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BorderStyle="None"
    AllowSorting="true" CellPadding="0" CssClass="table" GridLines="Both" Width="100%">
    <AlternatingRowStyle CssClass="odd" />
    <Columns>
        <asp:BoundField DataField="Department" HeaderText="مدیریت عمومی" SortExpression="Department" />
        <asp:BoundField DataField="GDepName" HeaderText="امریت" SortExpression="GDepName" />
        <asp:BoundField DataField="DName" HeaderText="ریاست" SortExpression="DName" />
        <asp:BoundField DataField="GDirectorateName" HeaderText="ریاست عمومی" SortExpression="GDirectorateName" />
        <asp:BoundField DataField="Moeenyat" HeaderText="معنیت" SortExpression="Moeenyat" />
        <asp:BoundField DataField="Comments" HeaderText="ملاحظات" SortExpression="Comments" />
        <asp:TemplateField HeaderText="">
            <ItemTemplate>
                <asp:LinkButton ID="lbEdit" Style="text-decoration: none;" runat="server" CommandName="lbEdit"
                    CommandArgument='<%# Eval("Id") %>'>
                    <asp:Image ID="imgEdit" runat="server" ImageUrl="~/Photo/Chrysanthemum.jpg" Width="25"
                        Height="25" ImageAlign="AbsMiddle" />
                    تغیر
                </asp:LinkButton>
                <asp:LinkButton ID="lbDelete" Style="text-decoration: none;" runat="server" CommandName="lbDelete"
                    CommandArgument='<%# Eval("Id") %>' OnClientClick='if(!confirm("ایا میخواهد این ریکارد را حذف کند؟")){ return false; };'>
                    <asp:Image ID="ImgDelete" runat="server" ImageUrl="~/Photo/Jellyfish.jpg" Width="25"
                        Height="25" ImageAlign="AbsMiddle" />
                    حذف
                </asp:LinkButton>
            </ItemTemplate>
            <ItemStyle Width="110px" />
        </asp:TemplateField>
    </Columns>
    <PagerStyle CssClass="pagging" />
</asp:GridView>
<asp:Button ID="btnExport" runat="server" Text="Export To Excel" OnClick="ExportToExcel" />

C#

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

private void BindGrid()
{
    DataTable dt = new DataTable();
    dt.Columns.Add("Id", typeof(int));
    dt.Columns.Add("Department", typeof(string));
    dt.Columns.Add("GDepName", typeof(string));
    dt.Columns.Add("DName", typeof(string));
    dt.Columns.Add("GDirectorateName", typeof(string));
    dt.Columns.Add("Moeenyat", typeof(string));
    dt.Columns.Add("Comments", typeof(string));
    dt.Rows.Add(1, "Electronics", "Engg", "Fazal", "2", "Good","No Comments");
    dt.Rows.Add(2, "Biomedical", "Medical", "Fazal", "2", "Good", "No Comments");
    dt.Rows.Add(3, "Architecture", "Architecture", "Fazal", "2", "Good","No Comments");
    dt.Rows.Add(4, "Mechanical", "Engg", "Fazal", "2", "Good", "No Comments");
    dt.Rows.Add(5, "Computer", "Engg", "Fazal", "2", "Good", "No Comments");
    dt.Rows.Add(6, "MBBS", "Medical", "Fazal", "2", "Good", "No Comments");
    GridView1.DataSource = dt;
    GridView1.DataBind();
}

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 */
}

Screenshot


Posted 9 days ago

Dear Zameer,

 

thanks it is working now the issue was that i used update panel once i take out the button from panel then it worked. 

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html