exporting chinese character to excel file

Question

In my database the chinese characters are in this format å¤§è‚¡ä¸œ.

Originally the Chinese characters are 大股东. And its showing at web page correctly. when i download the data from page in *.xls file then its generating the same string as in database( å¤§è‚¡ä¸œ) . I am looking for solution to convert these characters to its original chinese characters in downloaded .xls file.

I am doing this on page by putting the header('Content-type: text/html; charset=utf-8'); at the top of page.

I have also added this header in excel downloading code but not solved.

Here is the php code for downloading excel file.

header('Content-type: text/html; charset=utf-8');

    @include_once('Worksheet.php');
    @include_once('Workbook.php');
    $Module = "searchxlship.php";


    @include_once("../db_class.php");
    $DB = new DBConnect();

    @ini_set('max_execution_time', 1200);
    //================================================================
    //DELETE THE OLD EXISTING FILE
    @chmod("searchxlship.xls", 0777);
    @unlink("searchxlship.xls");

    $workbook       = new Workbook("searchxlship.xls");
    $ColumnWidthArr = array(
        "0" => "0##23##20"
    );

    $worksheet2 =& $workbook->add_worksheetdefault('Ship_order-' . date('d-M-Y'), $ColumnWidthArr);
    //$worksheet2->set_footer("Copyright (R) Worldyards.com Pte Ltd, details without guarantee.");


    //=======Format For Merge Cells========================================================         
    $formatmerg =& $workbook->add_format();
    $formatmerg->set_color('black');
    $formatmerg->set_pattern();
    $formatmerg->set_fg_color('white');
    $formatmerg->set_merge_cell();
    //=====================================================================================

    //======================================================================================================
    $ColHead =& $workbook->add_format();
    $ColHead->set_size(8);
    $ColHead->set_align('center');
    $ColHead->set_color('white');
    $ColHead->set_pattern();
    $ColHead->set_bg_color('green');
    $ColHead->set_bold(1);
    $ColHead->set_bottom(2);
    $ColHead->set_top(2);
    $ColHead->set_bottom_color('black');
    //======================================================================================================

    $formatot =& $workbook->add_format();
    $formatot->set_size(8);
    $formatot->set_align('center');
    $formatot->set_color('black');
    $formatot->set_pattern();
    $formatot->set_fg_color('white');
    $formatot->set_left(2);
    $formatot->set_left_color('black');
    $formatot->set_right_color('black');

    $colformatot =& $workbook->add_format();
    $colformatot->set_size(8);
    $colformatot->set_align('center');
    $colformatot->set_color('brown');
    $colformatot->set_bold(1);
    $colformatot->set_pattern();
    $colformatot->set_fg_color('white');
    $colformatot->set_left(2);
    $colformatot->set_left_color('black');
    $colformatot->set_right_color('black');

    $forredformat =& $workbook->add_format();
    $forredformat->set_size(8);
    $forredformat->set_align('center');
    $forredformat->set_color('red');
    $forredformat->set_bold(1);
    $forredformat->set_pattern();
    $forredformat->set_fg_color('white');
    $forredformat->set_left(2);
    $forredformat->set_left_color('black');
    $forredformat->set_right_color('black');


    $forgreenformat =& $workbook->add_format();
    $forgreenformat->set_size(8);
    $forgreenformat->set_align('center');
    $forgreenformat->set_color('green');
    $forgreenformat->set_bold(1);
    $forgreenformat->set_pattern();
    $forgreenformat->set_fg_color('white');
    $forgreenformat->set_left(2);
    $forgreenformat->set_left_color('black');
    $forgreenformat->set_right_color('black');


    $forbluematot =& $workbook->add_format();
    $forbluematot->set_size(8);
    $forbluematot->set_align('center');
    $forbluematot->set_color('blue');
    $forbluematot->set_bold(1);
    $forbluematot->set_pattern();
    $forbluematot->set_fg_color('white');
    $forbluematot->set_left(2);
    $forbluematot->set_left_color('black');
    $forbluematot->set_right_color('black');

    $formatforVerticalLine =& $workbook->add_format();
    $formatforVerticalLine->set_color('black');
    $formatforVerticalLine->set_pattern();
    $formatforVerticalLine->set_fg_color('white');
    $formatforVerticalLine->set_bold(0);
    $formatforVerticalLine->set_size(8);
    $formatforVerticalLine->set_align('center');
    $formatforVerticalLine->set_left(2);
    $formatforVerticalLine->set_bottom(2);
    $formatforVerticalLine->set_left_color('black');
    $formatforVerticalLine->set_bottom_color('white');

    //==========Format for horizontal line=========
    $formatforDarkHLine =& $workbook->add_format();
    $formatforDarkHLine->set_size(8);
    $formatforDarkHLine->set_align('center');
    $formatforDarkHLine->set_color('black');
    $formatforDarkHLine->set_pattern();
    $formatforDarkHLine->set_bold(1);
    // $formatforDarkHLine->set_bottom(2);            
    $formatforDarkHLine->set_top(2);
    $formatforDarkHLine->set_left(2);
    $formatforDarkHLine->set_right(2);
    $formatforDarkHLine->set_left_color('white');
    $formatforDarkHLine->set_right_color('white');
    $formatforDarkHLine->set_fg_color('white');
    //=============================================//

    //==========Format for horizontal line=========
    $formatformLine =& $workbook->add_format();
    $formatformLine->set_size(8);
    $formatformLine->set_align('center');
    $formatformLine->set_color('black');
    $formatformLine->set_fg_color('white');
    $formatformLine->set_pattern();
    $formatformLine->set_bold(1);
    //  $formatformLine->set_bottom(1);           
    $formatformLine->set_top(2);
    //$formatformLine->set_left(2);
    //$formatformLine->set_right(2);
    $formatformLine->set_bottom_color('black');
    $formatformLine->set_right_color('black');
    $formatformLine->set_fg_color('white');

    $HeadFormat =& $workbook->add_format();
    $HeadFormat->set_size(12);
    $HeadFormat->set_align('left');
    $HeadFormat->set_color('black');
    $HeadFormat->set_pattern();
    $HeadFormat->set_fg_color('white');
    $HeadFormat->set_bold(1);
    $HeadFormat->set_text_wrap(0);


    $ShipQuery = $_POST['xlquery'];


        $Heading = array(
            'S.no',
            'Built/Delivery date',
            'IMO num / WY num WY No in red and Invalid IMO/WY no in brown ',
            'Ship name ',
            'Chinese ship name ',
            'Segment',
            'Sub-segment'
        );




    $ShipRes = $DB->Select($ShipQuery, 'FetchShips', $Module);
    for ($i = 0; $i < $RecordCount + 2000; $i++)
    {
        $worksheet2->set_row($i, 20, $formatmerg);
    }
    $Sno = 2;

    $Sno = $Sno + 2;

        $worksheet2->write_string($Sno, 0, $Heading[0], $ColHead);
        $worksheet2->write_string($Sno, 1, $Heading[1], $ColHead);
        $worksheet2->write_string($Sno, 2, $Heading[2], $ColHead);
        $worksheet2->write_string($Sno, 3, $Heading[3], $ColHead);
        $worksheet2->write_string($Sno, 4, $Heading[4], $ColHead);
        $worksheet2->write_string($Sno, 5, $Heading[5], $ColHead);


    $Sno = $Sno + 1;
    $Col = 0;
    $Num = 1;
    while ($Row = @mysql_fetch_object($ShipRes))
    {
        $worksheet2->write_string($Sno, $Col++, $Num++, $formatot);
        $worksheet2->write_string($Sno, $Col++, $Row->builtdate, $formatot);
        $worksheet2->write_string($Sno, $Col++, $Row->imo, $formatot);
        $worksheet2->write_string($Sno, $Col++, $Row->shipnam, $formatot);
        $worksheet2->write_string($Sno, $Col++, $Row->chineseshipnam, $formatot);        
        $worksheet2->write_string($Sno, $Col++, $Row->segnam, $formatot);
        $worksheet2->write_string($Sno, $Col++, $Row->typnam, $formatot);

        $worksheet2->write_string($Sno, $Col++, "", $formatforVerticalLine);
        $Sno = $Sno + 1;
        $Col = 0;
    }
    //$Sno=$Sno+1;
    $worksheet2->write_string($Sno, 0, '', $formatforDarkHLine);
    $worksheet2->write_string($Sno, 1, '', $formatforDarkHLine);
    $worksheet2->write_string($Sno, 2, '', $formatforDarkHLine);
    $worksheet2->write_string($Sno, 3, '', $formatforDarkHLine);
    $worksheet2->write_string($Sno, 4, '', $formatforDarkHLine);
    $worksheet2->write_string($Sno, 5, '', $formatforDarkHLine);

    $workbook->close();

    $filename = "searchxlship.xls";

    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Content-Type: application/vnd.ms-excel");
    header("Content-Disposition: attachment; filename=" . basename($filename) . ";");
    header("Content-Transfer-Encoding: binary");
    readfile($filename);

change excel sheet's encoding to chinese.

Yogesh Suthar
– Yogesh Suthar

2012-10-06 07:19:02 +00:00
Commented Oct 6, 2012 at 7:19 — Yogesh Suthar
– Yogesh Suthar, Commented Oct 6, 2012 at 7:19

chooban · Accepted Answer · 2012-10-06 10:44:20Z

1

Given that you're getting 9 characters in the output for 3 characters in the input, it's most likely that Windows is defaulting to thinking that your spreadsheet is encoded using Cpc-1252.

I haven't used the libraries in question, but Googling suggests that this might be the answer.

Going by your code, I think that once you create the $worksheet variable, you want to try the following:

$worksheet2->setInputEncoding('utf-8');

That's assuming that your database storage and connection are set to UTF-8. Basically, set the worksheet encoding to be the same as what you're storing your data as.

edited Oct 6, 2012 at 10:44

answered Oct 6, 2012 at 10:26

chooban

9,2662 gold badges23 silver badges37 bronze badges

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

2 Comments

Bajrang Over a year ago

I have already tried with this Error:- fetal error function not found setInputEncoding.

chooban Over a year ago

Can you open Worksheet.php and have a look for likely methods, or is it obfuscated?

Olaf Erlandsen · Accepted Answer · 2012-10-06 07:22:43Z

0

try with this code:

$text = htmlentities ( "大股东" , ENT_COMPAT ,  'UTF-8', true);

answered Oct 6, 2012 at 7:22

Olaf Erlandsen

6,07611 gold badges48 silver badges78 bronze badges

2 Comments

Bajrang Over a year ago

This encoded in this format å¤§è‚¡ä¸œ (Copied from excel file)

Bajrang Over a year ago

If you are saying about the excel file encoding then its "Western European(Window)". I have saw this at - Excel option -> Advanced -> Web option(button) -> Encoding(Tab)

Collectives™ on Stack Overflow

exporting chinese character to excel file

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related