我已经卡了几天,尝试用PHP导出UTF-8 CSV文件,并包含中文字符,在Windows Excel上显示的是乱码。我已经添加了BOM字节标记并尝试编码,但结果都不理想。
这些CSV文件在Notepad++、Google Spreadsheet和Mac Numbers上打开没有问题,但客户要求在Excel上打开。使用Notepad++打开时,编码显示为UTF-8。如果我手动更改为UTF-8并保存,文件就可以在Excel上正常打开。
看起来BOM字节标记没有被保存到输出中,因为Notepad++总是检测到UTF-8而没有BOM。
此外,CSV文件没有保存在服务器上。数据从数据库中检索,然后直接导出。
以下是我的代码:
// 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;