Exporting datagrid to Excel using C#.net

Datagrid is one of the most coolest controls in the Asp.net.

One thing that all developers need is to put the data grid data into excel sheet. In this article I will show you that how you can export your datagrid data to Excel file.

Exporting datagrid to excel might sounds complex but its pretty simple. Let's see how this can be done.

Here is the code:

protected void img_Export_Click(object sender, EventArgs e)
{
// export to excel
DataTable dt = new DataTable();
dt.Columns.Add("a");
dt.Columns.Add("b");
dt.Columns.Add("c");
dt.Rows.Add();
dt.Rows[0]["a"] = "aaaaaaaaaaaaaa";
dt.Rows[0]["b"] = "bbbbbbb";
dt.Rows[0]["c"] = "ccccccccccc";
dt.Rows.Add();
dt.Rows[1]["a"] = "11111111111111111";
dt.Rows[1]["b"] = "222222222222";
dt.Rows[1]["c"] = "333333333333";

DataGrid1.DataSource = dt;

DataGrid1.DataBind();
/**************************************************/
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
this.EnableViewState = false;
Response.Cache.SetExpires(DateTime.Now.AddSeconds(1));
Response.Write("");
Response.Write("\r\n");
Response.Write("");
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
this.ClearControls(DataGrid1);
DataGrid1.RenderControl(oHtmlTextWriter);
Response.AppendHeader("content-disposition", "attachment;filename=DailyApplicationReport_" + DateTime.Now.Year + DateTime.Now.Month + DateTime.Now.Day + "_" + DateTime.Now.Hour + DateTime.Now.Minute + DateTime.Now.Second + ".xls");
Response.Write(oStringWriter.ToString());
Response.End();

}


private void ClearControls(Control control)
{
for (int i = control.Controls.Count - 1; i >= 0; i--)
{
ClearControls(control.Controls[i]);
}
if (!(control is TableCell))
{
if (control.GetType().GetProperty("SelectedItem") != null)
{
LiteralControl literal = new LiteralControl();
control.Parent.Controls.Add(literal);
try
{
literal.Text = (string)control.GetType().GetProperty("SelectedItem").GetValue(control, null);
}
catch
{
}
control.Parent.Controls.Remove(control);
}
else
if (control.GetType().GetProperty("Text") != null)
{
LiteralControl literal = new LiteralControl();
control.Parent.Controls.Add(literal);
literal.Text = (string)control.GetType().GetProperty("Text").GetValue(control, null);
control.Parent.Controls.Remove(control);
}
}
return;
}
}


Download code