是否可以强制 Excel 自动识别 UTF-8 CSV 文件? [英] Is it possible to force Excel recognize UTF-8 CSV files automatically?

问题描述

我正在开发负责将一些数据导出到 CSV 文件的应用程序的一部分.该应用程序始终使用 UTF-8，因为它在所有级别都具有多语言特性.但是在 Excel 中打开此类 CSV 文件(包含例如变音符号、西里尔字母、希腊字母)并没有达到预期的结果，显示类似 Г„/Г¤, Г–/Г¶.而且我不知道如何强制 Excel 理解打开的 CSV 文件是用 UTF-8 编码的.我还尝试指定 UTF-8 BOM EF BB BF，但 Excel 忽略了这一点.

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 相似?

P.S. Which tools may potentially behave like Excel does?

更新

我不得不说，我已经将社区与问题的表述混淆了.当我问这个问题时，我要求一种在 Excel 中以流畅和透明的方式打开 UTF-8 CSV 文件而不会对用户造成任何问题的方法.但是，我使用了错误的公式，要求自动.这非常令人困惑，并且与 VBA 宏自动化相冲突.我最欣赏这个问题的两个答案:Alex https://stackoverflow.com/a/6002338 的第一个答案/166589，我已经接受了这个答案；马克 https://stackoverflow.com/a/6488070/166589 稍后出现的第二个.从可用性的角度来看，Excel 似乎缺乏对用户友好的 UTF-8 CSV 支持，所以我认为两个答案都是正确的，我首先接受了 Alex 的答案，因为它确实说明了Excel 无法透明地做到这一点.这就是我在这里与自动混淆的地方.Mark 的回答为更高级的用户提供了一种更复杂的方法来达到预期的结果.两个答案都很好，但亚历克斯的一个更适合我没有明确指定的问题.

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

在上次编辑五个月后，我注意到亚历克斯的回答由于某种原因消失了.我真的希望这不是技术问题，我希望现在不再讨论哪个答案更好.所以我接受马克的回答是最好的.

Five months later after the last edit, I've noticed that Alex's answer has disappeared for some reason. I really hope it wasn't a technical issue and I hope there is no more discussion on which answer is greater now. So I'm accepting Mark's answer as the best one.

推荐答案

Alex 是正确的，但是由于您必须导出到 csv，因此您可以在打开 csv 文件时向用户提供以下建议:

Alex is correct, but as you have to export to csv, you can give the users this advice when opening the csv files:

1. 将导出的文件另存为 csv
2. 打开 Excel
3. 使用Data导入数据-->导入外部数据-->导入数据
4. 选择csv"文件类型并浏览到您的文件
5. 在导入向导中将 File_Origin 更改为65001 UTF"(或选择正确的语言字符标识符)
6. 将分隔符更改为逗号
7. 选择导入位置并完成

这样特殊字符应该可以正确显示.

This way the special characters should show correctly.

这篇关于是否可以强制 Excel 自动识别 UTF-8 CSV 文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！