Export as it is Gridview to Excel in asp.net c#

Question

User2003675111 posted

Hi All,

I have a gridview with different background color of cells based on the conditions.

Now just I want to export data and its cells color to excel means if in gridview any column is red then it should be red in excel cell also.

Please help out on this.

Thanks in advance.

Answer 1

User665608656 posted

Hi Neeraj,

According to your description, you could export the current GridView to excel in the code behind by using RenderControl and HtmlTextWriter method .

For more details, you could refer to the following code:

     <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
            DataKeyNames="RowNumber" OnRowDataBound="GridView1_RowDataBound"  >
            <Columns>
                <asp:BoundField DataField="RowNumber" HeaderText="RowNumber" ReadOnly="True" SortExpression="RowNumber" />
                <asp:BoundField DataField="FruitName" HeaderText="FruitName" SortExpression="FruitName" />
                <asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" SortExpression="UnitPrice" />
                <asp:BoundField DataField="Quantity" HeaderText="Quantity" SortExpression="Quantity" />
            </Columns>
        </asp:GridView>
        <asp:Button ID="Button1" runat="server" Text="Export" OnClick="Button1_Click" />

code behind:

protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindData();
            }
        }
        protected void BindData()
        {
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            string strSql = "select * from Fruits";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(strSql, connection))
                {
                    try
                    {
                        connection.Open();
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        DataSet ds = new DataSet();
                        da.Fill(ds);
                        GridView1.DataSource = ds.Tables[0];
                        GridView1.DataBind();
                    }
                    catch (SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
        }
        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                e.Row.Cells[0].BackColor = Color.AliceBlue;
                e.Row.Cells[1].BackColor = Color.Chocolate;
                e.Row.Cells[2].BackColor = Color.IndianRed;
                e.Row.Cells[3].BackColor = Color.Turquoise;
            }
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
             ExportToExcel();
        }
        protected void ExportToExcel()
        {
            GridView1.AllowPaging = false;
            BindData();

            HttpContext.Current.Response.Charset = "UTF-8";
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
            HttpContext.Current.Response.ContentType = "application/ms-excel";
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + "" + HttpUtility.UrlEncode("GridView_Excel.xls", System.Text.Encoding.UTF8));
            GridView1.Page.EnableViewState = false;

            StringWriter tw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(tw);
            GridView1.RenderControl(hw);
            HttpContext.Current.Response.Write(tw.ToString());
            HttpContext.Current.Response.End();
        }
        public override void VerifyRenderingInServerForm(Control control)
        {
            /* Confirms that an HtmlForm control is rendered for */
        }

The result of my work demo: 
 

 
Best Regards,

YongQing.