#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    37
    Rep Power
    3

    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"0x8090x80x00x100x00x0);
            return;
            }
            function 
    xlsEOF()
            {
            echo 
    pack("ss"0x0A0x00);
            return;
            }
            function 
    xlsWriteNumber($Row$Col$Value)
            {
            echo 
    pack("sssss"0x20314$Row$Col0x0);
            echo 
    pack("d"$Value);
            return;
            }
            function 
    xlsWriteLabel($Row$Col$Value )
            {
            
    $L strlen($Value);
            echo 
    pack("ssssss"0x204$L$Row$Col0x0$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.
  2. #2
  3. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,183
    Rep Power
    9398
    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.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Location
    India
    Posts
    2
    Rep Power
    0
    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.
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    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.
  8. #5
  9. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,316
    Rep Power
    7170
    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

    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
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    37
    Rep Power
    3
    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
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    37
    Rep Power
    3
    Originally Posted by Jacques1
    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.
    thanks for your post but i am just a beginner and don't know what do you mean
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    37
    Rep Power
    3
    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 ?
  16. #9
  17. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    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).
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    37
    Rep Power
    3
    Originally Posted by Jacques1
    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).
    i have added what you said but the output isn't correct it appears as م*مد i am using office 2013
  20. #11
  21. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    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).
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    37
    Rep Power
    3
    Originally Posted by Jacques1
    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).
    ok thanks

IMN logo majestic logo threadwatch logo seochat tools logo