我有一个非常简单的东西，它只是输出一些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，但我不知道如何做到这一点。正如你所看到的，我只是回显这些数据，我没有写入任何文件。如果我需要，我可以这样做，我只是没有因为在这一点上似乎不需要这样做。任何帮助吗?
更新:我尝试将BOM作为回显包(“CCC”，0xef, 0xbb, 0xbf);这是我刚刚从一个试图检测BOM的网站上找到的。但Excel只是在导入时将这三个字符附加到第一个单元格，仍然会把特殊字符弄乱。
我用了这个，很管用
header('Content-Description: File Transfer');
header('Content-Type: text/csv; charset=UTF-16LE');
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');
// output headers so that the file is downloaded rather than displayed
// create a file pointer connected to the output stream
$output = fopen('php://output', 'w');
fputs( $output, "\xEF\xBB\xBF" );
// output the column headings
fputcsv($output, array('Thông tin khách hàng đăng ký'));
// fetch the data
$setutf8 = "SET NAMES utf8";
$q = $conn->query($setutf8);
$setutf8c = "SET character_set_results = 'utf8', character_set_client =
'utf8', character_set_connection = 'utf8', character_set_database = 'utf8',
character_set_server = 'utf8'";
$qc = $conn->query($setutf8c);
$setutf9 = "SET CHARACTER SET utf8";
$q1 = $conn->query($setutf9);
$setutf7 = "SET COLLATION_CONNECTION = 'utf8_general_ci'";
$q2 = $conn->query($setutf7);
$sql = "SELECT id, name, email FROM myguests";
$rows = $conn->query($sql);
$arr1= array();
if ($rows->num_rows > 0) {
// output data of each row
while($row = $rows->fetch_assoc()) {
$rcontent = " Name: " . $row["name"]. " - Email: " . $row["email"];
$arr1[]["title"] = $rcontent;
}
} else {
echo "0 results";
}
$conn->close();
// loop over the rows, outputting them
foreach($arr1 as $result1):
fputcsv($output, $result1);
endforeach;
下面是我怎么做的(这是提示浏览器下载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();
唯一的一件事，它修复了UTF8编码问题在CSV预览当你在Mac上按空格键。但不是在Excel Mac 2008…不知道为什么
**This is 100% works fine in excel for both Windows7,8,10 and also All Mac OS.**
//Fix issues in excel that are not displaying characters containing diacritics, cyrillic letters, Greek letter and currency symbols.
function generateCSVFile($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;
}
当我调查时，我发现UTF-8在MAC和Windows上工作得不好，所以我尝试了Windows-1252，它在两者上都支持得很好，但你必须在ubuntu上选择编码类型。
下面是我的代码$valueToWrite = mb_convert_encoding($value， 'Windows-1252');
$response->headers->set('Content-Type', $mime . '; charset=Windows-1252');
$response->headers->set('Pragma', 'public');
$response->headers->set('Content-Endcoding','Windows-1252');
$response->headers->set('Cache-Control', 'maxage=1');
$response->headers->set('Content-Disposition', $dispositionHeader);
echo "\xEF\xBB\xBF"; // UTF-8 BOM
