如何在PHP中输出一个Excel可以正确读取的UTF-8格式的CSV文件？

添加：

fprintf($file, chr(0xEF).chr(0xBB).chr(0xBF));

或者：

fprintf($file, "\xEF\xBB\xBF");

<?php
$file = fopen( "file.csv", "w");
fprintf( $file, "\xEF\xBB\xBF");
fputcsv( $file, ["english", 122, "বাংলা"]);
fclose($file);

使用mb_convert_encoding转换已经是utf-8编码的文本是不必要的。只需在原始内容前添加三个字符即可：

$newContent = chr(239) . chr(187) . chr(191) . $originalContent

对我来说，这解决了csv文件中特殊字符的问题。

由于UTF8编码与Excel不兼容，您可以使用iconv()将数据转换为其他编码类型。

例如：

iconv('UTF-8', 'ISO-8859-1//TRANSLIT', $value),

**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;
}

$csvString = "Möckmühl;in Möckmühl ist die Hölle los\n";
file_put_contents('path/newTest.csv',iconv("UTF-8", "ISO-8859-1//TRANSLIT",$csvString) );

在我的调查中，我发现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

// NO NO NO header('Content-Encoding: Windows-1252');
header('Content-type: text/csv; charset=Windows-1252');
header("Content-Disposition: attachment; filename={$filename}");

private function convertToWindowsCharset($string) {
  $encoding = mb_detect_encoding($string);

  return iconv($encoding, "Windows-1252", $string);
}

对我来说，上面提供的解决方案都没有起作用。以下是我解决这个问题的方法：在PHP代码中使用此函数修改值：

$value = utf8_encode($value);

我在Mac上，我只需要用"sep=;\n"指定分隔符，并将文件编码为UTF-16LE，就像这样：

$data = "sep=;\n" .mb_convert_encoding($data, 'UTF-16LE', 'UTF-8');

在导出文件之前，您可以将这3个字节附加到文件中，这对我很有效。在这样做之前，该系统仅在Windows和HP-UX上运行，但在Linux上失败。

FileOutputStream fStream = new FileOutputStream( f );
final byte[] bom = new byte[] { (byte) 0xEF, (byte) 0xBB, (byte) 0xBF };
OutputStreamWriter writer = new OutputStreamWriter( fStream, "UTF8" );
fStream.write( bom );