php export to excel using utf-8 for french characters

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
cjkeane
Forum Contributor
Posts: 217
Joined: Fri Jun 11, 2010 1:17 pm

php export to excel using utf-8 for french characters

Post by cjkeane »

hi everyone.

i'm using the following script to export data from mysql to an excel spreadsheet. the export does function, however when the data is exported and the excel file is opened, weird characters like è é ê ë are displayed instead of french characters.

the data stored in the mysql database is utf-8.
i have tried:
header("Content-type: text/html; charset: utf-8");
and
header("Content-type:application/vnd.ms-excel;charset:UTF-8");

but neither header make any difference to the output. i still receive weird characters. any ideas?

Code: Select all

<?PHP
include('db.php');

  function cleanData(&$str)
  {
    $str = preg_replace("/\t/", "\\t", $str);
    $str = preg_replace("/\r?\n/", "\\n", $str);
    if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
  }

  // file name for download
  $filename = "WebsiteData_" . date('Y-m-d') . ".xls";

  header("Content-Disposition: attachment; filename=\"$filename\"");
  	header("Content-type: text/html; charset: utf-8");
	
  $flag = false;
  $result = mysql_query("SELECT CompanyName, CONCAT(NameLast_1, ', ', NameFirst_1) As Name, DateRecorded, DateClosed
FROM records 
GROUP BY CompanyName
ORDER BY CompanyName") 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)) . "\n";
      $flag = true;
    }
    array_walk($row, 'cleanData');
    echo implode("\t", array_values($row)) . "\n";
  }

?>
User avatar
flying_circus
Forum Regular
Posts: 732
Joined: Wed Mar 05, 2008 10:23 pm
Location: Sunriver, OR

Re: php export to excel using utf-8 for french characters

Post by flying_circus »

I really dont have the answer, but I would suggest using multibyte character functions. In your cleanData function, strstr() does not handle multibyte characters. You should try the mb_strstr() instead.

Here is a link to the mbstring manual

If you output the same data to a UTF-8 encoded webpage, do the french characters show up properly?
cjkeane
Forum Contributor
Posts: 217
Joined: Fri Jun 11, 2010 1:17 pm

Re: php export to excel using utf-8 for french characters

Post by cjkeane »

i'll look into mbstring. thanks for the suggestion.
i've been looking into unicode encoding, and encoding the excel file as ansi. it seems that i can download the excel file, open it in notepad, save it as ANSI encoding and then when i open the same file in excel, french characters are displayed.

is there specific encoding format for ansi ? i have tried charset=UTF-16 but it still makes no difference.

in the mysql data and when viewing the data on a webpage, french characters are displayed.
Post Reply