PHP将mysql数据库导出为excel表

WBOY

Libérer： 2016-05-25 16:38:06

original

1986 Les gens l'ont consulté

利用php导出mysql数据库为excel表格的方法很多,最简单的就直接使用php fputcsv函数了,还有就是直接输入csv格式也是可以了,要生成excel标准格式我们需使用第三方插件了.

方法一,利用fputcsv,代码如下:

// 输出Excel文件头，可把user.csv换成你要的文件名  
header(&#39;Content-Type: application/vnd.ms-excel&#39;);  
header(&#39;Content-Disposition: attachment;filename="user.csv"&#39;);  
header(&#39;Cache-Control: max-age=0&#39;); 
 
// 从数据库中获取数据，为了节省内存，不要把数据一次性读到内存，从句柄中一行一行读即可  
$sql = &#39;select * from tbl where ……&#39;;  
$stmt = $db->query($sql); 
 
// 打开PHP文件句柄，php://output 表示直接输出到浏览器  
$fp = fopen(&#39;php://output&#39;, &#39;a&#39;); 
 
// 输出Excel列名信息  
$head = array(&#39;姓名&#39;, &#39;性别&#39;, &#39;年龄&#39;, &#39;Email&#39;, &#39;电话&#39;, &#39;……&#39;);  
foreach ($head as $i => $v) {  
// CSV的Excel支持GBK编码，一定要转换，否则乱码  
$head[$i] = iconv(&#39;utf-8&#39;, &#39;gbk&#39;, $v);  
} 
 
// 将数据通过fputcsv写到文件句柄  
fputcsv($fp, $head); 
 
// 计数器  
$cnt = 0;  
// 每隔$limit行，刷新一下输出buffer，不要太大，也不要太小  
$limit = 100000; 
 
// 逐行取出数据，不浪费内存  
while ($row = $stmt->fetch(Zend_Db::FETCH_NUM)) { 
//开源代码phprm.com 
$cnt ++;  
if ($limit == $cnt) { //刷新一下输出buffer，防止由于数据过多造成问题  
ob_flush();  
flush();  
$cnt = 0;  
} 
 
foreach ($row as $i => $v) {  
$row[$i] = iconv(&#39;utf-8&#39;, &#39;gbk&#39;, $v);  
}  
fputcsv($fp, $row);  
}

Copier après la connexion

方法二,直接在浏览器用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&#39;t connect.");
mysql_query("Set Names &#39;utf8&#39;");
$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 ("$title\n");
/*查询数据库*/
$sql = "Select * from $DB_TBLName";
$ALT_Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn&#39;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";
}

Copier après la connexion

例3, 第二个差不多了, 代码如下:

//搜索
$start_time = strtotime($start_date);
$end_time = strtotime($end_date);
$sql = "select a.*,b.order_amount,b.money_paid from " . $ecs->table(&#39;invoice&#39;) . " as a " . " left join " . $ecs->table(&#39;order_info&#39;) . " as b on a.order_id=b.order_s\n" . " where a.add_time >=" . $start_time . " and a.add_time <=" . $end_time . " ";
$temp_list = $db->getAll($sql);
if ($temp_list) { //有数据
$Html = &#39;<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>&#39; . chr(13) . chr(10);
$Html.= &#39;<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">&#39; . $start_date . &#39;~&#39; . $end_date . &#39;</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>&#39;;
//取得符合条件的数组
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(&#39;Y-m-d&#39;, $temp_list[$i][&#39;add_time&#39;]);
$Html.= &#39;<tr align="center"> 
 <td align="center" nowrap="nowrap">&#39; . $temp_i . &#39;</td> 
 <td align="center" nowrap="nowrap">&#39; . $temp_list[$i][type_name] . &#39;</td> 
 <td align="center" nowrap="nowrap">&#39; . $temp_list[$i][top] . &#39;</td> 
 <td align="center" nowrap="nowrap">&#39; . $temp_list[$i][content] . &#39;</td> 
 <td align="center" nowrap="nowrap" style="vnd.ms-excel.numberformat:@">&#39; . $temp_list[$i][order_id] . &#39;</td> 
 <td align="center" nowrap="nowrap">&#39; . $temp_money . &#39;</td> 
 <td align="center" nowrap="nowrap">&#39; . $temp_time . &#39;</td> 
 <td align="center" nowrap="nowrap">&#39; . $temp_list[$i][user_name] . &#39;</td> 
 <td align="center" nowrap="nowrap">&#39; . $temp_list[$i][mobile] . &#39; &#39; . $temp_list[$i][tel] . &#39; </td> 
 <td align="center" nowrap="nowrap">&#39; . $temp_list[$i][address] . &#39;</td> 
   </tr>&#39;;
}
$Html.= &#39;</table>&#39;;
$Html.= &#39;</body></html>&#39;;
$mime_type = &#39;application/vnd.ms-excel&#39;;
header(&#39;Content-Type: &#39; . $mime_type);
header(&#39;Content-Disposition: attachment; filename="invoice.xls"&#39;);
header(&#39;Cache-Control: must-revalidate, post-check=0, pre-check=0&#39;);
header(&#39;Pragma: public&#39;);
echo $Html;

Copier après la connexion

有时excel会自动把数字转换格式, 于是有些手机号码, 身份证之类的就乱了, 因此可以在导出时, 先定义好如下代码: '.$temp_list[$i][order_id].'