mysql数据导出为excel表 php_PHP将mysql数据导出为Excel

项目需要，由于系统工作日志数据量很大，所以定期将历史日志导出为Excel。

功能目录：root@bupt925:/home/bupt/xuxueliang/OutputExcel# ll

total 24

drwxr-xr-x 6 root root 4096 10月 16 10:46 ./

d-wx-wx--x 9 1000 bupt 4096 10月 16 10:38 ../

drwxr-xr-x 2 root root 4096 10月 16 10:46 bin/

drwxr-xr-x 2 root root 4096 10月 16 10:36 conf/

drwxr-xr-x 2 root root 4096 10月 16 10:46 data/

drwxr-xr-x 2 root root 4096 10月 16 10:33 log/

功能代码：<?php

/*

Date:2013-10-16

author:xxl

function:对历史日志导出为excel文件

*/

include '../conf/connect_mysql.php';

include '../conf/conf.php';

header('Content-Type: application/vnd.ms-excel');

header('Content-Disposition: p_w_upload;filename="user.csv"');

header('Cache-Control: max-age=0');

$fiveago = mktime(date("H"),date("i"),date("s"),date("m")-1,date("d"),date("Y"));

$date = date("Y-m-d",$fiveago);

$sql = "select * from Record_Log where date

$sqlf = mysql_query($sql);

$file_name = "../data/Log_".$date.".csv";

$fp = fopen($file_name,'a');

$head = array('ID','用户名','时间','类型','井场名','工作内容','回执内容');

foreach($head as $i=>$v)

{

$head[$i] = iconv('utf-8','gbk',$v);

}

fputcsv($fp,$head);

$cnt = 0;

$limit = 100000;

while(($info = mysql_fetch_array($sqlf,MYSQL_NUM)))

{

$cnt ++ ;

if($cnt == $limit)

{

$cnt = 0;

ob_flush();

flush();

}

foreach($info as $i=>$v)

{

$info[$i] = iconv('utf-8','gbk',$v);

}

fputcsv($fp,$info);

}

if($ifdelete)

{

$sql = "delete from Record_Log where date

$sqlf = mysql_query($sql);

if($sqlf)

echo "Delete Success.\n";

else

echo "Delete Error.\n";

}

echo "Done\n";

?>

生成的csv文件：root@bupt925:/home/bupt/xuxueliang/OutputExcel/data# ll

total 12

drwxr-xr-x 2 root root 4096 10月 16 10:47 ./

drwxr-xr-x 6 root root 4096 10月 16 10:46 ../

-rw-r--r-- 1 root root 5564 10月 16 10:47 Log_2013-09-16.csv