On the whole encoding and localization has mostly escaped my attention because the company I used to work for had their own team to handle just these cases. This suited me fine as I find these types of issues niggly and annoying. However, I couldn’t duck this one so had to dig a bit deeper.
The first thing I did was to isolate a character, in this case: é. When using System.Text.Encoding.UTF8 the output I saw é represented by 4 bytes: C3 A9. This was causing me trouble because when the resulting excel file was opened é turned in to é (garbage).
I was able to tell how excel expected to see é by creating a new spreadsheet, filling in a cell, saving it, and looking in the hex editor. Excel was expecting é to be represented in 2 bytes: E9. I quick search on told me this was ISO 8859-1 encoding. While this encoding is not in the System.Text.Encoding enumerator you can obtain it with GetEncoding(). For whatever about my stylesheet and output being in ISO 8859-1 what really made the difference was setting response.ContentEncoding. Once set correctly my fadás displayed correctly and another frustrating chapter has come to a close.
Below is my code for transforming a DataSet to a tab delimited excel file.
// Appending Headers
response.Clear();
response.Buffer= true;
response.ContentEncoding = System.Text.Encoding.GetEncoding("iso-8859-1");
response.ContentType = "application/vnd.ms-excel";
// XSLT to use for transforming this dataset.
MemoryStream stream = new MemoryStream( );
XmlTextWriter writer = new XmlTextWriter(stream, System.Text.Encoding.GetEncoding("iso-8859-1"));
CreateStylesheet(writer, sHeaders, sFileds, FormatType, sSort);
writer.Flush( );
stream.Seek( 0, SeekOrigin.Begin);
XmlDataDocument xmlDoc = new XmlDataDocument(dsExport);
XslTransform xslTran = new XslTransform();
xslTran.Load(new XmlTextReader(stream), null, null);
MemoryStream output = new MemoryStream();
xslTran.Transform(xmlDoc, null, output, null);
output.Position = 0;
StreamReader sr = new StreamReader(output, System.Text.Encoding.GetEncoding("iso-8859-1"));
//Writeout the Content
response.Write(sr.ReadToEnd());
sr.Close();
output.Close();
writer.Close();
stream.Close();
response.End();