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);

Answer 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.

Answer 2

try with this code:

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