首頁 > 開發者 > MySQL

PHP將mysql資料庫匯出為excel表

來源：互聯網
上載者：User
創建阿里雲帳戶，並獲得超過 40 款產品的免費試用版；而企業帳戶則可以享有總值 $1200 的免費試用版。立即註冊！

方法一，利用fputcsv

 代碼如下 複製代碼


// 輸出Excel檔案頭，可把user.csv換成你要的檔案名稱
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="user.csv"');
header('Cache-Control: max-age=0');

// 從資料庫中擷取資料，為了節省記憶體，不要把資料一次性讀到記憶體，從控制代碼中一行一行讀即可
$sql = 'select * from tbl where ……';
$stmt = $db->query($sql);

// 開啟PHP檔案控制代碼，php://output 表示直接輸出到瀏覽器
$fp = fopen('php://output', 'a');

// 輸出Excel列名資訊
$head = array('姓名', '性別', '年齡', 'Email', '電話', '……');
foreach ($head as $i => $v) {
// CSV的Excel支援GBK編碼，一定要轉換，否則亂碼
$head[$i] = iconv('utf-8', 'gbk', $v);
}

// 將資料通過fputcsv寫到檔案控制代碼
fputcsv($fp, $head);

// 計數器
$cnt = 0;
// 每隔$limit行，重新整理一下輸出buffer，不要太大，也不要太小
$limit = 100000;

// 逐行取出資料，不浪費記憶體
while ($row = $stmt->fetch(Zend_Db::FETCH_NUM)) {

$cnt ++;
if ($limit == $cnt) { //重新整理一下輸出buffer，防止由於資料過多造成問題
ob_flush();
flush();
$cnt = 0;
}

foreach ($row as $i => $v) {
$row[$i] = iconv('utf-8', 'gbk', $v);
}
fputcsv($fp, $row);
}

方法二，直接在瀏覽器用header輸出csv格式的資料

 代碼如下 複製代碼

<?php
/*串連資料庫*/
$DB_Server = "localhost";
$DB_Username = "root";
$DB_Password = "123456";
$DB_DBName = "mydb";  //目標資料庫名
$DB_TBLName = "mytable";  //目標表名
$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=utf8");
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("$titlen");

/*查詢資料庫*/
$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) . ",";
}
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] != "")
    $data .= "$row[$i]".$sep;
   else
    $data .= "".$sep; //處理空欄位
  }
 echo $data."n";
}
?>

例3，第二個差不多了

 代碼如下 複製代碼


//搜尋
    $start_time = strtotime($start_date);
    $end_time = strtotime($end_date);
    $sql = "select a.*,b.order_amount,b.money_paid from ".$ecs->table('invoice')." as a ".
                " left join ".$ecs->table('order_info')." as b on a.order_id=b.order_sn".
                " where a.add_time >=".$start_time." and a.add_time <=".$end_time." ";
    $temp_list = $db->getAll($sql);

    if($temp_list){//有資料
        $Html='<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><body>'.chr(13).chr(10);
        $Html.='<table width="700" border="1" align="center" cellpadding="2" cellspacing="1">
                            <tr align="center">
                                    <td align="center" nowrap="nowrap">時間：</td>
                                    <td align="center" nowrap="nowrap" colspan="9">'.$start_date.'~'.$end_date.'</td>
                            </tr>
                            <tr align="center">
                                     <td align="center" nowrap="nowrap">編號</td>
                                     <td align="center" nowrap="nowrap">發票類型</td>
                                     <td align="center" nowrap="nowrap">發票抬頭</td>
                                     <td align="center" nowrap="nowrap">發票內容</td>
                                     <td align="center" nowrap="nowrap">訂單號</td>
                                     <td align="center" nowrap="nowrap">金額</td>
                                     <td align="center" nowrap="nowrap">添加日期</td>
                                     <td align="center" nowrap="nowrap">收件者</td>
                                     <td align="center" nowrap="nowrap">連絡方式</td>
                                     <td align="center" nowrap="nowrap">地址</td>
                           </tr>';
            //取得合格數組
            for($i=0;$i<count($temp_list);$i++){
                 $temp_i = $i+1;
                 if($temp_list[$i][order_amount]==0){
                     $temp_money = $temp_list[$i][money_paid];
                 }else{
                     $temp_money = $temp_list[$i][order_amount];
                 }

                 $temp_time = date('Y-m-d', $temp_list[$i]['add_time']);
                 $Html.='<tr align="center">
                                     <td align="center" nowrap="nowrap">'.$temp_i.'</td>
                                     <td align="center" nowrap="nowrap">'.$temp_list[$i][type_name].'</td>
                                     <td align="center" nowrap="nowrap">'.$temp_list[$i][top].'</td>
                                     <td align="center" nowrap="nowrap">'.$temp_list[$i][content].'</td>
                                     <td align="center" nowrap="nowrap" style="vnd.ms-excel.numberformat:@">'.$temp_list[$i][order_id].'</td>
                                     <td align="center" nowrap="nowrap">'.$temp_money.'</td>
                                     <td align="center" nowrap="nowrap">'.$temp_time.'</td>
                                     <td align="center" nowrap="nowrap">'.$temp_list[$i][user_name].'</td>
                                     <td align="center" nowrap="nowrap">'.$temp_list[$i][mobile].' '.$temp_list[$i][tel].' </td>
                                     <td align="center" nowrap="nowrap">'.$temp_list[$i][address].'</td>
                           </tr>';
             }
             $Html.='</table>';
             $Html.='</body></html>';
             $mime_type = 'application/vnd.ms-excel';
             header('Content-Type: ' . $mime_type);
             header('Content-Disposition: attachment; filename="invoice.xls"');
             header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
             header('Pragma: public');
            echo $Html;

有時excel會自動把數字轉換格式，於是有些手機號碼，身份證之類的就亂了，因此可以在匯出時，先定義好

 代碼如下 複製代碼

<td align="center" nowrap="nowrap" style="vnd.ms-excel.numberformat:@">'.$temp_list[$i][order_id].'</td>

本文章原先以中文撰寫並發佈於 aliyun.com，亦設英文版本，僅作資訊用途。本網站不對文章的準確性，完整性或可靠性或其任何翻譯作出任何明示或暗示的陳述或保證。如對該文章有任何疑慮或投訴，請傳送電郵至 info-contact@alibabacloud.com 並提供相關疑慮或投訴的詳細說明。職員會於 5 個工作天內與您聯絡，一經驗證之後，即會刪除該侵權內容。

聯繫我們

該頁面正文內容均來源於網絡整理，並不代表阿里雲官方的觀點，該頁面所提到的產品和服務也與阿里云無關，如果該頁面內容對您造成了困擾，歡迎寫郵件給我們，收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容，歡迎發送郵件至： info-contact@alibabacloud.com 進行舉報並提供相關證據，工作人員會在 5 個工作天內聯絡您，一經查實，本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

  • Sales Support

    1 on 1 presale consultation

    Chat Contact Sales

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    Open a Ticket

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

    Learn More