Php Excel encoding issue

Question

I am converting exporting some database values to excel using phpexcel. When i echo the value everything works fine but when i am trying to export the value to excel for example The Westminster Terrace,Yau Lai Rd
 Hong Kong it just print the same in excel whereas if i echo it prints The Westminster Terrace, Yau Lai Rd , Hong Kong. The database has utf8_general_ci collation. I had used the following code .

foreach ($data as $question) {
    $c = $c + 1;
    //echo $question['their_answer'];
    $objPHPExcel -> setActiveSheetIndex(0) -> setCellValue($x . $d, $question['their_answer']);
$x++;
}

How to print the same in excel column as echoed any idea?
Thanks
Somdeb

The   and 
 are entities, I think control characters. When you say When i echo the value everything works fine are you in a browser? If so the browser is rendering those. Take a look at the source and it will be the same. — chris85
– chris85, Commented Nov 4, 2015 at 18:30
@SomdebMukherjee Because the browser can interpret HTML, and these are HTML Entities. Excel cannot. — Mikel Bitson
– Mikel Bitson, Commented Nov 4, 2015 at 18:35
Yea, browsers render entities as their characters. You have two answers below take a look. — chris85
– chris85, Commented Nov 4, 2015 at 18:35

Mikel Bitson · Accepted Answer · 2015-11-04 18:33:33Z

2

These characters are HTML entities. Try html_entity_decode, like this:

foreach ($data as $question) {
    $c = $c + 1;
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue($x . $d, html_entity_decode($question['their_answer']));
    $x++;
}

answered Nov 4, 2015 at 18:33

Mikel Bitson

3,6471 gold badge20 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2023-11-17 20:07:23Z

2

That's because Excel isn't HTML and  and 
 are HTML Entities (actually encoded carriage return and line feed characters)..... they're nothing more than literal character in a string to Excel. You need to convert them to their UTF-8 character using something like html_entity_decode()

$string = 'The Westminster Terrace,Yau Lai Rd&#13;&#10; Hong Kong';
$string = html_entity_decode($string, ENT_NOQUOTES, 'UTF-8');

You'll probably also want to set the cell to wrap, and autoheight for the row to handle those return characters as well

edited Nov 17, 2023 at 20:07

CommunityBot

11 silver badge

answered Nov 4, 2015 at 18:33

Mark Baker

213k34 gold badges354 silver badges390 bronze badges

Comments

Thiago Elias · Accepted Answer · 2015-11-04 18:55:12Z

0

Maybe you're experiencing problems with the entities eg: .

Try to decode the entities using html_entity_decode while running your code like this:

foreach ($data as $question) {
    $c = $c + 1;
    //echo $question['their_answer'];
    $objPHPExcel -> setActiveSheetIndex(0)->setCellValue($x . $d, html_entity_decode($question['their_answer']));
$x++;
}

The reason for this, may be the fact that your database already saved data with the entities encoded, so it will print correctly in the browser when you echo but won't print in the excel file (because he can't understand what those values are)

Let me know if it works.

edited Nov 4, 2015 at 18:55

answered Nov 4, 2015 at 18:36

Thiago Elias

9391 gold badge6 silver badges21 bronze badges

Collectives™ on Stack Overflow

Php Excel encoding issue

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related