Character encoding on Excel export from PHP

Question

UPDATE: I have changed the code in order to insert a BOM, with no possitive result. Here's the updated piece of code:

$output = fopen($file, 'w'); $utf8_output_with_bom = chr(239) . chr(187) . chr(191) . $output; fputcsv($output, array('NOMBRE','APELLIDO','TELEFONO', 'RESPUESTA', 'DIA', 'HORA'), "\t"); if ($key1!="No definida"){ // fetch the data $rows1 = mysqli_query($con, "SELECT cm_name, cm_surname, cr_phone, cr_message, DATE_FORMAT(cr_time,'%d/%m/%Y'), DATE_FORMAT(cr_time,'%H:%i') FROM ws_campreplies JOIN ws_campmsg WHERE cr_phone=cm_phone AND cr_fk_us_id='$id' AND cr_message='$key1' AND cm_fk_ca_id='$ca_id'"); // loop over the rows, outputting them while ($row1 = mysqli_fetch_assoc($rows1)) { fputcsv($output, $row1, "\t"); } } 

---

I have a utf8_general_ci database, with this example of records on the two involved tables (all utf8_general_ci):

Table ws_campmsg:

Table ws_campreplies:

The only thing I need is to export this on Excel, and this is almost done. This PHP file creates a SQL and exports a correct XLS file:

$con = mysqli_connect(DB_SERVER,DB_USER,DB_PASS,DB_NAME); mysqli_set_charset($con, 'utf8'); [....] $ca_id = $_POST['ca_id']; $key1 = $_POST['key1']; $file='../uploads/TemporalCsv_'.time().'.xls'; header("Content-Type: application/xls"); header("Content-Disposition: attachment; filename=$file"); header("Pragma: no-cache"); header("Expires: 0"); $output = fopen($file, 'w'); fputcsv($output, array('NOMBRE','APELLIDO','TELEFONO', 'RESPUESTA', 'DIA', 'HORA'), "\t"); if ($key1!="No definida"){ // fetch the data $rows1 = mysqli_query($con, "SELECT cm_name, cm_surname, cr_phone, cr_message, DATE_FORMAT(cr_time,'%d/%m/%Y'), DATE_FORMAT(cr_time,'%H:%i') FROM ws_campreplies JOIN ws_campmsg WHERE cr_phone=cm_phone AND cr_fk_us_id='$id' AND cr_message='$key1' AND cm_fk_ca_id='$ca_id'"); // loop over the rows, outputting them while ($row1 = mysqli_fetch_assoc($rows1)) { fputcsv($output, $row1, "\t"); } } fclose($output); echo $file; 

If I pass ca_id="438", and key1="CLAVE7", the exported file is right this one:

---

As you see the only problem now is that it does not show properly accents like á or Á. As I have searched quite a bit about solving this, there are some tries I have made with no possitive result, like trying to export on text format dfirectly on browser (exports the same coding mistakes):

$file='../uploads/TemporalCsv_'.time().'.txt'; header("Content-Type: text/tab-separated-values"); 

Also tried changing headers (exports the same coding mistakes)

header("Content-Type: application/vnd.ms-excel; charset=utf-8"); header("Content-type: application/x-msexcel; charset=utf-8"); header("Content-Disposition: attachment; filename=ventas.xls"); header("Expires: 0"); header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Cache-Control: private",false); 

On the last tries I found that maybe converting with iconv() would be effective, but with this modification an error emerges, as html_entity_decode() accepts strings, not arrays:

if ($key1!="No definida"){ // fetch the data $rows1 = mysqli_query($con, "SELECT cm_name, cm_surname, cr_phone, cr_message, DATE_FORMAT(cr_time,'%d/%m/%Y'), DATE_FORMAT(cr_time,'%H:%i') FROM ws_campreplies JOIN ws_campmsg WHERE cr_phone=cm_phone AND cr_fk_us_id='$id' AND cr_message='$key1' AND cm_fk_ca_id='$ca_id'"); // loop over the rows, outputting them while ($row1 = mysqli_fetch_assoc($rows1)) { iconv("UTF-8","WINDOWS-1255",html_entity_decode( $row1 ,ENT_COMPAT,'utf-8')); fputcsv($output, $row1, "\t"); } } 

I hope I explained myself crearly. The use of PHPExcel is my last chance, if there is any posibility of correcting this issue without using PHPExcel, I would prefer it. Very grateful.

Answer 1

You have to add a BOM at the beginning of your output.

$utf8_output_with_bom = chr(239) . chr(187) . chr(191) . $utf8_output; 

The BOM is not required in UTF8.

Example:

$output = fopen($file, 'w'); fwrite($output, chr(239) . chr(187) . chr(191)); // Write the BOM to the beginning of the file fputcsv($output, <...>); fclose($output); 

Code to write the CSV:

$output = fopen($file, 'w'); fwrite($output, chr(239) . chr(187) . chr(191)); fputcsv($output, array('NOMBRE', 'APELLIDO', 'TELEFONO', 'RESPUESTA', 'DIA', 'HORA'), "\t"); if ($key1 != "No definida") { // fetch the data $rows1 = mysqli_query($con, "SELECT cm_name, cm_surname, cr_phone, cr_message, DATE_FORMAT(cr_time,'%d/%m/%Y'), DATE_FORMAT(cr_time,'%H:%i') FROM ws_campreplies JOIN ws_campmsg WHERE cr_phone=cm_phone AND cr_fk_us_id='$id' AND cr_message='$key1' AND cm_fk_ca_id='$ca_id'"); // loop over the rows, outputting them while ($row1 = mysqli_fetch_assoc($rows1)) { fputcsv($output, $row1, "\t"); } } fclose($output);