PHP export excel unicode ( russian chars ) problem

Question

I have used below code to export excel file using php. For english text, I didn't find any issues. But Russian characters I find some issues. I have attached my code.

mysql_connect("localhost","USERNAME","PASSWORD");
mysql_select_db("DATABASE");
mysql_query("SET NAMES 'UTF8'");
// Get data records from table.
$result=mysql_query("select * from usertbl order by user_id asc");

// Functions for export to excel.
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/vnd.ms-excel;charset:UTF-8"); 
  header("Content-Type: application/force-download");
  header("Content-Type: application/octet-stream");
  header("Content-Type: application/download");
  header("Content-Disposition: attachment;filename=orderlist.xls ");
  header("Content-Transfer-Encoding: binary ");



  xlsBOF();

 /*
 Make a top line on your excel sheet at line 1 (starting at 0).
 The first number is the row number and the second number is the column, both are 
 start  at '0'*/

 xlsWriteLabel(0,0,"List of car company.");

 // Make column labels. (at line 3)
 xlsWriteLabel(2,0,"No.");
 xlsWriteLabel(2,1,"Nickname");

 $xlsRow = 3;

 // Put data records from mysql by while loop.
 while($row=mysql_fetch_array($result)){

   xlsWriteNumber($xlsRow,0,$row['user_id']);
   //$row['nickname']= iconv('UTF-8', 'SJIS', $row['NAME']);
   //$row['nickname'] = mb_convert_encoding($row['NAME'], 'UTF-16LE', 'UTF-8');
     xlsWriteLabel($xlsRow,1,$row['NAME']);

    $xlsRow++;
  }
 xlsEOF();
 exit();

Answer 1

I'd recommend using a library such as my own PHPExcel for writing Excel files; but you could try setting a codepage record to tell Excel that your strings are UTF-8.

function xlsCodepage($codepage) {
    $record    = 0x0042;    // Codepage Record identifier
    $length    = 0x0002;    // Number of bytes to follow

    $header    = pack('vv', $record, $length);
    $data      = pack('v',  $codepage);

    echo $header , $data;
}

and call the xlsCodepage() function after your call to xlsBOF(), and before you write any data.

Possible values for codepage (depending on the character set you are using) are:

367     ASCII       //  ASCII
437     CP437       //  OEM US
737     CP737       //  OEM Greek
775     CP775       //  OEM Baltic
850     CP850       //  OEM Latin I
852     CP852       //  OEM Latin II (Central European)
855     CP855       //  OEM Cyrillic
857     CP857       //  OEM Turkish
858     CP858       //  OEM Multilingual Latin I with Euro
860     CP860       //  OEM Portugese
861     CP861       //  OEM Icelandic
862     CP862       //  OEM Hebrew
863     CP863       //  OEM Canadian (French)
864     CP864       //  OEM Arabic
865     CP865       //  OEM Nordic
866     CP866       //  OEM Cyrillic (Russian)
869     CP869       //  OEM Greek (Modern)
874     CP874       //  ANSI Thai
932     CP932       //  ANSI Japanese Shift-JIS
936     CP936       //  ANSI Chinese Simplified GBK
949     CP949       //  ANSI Korean (Wansung)
950     CP950       //  ANSI Chinese Traditional BIG5
1200    UTF-16LE    //  UTF-16 (BIFF8)
1250    CP1250      //  ANSI Latin II (Central European)
1251    CP1251      //  ANSI Cyrillic
1252    CP1252      //  ANSI Latin I (BIFF4-BIFF7)
1253    CP1253      //  ANSI Greek
1254    CP1254      //  ANSI Turkish
1255    CP1255      //  ANSI Hebrew
1256    CP1256      //  ANSI Arabic
1257    CP1257      //  ANSI Baltic
1258    CP1258      //  ANSI Vietnamese
1361    CP1361      //  ANSI Korean (Johab)
10000   MAC         //  Apple Roman
65001   UTF-8       //  Unicode (UTF-8)

Answer 2

XLS file format is a really complex one and I would strongly advise against writing your own writer implementation.

Instead use one of the existing implementations, like phpexcel or even choose to write to CSV or XLSX formats.