Arabic characters doesn't appear in exported excel file

Dev Shed Forums Sponsor:

February 5th, 2013, 09:22 PM

wama_mms

Contributing User

Join Date: May 2012

Posts: 37

Time spent in forums: 4 h 27 m 40 sec
Reputation Power: 2

Arabic characters doesn't appear in exported excel file

PHP Code:


		
			
<?php

mysql_query("set names utf8");





mysql_connect('localhost','root','admin');

mysql_select_db('mms');



        $result=mysql_query("select * from test");

        function xlsBOF()

        {

        echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);

        return;

        }

        function xlsEOF()

        {

        echo pack("ss", 0x0A, 0x00);

        return;

        }

        function xlsWriteNumber($Row, $Col, $Value)

        {

        echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);

        echo pack("d", $Value);

        return;

        }

        function xlsWriteLabel($Row, $Col, $Value )

        {

        $L = strlen($Value);

        echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);

        echo $Value;

        return;

        }

        header("Pragma: public");

        header("Expires: 0");

        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");

        header("Content-Type: application/force-download");

        header("Content-Type: application/octet-stream");

        header("Content-Type: application/download");;

        header("Content-Disposition: attachment;filename=list.xls");

        header("Content-Transfer-Encoding: binary ");

        xlsBOF();

         

        xlsWriteLabel(0,0,"id");

        xlsWriteLabel(0,1,"name");

        $xlsRow = 1;

        while($row=mysql_fetch_array($result))

        {

        xlsWriteNumber($xlsRow,0,$row['id']);

        xlsWriteLabel($xlsRow,1,$row['name']);

        $xlsRow++;

        }

        xlsEOF();

        ?>

and i have tried a lot to solve this problem for exporting arabic data in excel sheet as it appears "????" from database but i can't find a solution. so please help me

Last edited by requinix : February 5th, 2013 at 10:11 PM.

February 5th, 2013, 10:14 PM

requinix

Still alive

Join Date: Mar 2007

Location: Washington, USA

Posts: 12,682

Folding Points: 417516 Folding Title: Super Ultimate Folder - Level 1

Time spent in forums: 5 Months 1 Week 4 Days 2 h 35 m 32 sec
Reputation Power: 8969

Send a message via Google Talk to requinix

Almost had that. Paste your code into the textarea, select it, then hit the

PHP code button.

You can't do a SET NAMES before you've connected to the server. Try moving that.

__________________
Site rules and FAQ | Project Suggestions | mod_rewrite guide | PHP New User Guide and FAQ - PHP Stickies | Regex Resources

February 5th, 2013, 10:48 PM

ITechonology

Registered User

Join Date: Jan 2013

Location: India

Posts: 2

Time spent in forums: 19 m 9 sec
Reputation Power: 0

Send a message via Google Talk to ITechonology

Send a message via Skype to ITechonology

Send a message via XFire to ITechonology

requinix is right ...
Try this one and check it
It's work.
I checked and use it.

Comments on this post

ptr2void disagrees: Gonna guess you're another Indian body shop spammer/SEO.

February 5th, 2013, 10:59 PM

Jacques1

pollyanna

Join Date: Jul 2012

Location: Germany

Posts: 1,847

Time spent in forums: 1 Month 2 Weeks 1 Day 10 h 36 m 10 sec
Reputation Power: 813

Hi,

actually, you mustn't use "SET NAMES" at all, because it's a security risk as it can break mysql_real_escape_string(). The manual has a big yellow warning for that.

Use mysql_set_charset() or change the default encoding on the server. Or even better: Dump the old MySQL extension and switch to one of the modern extensions (unless this script is embedded in a lot of legacy code).

I'd also consider using an Excel library. This byte packing looks horrible.

February 5th, 2013, 11:23 PM

E-Oreo

Lost in code

Join Date: Dec 2004

Posts: 7,931

Folding Points: 945 Folding Title: Novice Folder

Time spent in forums: 2 Months 7 h 43 m 47 sec
Reputation Power: 6991

PHPExcel is one such library that will make that code a lot cleaner and make your spreadsheet more compatible at the same time.

__________________
PHP FAQ
How to program a basic, secure login system using PHP

Quote:

Originally Posted by Spad

Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around

February 6th, 2013, 08:23 AM

wama_mms

Contributing User

Join Date: May 2012

Posts: 37

Time spent in forums: 4 h 27 m 40 sec
Reputation Power: 2

Quote:

Originally Posted by requinix

Almost had that. Paste your code into the textarea, select it, then hit the

PHP code button.

You can't do a SET NAMES before you've connected to the server. Try moving that.

i have moved it and modified as following

mysql_connect('localhost','root','admin');
mysql_select_db('mms');
mysql_query("set names utf8");

but the output becamse Ù…Ø*Ù…Ø¯ instead of ?????? any suggestions

February 6th, 2013, 08:24 AM

wama_mms

Contributing User

Join Date: May 2012

Posts: 37

Time spent in forums: 4 h 27 m 40 sec
Reputation Power: 2

Quote:

Originally Posted by Jacques1

thanks for your post but i am just a beginner and don't know what do you mean

February 6th, 2013, 08:25 AM

wama_mms

Contributing User

Join Date: May 2012

Posts: 37

Time spent in forums: 4 h 27 m 40 sec
Reputation Power: 2

Quote:

Originally Posted by E-Oreo

PHPExcel is one such library that will make that code a lot cleaner and make your spreadsheet more compatible at the same time.

can you give me a link or tutorial ?

February 6th, 2013, 01:10 PM

Jacques1

pollyanna

Join Date: Jul 2012

Location: Germany

Posts: 1,847

Time spent in forums: 1 Month 2 Weeks 1 Day 10 h 36 m 10 sec
Reputation Power: 813

Quote:

Originally Posted by wama_mms

thanks for your post but i am just a beginner and don't know what do you mean

Well, like I said: Do not query the database with "SET NAMES". This is wrong and can lead to security problems. The correct way of setting the character encoding is by calling mysql_set_charset(). So in your case, you'd write

PHP Code:


		
			
 $db_link = mysql_connect('localhost', 'root', 'admin');    // I hope "root" and the password are just for testing ;-)

mysql_select_db('mms');

mysql_set_charset('utf8');

And in addition to that, I said that the "mysql_" functions you're using are obsolete. So if you have the chance to update them, do it (check the link I gave you).

#10

February 6th, 2013, 06:14 PM

wama_mms

Contributing User

Join Date: May 2012

Posts: 37

Time spent in forums: 4 h 27 m 40 sec
Reputation Power: 2

Quote:

Originally Posted by Jacques1

PHP Code:


		
			
 $db_link = mysql_connect('localhost', 'root', 'admin');    // I hope "root" and the password are just for testing ;-)

mysql_select_db('mms');

mysql_set_charset('utf8');

And in addition to that, I said that the "mysql_" functions you're using are obsolete. So if you have the chance to update them, do it (check the link I gave you).

i have added what you said but the output isn't correct it appears as Ù…Ø*Ù…Ø¯ i am using office 2013

#11

February 6th, 2013, 07:05 PM

Jacques1

pollyanna

Join Date: Jul 2012

Location: Germany

Posts: 1,847

Time spent in forums: 1 Month 2 Weeks 1 Day 10 h 36 m 10 sec
Reputation Power: 813

The mysql_set_charset() was a security suggestion (like I said), it wasn't meant to fix the issue.

Have you checked the "raw" data directly after you fetched it from the database? If it's OK there and only broken in the Excel file, it's obviously a problem of your Excel functions. In that case, I strongly suggest using a library (see E-Oreo's post) rather than fumbling with your own home-made functions.

If the data itself is broken, it's an issue of your database (or the input).

#12

February 7th, 2013, 09:03 AM

wama_mms

Contributing User

Join Date: May 2012

Posts: 37

Time spent in forums: 4 h 27 m 40 sec
Reputation Power: 2

Quote:

Originally Posted by Jacques1

ok thanks

Arabic characters doesn't appear in exported excel file

Developer Shed Advertisers and Affiliates