Export Data From DataGrid to Excel and Format The Cells
There is an easy way to export your data from a DataGrid to Excel and control the formatting of the data. If you do not provide formatting for Excel you may lose leading zeros or have your numbers converted to scientific notation or have other pieces of data get lost.
The trick is to dynamically add styles, that Excel recognizes, to the Response object. When Excel is opening the HTML from the DataGrid and sees the styles it will set the formatting for you. To see a list of available styles refer to this link
After you call DataBind() on your DataGrid you can call the following function.
private void ExportGrid(string fileName)
{
Response.Clear();
Response.Buffer = true;
Response.Charset = “”;
Response.AddHeader(“content-disposition”, “attachment; filename=” + fileName);
Response.ContentType = “application/vnd.ms-excel”;
StringWriter sw = new System.IO.StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
DataTable dt = (DataTable)DataGrid1.DataSource;
// here, we loop through each row and then each cell and check the data type. We then add the appropriate class
foreach (DataGridItem itm in DataGrid1.Items)
{
for (int i = 0; i < itm.Cells.Count; i++)
{
TableCell tc = itm.Cells[i];
switch (dt.Columns[i].DataType.ToString().ToLower())
{
case “system.string”:
tc.Attributes.Add(“class”, “text”);
break;
case “system.datetime”:
tc.Attributes.Add(“class”, “dt”);
break;
case “system.decimal”:
tc.Attributes.Add(“class”, “num”);
break;
default:
break;
}
}
}
DataGrid1.RenderControl(htw);
StringBuilder sb = new StringBuilder();
sb.Append(@”<style> ”);
sb.Append(@” .text { mso-number-format:\@; } ”);
sb.Append(@” .num { mso-number-format:0\.00; } ”);
sb.Append(@” .dt { mso-number-format: ’Short Date’; } ”);
sb.Append(@”</style>”);
Response.Write(sb.ToString()); // write the styles to the Response
Response.Write(sw.ToString());
Response.Flush();
Response.End();
}
posted in C#, Microsoft .Net, asp.net | 1 Comment
