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的答案不知为何消失了。我真的希望这不是一个技术问题，我希望现在不再有关于哪个答案更好的讨论。所以我认为马克的答案是最好的。
这是一个老问题，但我刚刚遇到过类似的问题，解决方案可能会帮助其他人:
同样的问题是，将CSV文本数据写入文件，然后在Excel中打开生成的. CSV，将所有文本转移到单个列中。在阅读了上面的答案后，我尝试了下面的答案，这似乎可以解决问题。
在创建StreamWriter时应用UTF-8编码。就是这样。
例子:
using (StreamWriter output = new StreamWriter(outputFileName, false, Encoding.UTF8, 2 << 22)) {
/* ... do stuff .... */
output.Close();
}
只是为了帮助有兴趣在Excel上打开文件实现这个线程的用户。
我使用了下面的向导，它对我来说工作得很好，导入了一个UTF-8文件。
不是透明的，但如果您已经有了该文件，则非常有用。
Open Microsoft Excel 2007.
Click on the Data menu bar option.
Click on the From Text icon.
Navigate to the location of the file that you want to import. Click on the filename and then click on the Import button. The Text Import Wizard - Step 1 or 3 window will now appear on the screen.
Choose the file type that best describes your data - Delimited or Fixed Width.
Choose 65001: Unicode (UTF-8) from the drop-down list that appears next to File origin.
Click on the Next button to display the Text Import Wizard - Step 2 or 3 window.
Place a checkmark next to the delimiter that was used in the file you wish to import into Microsoft Excel 2007. The Data preview window will show you how your data will appear based on the delimiter that you chose.
Click on the Next button to display the Text Import Wizard - Step 3 of 3.
Choose the appropriate data format for each column of data that you want to import. You also have the option to not import one or more columns of data if you want.
Click on the Finish button to finish importing your data into Microsoft Excel 2007.
来源:https://www.itg.ias.edu/content/how-import-csv-file-uses-utf-8-character-encoding-0
简单的vba宏用于打开utf-8文本和csv文件
Sub OpenTextFile()
filetoopen = Application.GetOpenFilename("Text Files (*.txt;*.csv), *.txt;*.csv")
If filetoopen = Null Or filetoopen = Empty Then Exit Sub
Workbooks.OpenText Filename:=filetoopen, _
Origin:=65001, DataType:=xlDelimited, Comma:=True
End Sub
原点:=65001为UTF-8。
逗号:对于按列分布的.csv文件为True
保存在个人。XLSB使它始终可用。
个性化excel工具栏添加一个宏调用按钮，并从那里打开文件。
您可以添加更多的格式到宏，如列自动拟合，对齐等。
