Unable to export UTF-8 characters to Excel5 XLS file

Topics: Developer Forum, User Forum
Dec 10, 2010 at 1:24 AM

Hi,

I have a strange problem.

If I unzip the PHPExcel soucre file named 1.7.4.zip on my computer (Windows XP) and I run the Tests/01simple-download-xls.php it works fine. However, as soon as  modify this file, it stop working (I mean I still can download the file but Excel do not regognize it. I tried with 2 different versions of MS Excel). When I modify the file, I save it in UTF-8 format (using Windows Notepad) since the file seams already in UTF-8 format.

If I save the file in ANSI format, it will start back to work except that I do not see miscellaneous glyphs characters ('éàèùâêîôûëïüÿäöüç') anymore in the output Excel file.

Is somebody could help me since I need to export UTF-8 characters to an Excel5 XLS file and for the moment, nothing seams to work... :-(

Thanks in advance anyway.

Guylain Plante

 

 

 

Dec 10, 2010 at 1:35 PM

OK, I finally found a work arround. I put the following 2 lines of code before redirecting output to the client browser and it looks that it fixed the problem. Probably that strange characters were sent to the browser. So, calling ob_end_clean() seams to clean the output buffer before sending the excel document. ob_start() is also important to reopen the output buffer.

...

ob_end_clean(); // Added by me
ob_start(); // Added by me

// Redirect output to a clients web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
...

 

Dec 10, 2010 at 4:47 PM

Finally, this work arround seams to work only when the web server is on Linux.

I still have the problem under Windows...

 

 

Coordinator
Dec 10, 2010 at 5:54 PM

When you save the file, ensure that you save it as UTF-8 without a BOM marker. Your modifications are failing because PHP will treat the BOM marker as output, which will then trigger an error when your script tries to send the headers, and this error display gets included in the Excel file that is generated. Using output buffering is preventing this output from occuring before the headers, so there won't be any error, but the BOM marker itself will still be included in the Excel file that is generated.

Dec 10, 2010 at 6:37 PM

Hi Mark,

Thank you for your answer. I better understand now.

1) I will try that. Do I have to use a special editor to be able to save my php file without BOM information since my Windows NotePad editor does not seam to have that option.

2) Just for your information I edited the corrupted excel file and I saw 6 BOM back to back. Here is the beginning of the file:

EF BB BF EF BB BFEF BB BFEF BB BFEF BB BFEF BB BF D0 CF 11

After I removed the red part using an Hex File Editor, my excel fine worked just fine. So, I hope that saving my php file without BOM will correct the problem. I will let you know.

Thanks again.

 

Coordinator
Dec 10, 2010 at 7:43 PM

If you're on Windows, then WordPad should allow you to save a file as UTF-8 without a BOM. Alternatively, most PHP IDEs will offer this option as well.

Dec 10, 2010 at 7:46 PM

Ok, I downloaded the editor Notepad++ and saved the file without BOM but unfortunately, I still had several BOM at the beginning of the excel file. So, to fix it, now I'm removing BOM directly into PHP and it seams to work just fine. Here is my code:

function removeAllBOM($str="")
{
 $tmpstr = $str; 
 while ((substr($tmpstr, 0,3) == pack("CCC",0xef,0xbb,0xbf)))
 {
  $tmpstr=substr($tmpstr, 3);
 } 
 return $tmpstr;
}

...

$objWriter->save('php://output');
$out1 = ob_get_contents();
$out1 = removeAllBOM($out1);
ob_end_clean();
ob_start();
echo $out1;

...

That time I got something that seam to work each time.

Thanks for you help.

 

 

Coordinator
Dec 10, 2010 at 7:53 PM

Check any other files that you might be including to see if they contain a BOM or not