如何在PHP中输出UTF-8 CSV，Excel将正确读取？ [英] How can I output a UTF-8 CSV in PHP that Excel will read properly?

问题描述

我有一个非常简单的东西，只是输出一些CSV格式的东西，但它必须是UTF-8。我在TextEdit或TextMate或Dreamweaver中打开此文件，并且它正确显示UTF-8字符，但是如果我在Excel中打开它，它会做这个蠢事。这是我在我的文档头：

  header（content-type：application / csv; charset = UTF-8）; 
 header（Content-Disposition：attachment; filename = \CHS.csv\）; 
  

这一切似乎都有效果，除了Excel（Mac，2008）它正确。在Excel中没有选项可以打开为UTF-8或任何东西，所以...我有点恼火。

我似乎找不到任何明确的解决方案，任何地方，尽管很多人有同样的问题。我最看的东西是包括BOM，但我不能确切地找出如何做到这一点。正如你可以看到，我只是 echo 这个数据，我不写任何文件。我可以这样做，如果我需要，我只是不是因为在这一点上似乎不需要它。

更新：我尝试以 echo pack（CCC，0xef，0xbb，0xbf） $ c>，我刚从一个试图检测BOM的网站。但是Excel只是在导入时将这三个字符附加到第一个单元格，并仍然会混淆特殊字符。

解决方案

quote Microsoft支持工程师，

Excel for Mac目前不支持UTF-8

为了输出UTF-8内容，Excel在Windows和OS X上都能够要成功阅读，您需要执行以下两项操作：

1. 确保将UTF-8文本转换为UTF- 16LE

     mb_convert_encoding（$ csv，'UTF-16LE'，'UTF-8'）; 
     




2. 请确保您添加了UTF-16LE字节顺序标记

     chr（255）。 chr（254）
     

只有在OS X（但是而不是 Windows）上使用Excel查看CSV文件时，将使用逗号分隔的值，Excel将仅渲染一行，所有文本和逗号分隔，

使用

避免这种情况的方法是使用制表符作为您的分隔值。 href =http://li1.php.net/manual/en/function.fputcsv.php#84783>此函数从PHP注释（使用选项卡\t而不是逗号）和它在OS X和Windows Excel上完美工作。

请注意，要解决一个空列的问题，作为行的结尾，我必须更改行代码：

  $ field_cnt = count（$ fields）; 
  

到

  $ field_cnt = count（$ fields）-1; 
  

像这个网页上的其他一些评论说，其他电子表格应用程序，如OpenOffice Calc，Apple自己的数字和

请参阅此问题中的表格对于Excel中的Unicode CSV文件是否有效和不起作用

---

另外，如果您使用 Composer ，您应该查看添加 League\Csv 到你的要求。 League\Csv 有一个非常不错的API用于构建CSV文件。

要使用 League\Csv 使用此创建CSV文件的方法，请查看此示例

I've got this very simple thing that just outputs some stuff in CSV format, but it's got to be UTF-8. I open this file in TextEdit or TextMate or Dreamweaver and it displays UTF-8 characters properly, but if I open it in Excel it's doing this silly íÄ kind of thing instead. Here's what I've got at the head of my document:

header("content-type:application/csv;charset=UTF-8");
header("Content-Disposition:attachment;filename=\"CHS.csv\"");

This all seems to have the desired effect except Excel (Mac, 2008) doesn't want to import it properly. There's no options in Excel for me to "open as UTF-8" or anything, so … I'm getting a little annoyed.

I can't seem to find any clear solutions to this anywhere, despite a lot of people having the same problem. The thing I see the most is to include the BOM, but I can't exactly figure out how to do that. As you can see above I'm just echoing this data, I'm not writing any file. I can do that if I need to, I'm just not because there doesn't seem like a need for it at this point. Any help?

Update: I tried echoing the BOM as echo pack("CCC", 0xef, 0xbb, 0xbf); which I just pulled from a site that was trying to detect the BOM. But Excel just appends those three characters to the very first cell when it imports, and still messes up the special characters.

解决方案

To quote a Microsoft support engineer,

Excel for Mac does not currently support UTF-8

In order to output UTF-8 content that Excel both on Windows and OS X will be able to successfully read, you will need to do two things:

1. Make sure that you convert your UTF-8 text to UTF-16LE

   mb_convert_encoding($csv, 'UTF-16LE', 'UTF-8');
   

2. Make sure that you add the UTF-16LE byte order mark

   chr(255) . chr(254)
   

The next problem that appears only with Excel on OS X (but not Windows) will be when viewing a CSV file with comma separated values, Excel will render rows only with one row and all of the text along with the commas in the first row.

The way to avoid this is to use tabs as your separated value.

I used this function from the PHP comments (using tabs "\t" instead of commas) and it worked perfectly on OS X and Windows Excel.

Note that to fix an issue with an empty column as the end of a row, that I did have to change the line of code that says:

    $field_cnt = count($fields);

to

    $field_cnt = count($fields)-1;

As some of the other comments on this page say, other spreadsheet apps like OpenOffice Calc, Apple's own Numbers and Google Doc's Spreadsheet have no issues with UTF-8 files with commas.

See the table in this question for what works and doesn't work for Unicode CSV files in Excel

---

As a side note, I might add that if you are using Composer, you should have a look at adding League\Csv to your requires. League\Csv has a really nice API for building CSV files.

To use League\Csv with this method of creating CSV files, check out this example

这篇关于如何在PHP中输出UTF-8 CSV，Excel将正确读取？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！