PHP export mysql database to excel table_PHP tutorial

There are many ways to use php to export mysql database into excel tables. The simplest one is to directly use the php fputcsv function. You can also directly enter the csv format. To generate the excel standard format, we need to use a third-party plug-in

Method 1, use fputcsv

The code is as follows

Copy code

代码如下

复制代码

// 输出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); }

// Output the Excel file header, you can replace user.csv with the file name you want
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="user.csv"');
header('Cache-Control: max-age=0');

// Get data from the database. In order to save memory, do not read the data into the memory at once. Just read it line by line from the handle
$sql = 'select * from tbl where ……';
$stmt = $db->query($sql);

代码如下

复制代码

/*连接数据库*/ $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 if(!isset($row[$i])) $data .= "NULL".$sep; //处理NULL字段 elseif ($row[$i] != "") $data .= "$row[$i]".$sep; else $data .= "".$sep; //处理空字段 } echo $data."n"; } ?>

// Open the PHP file handle, php://output means output directly to the browser
$fp = fopen('php://output', 'a'); //Output Excel column name information
$head = array('Name', 'Gender', 'Age', 'Email', 'Telephone', '……');
foreach ($head as $i => $v) {
// Excel for CSV supports GBK encoding, it must be converted, otherwise the code will be garbled
$head[$i] = iconv('utf-8', 'gbk', $v);
} //Write data to the file handle through fputcsv
fputcsv($fp, $head); // Counter
$cnt = 0;
// Every $limit line, refresh the output buffer, not too big, not too small
$limit = 100000; // Fetch data line by line without wasting memory
while ($row = $stmt->fetch(Zend_Db::FETCH_NUM)) { $cnt ++;
if ($limit == $cnt) { //Refresh the output buffer to prevent problems caused by too much data
ob_flush();
flush();
$cnt = 0;
} foreach ($row as $i => $v) {
$row[$i] = iconv('utf-8', 'gbk', $v);
}
fputcsv($fp, $row);
}

Method 2, directly use the header to output data in csv format in the browser

The code is as follows

Copy code

/*Connect to database*/ $DB_Server = "localhost"; $DB_Username = "root"; $DB_Password = "123456"; $DB_DBName = "mydb"; //Target database name $DB_TBLName = "mytable"; //Target table name $Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect."); mysql_query("Set Names 'utf8'");<🎜> <🎜>$savename = date("YmjHis"); //Export excel file name $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");<🎜> <🎜>/*Write remark information*/ $now_date = date("Y-m-j H:i:s"); $title = "Database name: $DB_DBName, data table: $DB_TBLName, backup date: $now_date"; echo("$titlen");<🎜> <🎜>/*Query database*/ $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());<🎜> <🎜>/*Write table field name*/ for ($i = 0; $i < mysql_num_fields($result); $i++) { echo mysql_field_name($result,$i) . ","; } echo "n";<🎜> <🎜>/*Write table data*/ $sep = ",t"; while($row = mysql_fetch_row($result)) { $data = ""; for($i=0; $i if(!isset($row[$i])) $data .= "NULL".$sep; //Processing NULL fields elseif ($row[$i] != "") $data .= "$row[$i]".$sep; else $data .= "".$sep; //Processing empty fields } echo $data."n"; } ?>

Example 3, the second one is almost done

The code is as follows

Copy code

//Search
$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=''.chr(13).chr(10);
        $Html.='

                           
                                   
                                   
                           
                           
                                    
                                    
                                    
                                    
                                    
                                    
                                    
                                    
                                    
                                    
                           ';
            //取得符合条件的数组
            for($i=0;$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.='

                                    
                                    
                                    
                                    
                                    
                                    
                                    
                                    
                                    
                                    
                           ';
             }
             $Html.='

时间：	'.$start_date.'~'.$end_date.'
编号	发票类型	发票抬头	发票内容	订单号	金额	添加日期	收件人	联系方式	地址
'.$temp_i.'	'.$temp_list[$i][type_name].'	'.$temp_list[$i][top].'	'.$temp_list[$i][content].'	'.$temp_list[$i][order_id].'	'.$temp_money.'	'.$temp_time.'	'.$temp_list[$i][user_name].'	'.$temp_list[$i][mobile].' '.$temp_list[$i][tel].'	'.$temp_list[$i][address].'

';
             $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;

Sometimes excel will automatically convert the format of numbers, so some mobile phone numbers, ID cards, etc. will be messed up, so you can define them first when exporting

The code is as follows

代码如下	复制代码
		'.$temp_list[$i][order_id].'

Copy code

'.$temp_list[$i][order_id].'

http://www.bkjia.com/PHPjc/632921.htmlwww.bkjia.comtrue

http: //www.bkjia.com/PHPjc/632921.html

TechArticle

There are many ways to use php to export mysql database to excel table. The simplest one is to directly use the php fputcsv function. You can also directly enter the csv format. To generate excel standard format...