【大数据量导出】 PHP导出上万上百万条数据
程序员文章站
2022-07-13 13:21:14
...
直接po代码:
超过5000行用第一段代码,样式不好看,但是导出大数据量基本没问题。
/**
* @Author echo
* @DateTime 2018-11-23
* @description 超过5000行数据用此方法
* @param [array] $export_data [导出的]
* @param [int] $count [导出数据的总行数]
* @return [source] [文件流]
* 例:
* $export_data['filename'] = '测试';
$export_data['cellName'] = array('编号','姓名','性别');
$export_data['sql'] = "select name from user"
);
*/
public static function batchExport($export_data, $count)
{
set_time_limit(0);
ini_set('memory_limit', '128M');
$fileName = $export_data['filename']; //文件名
$title = $export_data['cellName']; //标题名称
$sql = $export_data['sql']; //需要拼接的sql
header('Content-Type: application/vnd.ms-execl');
header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');
//打开php标准输出流
//以写入追加的方式打开
$fp = fopen('php://output', 'a');
//一次取5000条数据
$step = ceil($count/5000);
foreach($title as $key => $item) {
$title[$key] = iconv('UTF-8', 'GBK', $item);
}
//将标题写到标准输出中
fputcsv($fp, $title);
$order = 'sum(ga.num) DESC';
for($s = 0; $s < $step; $s++) {
$page_count = $s;
$page_size = 5000;
if ($page_count == $step-1){
$page_size = $step*5000-$count;
}
$data = Db::query($sql, [$page_count, $page_size]);
foreach ($data as $k => &$v) {
//避免科学计数法导出数据错误,给每个数据加上\t
array_walk($v,function (&$s, $k, $prefix = "\t") {
$s = str_pad($s, strlen($prefix) + strlen($s), $prefix, STR_PAD_LEFT);}
);
foreach ($v as $key => &$value) {
//这里必须转码,不然会乱码
$v[$key] = iconv('UTF-8', 'GBK', $value);
}
fputcsv($fp, $v);
}
//每5000条数据就刷新缓冲区
ob_flush();
flush();
}
return;
}
少于5000行用下面的代码,虽然处理量不大,但是能做出好看的样式
/**
* [数据导出公共方法]
* @param [type] $export_data['filename'] [文件名]
* @param [type] $export_data['data'] [导出数据]
* @param [type] $export_data['cellName'] [导出字段]
* 例:
* $export_data['filename'] = '测试';
$export_data['data'] = array(
array('1','miss','男'),
array('2','echo','女')
);
$export_data['cellName'] = array('编号','姓名','性别');
*/
public static function Export($export_data)
{
//创建一个PHPExcel对象
$objPHPExcel = new \PHPExcel();
$startLine = 3; //数据开始的行数第三行,前两行分别为标题、字段
$cols = count($export_data['data']); //数据条数
$len = count($export_data['cellName']);//字段数
// echo $len;die;
$cellKey = array(
'A','B','C','D','E','F','G','H','I','J','K','L','M',
'N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
'AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM',
'AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ'
);
$objPHPExcel->getActiveSheet()
->getStyle('A1')
->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//设置标题文字大小
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(15);
//写入标题 并给标题、字段加底色
for ($i=0; $i < $len; $i++) {
//设置每一列宽度
$objPHPExcel->getActiveSheet()->getColumnDimension($cellKey[$i])->setWidth(15);
//设置标题高度
$objPHPExcel->getActiveSheet()->getDefaultRowDimension($cellKey[$i] . ($startLine-2))->setRowHeight(40);
//设置字段高度
$objPHPExcel->getActiveSheet()->getDefaultRowDimension($cellKey[$i] . ($startLine-1))->setRowHeight(25);
//设置标题颜色
$objPHPExcel->getActiveSheet()->getStyle($cellKey[$i] . ($startLine-2))->getFill()- >setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FF9CC3E5');
//设置字段颜色
$objPHPExcel->getActiveSheet()->getStyle($cellKey[$i] . ($startLine-1))->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFBDD7EE');
//写入字段名称
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellKey[$i] . ($startLine-1), $export_data['cellName'][$i], \PHPExcel_Cell_DataType::TYPE_STRING);
//设置字段右侧边框
$objPHPExcel->getActiveSheet()->getStyle($cellKey[$i] . ($startLine-1))->getBorders()->getRight()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
}
//设置表格样式 (合并单元格)
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $export_data['filename'], \PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->getActiveSheet()->mergeCells('A1:'.$cellKey[count($export_data['cellName'])-1].'1');
//冻结标题
$objPHPExcel->getActiveSheet()->freezePane('A2');
$objPHPExcel->getActiveSheet()->freezePane('A3');
//写入数据
for ($i=0; $i < $cols; $i++) {
// echo $i.'</br>';
$objPHPExcel->getActiveSheet()->getStyle('A'. ($i+$startLine))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
for ($j = 0; $j < $len; $j++) {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellKey[$j] . ($i+$startLine), $export_data['data'][$i][$j].'', \PHPExcel_Cell_DataType::TYPE_STRING);
}
}
// 文件中的第一个表格的名
$objPHPExcel->getActiveSheet()->setTitle($export_data['filename']); // 设置操作的表是第一个表
$objPHPExcel->setActiveSheetIndex(0); // 直接在浏览器中输出文件(Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename='.iconv("utf-8","gb2312",$export_data['filename']).'.xlsx');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=0');
// If you're serving to IE over SSL, then the following may be needed
header('Cache-Control: max-age=1');
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
return;
}
下一篇: 大数据量删除的思考(二)