Page 1 of 1

export to xls from sql qith php script

Posted: Tue Aug 04, 2009 10:52 am
by killingbegin
Hello people i have a problem with the xls file that i;m exporting.
All things are fine exept that i cant see the "greek" words in the excel document.
all that program works fine exept the words.I think that the utf format is right.

plz if somone know somthing reply.This is the last part of my last
Exercise at the university and i must give it in 2 days.

Code: Select all

 
$database="lottary";
$table="users";
 
if($database!="" && $table!=""){
 
$Host = "localhost";
$User = "root";
$Password = "";
$DBName = $database;
$TableName = $table;
 
 
$link = @mysql_connect($Host, $User, $Password) or die('Could not connect: ' . mysql_error());
 
mysql_query("SET NAMES 'utf8'");
mysql_query("SET CHARACTER SET 'utf8'");
 
@mysql_select_db($DBName) or die('Could not select database');
mysql_query("SET NAMES 'utf8'");
$select = "SELECT * FROM `".$TableName."`";
$export = mysql_query($select); 
mysql_query("SET NAMES 'utf8'");
 
$fields = mysql_num_fields($export); 
mysql_query("SET NAMES 'utf8'");
for ($i = 0; $i < $fields; $i++) {
    $csv_output .= mysql_field_name($export, $i) . "\t";
    
}
$csv_output=mb_convert_encoding($csv_output, "ISO-8859-7","UTF-8");
 
while($row = mysql_fetch_row($export)) {
    $line = '';
    foreach($row as $value) {
        if ((!isset($value)) OR ($value == "")) {
            $value = "\t"; 
        } else {
            $value = str_replace('"', '""', $value);
            $value = '"' . $value . '"' . "\t"; 
        }
        $line .= $value;
    }
    $data .= trim($line)."\n";
}
$data = str_replace("\r","",$data);
 
 
//print mb_detect_encoding($data);
$data = mb_convert_encoding($data,"ISO-8859-7","UTF-8");
 
$db=str_replace(" ","_",$database);
$filename=$db."_".$table;
 
header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment; filename=".$filename.".xls");
header("Pragma: no-cache");
header("Expires: 0");
print $csv_output."\n".$data;
exit;
 

Re: export to xls from sql qith php script

Posted: Tue Aug 04, 2009 12:32 pm
by Mark Baker
Well you're not generating an xls file, you're generating a tab-separated file with an .xls extension.
Excel will read the file, but has no idea that it's UTF-8 content, so it simply reads it as ASCII data.
Either create a genuine Excel file, or write a BOM marker at the beginning of your file.... I don't know if Excel can interpret BOM markers, but if it does, then it should recognise the content as UTF-8