Datagrid to Excel Formatting Tip
When I signed up for a blog account here, I thought all my entries would be about Datagrids. But I've been neglecting what is really my favorite topic, so tonight I'll share a fantastic tip that came to me today from a visitor to my site, Jim Cristofono.
I've been doing a lot recently with Datagrids and Excel, and Jim and I have corresponded on this topic a bit. One limitation (or so I thought), of using the RenderControl method to export a Datagrid to Excel is that you have little or no control over the formatting once the data gets to Excel. For instance, large numbers get converted to the (ugly) x.xxxxxxE-yy syntax.
Jim came up with an super clever workaround for this problem--code below:
Response.Cache.SetExpires(DateTime.Now.AddSeconds(1));
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
this.EnableViewState = false;
Response.Write("<html
xmlns:x=\"urn:schemas-microsoft-com:office:excel\">");
Response.Write("\r\n");
Response.Write("<style> .mystyle1 " + "\r\n" + "
{mso-style-parent:style0;mso-number-format:\""+@"\@"+"\""+";} " +
"\r\n" +
"</style>");
StringWriter tw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
DataGrid1.RenderControl(hw);
Response.AppendHeader
("content-disposition","attachment;filename=x.xls");
Response.Write(tw.ToString());
Response.End();
private void DataGrid1_ItemCreated(object sender,
System.Web.UI.WebControls.DataGridItemEventArgs e)
{
int i=0;
if (e.Item.ItemType == ListItemType.Item | e.Item.ItemType ==
ListItemType.AlternatingItem)
{
foreach (cRecordTemplate MyRec in arrRec)
{
if (MyRec.FieldType=="dbChar")
e.Item.Cells[i].Attributes.Add("class", "mystyle1");
i++;
}
}
}
Happy Datagridding!