Code for Data Export from GridView to Excel in Asp.Net- c#

In this blog, I am going to show how we could export our data from a gridview to excel sheet in Asp.net- c# (Export to excel data)

  • First of all, just take a gridview on your design page and fill data as below ::

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

    protected void BindGrid()
    {
        gvDetails.DataSource = this.getData();
        gvDetails.DataBind();
    }

    private DataTable getData()
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("UserId", typeof(Int32));
        dt.Columns.Add("Name", typeof(string));
        dt.Columns.Add("Designation", typeof(string));
        dt.Columns.Add("Department", typeof(string));

        DataRow dr = dt.NewRow();
        dr["UserId"] = 101;
        dr["Name"] = "Zaheer";
        dr["Designation"] = "Associate Technical Consultant";
        dr["Department"] = null;
        dt.Rows.Add(dr);

        dr = dt.NewRow();
        dr["UserId"] = 12;
        dr["Name"] = "Anish";
        dr["Designation"] = null;
        dr["Department"] = null;
        dt.Rows.Add(dr);

        dr = dt.NewRow();
        dr["UserId"] = 113;
        dr["Name"] = "Manish";
        dr["Designation"] = null;
        dr["Department"] = "Navision";
        dt.Rows.Add(dr);

        dr = dt.NewRow();
        dr["UserId"] = 4;
        dr["Name"] = "Ravish";
        dr["Designation"] = "Tech Lead";
        dr["Department"] = "CRM";
        dt.Rows.Add(dr);

        for (int i = 0; i < dt.Rows.Count; i++)
        {
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                if (string.IsNullOrEmpty(dt.Rows[i][j].ToString()))
                {
                    dt.Rows[i][j] = "Data Not Available";
                }
            }
        }
        return dt;
    }

  • After filling data to the GridView Write the code for export the data to excel in c# on button click. Take a button on design page and write the below code on button click event ::

   protected void btnExcel_Click(object sender, EventArgs e)
    {
        Response.ClearContent();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Details.xls"));
        Response.ContentType = "application/ms-excel";
        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);

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

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

Comments

Popular posts from this blog

StateCode and StatusCode Values for mostly used entities in Microsoft Dynamics CRM 2013

How to import CSV files into DataTable in C#

Search data in Gridview on Textbox Key press event using JQuery in Asp.Net- C#

go to top image