PHP将mysql数据库导出为excel表
利用php导出mysql数据库为excel表格的方法很多,最简单的就直接使用php fputcsv函数了,还有就是直接输入csv格式也是可以了,要生成excel标准格式我们需使用第三方插件了.
方法一,利用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)) { //开源代码phpfensi测试数据 $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://HdhCmsTestw3.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>
查看更多关于PHP将mysql数据库导出为excel表 - php高级应用的详细内容...
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did30493