PHP匯出Mysql資料到Excel

標籤：style   blog   io   ar   color   os   sp   for   on   

臨時需要將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";  } ?> 

好了，這樣直接存取這個php檔案就可以將指定的表中資料匯出了。

PHP匯出Mysql資料到Excel