欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

【大数据量导出】 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;
}