如何在 PHP 中输​​出 Excel 可以正确读取的 UTF-8 CSV? [英] 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处理这些数据，我没有写任何文件.如果需要，我可以这样做，我只是不这样做，因为此时似乎不需要它.有什么帮助吗?

更新:我尝试将 BOM 回显为 echo pack("CCC", 0xef, 0xbb, 0xbf); 我刚刚从试图检测 BOM 的站点中提取.但是 Excel 只是在导入时将这三个字符附加到第一个单元格中，并且仍然弄乱了特殊字符.

解决方案

引用 a/p><块引用>

Excel for Mac 目前不支持 UTF-8

更新，2017 年:Office 2016 之前的所有 Microsoft Excel for Mac 版本都是如此.较新的版本(来自 Office 365)现在支持 UTF-8.

为了输出 Excel 在 Windows 和 OS X 上都能成功读取的 UTF-8 内容，您需要做两件事:

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

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

2. 确保您添加了 UTF-16LE 字节顺序标记 到文件的开头

   chr(255) .铬(254)

仅在 OS X 上(但不是 Windows)上的 Excel 出现的下一个问题将是在查看带有逗号分隔值的 CSV 文件时，Excel 将仅呈现具有一行和所有文本以及第一行中的逗号.

避免这种情况的方法是使用制表符作为分隔值.

我使用了 PHP 注释中的这个函数(使用标签" " 而不是逗号)，它在 OS X 和 Windows Excel 上运行良好.

请注意，要解决以空列作为行尾的问题，我确实必须更改以下代码行:

 $field_cnt = count($fields);

到

 $field_cnt = count($fields)-1;

正如此页面上的其他一些评论所说，其他电子表格应用程序，例如 OpenOffice Calc、Apple 自己的 Numbers 和 Google Doc 的电子表格，在处理带逗号的 UTF-8 文件时没有问题.

请参阅此问题中的表格，了解哪些对 Excel 中的 Unicode CSV 文件有效，哪些无效

<小时>

作为旁注，我可能要补充一点，如果您使用的是 Composer，您应该看看添加 LeagueCsv 到您的要求.LeagueCsv 有一个非常好的用于构建 CSV 文件的 API.>

要将 LeagueCsv 与这种创建 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

Update, 2017: This is true of all versions of Microsoft Excel for Mac before Office 2016. Newer versions (from Office 365) do now 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 CSV text to UTF-16LE

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

2. Make sure that you add the UTF-16LE byte order mark to the start of the file

   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 " " 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 LeagueCsv to your requires. LeagueCsv has a really nice API for building CSV files.

To use LeagueCsv with this method of creating CSV files, check out this example

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