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文件....请注意与此主题相关的最后一个函数参数

function array2csv($data, $file = '', $download = true, $mode = 'w+', $delimiter = ',', $enclosure = '"', $escape_char = "\\", $addUnicodeBom = false)
{
    $return = false;

    if ($file == '') {
        $f = fopen('php://memory', 'r+');
    } else {
        $f = fopen($file, $mode);
    }

    if ($addUnicodeBom) {
        $utf8_with_bom = chr(239) . chr(187) . chr(191);
        fwrite($f, $utf8_with_bom);
    }


    foreach ($data as $line => $item) {

        fputcsv($f, $item, $delimiter, $enclosure, $escape_char);
    }

    rewind($f);

    if ($download == true) {
        $return = stream_get_contents($f);
    } else {
        $return = true;
    }

    return $return;
}

其他回答

找到了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。

您可以转换。csv文件到UTF-8与BOM通过notepad++:

在notepad++中打开文件。 进入“编码→转换为UTF-8-BOM”菜单。 进入菜单文件→保存。 关闭记事本+ +。 在Excel中打开文件。

在Microsoft Excel 2013 (15.0.5093.1000) MSO(15.0.5101.1000) 64位中工作,来自Microsoft Office Professional Plus 2013在Windows 8.1上,非unicode程序的区域设置为“德语(德国)”。

这是一个老问题,但我刚刚遇到过类似的问题,解决方案可能会帮助其他人:

同样的问题是,将CSV文本数据写入文件,然后在Excel中打开生成的. CSV,将所有文本转移到单个列中。在阅读了上面的答案后,我尝试了下面的答案,这似乎可以解决问题。

在创建StreamWriter时应用UTF-8编码。就是这样。

例子:

using (StreamWriter output = new StreamWriter(outputFileName, false, Encoding.UTF8, 2 << 22)) {
   /* ... do stuff .... */
   output.Close();
}

是的,这是可能的。正如之前多个用户所指出的,当文件以UTF-8编码时,excel读取正确的字节顺序标记似乎存在问题。对于UTF-16,它似乎没有问题,所以它是UTF-8特有的。我为此使用的解决方案是添加BOM,两次。为此,我执行了两次下面的sed命令:

sed -I '1s/^/\xef\xbb\xbf/' *.csv

,其中通配符可以替换为任何文件名。然而,这会导致.csv文件开头的sep=发生突变。然后,.csv文件将在excel中正常打开,但在第一个单元格中有一个带有“sep=”的额外行。 "sep="也可以在源文件的.csv中删除,但是当用VBA打开文件时,应该指定分隔符:

Workbooks.Open(name, Format:=6, Delimiter:=";", Local:=True)

格式6是.csv格式。将Local设置为true,以防文件中有日期。如果Local未设置为true,日期将被美国化,这在某些情况下会破坏.csv格式。

Excel 2013中忽略BOM的错误似乎已经修复。我有同样的问题与西里尔字母,但添加BOM字符\uFEFF确实有帮助。