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

我就是我 2023-05-30 08:58 42阅读 0赞

### 文章目录 ###

*  需求
 *  问题
 *  解决

# 需求 #

导出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();
    
        }