牛骨文教育服务平台(让学习变的简单)
博文笔记

php导出超过10万行数据到excel

创建时间:2017-06-08 投稿人: 浏览次数:313

数据量超过10万,用php_excel导出很慢而且数据导入不全,还占空间,用csv格式导出,很快还不出错.

set_time_limit(0);
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment;filename="amazon_product_quantity.csv"");
header("Cache-Control: max-age=0");
$res = (new ListModel);
$counts = $res->query("select count(*) from test");
$limit = ceil($counts/1000);
// 打开PHP文件句柄,php://output 表示直接输出到浏览器
$fp = fopen("php://output", "a");
$head = array("id", "sno", "num", "name","text","img","small_img");
foreach ($head as $i => $v) {
// CSV的Excel支持GBK编码,一定要转换,否则乱码
$head[$i] =iconv("utf-8","gb2312//IGNORE",$v);

}
fputcsv($fp, $head);
$i = 1;
$n = 1;
while($n <= $limit){
$items = $res->selectAll($n,0);
foreach($items as $key => $val){
fputcsv($fp, $val);
$i++;
}
if($i>20000){//读取一部分数据刷新下输出buffer
ob_flush();
flush();
$i = 0;
}
$n++;
/*
if($n == 300){
break;
}
*/

}


导出csv文件后

发现记事本打开中文正常,但是excel打开中文乱码;

原因是 通过查看编码发现,导出的 CSV 程序文件是 UTF-8 无BOM编码格式,而我们通常使用 UTF-8 编码格式 都是有 BOM 的。吧执行导出的代码改为有bom编码,再次导出好了


csv文件的读取:

set_time_limit(0);
$file = fopen(APP_PATH."application/controllers/test.csv","r");
while ($data = fgetcsv($file)) {    //每次读取CSV里面的一行内容
//print_r($data); //此为一个数组,要获得每一个数据,访问数组下标即可
$goods_list[] = $data;
}
echo $goods_list[0][1];
fclose($file);


下面是用php_excel导出的


$res = (new ListModel);
$counts = $res->query("select count(*) from test");
$limit = ceil($counts/1000);
$i = 1;
$n = 1;
$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->setCellValue("A".$i, "id");
$objPHPExcel->getActiveSheet()->setCellValue("B".$i, "sno");
$objPHPExcel->getActiveSheet()->setCellValue("C".$i, "num");
$objPHPExcel->getActiveSheet()->setCellValue("D".$i, "name");
$objPHPExcel->getActiveSheet()->setCellValue("E".$i, "text");
$objPHPExcel->getActiveSheet()->setCellValue("F".$i, "img");
$objPHPExcel->getActiveSheet()->setCellValue("G".$i, "small_img");
$i++;
while($n <= $limit){
$items = $res->selectAll($n,0);
foreach($items as $key => $val){
$objPHPExcel->getActiveSheet()->setCellValue("A".$i, $val["id"]);
$objPHPExcel->getActiveSheet()->setCellValue("B".$i, $val["sno"]);
$objPHPExcel->getActiveSheet()->setCellValue("C".$i, $val["num"]);
$objPHPExcel->getActiveSheet()->setCellValue("D".$i, $val["name"]);
$objPHPExcel->getActiveSheet()->setCellValue("E".$i, $val["text"]);
$objPHPExcel->getActiveSheet()->setCellValue("F".$i, $val["img"]);
$objPHPExcel->getActiveSheet()->setCellValue("G".$i, $val["small_img"]);
if($i  == 10000){
sleep(1);
}
$i++;
}
$n++;
if($n == 300){
break;
}
}

$objPHPExcel->getActiveSheet()->setTitle("人员表"); //
$fileName = "ryuan";
header("Pragma: public");
header("Expires: 0");
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type:application/vnd.ms-execl");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");;
header("Content-Disposition: attachment;filename="".$fileName.".xls"");
header("Content-Transfer-Encoding:binary");
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel5");
$objWriter->save("php://output");

声明:该文观点仅代表作者本人,牛骨文系教育信息发布平台,牛骨文仅提供信息存储空间服务。