我如何在PHP中输出一个UTF-8 CSV, Excel将会正确读取?

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，但我不知道该怎么做。正如你看到的，我只是重复这个数据，我没有写任何文件。如果我需要的话，我可以这么做，我只是不是因为在这一点上似乎不需要它。任何帮助吗?

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.

更新:我尝试响应BOM作为echo pack(“CCC”，0xef, 0xbb, 0xbf);我刚从一个试图检测BOM的网站上找到的。但是，Excel只是将这三个字符附加到第一个单元格，当它导入时，仍然会使特殊字符混乱。

30 个解决方案

#1

To quote a Microsoft support engineer,

引用微软支持工程师的话，

Excel for Mac does not currently support UTF-8

Mac的Excel目前不支持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:所有版本的微软Excel在Office 2016之前都是这样。新版本(来自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上都能成功地读取UTF-8内容，您需要做两件事:

1. Make sure that you convert your UTF-8 text to UTF-16LE

   确保将UTF-8文本转换为UTF-16LE。

   mb_convert_encoding($csv, 'UTF-16LE', 'UTF-8');
   

2. Make sure that you add the UTF-16LE byte order mark

   确保添加了UTF-16LE字节顺序标记。

   chr(255) . chr(254)
   

The next problem that appears only with Excel on OS X (but not Windows) 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)上显示的问题是，当使用逗号分隔值查看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、苹果自己的数字和谷歌Doc的电子表格等其他电子表格应用程序与UTF-8文件没有任何问题。

See the table in this question for 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\Csv to your requires. League\Csv has a really nice API for building CSV files.

作为附带说明，我可能会补充说，如果您使用的是Composer，那么您应该考虑在您的要求中添加League\Csv。Csv有一个非常好的API来构建Csv文件。

To use League\Csv with this method of creating CSV files, check out this example

要使用这种方法创建Csv文件，请查看本示例。

#2

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上运行。

#3

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电脑上点击空格键时，它唯一固定的UTF8编码问题是在CSV预览中。但在Excel Mac 2008……不知道为什么

#4

I just dealt with the same problem, and came up with two solutions.

我刚刚处理了同样的问题，提出了两个解。

1. Use the PHPExcel class as suggested by bpeterson76.

   使用bpeterson76所建议的PHPExcel类。

   • 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.
   • 使用这个类生成最广泛兼容的文件，我能够从UTF-8编码的数据生成一个文件，这些数据在Excel 2008 Mac、Excel 2007 Windows和谷歌文档中都可以使用。
   • 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.
   • 使用PHPExcel最大的问题是它的速度很慢，并且使用了大量的内存，这对于合理大小的文件来说不是问题，但是如果您的Excel/CSV文件有成百上千行，那么这个库就无法使用了。

   • 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.

     这是一个PHP方法,将一些TSV数据和输出的Excel文件浏览器,请注意,它使用Excel5作家,这意味着应该兼容旧版本的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;   
     }
     

2. 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.

   由于PHPExcel的效率问题，我还必须弄清楚如何生成UTF-8和Excel兼容的CSV或TSV文件。

   • 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.
   • 我能想到的最好的是一个与Excel 2008 Mac和Excel 2007 PC兼容的文件，但不是谷歌文档，这对我的应用程序来说已经足够好了。
   • I found the solution here, specifically, this answer, but you should also read the accepted answer as it explains the problem.
   • 我在这里找到了解决方案，具体来说就是这个答案，但是你也应该读一下被接受的答案，因为它解释了这个问题。

   • Here is the PHP code I used, note that I am using tsv data (tabs as delimiters instead of commas):

     这里是我使用的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;
     

#5

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');

#6

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();

#7

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 Excel is breaking them. I save as CSV, and reimport, and all the characters are messed up.

看起来问题只是简单地在Mac上使用Excel，而不是我如何生成文件，因为即使从Excel中生成CSVs也会破坏它们。我保存为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的建议可能是其他计算机最好的答案。但它仍然不能解决我的问题。

#8

The CSV File musst include a Byte Order Mark.

CSV文件musst包括一个字节顺序标记。

Or as suggested and workaround just echo it with the HTTP body

或者正如所建议的那样，解决方案只需与HTTP主体进行响应。

#9

Add:

添加:

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

Or:

或者:

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

Before writing any content to CSV file.

在将任何内容写入CSV文件之前。

Example:

例子:

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

#10

Since UTF8 encoding doesn't play well with Excel. You can convert the data to another encoding type using iconv().

因为UTF8编码不适合Excel。您可以使用iconv()将数据转换为另一种编码类型。

e.g.

如。

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

#11

As I investigated and I found that UTF-8 is not working well on MAC and Windows so I tried with Windows-1252 , it supports well on both of them but you must select type of encoding on ubuntu. Here is my code$valueToWrite = mb_convert_encoding($value, 'Windows-1252');

在我调查的时候，我发现UTF-8在MAC和Windows上运行得不好，所以我尝试了Windows-1252，它在这两方面都很好，但是你必须在ubuntu上选择编码类型。这里是我的代码$valueToWrite = mb_convert_encoding($value， ' window -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

#12

This works fine in excel for both Windows and also Mac OS.

Fix issues in excel that are not displaying characters containing diacritics, cyrillic letters, greek letter and currency symbols.

在excel中解决问题，不显示包含diacritics、cyrillic字母、希腊字母和货币符号的字符。

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

#13

How about just outputting for Excel itself? This is an excellent class that allows you to generate XLS files server-side. I use it frequently for clients who can't "figure out" csv's and so far have never had a complaint. It also allows some extra formatting (shading, rowheights, calculations, etc) that csv won't ever do.

不如干脆把Excel拿出来?这是一个优秀的类，它允许您生成XLS文件服务器端。我经常为那些无法“搞清楚”csv的客户使用它，到目前为止还没有收到过投诉。它也允许一些额外的格式(阴影，旋转，计算等等)，csv不会做。

#14

you can convert your CSV String with iconv. for example:

您可以使用iconv转换您的CSV字符串。例如:

$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) );

#15

You have to use the encoding "Windows-1252".

您必须使用编码“Windows-1252”。

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

Maybe you have to convert your strings:

也许你需要转换你的字符串:

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

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

#16

For me none of the solution above worked. Below is what i did to resolve the issue: modify the value using this function in the PHP code:

对我来说，上述的解决方案都没有奏效。下面是我解决这个问题的方法:在PHP代码中使用这个函数修改值:

$value = utf8_encode($value);

This output values properly in an excel sheet.

该输出值在excel表中正确地显示。

#17

I had this same problem when I had an Excel VBA routine that imported data. Since CSV is a plain text format, I was working around this by programatically opening the data in a simple file editor like wordpad, and re-saving it as unicode text, or copying it to the clipboard from there and pasting it to Excel. If excel doesn't automatically parse the CSV into cells, this is easily remedied using the built in "Text to Columns" feature.

当我有一个Excel VBA例程导入数据时，我遇到了同样的问题。因为CSV是一种纯文本格式，所以我在此工作的方法是通过编程方式将数据打开到一个简单的文件编辑器中，比如wordpad，并将其重新保存为unicode文本，或者将其复制到剪贴板上，然后粘贴到Excel中。如果excel没有自动将CSV解析成单元格，那么使用内置的“Text to Columns”特性可以很容易地解决这个问题。

#18

Does the problem still occur when you save it as a .txt file and them open that in excel with comma as a delimiter?

当您将它保存为.txt文件并在excel中以逗号作为分隔符打开时，问题仍然存在吗?

The problem might not be the encoding at all, it might just be that the file isn't a perfect CSV according to excel standards.

问题可能根本不是编码，可能只是文件不是一个完美的CSV，根据excel标准。

#19

This Post is quite old, but after hours of trying I want to share my solution … maybe it helps someone dealing with Excel and Mac and CSV and stumbles over this threat. I am generating a csv dynamically as output from a Database with Excel Users in mind. (UTF-8 with BOM)

这篇文章很旧，但经过几个小时的尝试，我想分享我的解决方案……也许它能帮助处理Excel、Mac和CSV的人，并为这一威胁而困惑。我正在以Excel用户的身份从数据库中动态生成一个csv。(与BOM utf - 8)

I tried a lot of iconv´s but couldn´t get german umlauts working in Mac Excel 2004. One solution: PHPExcel. It is great but for my project a bit too much. What works for me is creating the csv file and convert this csv file to xls with this PHPsnippet: csv2xls. the result xls works with excel german umlauts (ä,ö,Ü,...).

我尝试了很多iconv´s就是´t得到德国变音符号在Mac Excel 2004工作。一个解决办法:PHPExcel。这很好，但是对我的项目来说有点太大了。我的工作是创建csv文件并将这个csv文件转换为xls，并使用这个PHPsnippet: csv2xls。结果xls使用excel德语umlauts (a, o, U，…)。

#20

I just tried these headers and got Excel 2013 on a Windows 7 PC to import the CSV file with special characters correctly. The Byte Order Mark (BOM) was the final key that made it work.

我刚刚尝试了这些头，并在Windows 7 PC上获得了Excel，以正确地导入带有特殊字符的CSV文件。字节顺序标记(BOM)是使其工作的最终关键。

    header('Content-Encoding: UTF-8');
    header('Content-type: text/csv; charset=UTF-8');
    header("Content-disposition: attachment; filename=filename.csv");
    header("Pragma: public");
    header("Expires: 0");
    echo "\xEF\xBB\xBF"; // UTF-8 BOM

#21

You may append the 3 bytes to the file before exporting, it works for me . Before doing that system only work in Windows and HP -UX but failed in Linux.

您可以在导出前将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 );

Have a UTF-8 BOM (3 bytes, hex EF BB BF) at the start of the file. Otherwise Excel will interpret the data according to your locale's default encoding (e.g. cp1252) instead of utf-8

在文件的开头有一个UTF-8 BOM(3个字节，hex EF BB BF)。否则，Excel将根据您的语言环境的默认编码(例如cp1252)而不是utf-8来解释数据。

Generating CSV file for Excel, how to have a newline inside a value

为Excel生成CSV文件，如何在一个值中拥有一条新行。

#22

Otherwise you may:

否则你可能会:

header("Content-type: application/x-download");
header("Content-Transfer-Encoding: binary");
header("Content-disposition: attachment; filename=".$fileName."");
header("Cache-control: private");

echo utf8_decode($output);

#23

Converting already utf-8 encoded text by using mb_convert_encoding is not needed. Just add three characters in front of the original content:

不需要使用mb_convert_encoding转换已编码的utf-8编码文本。在原内容前面加上三个字符:

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

For me this resolved the problem of special characters in csv files.

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

#24

EASY solution for Mac Excel 2008: I struggled with this soo many times, but here was my easy fix: Open the .csv file in Textwrangler which should open your UTF-8 chars correctly. Now in the bottom status bar change the file format from "Unicode (UTF-8)" to "Western (ISO Latin 1)" and save the file. Now go to your Mac Excel 2008 and select File > Import > Select csv > Find your file > in File origin select "Windows (ANSI)" and voila the UTF-8 chars are showing correctly. At least it does for me...

Mac Excel 2008的简单解决方案:我曾多次遇到这种情况，但这里有一个简单的解决方法:在Textwrangler中打开.csv文件，该文件应该正确打开UTF-8字符。现在，在底部状态栏中，将文件格式从“Unicode (UTF-8)”改为“Western (ISO Latin 1)”并保存文件。现在到你的Mac Excel 2008，选择文件>导入>选择csv >在文件来源选择“窗口(ANSI)”中找到你的文件>，然后你就可以看到UTF-8字符显示正确了。至少对我来说是这样……

#25

I use this and it works

我用这个，它可以工作。

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;

#26

I'm on Mac, in my case I just had to specify the separator with "sep=;\n" and encode the file in UTF-16LE like this:

我在Mac电脑上，在我的例子中，我只需要用“sep=;\n”来指定分隔符，然后用UTF-16LE编码文件，就像这样:

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

#27

#output a UTF-8 CSV in PHP that Excel will read properly.

在PHP中输出一个UTF-8 CSV, Excel将会正确读取。

//Use tab as field separator   
$sep  = "\t";  
$eol  = "\n";    
$fputcsv  =  count($headings) ? '"'. implode('"'.$sep.'"', $headings).'"'.$eol : '';

#28

In my case following works very nice to make CSV file with UTF-8 chars displayed correctly in Excel.

在我的案例中，下面的工作非常好，可以在Excel中正确显示UTF-8字符的CSV文件。

$out = fopen('php://output', 'w');
fprintf($out, chr(0xEF).chr(0xBB).chr(0xBF));
fputcsv($out, $some_csv_strings);

The 0xEF 0xBB 0xBF BOM header will let Excel know the correct encoding.

0xEF 0xBB 0xBF BOM头将让Excel知道正确的编码。

#29

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

#30

//convert UTF-8 file without BOM to UTF-16LE for excel on mac
$fileUtf8String = file_get_contents("file.ext");
file_put_contents("file.ext", "\xFF\xFE" . mb_convert_encoding($fileUtf8String, "UTF-16LE", "UTF-8"));