Tuesday, March 3, 2009

Tip: Export a HTML table or a GridView with unicode characters such as Chinese to Excel

The ability to export data to Excel from a web page is a great ability. However, you might want to cater your web page for multi languages. Usually this is how you can export the data without the language support:

Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("content-disposition", "attachment;filename=" + strFileName);
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
MyCustomControl.RenderControl(htmlWrite); //This is where we call our custom object
//to render its' HTML, it can be an ascx control.
Response.Write(stringWrite.ToString());
Response.End();

The versiont that can export to Chinese zh-CN:

byte[] leader = new byte[2]; //To tell Excel engine to interpret the content correctly.

leader[0] = 0xFF;
leader[1] = 0xFE;
Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("content-disposition",
"attachment;filename=" + strFileName);

Response.ContentEncoding = Encoding.Unicode; //indicates that the content is unicode
Response.BinaryWrite(leader);
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
MyCustomControl.RenderControl(htmlWrite);
Response.Write(style);
Response.Write(stringWrite.ToString());
Response.End();