What character set do I need for the correct encoding of the Japanese language in CSV, which I need to open in Excel?

Question

My asp.net application expects to export the dataset as a CSV file for opening in Excel. In addition to English characters, the data set contains Japanese double-width kanji characters.

I tried several different combinations of headers, character sets and content encoding, but I could not find the right combination that allows Excel to open the file and correctly display Japanese characters.

If, however, I open the file in Notepad and then save it using UTF-8 encoding, I can open the file in Excel and see the Japanese characters as intended. Therefore, this should be possible, but I can’t find the right combination of headers to make it work without opening and saving the exported file in Notepad.

Private Sub TestCSV() Dim context As HttpContext = HttpContext.Current context.Response.Clear() context.Response.ClearHeaders() context.Response.ClearContent() context.Response.Cache.SetCacheability(HttpCacheability.NoCache) context.Response.AddHeader("Content-Disposition", "attachment; filename=test.csv") context.Response.ContentType = "text/csv" context.Response.Charset = Encoding.UTF8.WebName context.Response.Write("English,Japanese") context.Response.Write(Environment.NewLine) context.Response.Write("Test,日本語") context.Response.End() End Sub 

Any help getting this code to work would be greatly appreciated.

I also tried using the following lines of code in various combinations and orders, but nothing worked.

  context.Response.BinaryWrite(Encoding.UTF8.GetPreamble()) context.Response.BinaryWrite(Encoding.GetEncoding("utf-16le").GetPreamble()) context.Response.Charset = Encoding.GetEncoding("utf-16le").WebName context.Response.AddHeader("Content-Type", "text/csv; charset=utf-16") context.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250") 

Thanks!

Answer 1

Got! I added several other languages ​​and characters to do this doubly.

The key was to set the encoding in the header, the encoding of the content and the preamble for UTF-8.

 Private Sub TestCSV() Dim context As HttpContext = HttpContext.Current context.Response.Clear() context.Response.ClearHeaders() context.Response.ClearContent() context.Response.Cache.SetCacheability(HttpCacheability.NoCache) context.Response.ContentType = "text/csv" context.Response.AddHeader("Content-Disposition", "attachment; filename=test.csv") context.Response.Charset = Encoding.UTF8.WebName context.Response.ContentEncoding = Encoding.UTF8 context.Response.BinaryWrite(Encoding.UTF8.GetPreamble) context.Response.Write("Language,Sample") context.Response.Write(Environment.NewLine) context.Response.Write("Symbol,™£©€®") context.Response.Write(Environment.NewLine) context.Response.Write("Japanese,日本語") context.Response.Write(Environment.NewLine) context.Response.Write("Chinese Simplified,中文(简体)") context.Response.Write(Environment.NewLine) context.Response.Write("Spanish,Español") context.Response.Write(Environment.NewLine) context.Response.Write("Chinese Traditional,中文(繁體)") context.Response.Write(Environment.NewLine) context.Response.Write("Korean,한국어") context.Response.End() End Sub 

Thanks!

Answer 2

 /// <summary> /// method to export report into excel /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void linkProcess_Click(object sender, EventArgs e) { Response.ClearContent(); Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.xls", "YTDReviewsData")); Response.ContentType = "application/ms-excel"; Response.ContentEncoding = System.Text.Encoding.Unicode; Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble()); System.IO.StringWriter stringWrite = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite); gvDealDetails.RenderControl(htmlWrite); Response.Write(stringWrite.ToString()); Response.End(); }