export to xls from sql qith php script

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
killingbegin
Forum Newbie
Posts: 20
Joined: Sun Feb 08, 2009 5:07 am

export to xls from sql qith php script

Post 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;
 
Mark Baker
Forum Regular
Posts: 710
Joined: Thu Oct 30, 2008 6:24 pm

Re: export to xls from sql qith php script

Post 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
Post Reply