php/mysql export data to excel - accented characters garbled
Posted: Sun Nov 13, 2011 2:09 pm
Hi everyone,
The script below successfully exports data to excel, however all accented characters and apostrophe's become either ascii equivalents or gibberish. the db is utf-8_unicode. I've tried setting the header charset to Western ISO, encoding to utf8, decoding utf8, but all accented characters are still garbled. Does anyone have any ideas on how to resolve the issue?
The script below successfully exports data to excel, however all accented characters and apostrophe's become either ascii equivalents or gibberish. the db is utf-8_unicode. I've tried setting the header charset to Western ISO, encoding to utf8, decoding utf8, but all accented characters are still garbled. Does anyone have any ideas on how to resolve the issue?
Code: Select all
<?php
include('db.php');
function cleanData(&$str)
{
$str = preg_replace("/\t/", "\\t", $str);
if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
return utf8_decode($str);
}
$FromDate = $_SESSION['FromDate'];
$ToDate = $_SESSION['ToDate'];
// file name for download
$filename = "Data_Dump_" . date('Y-m-d') . ".xls";
header("Expires: 0");
header("Cache-control: private");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Description: File Transfer");
header("Content-type: application/vnd.ms-excel; charset=utf-8" );
header("Content-Disposition: attachment; filename=\"$filename\"");
$flag = false;
$result = mysql_query("SELECT CompanyName, CompanyBranch, ReferenceNumber, IDNumber, AgreementDate FROM records
WHERE AgreementDate Between '$FromDate' And '$ToDate' ORDER BY IDNumber DESC") or die('Query failed!');
while(false !== ($row = mysql_fetch_assoc($result))) {
if(!$flag) {
// display field/column names as first row
echo implode("\t", array_keys($row)) . "\r\n";
$flag = true;
}
array_walk($row, 'cleanData');
echo implode("\t", array_values($row)) . "\r\n";
}
?>