Foreign characters when creating Excel 5 sheet

Topics: Developer Forum, User Forum
Feb 5, 2010 at 3:33 PM
Edited Feb 5, 2010 at 3:34 PM

Hello,

I'm trying to create a spreadsheet which takes some of its cell values from an xml file, but when I try to insert words with foreign characters in them, it fails.

The xml file has utf-8 encoding.

I am creating an Excel 5 sheet using

 

$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B1', $str);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save($pathToExcelFile);

 

I have tried using iconv and mbstring to encode the string before it is entered into the cell

e.g.

 

$current_enc = mb_detect_encoding($str);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B1', iconv($current_enc, "UTF-8", $str));	

$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B1', mb_convert_encoding($str, "UTF-8", $current_enc));	

 

and I get the same result each time.

If i am trying to insert a string like "Tvättsäck" it appears in the spreadsheet as "Tv"

Any help would be greatly appreciated.

This has been a really useful class so far, we are using it to embed images into the spreadsheet, which I think is why we went for the Excel5 type of file.

 

Thanks

 

Peter

Developer
Feb 17, 2010 at 12:12 AM

This is strange. There should be no problems with UTF-8 in Excel5 writer.

As a test, can you try to insert the string directly. This should be working if your PHP script is saved as UTF-8 :

 

$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B1', 'Tvättsäck');

Mar 4, 2010 at 2:59 PM

Hello,

I've tried this out and I still get the same result...

I have tried

$str = 'Tvättsäck';
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$i, $str);

and

$str = 'Tvättsäck';
$current_enc = mb_detect_encoding($str);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$i, iconv($current_enc, "UTF-8", $str));	

which both result in just "Tv" appearing in the cell

if i do

$str = 'Tvättsäck';
$current_enc = mb_detect_encoding($str);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$i, mb_convert_encoding($str, "UTF-8", $current_enc));

I get "Tvttsck"

which is a different result, but still not quite what I'm looking for!

Can you suggest anything else I could try. Are different versions of excel likely to be more effective?

Is there anything I can pass when I output the file to specify the encoding type?

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save($pathToExcelFile);

thanks for your help

 

Peter

 

Mar 4, 2010 at 3:11 PM

Update:

I've just read

http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=71406

who found that using utf8_encode worked, so I tried

$str = utf8_encode('Tvättsäck');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$i, $str);

and that seems to work.

However I noticed in the post I read that you advise against this because it slows down the code and shouldn't be neccessary, so if there is a way I could get it to work without doing this then I would still rather do that.

Thanks

Peter

Mar 4, 2010 at 3:45 PM

Hurrah, I've figured it out.

 

I couldn't work out why the characters in 01simple.php was working, but this wasn't.

It was becasue the code wasn't saved as utf-8.

I'm editing in dreamweaver cs4, and I have the default encoding (edit > preferences > new document > default encoding)

set to utf-8, so I assumed that this doc was utf-8.

However, I discovered that if you go into

modify > page properties > title/encoding > encoding

you can modify the encoding of the page.

Also, make sure you don't use utf8_encode once you've set the encoding up in the page or it will turn out weird.

 

Thanks for your help, hope this helps someone else solve this problem a bit more quickly than I did!

 

Cheers

 

Peter