PHP导出Mysql数据到Excel

浏览数：25 / 时间：2015年06月12日

临时需要将Mysql中一张表导出成Excel表格，有个phpexcel的插件可以用，我觉得有点麻烦，况且我是临时要备份的，就直接自己写了。

<?php 
/*连接数据库*/
 $DB_Server = "ServerIP";
 $DB_Username = "UserName"; 
 $DB_Password = "PassWord"; 
 $DB_DBName = "DBname";  //目标数据库名
$DB_TBLName = "TableName";  //目标表名

$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn‘t connect."); 
 mysql_query("set names utf8"); 

$savename = date("YmjHis"); //导出excel文件名
$file_type = "vnd.ms-excel"; 
 $file_ending = "xls"; 
 header("Content-Type: application/$file_type;charset=utf-8"); 
 header("Content-Disposition: attachment; filename=".$savename.".$file_ending"); 
header("Pragma: no-cache"); 

/*写入备注信息*/
 $now_date = date("Y-m-j H:i:s"); 
 $title = "数据库名:$DB_DBName,数据表:$DB_TBLName,备份日期:$now_date"; 
 echo iconv("utf-8","gbk",$title)."\n"; 

/*查询数据库*/
 $sql = "Select * from $DB_TBLName"; 
 $ALT_Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn‘t select database"); 
 $result = @mysql_query($sql,$Connect) or die(mysql_error()); 

/*写入表字段名*/
for ($i = 0; $i < mysql_num_fields($result); $i++) { 
  
  echo mysql_field_name($result,$i) . "\t"; 
 } 
 echo "\n";

/*写入表数据*/
 $sep = "\t"; 
 while($row = mysql_fetch_row($result)) { 
  $data = ""; 
   for($i=0; $i<mysql_num_fields($result);$i++) { 
    if(!isset($row[$i])) 
     $data .= "NULL".$sep; //处理NULL字段
   elseif ($row[$i] != ""){
     $datmp=iconv("utf-8", "gbk",$row[$i]);
     $data .= $datmp.$sep; 
   }
    else 
     $data .= "".$sep; //处理空字段
  } 
  echo $data."\n"; 
 }
 ?>