一.起因：近段时间老是被公司excel导入导出大数据的问题缠绕，今天刚好修复bug，引入了yield方法，在此，做了一个总结。
二.代码实现
“/**
* 调班记录和导出
* @param $params
* @return string
*/
public static function getEmployeeShiftRecord($params)
{
set_time_limit(0);
$pageSize = $params[‘per_page’] ?? 10;
$where = [
‘r.company_id’ => $params[‘company_id’],
‘r.del’ => 0
];
$records = DB::table(‘attendance_employee_shift_record as r’)
->select(‘r.*’,’t.name as team_name’,’e.deptid’,’e.company_branch_id as sub_id’,’e.fullName’,’e.uuid’)
->leftJoin(‘attendance_team as t’,’t.id’,’=’,’r.team_id’)
->leftJoin(’employees as e’,’e.id’,’=’,’r.eid’)
->where($where)
->where(function($query) use($params) {
//判断是否有关键词
if (!empty($params[‘keyword’])) {
$keyEid = Employee::where(‘fullName’, ‘like’, ‘%’.$params[‘keyword’].’%’)->value(‘id’);
$query->where(‘e.fullName’, ‘like’, ‘%’.$params[‘keyword’].’%’)->orWhere(‘r.eid’,$keyEid)->orWhere(‘r.target_eid’,$keyEid);
}
//判断考勤组
if(!empty($params[‘group_id’])){
$query->where(‘r.group_id’,$params[‘group_id’]);
}
//权限判断
if(!empty($params[‘permission_range’])){
$uuids = self::getEmployeeUuidByPermissionRange($params[‘permission_range’],$params[‘company_id’]);
if(count($uuids)){
$query->whereIn(‘e.uuid’, $uuids);
}
}
if(!empty($params[‘start_time’]) && !empty($params[‘end_time’])){
$date = [date(‘Y-m-d’,strtotime($params[‘start_time’])),date(‘Y-m-d’,strtotime($params[‘end_time’]))];
$query->whereBetween(‘day’,$date)->orWhereBetween(‘target_day’,$date);
}
})
->orderBy(‘created_at’,’desc’)
->paginate($pageSize);
## 导出处理
if(!empty($params[‘export’])){
//获取调班记录数据
$data = array();
//组装表头内容
$title = [
‘fullName’=> ‘调班员工’,
‘subName’ => ‘公司/中心’,
‘deptName’ => //’部门’,
‘group_name’ => ‘考勤组’,
‘team_name’ => ‘班组’,
‘day’ => ‘调班日期’,
‘class_name’ => ‘调班班次’,
‘target_employee_name’ => ‘目标员工’,
‘target_day’ => ‘目标日期’,
‘target_class_name’ => ‘目标班次’,
];
##yield引入
$records = self::yieldData($records);
foreach ($records as $key => &$record ){
foreach ($title as $k => $value){
$data[$key][$k] = $record->$k;
}
}
array_unshift($data, $title);
$remark = trans(‘attendance.1048’); //调班记录表
$extension = ‘xlsx’;
\Excel::create($remark, function($excel) use($data) {//调班
$excel->sheet(‘store’, function ($sheet) use ($data) {
//超时处理
$cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_in_memory;
\PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
$sheet->setWidth(array(
‘A’ => 10,
‘B’ => 30,
‘C’ => 20,
‘D’ => 20,
‘E’ => 20,
));
$sheet->cells(‘A:Z’, function ($cells) {
$cells->setAlignment(‘center’);
$cells->setValignment(‘center’);
});
$sheet->fromArray($data, null, ‘A1’, true, false);
});
})->export($extension);
return true;
}
return $records;
}
//引入yield
public static function yieldData($data)
{
foreach ($data as $datum){
yield $datum;
}
}
“`- 总结为什么要引入yield
yield生成器
– 当没引入yield时，如果使用数组，那么你就是把所有excel表格数据先存入数组，而数组是占服务器的内存，当数据量达到一定量时，服务器就会瘫痪。
– 当引入yield时，那么excel表数据就相当于一个函数（如：yieldData）,你不调用它，它就不会占用内存。假如，你调用它（yieldData），那么yield生成器就会把excel数据一行一行的读取并且同时清理掉你调用的那一行的内存（即是读一行清理一行内存）。
– 因此，它在使用时，就几乎不占用内存,这样效率将会大大提高。
转载请注明：XAMPP中文组官网 » PHP导出excel大数据时引用yield的性能优化