我的Maatwebsite 导出案例
记录一个我的Maatwebsite 导出案例
官方文档有详细讲解
github:https://github.com/Maatwebsite/Laravel-Excel
使用文档:https://docs.laravel-excel.com/
service
public function export($model, $module = 'export', $request = [])
{
// 导出
if ($module == 'exportStudent') {
$list = $this->exportStudentData($request);
$sheet = new UserStudentExport($list);
return Excel::download($sheet, $module . '_' . time() . '.xlsx');
}
}
function exportStudentData($request){
//获取参数
$name = $request['name']!="null"?substr($request['name'],1):null;
$bed_id = $request['bed_id']!="null"?$request['bed_id']:null;
$number = $request['number']!="null"?substr($request['number'],1):null;
$nationality = $request['nationality']!="null"?substr($request['nationality'],1):null;
$politics = $request['politics']!="null"?substr($request['politics'],1):null;
$college = $request['college']!="null"?substr($request['college'],1):null;
$major = $request['major']!="null"?substr($request['major'],1):null;
$class = $request['class']!="null"?substr($request['class'],1):null;
$grade = $request['grade']!="null"?substr($request['grade'],1):null;
$system = $request['system']!="null"?substr($request['system'],1):null;
$level = $request['level']!="null"?$request['level']:null;
//获取数据
$queryUserStudent =UserStudent::query()
->with('bed')
// ->whereHas('bed',function ($query) use ($bed_id){
// $query->where('id','=',$bed_id);
// })
->when($name,function ($query) use ($name){
$query->where('name','like',$name);
})
->when($number,function ($query) use ($number){
$query->where('number','like',$number);
})
->when($nationality,function ($query) use ($nationality){
$query->where('nationality','like',$nationality);
})
->when($politics,function ($query) use ($politics){
$query->where('politics','like',$politics);
})
->when($college,function ($query) use ($college){
$query->where('college','like',$college);
})
->when($major,function ($query) use ($major){
$query->where('major','like',$major);
})
->when($class,function ($query) use ($class){
$query->where('class','like',$class);
})
->when($grade,function ($query) use ($grade){
$query->where('grade','like',$grade);
})
->when($system,function ($query) use ($system){
$query->where('system','like',$system);
})
->when($level,function ($query) use ($level){
$query->where('level','=',$level);
})
->get();
return $queryUserStudent;
}
export
<?php
namespace App\Exports;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\AfterSheet;
class UserStudentExport implements FromCollection, WithHeadings, WithMapping, WithEvents
{
public $data;
public function __construct( $data)
{
$this->data = $data;
}
public function registerEvents(): array
{
return [
AfterSheet::class => function (AfterSheet $event) {
//设置列宽 单个设置
$event->sheet->getDelegate()->getColumnDimension('A',)->setWidth(16);
$event->sheet->getDelegate()->getColumnDimension('B',)->setWidth(16);
$event->sheet->getDelegate()->getColumnDimension('C',)->setWidth(16);
$event->sheet->getDelegate()->getColumnDimension('D',)->setWidth(16);
$event->sheet->getDelegate()->getColumnDimension('E',)->setWidth(16);
$event->sheet->getDelegate()->getColumnDimension('F',)->setWidth(16);
$event->sheet->getDelegate()->getColumnDimension('G',)->setWidth(16);
$event->sheet->getDelegate()->getColumnDimension('H',)->setWidth(16);
$event->sheet->getDelegate()->getColumnDimension('I',)->setWidth(16);
$event->sheet->getDelegate()->getColumnDimension('J',)->setWidth(16);
$event->sheet->getDelegate()->getColumnDimension('K',)->setWidth(16);
$event->sheet->getDelegate()->getColumnDimension('L',)->setWidth(16);
//设置区域单元格垂直居中 A1:Z1265是一张表的大小
$event->sheet->getDelegate()->getStyle('A1:Z1265')->getAlignment()->setVertical('center');
//设置区域单元格水平居中
$event->sheet->getDelegate()->getStyle('A1:Z1265')->getAlignment()->setHorizontal('center');
//控制样式
$event->sheet->getDelegate()->getStyle('A1:L1')->applyFromArray([
'font'=>[
'name' => 'Arial',
'bold' => true,
'italic' => false,
'strikethrough' => false,
'color' => [
'rgb' => '000000'
]
],
'fill'=>[
'fillType' => 'linear', //线性填充,类似渐变
'rotation' => 45, //渐变角度
'startColor' => [
'rgb' => 'DCDCDC' //初始颜色
],
//结束颜色,如果需要单一背景色,请和初始颜色保持一致
'endColor' => [
'argb' => 'DCDCDC'
]
]
]);
//合并单元格
// $event->sheet->getDelegate()->mergeCells('A2:B3');
}
];
}
public function collection()
{
//如果这里不是集合就new一个集合出来 比如 return new Collection($this->data)
return $this->data;
}
public function headings(): array
{
return ['床位','姓名','学号', '性别', '民族', '政治面貌', '学院', '专业', '班级', '年级', '学制', '层次'];
}
public function map($row): array
{
return [
$row->bed?$row->bed->name:null,
$row->name,
$row->number,
$row->sex,
$row->nationality,
$row->politics,
$row->college,
$row->major,
$row->class,
$row->grade,
$row->system,
$row->level,
];
}
}