exporting php output as excel

Question

include_once 'mysqlconn.php';
include_once "functions.php";
$filename = $_GET['par'].".xls";
header("Content-type: application/x-msexcel"); 
header('Content-Disposition: attachment; filename="'.basename($filename).'"'); 
if ($_GET['i'] == "par1") {
  func1();
} else if ($_GET['i'] == "par2") {
  echo "şşşıııİİİ";
  func2();  
} else if ($_GET['i'] == "par3") {  
  echo "şşşıııİİİ";
  func3();  
} 

this is my export2excel.php file and func1,2,3 are in functions.php file and produces table output all work well except character encoding in a strange way. I am using utf-8 encoding for all my files. 2nd else if statement above produces healthy encoded output but rest 2 are encodes my output with strange characters like "BÃœTÇE İÇİ". it is "BÜTÇE İÇİ" in turkish.

in short. same files, same encoding, same database but different results.

any idea?

Answer 1

Excel uses UTF-16LE as the default encoding. So you should either convert UTF-8 to UTF-16LE yourself or use one of the tried and tested Excel PHP libs instead of trying to reinvent the wheel. I would recommend using PHPExcel...

Answer 2

Excel uses UTF-16LE + BOM as default Unicode encoding.
So you have to convert your output to UTF-16LE and prepend the UTF-16LE-BOM "\xFF\xFE".

Some further information:

• Microsoft Excel mangles Diacritics in .csv files?
• Exporting data to CSV and Excel in your Rails apps

Instead I would use one of the existing libraries

• PHP Excel Extension PECL extension by Ilia Alshanetsky (Core PHP Developer & Release Master)
• Spreadsheet_Excel_Writer PEAR Package
• PHPExcel

Edit:
Some code that could help if you really not want to use an existing library

<?php
ob_start();
?>
<table>
    <tr>
        <td>şşşıııİİİ</td>
        <td>bar</td>
    </tr>
    <tr>
        <td>foo</td>
        <td>şşşıııİİİ</td>
    </tr>
</table>
<?php
$output = ob_get_clean();
// Convert to UTF-16LE
$output = mb_convert_encoding($output, 'UTF-16LE', 'UTF-8'); 

$fh = fopen('foo.xls', "w");

 // Prepend BOM
fwrite($fh, "\xFF\xFE");
fwrite($fh, $output);
fclose($fh);

header("Content-type: application/x-msexcel"); 
header('Content-Disposition: attachment;  filename="foo.xls"');
readfile('foo.xls');  
?>