mysql如何将数据库导出到excel

方便快捷的PHP连接MySQL数据库并导出Excel文件

废话不多说，直接上代码

$host = '127.0.0.1';

$username = 'username';

$passwd = 'passwd';

$dbname = 'dbname';

$port = 3306;

$db = mysqli_connect($host,$username,$passwd,$dbname,$port);

$sql = "select id,username,sex,age from table_name ORDER BY id DESC";

mysqli_query($db,'set names utf8');

$result = mysqli_query($db,$sql);

$arr=array();//定义空数组

while($row =mysqli_fetch_array($result)){

$list['id'] = $row['id'];

$list['username'] = $row['username'];

$list['sex'] = $row['sex'];

$list['age'] = $row['age'];

array_push($arr,$list);

}

createtable($arr,"文件名");//调用生成Excel文件方法

function createtable($list,$filename){

header("Content-type:application;);

header("Content-Disposition:filename=".$filename.".xls");

$strexport = "编号\t姓名\t性别\t年龄\r";

foreach ($list as $row){

$strexport.=$row['id']."\t";

$strexport.=$row['username']."\t";

$strexport.=$row['sex']."\t";

$strexport.=$row['age']."\r";

}

//$strexport=iconv('UTF-8',"GB2312//IGNORE",$strexport); //需要转码的，请打开此处编码转换

exit($strexport);

}

欲了解更多，请关注订阅我们的头条号：IT点点滴