雨橙_Robinn.top_#PHP应用篇#--PHP导出MySQL数据到Excel报表

最近有项目需求，要求可以从外部随时很方便提取报名数据。决定用PHP实现一个数据提取接口

首先开通服务器数据库访问权限。

然后开始写代码：

要生成Excel报表文件需要定义好header头

Header("Content-type:application/octet-stream");
Header("Accept-Ranges:bytes");
Header("Content-type:application/vnd.ms-excel");  
Header("Content-Disposition:attachment;filename=数据提取.xls");

设置数据库连接信息,ip地址,用户名,密码,选择数据库及设置UTF-8编码

$con = mysql_connect("ip地址","用户名","密码");
mysql_select_db("waphzboainetnew");
mysql_query("set names utf8");

最后我们通过SQL语句查询获取数据集及设置表头

$sql = "selects id,name,sex,age,date,time,hometel,ill,ip,email,address from sj_yuyue where 1=1 and name<>'' ".$wheresql." order by id desc";
$result = mysql_query($sql,$con);
$headers = mb_convert_encoding("编号\t姓名\t性别\t年龄\t提交时间\t手机号码\t描述\t来路IP\t来路URL\tFLAG", "gb2312" , "utf-8");

最后我们将数据循环输出到Excel表格

while($rs=mysql_fetch_array($result)){
 echo "\n";
 //iconv("gb2312","utf-8//IGNORE",$str)
 //$uname = mb_convert_encoding($rs['name'], "gb2312" , "utf-8"); 
 $uname = iconv("utf-8","gbk//IGNORE",$rs['name']);
 $usex = mb_convert_encoding($rs['sex'], "gb2312" , "utf-8");
 $uage = mb_convert_encoding($rs['age'], "gb2312" , "utf-8");
 $datetime = mb_convert_encoding($rs['date']." ".$rs['time'], "gb2312" , "utf-8");
 $uhometel = mb_convert_encoding($rs['hometel'], "gb2312" , "utf-8");
 $uill = mb_convert_encoding($rs['ill'], "gb2312" , "utf-8");
 $uip = mb_convert_encoding($rs['ip'], "gb2312" , "utf-8");
 $uaddr = mb_convert_encoding($rs['address'], "gb2312" , "utf-8");
 $uemail = mb_convert_encoding($rs['email'], "gb2312" , "utf-8"); 
 $uage = str_replace("\"","",$uage);
 $uill = str_replace("undefined","",$uill);
 $uill = str_replace("&ltbr","",$uill);
 $uill = str_replace("&gt","|",$uill);
 $uill = str_replace(array("/r", "/n", "/r/n"), "", $uill);
 $uill = str_replace(PHP_EOL, '', $uill);
 
 echo $rs['id']."\t".$uname."\t".$usex."\t".$uage."\t".$datetime."\t".$uhometel."\t".$uill."\t".$uip."\t".$uaddr."\t".$uemail;
}

完整代码如下：

<?php
Header("Content-type:application/octet-stream");
Header("Accept-Ranges:bytes");
Header("Content-type:application/vnd.ms-excel");  
Header("Content-Disposition:attachment;filename=数据提取.xls");
$con = mysql_connect("ip地址","用户名","密码");
mysql_select_db("waphzboainetnew");
mysql_query("set names utf8");
$k = $_GET["k"];
$wheresql = "";
if ($k<>""){
	$wheresql = "and address like '%".$k."%'";
}
$sql = "selects id,name,sex,age,date,time,hometel,ill,ip,email,address from sj_yuyue where 1=1 and name<>'' ".$wheresql." order by id desc";
$result = mysql_query($sql,$con);
$headers = mb_convert_encoding("编号\t姓名\t性别\t年龄\t提交时间\t手机号码\t描述\t来路IP\t来路URL\tFLAG", "gb2312" , "utf-8"); 
echo $headers;
while($rs=mysql_fetch_array($result)){
 echo "\n";
 //iconv("gb2312","utf-8//IGNORE",$str)
 //$uname = mb_convert_encoding($rs['name'], "gb2312" , "utf-8"); 
 $uname = iconv("utf-8","gbk//IGNORE",$rs['name']);
 $usex = mb_convert_encoding($rs['sex'], "gb2312" , "utf-8");
 $uage = mb_convert_encoding($rs['age'], "gb2312" , "utf-8");
 $datetime = mb_convert_encoding($rs['date']." ".$rs['time'], "gb2312" , "utf-8");
 $uhometel = mb_convert_encoding($rs['hometel'], "gb2312" , "utf-8");
 $uill = mb_convert_encoding($rs['ill'], "gb2312" , "utf-8");
 $uip = mb_convert_encoding($rs['ip'], "gb2312" , "utf-8");
 $uaddr = mb_convert_encoding($rs['address'], "gb2312" , "utf-8");
 $uemail = mb_convert_encoding($rs['email'], "gb2312" , "utf-8"); 
 $uage = str_replace("\"","",$uage);
 $uill = str_replace("undefined","",$uill);
 $uill = str_replace("&ltbr","",$uill);
 $uill = str_replace("&gt","|",$uill);
 $uill = str_replace(array("/r", "/n", "/r/n"), "", $uill);
 $uill = str_replace(PHP_EOL, '', $uill);
 
 echo $rs['id']."\t".$uname."\t".$usex."\t".$uage."\t".$datetime."\t".$uhometel."\t".$uill."\t".$uip."\t".$uaddr."\t".$uemail;
}
?>

注意：下面mb_convert_encoding函数主要作用就解决输出到Excel表格中的中文乱码问题。用这个函数转码即可。

mb_convert_encoding($rs['sex'], "gb2312" , "utf-8")

文中内容仅供学习交流，敬请参考！

posted at 2016-07-20

雨橙

中国.四川.成都