Thursday, 14 August 2014

Writing Chinese or other extended characters to CSV using C#.NET is garbled when viewed in Microsoft Excel

We've recently had a problem when writing Chinese or other extended characters to CSV using C#.NET is garbled when viewed in Microsoft Excel.

Firstly as you would expect if you use the standard ASCII encoding when using File.WriteAllText() method the Chinese characters are substituted for ????



You can work around this by overloading with the encoding parameter and using UTF8 encoding.
File.WriteAllText(Filename, "Some,Sample,这是一些示例文本 这是一些示例文本 这是一些示例文本, Data", Encoding.UTF8);

NOTE: if you use Unicode encoding the following may happen where Excel does not recognize the commas as delimiters.





If you're trying to write the CSV data to binary you will have another problem whereby the Chinese characters are garbled.




To resolve this issue you should use the GetPreamble() method to write the encoding to the start of the file. The file will now open correctly in Excel using the following sample code.

private void WriteCSV()
{
    byte[] CSVBinaryData = Encoding.UTF8.GetBytes("Some,Sample,这是一些示例文本 这是一些示例文本 这是一些示例文本, Data");
    CSVBinaryData = Encoding.UTF8.GetPreamble().Concat(CSVBinaryData).ToArray();
    File.WriteAllBytes(@"c:\temp\data.csv", CSVBinaryData);
}

For more information about the reporting and CSV capabilities of our product please see the following page.
http://www.centrel-solutions.com/XIAConfiguration/features.aspx?feature=Reporting

No comments:

Post a Comment