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

How to show enlarge image when mouse hover on image or link in Asp.Net(c#) using JavaScript

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

How to add custom filter lookup field based on OptionSet field selection in Dynamics CRM.

go to top image