使用C＃创建的UTF-8 CSV文件显示Excel中的字符

When a CSV file is generated using C# and opened in Microsoft Excel it displays Â characters before special symbols e.g. £

使用C#生成CSV文件并在Microsoft Excel中打开时,它会在特殊符号之前显示字符,例如£

In Notepad++ the hex value for Â is: C2

在Notepad ++中,Â的十六进制值为:C2

So before writing the £ symbol to file, I have tried the following...

所以在将£符号写入文件之前,我已尝试过以下内容......

    var test = "£200.00";
    var replaced = test.Replace("\xC2", " ");

    StreamWriter outputFile = File.CreateText("testoutput.csv"); // default UTF-8
    outputFile.WriteLine(replaced);
    outputFile.Close();

When opening the CSV file in Excel, I still see the "Â" character before the £ symbol (hex equivalent \xC2 \xA3); It made no difference.

在Excel中打开CSV文件时,我仍然在£符号前面看到“Â”字符(十六进制等效\ xC2 \ xA3);它没有任何区别。

Do I need to use a different encoding? or am I missing something?

我需要使用不同的编码吗?还是我错过了什么?

2 个解决方案

#1

I tried your code and Excel does show AŁ in the cell. Then I tried to open the csv with LibreOffice Clac. At first there too was AŁ, but on import the program will ask you about encoding. Once I chose UTF-8 the £ symbol was displayed correctly. My guess is that in fact there is an issue with your encoding.

我尝试了你的代码,Excel确实在单元格中显示了AŁ。然后我尝试用LibreOffice Clac打开csv。起初也有AŁ,但在导入时程序会询问有关编码的信息。一旦我选择了UTF-8,就会正确显示£符号。我的猜测是,实际上你的编码存在问题。

This might help with Excel https://superuser.com/questions/280603/how-to-set-character-encoding-when-opening-excel

这可能有助于Excel https://superuser.com/questions/280603/how-to-set-character-encoding-when-opening-excel

#2

Thank you @Evk and @Mortalier, your suggestions lead me to the right direction...

谢谢@Evk和@Mortalier,你的建议引导我走向正确的方向......

I needed to update my StreamWriter so it would explicitly include UTF-8 BOM at the beginning http://thinkinginsoftware.blogspot.co.uk/2017/12/correctly-generate-csv-that-excel-can.html

我需要更新我的StreamWriter,以便在开头明确包含UTF-8 BOM http://thinkinginsoftware.blogspot.co.uk/2017/12/correctly-generate-csv-that-excel-can.html

So my code has changed from:

所以我的代码改变了:

StreamWriter outputFile = File.CreateText("testoutput.csv"); // default UTF-8

To:

StreamWriter outputFile = new StreamWriter("testoutput.csv", false, new UTF8Encoding(true))

Or: Another solution I found here was to use a different encoding if you're only expecting latin characters... http://theoldsewingfactory.com/2010/12/05/saving-csv-files-in-utf8-creates-a-characters-in-excel/

或者:我在这里找到的另一个解决方案是使用不同的编码,如果你只想要拉丁字符... http://theoldsewingfactory.com/2010/12/05/saving-csv-files-in-utf8-creates- a字符合的excel /

StreamWriter outputFile = new StreamWriter("testoutput.csv", false, Encoding.GetEncoding("Windows-1252"))

My system will most likely use latin & non-latin characters so I'm using the UTF-8 BOM solution.

我的系统很可能会使用拉丁和非拉丁字符,所以我使用的是UTF-8 BOM解决方案。

Final code

    var test = "£200.00";
    StreamWriter outputFile = new StreamWriter("testoutput.csv", false, new UTF8Encoding(true))
    outputFile.WriteLine(test);
    outputFile.Close();

#1