如何在 PHP 中输出 Excel 可以正确读取的 UTF-8 CSV？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4348802/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I output a UTF-8 CSV in PHP that Excel will read properly?
提问by Ben Saufley
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:
我有一个非常简单的东西，它只是以 CSV 格式输出一些东西，但它必须是 UTF-8。我在 TextEdit 或 TextMate 或 Dreamweaver 中打开这个文件，它正确显示了 UTF-8 字符，但是如果我在 Excel 中打开它，它正在做这个愚蠢的 í？相反的事情。这是我的文档开头的内容：
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.
除了 Excel (Mac, 2008) 不想正确导入之外，这一切似乎都达到了预期的效果。Excel 中没有选项可以让我“以 UTF-8 格式打开”或任何其他选项，所以……我有点恼火。
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?
尽管很多人有同样的问题，但我似乎无法在任何地方找到任何明确的解决方案。我看到最多的是包含 BOM，但我无法完全弄清楚如何做到这一点。正如你在上面看到的，我只是在echo处理这些数据，我没有写任何文件。如果需要，我可以这样做，我只是不这样做，因为此时似乎不需要它。有什么帮助吗？
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.
更新：我尝试回显echo pack("CCC", 0xef, 0xbb, 0xbf);我刚刚从试图检测 BOM 的站点中提取的 BOM。但是 Excel 只是在导入时将这三个字符附加到第一个单元格，并且仍然弄乱了特殊字符。
采纳答案by Tim Groeneveld
To quote a Microsoft support engineer,
Excel for Mac does not currently support UTF-8
Excel for Mac 目前不支持 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.
2017 年更新：适用于Office 2016之前的所有 Microsoft Excel for Mac 版本。较新的版本（来自 Office 365）现在支持 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:
为了输出 Windows 和 OS X 上的 Excel 都能够成功读取的 UTF-8 内容，您需要做两件事：
Make sure that you convert your UTF-8 CSV text to UTF-16LE
mb_convert_encoding($csv, 'UTF-16LE', 'UTF-8');Make sure that you add the UTF-16LE byte order markto the start of the file
chr(255) . chr(254)
确保将 UTF-8 CSV 文本转换为 UTF-16LE
mb_convert_encoding($csv, 'UTF-16LE', 'UTF-8');确保将UTF-16LE 字节顺序标记添加到文件的开头
chr(255) . chr(254)
The next problem that appears only with Excel on OS X (but notWindows) 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.
下一个仅出现在 OS X（而不是Windows）上的 Excel 的问题将是查看带有逗号分隔值的 CSV 文件时，Excel 将仅呈现一行，并且所有文本以及第一行中的逗号。
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.
我从 PHP 注释中使用了这个函数（使用制表符“\t”而不是逗号），它在 OS X 和 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.
正如此页面上的其他一些评论所说，其他电子表格应用程序，如 OpenOffice Calc、Apple 自己的 Numbers 和 Google Doc 的电子表格，对于带逗号的 UTF-8 文件没有问题。
See the table in this questionfor what works and doesn't work for Unicode CSV files in Excel
请参阅此问题中的表格，了解对 Excel 中的 Unicode CSV 文件有效和无效的内容
As a side note, I might add that if you are using Composer, you should have a look at adding League\Csvto your requires. League\Csvhas a really nice API for building CSV files.
作为旁注，我可能会补充说，如果您使用Composer，您应该查看添加League\Csv到您的需求。League\Csv有一个非常好的 API 来构建 CSV 文件。
To use League\Csvwith this method of creating CSV files, check out this example
要使用League\Csv这种创建 CSV 文件的方法，请查看此示例
回答by Daniel Magliola
I have the same (or similar) problem.
我有相同（或类似）的问题。
In my case, if I add a BOM to the output, it works:
就我而言，如果我向输出添加 BOM，它会起作用：
header('Content-Encoding: UTF-8');
header('Content-type: text/csv; charset=UTF-8');
header('Content-Disposition: attachment; filename=Customers_Export.csv');
echo "\xEF\xBB\xBF"; // UTF-8 BOM
I believe this is a pretty ugly hack, but it worked for me, at least for Excel 2007 Windows. Not sure it'll work on Mac.
我相信这是一个非常丑陋的黑客，但它对我有用，至少对 Excel 2007 Windows 是这样。不确定它会在 Mac 上运行。
回答by Jazzer
Here is how I did it (that's to prompt browser to download the csv file):
这是我的做法（即提示浏览器下载 csv 文件）：
header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename=file.csv');
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
echo "\xEF\xBB\xBF"; // UTF-8 BOM
echo $csv_file_content;
exit();
The only thing it fixed UTF8 encoding problem in CSV preview when you hit space bar on Mac.. but not in Excel Mac 2008... don't know why
当您在 Mac 上点击空格键时，它唯一修复了 CSV 预览中的 UTF8 编码问题。但在 Excel Mac 2008 中却没有...不知道为什么
回答by Jasdeep Gosal
I just dealt with the same problem, and came up with two solutions.
我刚刚处理了同样的问题，并提出了两种解决方案。
Use the PHPExcelclass as suggested by bpeterson76.
- Using this class generates the most widely compatible file, I was able to generate a file from UTF-8 encoded data that opened fine in Excel 2008 Mac, Excel 2007 Windows, and Google Docs.
- The biggest problem with using PHPExcel is that it's slow and uses a lot of memory, which isn't an issue for reasonably sized files, but if your Excel/CSV file has hundreds or thousands of rows, this library becomes unusable.
Here is a PHP method that will take some TSV data and output an Excel file to the browser, note that it uses the Excel5 Writer, which means the file should be compatible with older versions of Excel, but I no longer have access to any, so I cannot test them.
function excel_export($tsv_data, $filename) { $export_data = preg_split("/\n/", $tsv_data); foreach($export_data as &$row) { $row = preg_split("/\t/", $row); } include("includes/PHPExcel.php"); include('includes/PHPExcel/Writer/Excel5.php'); $objPHPExcel = new PHPExcel(); $objPHPExcel->setActiveSheetIndex(0); $sheet = $objPHPExcel->getActiveSheet(); $row = '1'; $col = "A"; foreach($export_data as $row_cells) { if(!is_array($row_cells)) { continue; } foreach($row_cells as $cell) { $sheet->setCellValue($col.$row, $cell); $col++; } $row += 1; $col = "A"; } $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="'.$filename.'.xls"'); header('Cache-Control: max-age=0'); $objWriter->save('php://output'); exit; }
Because of the efficiency issues with PHPExcel, I also had to figure out how to generate a UTF-8 & Excel compatible CSV or TSV file.
- The best I could come up with was a file that was compatible with Excel 2008 Mac, and Excel 2007 PC, but not Google Docs, which is good enough for my application.
- I found the solution here, specifically, this answer, but you should also read the accepted answeras it explains the problem.
Here is the PHP code I used, note that I am using tsv data (tabs as delimiters instead of commas):
header ( 'HTTP/1.1 200 OK' ); header ( 'Date: ' . date ( 'D M j G:i:s T Y' ) ); header ( 'Last-Modified: ' . date ( 'D M j G:i:s T Y' ) ); header ( 'Content-Type: application/vnd.ms-excel') ; header ( 'Content-Disposition: attachment;filename=export.csv' ); print chr(255) . chr(254) . mb_convert_encoding($tsv_data, 'UTF-16LE', 'UTF-8'); exit;
按照bpeterson76 的建议使用PHPExcel类。
- 使用此类生成最广泛兼容的文件，我能够从 UTF-8 编码数据生成一个文件，该文件在 Excel 2008 Mac、Excel 2007 Windows 和 Google Docs 中打开良好。
- 使用 PHPExcel 的最大问题是它很慢并且使用大量内存，这对于合理大小的文件来说不是问题，但是如果您的 Excel/CSV 文件有成百上千的行，这个库就变得不可用了。
这是一个 PHP 方法，它将获取一些 TSV 数据并将 Excel 文件输出到浏览器，注意它使用 Excel5 Writer，这意味着该文件应该与旧版本的 Excel 兼容，但我不再访问任何，所以我无法测试它们。
function excel_export($tsv_data, $filename) { $export_data = preg_split("/\n/", $tsv_data); foreach($export_data as &$row) { $row = preg_split("/\t/", $row); } include("includes/PHPExcel.php"); include('includes/PHPExcel/Writer/Excel5.php'); $objPHPExcel = new PHPExcel(); $objPHPExcel->setActiveSheetIndex(0); $sheet = $objPHPExcel->getActiveSheet(); $row = '1'; $col = "A"; foreach($export_data as $row_cells) { if(!is_array($row_cells)) { continue; } foreach($row_cells as $cell) { $sheet->setCellValue($col.$row, $cell); $col++; } $row += 1; $col = "A"; } $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="'.$filename.'.xls"'); header('Cache-Control: max-age=0'); $objWriter->save('php://output'); exit; }
由于 PHPExcel 的效率问题，我还必须弄清楚如何生成与 UTF-8 和 Excel 兼容的 CSV 或 TSV 文件。
- 我能想到的最好的文件是兼容 Excel 2008 Mac 和 Excel 2007 PC，但不兼容 Google Docs，这对我的应用程序来说已经足够了。
- 我在这里找到了解决方案，特别是这个答案，但您也应该阅读已接受的答案，因为它解释了问题。
这是我使用的 PHP 代码，请注意我使用的是 tsv 数据（制表符作为分隔符而不是逗号）：
header ( 'HTTP/1.1 200 OK' ); header ( 'Date: ' . date ( 'D M j G:i:s T Y' ) ); header ( 'Last-Modified: ' . date ( 'D M j G:i:s T Y' ) ); header ( 'Content-Type: application/vnd.ms-excel') ; header ( 'Content-Disposition: attachment;filename=export.csv' ); print chr(255) . chr(254) . mb_convert_encoding($tsv_data, 'UTF-16LE', 'UTF-8'); exit;
回答by Reza Mamun
I was having the same issue and it was solved like below:
我遇到了同样的问题，解决方法如下：
header('Content-Encoding: UTF-8');
header('Content-Type: text/csv; charset=utf-8' );
header(sprintf( 'Content-Disposition: attachment; filename=my-csv-%s.csv', date( 'dmY-His' ) ) );
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
$df = fopen( 'php://output', 'w' );
//This line is important:
fputs( $df, "\xEF\xBB\xBF" ); // UTF-8 BOM !!!!!
foreach ( $rows as $row ) {
fputcsv( $df, $row );
}
fclose($df);
exit();
回答by Leopold Gault
Excel doesn't support UTF-8. You have to encode your UTF-8 text into UCS-2LE.
Excel 不支持 UTF-8。您必须将 UTF-8 文本编码为 UCS-2LE。
mb_convert_encoding($output, 'UCS-2LE', 'UTF-8');
回答by Codemole
In my case following works very nice to make CSV file with UTF-8 chars displayed correctly in Excel.
在我的情况下，以下非常适合制作带有 UTF-8 字符的 CSV 文件在 Excel 中正确显示。
$out = fopen('php://output', 'w');
fprintf($out, chr(0xEF).chr(0xBB).chr(0xBF));
fputcsv($out, $some_csv_strings);
The 0xEF 0xBB 0xBFBOM header will let Excel know the correct encoding.
该0xEF 0xBB 0xBFBOM头将让Excel知道正确的编码。
回答by Ben Saufley
To follow up on this:
跟进此事：
It appears that the problem is simply with Excel on the Mac. It's not how I'm generating the files, because even generating CSVs from Excelis breaking them. I save as CSV, and reimport, and all the characters are messed up.
看来问题仅在于 Mac 上的 Excel。这不是我生成文件的方式，因为即使从 Excel生成 CSV也会破坏它们。我另存为CSV，重新导入，所有字符都乱了。
So … there doesn't appear to be a correct answer to this. Thanks for all the suggestions.
所以……这似乎没有正确的答案。感谢所有的建议。
I would say that from all I've read, @Daniel Magliola's suggestion about the BOM would probably be the best answer for some other computer. But it still doesn't solve my problem.
我会说，从我读过的所有内容来看，@Daniel Magliola 关于 BOM 的建议可能是其他计算机的最佳答案。但它仍然没有解决我的问题。
回答by Murali Krishna
This works fine in excel for both Windows and also Mac OS.
这在 Excel 中适用于 Windows 和 Mac OS。
Fix issues in excel that are not displaying characters containing diacritics, cyrillic letters, greek letter and currency symbols.
修复 excel 中不显示包含变音符号、西里尔字母、希腊字母和货币符号的字符的问题。
function writeCSV($filename, $headings, $data) {
//Use tab as field separator
$newTab = "\t";
$newLine = "\n";
$fputcsv = count($headings) ? '"'. implode('"'.$newTab.'"', $headings).'"'.$newLine : '';
// Loop over the * to export
if (! empty($data)) {
foreach($data as $item) {
$fputcsv .= '"'. implode('"'.$newTab.'"', $item).'"'.$newLine;
}
}
//Convert CSV to UTF-16
$encoded_csv = mb_convert_encoding($fputcsv, 'UTF-16LE', 'UTF-8');
// Output CSV-specific headers
header('Set-Cookie: fileDownload=true; path=/'); //This cookie is needed in order to trigger the success window.
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private",false);
header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"$filename.csv\";" );
header("Content-Transfer-Encoding: binary");
header('Content-Length: '. strlen($encoded_csv));
echo chr(255) . chr(254) . $encoded_csv; //php array convert to csv/excel
exit;
}
回答by Cybot
The CSV File musst include a Byte Order Mark.
CSV 文件必须包含字节顺序标记。
Or as suggested and workaround just echo it with the HTTP body
或者按照建议和解决方法只是用 HTTP 正文回显它
