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)
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;
}
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 */
}
- 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
Post a Comment