Excel file with char and formatting problem

[RonnyZenn]

I create an excel file with php, everything seems fine but there're character problems, can't display some chars properly, and I can't format titles bold. Example code is below;

 

^<?

 

 

^{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;

^}

 

 

^{// Query Database}

    ^{$result=mysql_db_query($dbname,"select id,prename,name,sname,grade from appdata where course='$courseid' and sec='$section'")}

 

    ^{// Send Header}

    ^{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=$courseid-$sec.xls ");}

    ^{header("Content-Transfer-Encoding: binary ");}

 

    ^{// XLS Data Cell}

 

                ^xlsBOF();

                ^{xlsWriteLabel(1,0,"Student Register $semester/$year");}

                ^{xlsWriteLabel(2,0,"COURSENO : ");}

                ^{xlsWriteLabel(2,1,"$courseid");}

                ^{xlsWriteLabel(3,0,"TITLE : ");}

                ^{xlsWriteLabel(3,1,"$title");}

                ^{xlsWriteLabel(4,0,"SETION : ");}

                ^{xlsWriteLabel(4,1,"$sec");}

                ^{xlsWriteLabel(6,0,"NO");}

                ^{xlsWriteLabel(6,1,"ID");}

                ^{xlsWriteLabel(6,2,"Gender");}

                ^{xlsWriteLabel(6,3,"Name");}

                ^{xlsWriteLabel(6,4,"Lastname");}

                ^{$xlsRow = 7;}

                ^{while(list($id,$prename,$name,$sname,$grade)=mysql_fetch_row($result)) {}

                    ^++$i;

                          ^{xlsWriteNumber($xlsRow,0,"$i");}

                          ^{xlsWriteNumber($xlsRow,1,"$id");}

                          ^{xlsWriteLabel($xlsRow,2,"$prename");}

                          ^{xlsWriteLabel($xlsRow,3,"$name");}

                          ^{xlsWriteLabel($xlsRow,4,"$sname");}

                    ^$xlsRow++;

                    ^}

                    ^xlsEOF();

                ^exit();

^?>

 

Original article for the code could be found in

http://www.appservnetwork.com/modules.php?name=News&file=article&sid=8

 

Cheers

 

[RonnyZenn]

I tried the following utf8 decode function but it doesnt really solve the char problem as well

 

^<?php

^{# GLOBAL VARIABLES}

^{$url = "http://www.unicode.org/Public/MAPPINGS/ISO8859/8859-9.TXT";}

^{//$url = "8859-9.txt";}

^{$iso2utf = array();}

^{$utf2iso = array();}

 

^{# UNICODE MAPPING TABLE PARSING}

^{function create_map($url){}

    ^{global $iso2utf, $utf2iso;}

    ^{$fl = @(file($url)) OR (die("cannot open file : $url\n"));}

    ^{for ($i=0; $i<count($fl); $i++){}

        ^{if($fl[$i][0] != '#' && trim($fl[$i])){}

            ^{list($iso, $uni, $s, $desc) = split("\t",$fl[$i]);}

            ^{$iso2utf[$iso] = $uni;}

            ^{$utf2iso[$uni] = $iso;}

        ^}

    ^}

^}

 

^{# FINDING UNICODE LETTER'S DECIMAL ASCII VALUE}

^{function uniord($c){}

    ^{$ud = 0;}

    ^{if (ord($c{0})>=0 && ord($c{0})<=127)  $ud = $c{0};}

    ^{if (ord($c{0})>=192 && ord($c{0})<=223) $ud = (ord($c{0})-192)*64 + (ord($c{1})-128);}

    ^{if (ord($c{0})>=224 && ord($c{0})<=239) $ud = (ord($c{0})-224)*4096 + (ord($c{1})-128)*64 + (ord($c{2})-128);}

    ^{if (ord($c{0})>=240 && ord($c{0})<=247) $ud = (ord($c{0})-240)*262144 + (ord($c{1})-128)*4096 + (ord($c{2})-128)*64 + (ord($c{3})-128);}

    ^{if (ord($c{0})>=248 && ord($c{0})<=251) $ud = (ord($c{0})-248)*16777216 + (ord($c{1})-128)*262144 + (ord($c{2})-128)*4096 + (ord($c{3})-128)*64 + (ord($c{4})-128);}

    ^{if (ord($c{0})>=252 && ord($c{0})<=253) $ud = (ord($c{0})-252)*1073741824 + (ord($c{1})-128)*16777216 + (ord($c{2})-128)*262144 + (ord($c{3})-128)*4096 + (ord($c{4})-128)*64 + (ord($c{5})-128);}

    ^{if (ord($c{0})>=254 && ord($c{0})<=255) $ud = false; //error}

    ^{return $ud;}

^}

 

^{# PARSING UNICODE STRING}

^{function utf2iso($source) {}

    ^{global $utf2iso;}

    ^{$pos = 0;}

    ^{$len = strlen ($source);}

    ^{$encodedString = '';}

 

    ^{while ($pos < $len) {}

        ^{$is_ascii = false;}

        ^{$asciiPos = ord (substr ($source, $pos, 1));}

        ^{if(($asciiPos >= 240) && ($asciiPos <= 255)) {}

            ^{// 4 chars representing one unicode character}

            ^{$thisLetter = substr ($source, $pos, 4);}

            ^{$thisLetterOrd = uniord($thisLetter);}

            ^{$pos += 4;}

        ^}

        ^{else if(($asciiPos >= 224) && ($asciiPos <= 239)) {}

            ^{// 3 chars representing one unicode character}

            ^{$thisLetter = substr ($source, $pos, 3);}

            ^{$thisLetterOrd = uniord($thisLetter);}

            ^{$pos += 3;}

        ^}

        ^{else if(($asciiPos >= 192) && ($asciiPos <= 223)) {}

            ^{// 2 chars representing one unicode character}

            ^{$thisLetter = substr ($source, $pos, 2);}

            ^{$thisLetterOrd = uniord($thisLetter);}

            ^{$pos += 2;}

        ^}

        ^else{

            ^{// 1 char (lower ascii)}

            ^{$thisLetter = substr ($source, $pos, 1);}

            ^{$thisLetterOrd = uniord($thisLetter);}

            ^{$pos += 1;}

            ^{$encodedString .= $thisLetterOrd;}

            ^{$is_ascii = true;}

        ^}

        ^{if(!$is_ascii){}

            ^{$hex = sprintf("%X", $thisLetterOrd);}

            ^{if(strlen($hex)<4) for($t=strlen($hex);$t<4;$t++)$hex = "0".$hex;}

            ^{$hex = "0x".$hex;}

            ^{$hex = $utf2iso[$hex];}

            ^{$hex = str_replace('0x','',$hex);}

            ^{$dec = hexdec($hex);}

            ^{$encodedString .= sprintf("%c", $dec);}

        ^}

    ^}

    ^{return $encodedString;}

^}

 

^{# CREATING ISO2UTF & UTF2ISO MAPS}

^{create_map($url);}

 

^{# TESTING}

^{$unicode_string = "Ekonomi_ve_\xc4\xb0\xc5\x9f_D\xc3\xbcnyas\xc4\xb1";}

 

^{echo "unicode string : <b>" . $unicode_string . "</b>";}

^{echo "<br><br>";}

^{echo "ISO8859 (latin5 / turkish) converted string : <b>" . utf2iso($unicode_string) . "</b>";}

^?>