PHP导出CSV UTF-8与BOM不起作用 [英] PHP export CSV UTF-8 with BOM doesn't work

问题描述

在Windows Excel上显示乱码文字的中文字符输出UTF-8 CSV时，我已经停留了几天。我正在使用PHP，并已经添加了BOM字节标记，并尝试编码，但根本没有运气。

I have been stuck for days on exporting UTF-8 CSV with chinese characters that shows garbled text on Windows Excel. I am using PHP and have already added the BOM byte mark and tried encoding but no luck at all.

他们在Notepad ++，Google电子表格甚至Mac数字上打开了。但不是客户端要求的Excel。当用记事本++打开时，编码显示为UTF-8。如果我手动将其更改为UTF-8并保存，则该文件在Excel上打开。

They open fine on Notepad++, Google Spreadsheet and even on Mac Numbers. But not on Excel which is a requirement by the client. When opening with Notepad++ the encoding is shown as UTF-8. If I change it to UTF-8 manually and save, the file opens fine on Excel.

似乎BOM字节标记不会保存在输出中因为记事本++总是将其检测为不带BOM的UTF-8。

It seems as though the BOM byte mark doesn't get saved in the output as Notepad++ always detect it as UTF-8 without BOM.

此外，CSV不保存在服务器上。数据从数据库中检索，然后直接导出。

Also, the CSV is not saved on server. Data is retrieved from DB and then exported directly out.

这是我的代码：

// Setup headers
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Content-Description: File Transfer');
header("Content-type: text/csv");
header("Content-disposition: filename=".$filename.".csv");
header("Pragma: no-cache");

// First Method
$fp = fopen('php://output', 'w');
// Add BOM to fix UTF-8 in Excel, but doesn't work
fputs($fp, chr(0xEF) . chr(0xBB) . chr(0xBF) );

if ($fp) {

    fputcsv($fp, array("Header"), ",");
    fputcsv($fp, array($string_with_chinese_chars), ",");
}

fclose($fp);
exit();

// Second Method
$csv = "";
$sep = ",";
$newline = "\n"; // Also tried with PHP_EOL

$csv .= "Header";
$csv .= $newline;
$csv .= $string_with_chinese_chars;
$csv .= $newline;

// Tried all the below ways but doesn't work.
// Method 2.1
print chr(255) . chr(254) . mb_convert_encoding($csv, 'UTF-16LE', 'UTF-8');

// Method 2.2
print chr(239) . chr(187) . chr(191) . $csv;

// Method 2.3
print chr(0xEF).chr(0xBB).chr(0xBF);
print $newline;
print $csv;

推荐答案

根据上面的评论，它看起来像你的脚本在UTF-8 BOM之前不小心打印出一个换行符（十六进制 0A ），导致Excel不能将输出识别为UTF-8。

Based on your comment above, it looks like your script is accidentally printing out a newline (hex 0A) before the UTF-8 BOM, causing Excel not to recognize the output as UTF-8.

由于您使用的是PHP，请确保脚本中的<？php 标记之间没有空行，或任何其他PHP它可能包括的文件。另外，请确保在关闭？> 标记后，如果有的话，您所包含的任何文件都不会有空格

Since you're using PHP, make sure that there's no empty line before the <?php marker in your script, or in any other PHP file that it might include. Also make sure that none of the files you include has any whitespace after the closing ?> marker, if there is one.

在实践中，这可能很难做，因为许多文本编辑者坚持总是在最后一行的末尾添加一个换行符。因此，最安全和最简单的解决方案是简单地省略？ > 标记从您的PHP文件，除非您打印出后面的任何内容。 PHP不需要？> 存在，并且在不意味着混合PHP和文字模板HTML（或其他文本）的文件中使用它只是问对于这样的错误。

In practice, this can be quite hard to do, since many text editors insist on always appending a newline to the end of the last line. Thus, the safest and easiest solution is to simply leave out the ?> marker from your PHP files, unless you intend to print out whatever comes after it. PHP does not require the ?> to be present, and using it in files that are not meant to be mixed PHP and literal template HTML (or other text) is just asking for bugs like this.

这篇关于PHP导出CSV UTF-8与BOM不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！