PHP export CSV UTF-8 with BOM doesn't work

Question

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.

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.

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.

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

Here are my codes:

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

Answer 1

Hope this can help someone. What worked for me was I had to put both:

...
echo chr(0xEF) . chr(0xBB) . chr(0xBF);
$file = fopen('php://output', 'w');
fputs($file, chr(0xEF) . chr(0xBB) . chr(0xBF));
...

I'm not an expert in PHP so I can't explain why this works by I hope this helps someone because I had a hard time also solving this problem.

Answer 2

Below code worked for me. Output utf-8-bom characters before csv content:

  echo "\xEF\xBB\xBF"; // utf-8 bom 
  echo $csv;