laravel php 50W数据导出excel 分批分页导出

文章目录

• 需求
• 问题
• 解决

需求

导出50W左右的数据导excel表

问题

1. 使用phpexcel等插件，碰到数据量大很慢，可能能花半个小时以上
2. 数据量大查询慢
3. 内存不足
4. 执行超时

解决

1. 使用原生csv导出
2. 设置脚本超时和内存，进行加大内存，不限制超时时间
3. 进行分页查询

 public function bp_out(Request $request){
        error_reporting(E_ALL);
        ini_set('display_errors', TRUE);
        ini_set('display_startup_errors', TRUE);
        ini_set("memory_limit", "2048M");
        set_time_limit(0);
        if ($request->has('date')){
            $d1 =  strtotime(date("Y-m-d",strtotime($request->date." day")));
            $d2 =  strtotime(date("Y-m-d",strtotime(($request->date+1)." day")));
            $filename ="电池数据_".date("Y-m-d",strtotime($request->date." day")).".csv";
//            return $filename;
        }
        if ($request->has('d1')){
            $d1 = strtotime($request->d1);
            $d2 = strtotime($request->d2);

            $filename = "电池数据_".$request->d1."->".$request->d2.".csv";
        }
        $count = Logbp::whereBetween('client_time',[$d1,$d2])->count();
        $limit = 10000;/限制查询
        $page =ceil($count/$limit);//页数
        header('Content-Description: File Transfer');
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="'. $filename .'"');
        header('Expires: 0');
        header('Cache-Control: must-revalidate');
        header('Pragma: public');
        $fp = fopen('php://output', 'a');//打开output流
        ob_clean();
        $title =['车辆识别码','主板SN','车身常电连接状态','是否有效','电池电压V','发送时间','接收时间'];
        mb_convert_variables('GBK', 'UTF-8', $title);
        fputcsv($fp, $title);
        for ($i=0;$i<$page;$i++){
            $offset = $i*$limit;
            $dbs = DB::connection('mysql_online')->select("SELECT a.hwid,b.vin,a.charge,a.valid,a.voltage,from_unixtime(a.client_time,'%Y-%m-%d %H:%i:%S') AS start ,from_unixtime(a.server_time,'%Y-%m-%d %H:%i:%S') as end FROM tb_logs_bp a LEFT JOIN tb_equipment b on a.hwid = b.hwid WHERE a.client_time > {$d1} AND a.client_time<{$d2} limit {$limit} offset {$offset}" );
            foreach ($dbs as $db){
                $db->charge= $this->checkcharge($db->charge);
                $db->valid = $this->checkvalid($db->valid);
                $db->voltage  = $db->voltage/10;
                $t = substr($db->vin,0,1);
                if (!$t){
                    $db->vin ="`". $db->vin;
                }
                $data=[$db->vin,$db->hwid,$db->charge,$db->valid,$db->voltage,$db->start,$db->end];
                mb_convert_variables('GBK', 'UTF-8', $data);
                fputcsv($fp, $data);
            }
            unset($dbs);
        }
        ob_flush();
        flush();
        fclose($fp);
        exit();

    }

版权声明：本文为weixin_43674113原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文链接：https://blog.csdn.net/weixin_43674113/article/details/102977134