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

数据量超过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');