是否可以强制Excel自动识别UTF-8 CSV文件?

I'm developing a part of an application that's responsible for exporting some data into CSV files. The application always uses UTF-8 because of its multilingual nature at all levels. But opening such CSV files (containing e.g. diacritics, cyrillic letters, Greek letters) in Excel does not achieve the expected results showing something like Г„/Г¤, Г–/Г¶. And I don't know how to force Excel understand that the open CSV file is encoded in UTF-8. I also tried specifying UTF-8 BOM EF BB BF, but Excel ignores that.

有什么解决办法吗?

附注:哪些工具可能像Excel一样?

更新

I have to say that I've confused the community with the formulation of the question. When I was asking this question, I asked for a way of opening a UTF-8 CSV file in Excel without any problems for a user, in a fluent and transparent way. However, I used a wrong formulation asking for doing it automatically. That is very confusing and it clashes with VBA macro automation. There are two answers for this questions that I appreciate the most: the very first answer by Alex https://stackoverflow.com/a/6002338/166589, and I've accepted this answer; and the second one by Mark https://stackoverflow.com/a/6488070/166589 that have appeared a little later. From the usability point of view, Excel seemed to have lack of a good user-friendly UTF-8 CSV support, so I consider both answers are correct, and I have accepted Alex's answer first because it really stated that Excel was not able to do that transparently. That is what I confused with automatically here. Mark's answer promotes a more complicated way for more advanced users to achieve the expected result. Both answers are great, but Alex's one fits my not clearly specified question a little better.

更新2

在最后一次编辑5个月后，我注意到Alex的答案不知为何消失了。我真的希望这不是一个技术问题，我希望现在不再有关于哪个答案更好的讨论。所以我认为马克的答案是最好的。

当前回答

Alex是正确的，但是由于你必须导出到csv，你可以在打开csv文件时给用户这样的建议:

另存为csv格式打开Excel 使用“data”导入数据——>导入外部数据——>导入数据选择文件类型“csv”并浏览到您的文件在导入向导中将File_Origin更改为“65001 UTF”(或选择正确的语言字符标识符) 将分隔符更改为逗号选择要导入的位置并完成

这样特殊字符才能正确显示。

2011-06-27 02:19:21

其他回答

找到了ASP的解决方案。使用POM下载UTF8格式的CSV文件:

byte[] csvBytes = Encoding.Default.GetBytes(csvString);
UTF8Encoding utf8 = new UTF8Encoding(true);
byte[] bom = utf8.GetPreamble();
var result = bom.Concat(csvBytes).ToArray();
return new FileContentResult(result, MediaTypeHeaderValue.Parse("text/csv; charset=utf-8"));

Excel是识别下载的CSV文件而不是UTF8。

2022-08-23 12:47:10

现在是2022年3月，似乎我们不能同时使用BOM和sep=…线。添加sep=\t或类似的，使Excel忽略BOM。

使用分号似乎是Excel的默认理解，在这种情况下，我们可以跳过sep=…这样就行了。

这是微软365与Excel版本2110构建14527.20276。

2022-03-09 13:27:43

我过去也遇到过同样的问题(如何生成Excel可以读取的文件，以及其他工具也可以读取的文件)。我使用的是TSV而不是CSV，但同样的编码问题出现了。

我没能找到任何方法让Excel自动识别UTF-8，我也不愿意/不能给文件的使用者复杂的如何打开它们的指令。所以我将它们编码为UTF-16le(带有BOM)而不是UTF-8。大小是原来的两倍，但Excel可以识别编码。而且它们的压缩性很好，所以尺寸很少(但遗憾的是并非永远)重要。

2013-07-09 09:18:26

我们使用了以下方法:

转换CSV到UTF-16 LE 在文件开头插入BOM 使用制表符作为字段分隔符

2012-01-26 10:11:43