Wednesday, December 10, 2008

Exporting datagrid to Excel using C#.net

2 comments
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

2 comments:

vivekthangaswamy said...

Copied this content from my blog

Narender said...

May be!!

I have developed this blog to keep all my knowledge at one place so, I have not to search on google...

You have to be happy.. as you content in copied by others..

:)