1、导出
class ExportService implements FromCollection,WithHeadings, WithEvents
{
protected $data;
protected $headings;
protected $columnWidth = [];//设置列宽 key:列 value:宽
protected $rowHeight = []; //设置行高 key:行 value:高
protected $mergeCells = []; //合并单元格 value:A1:K8
protected $font = []; //设置字体 key:A1:K8 value:Arial
protected $fontSize = []; //设置字体大小 key:A1:K8 value:11
protected $bold = []; //设置粗体 key:A1:K8 value:true
protected $background = []; //设置背景颜色 key:A1:K8 value:#F0F0F0F
protected $vertical = []; //设置定位 key:A1:K8 value:center
protected $sheetName; //sheet title
protected $borders = []; //设置边框颜色 key:A1:K8 value:#000000
//构造函数传值
public function __construct($data, $headings,$sheetName)
{
$this->data = $data;
$this->headings = $headings;
$this->sheetName = $sheetName;
$this->createData();
}
public function headings(): array
{
return $this->headings;
}
//数组转集合
public function collection()
{
return new Collection($this->data);
}
//业务代码
public function createData()
{
$this->data = collect($this->data)->toArray();
}
public function registerEvents(): array
{
return [
AfterSheet::class => function(AfterSheet $event) {
//设置区域单元格垂直居中
$event->sheet->getDelegate()->getStyle('A1:Z1265')->getAlignment()->setVertical('center');
//设置区域单元格水平居中
$event->sheet->getDelegate()->getStyle('A1:Z1265')->getAlignment()->setHorizontal('center');
//设置列宽
foreach ($this->columnWidth as $column => $width) {
$event->sheet->getDelegate()
->getColumnDimension($column)
->setWidth($width);
}
//设置行高,$i为数据行数
foreach ($this->rowHeight as $row => $height) {
$event->sheet->getDelegate()
->getRowDimension($row)
->setRowHeight($height);
}
//设置区域单元格垂直居中
foreach ($this->vertical as $region => $position) {
$event->sheet->getDelegate()
->getStyle($region)
->getAlignment()
->setVertical($position);
}
//设置区域单元格字体
foreach ($this->font as $region => $value) {
$event->sheet->getDelegate()
->getStyle($region)
->getFont()->setName($value);
}
//设置区域单元格字体大小
foreach ($this->fontSize as $region => $value) {
$event->sheet->getDelegate()
->getStyle($region)
->getFont()
->setSize($value);
}
//设置区域单元格字体粗体
foreach ($this->bold as $region => $bool) {
$event->sheet->getDelegate()
->getStyle($region)
->getFont()
->setBold($bool);
}
//设置区域单元格背景颜色
foreach ($this->background as $region => $item) {
$event->sheet->getDelegate()->getStyle($region)->applyFromArray([
'fill' => [
'fillType' => 'linear', //线性填充,类似渐变
'startColor' => [
'rgb' => $item //初始颜色
],
//结束颜色,如果需要单一背景色,请和初始颜色保持一致
'endColor' => [
'argb' => $item
]
]
]);
}
//设置边框颜色
foreach ($this->borders as $region => $item) {
$event->sheet->getDelegate()->getStyle($region)->applyFromArray([
'borders' => [
'allBorders' => [
'borderStyle' =>Border::BORDER_THIN,
'color' => ['argb' => $item],
],
],
]);
}
//合并单元格
$event->sheet->getDelegate()->setMergeCells($this->mergeCells);
if(!empty($this->sheetName)){
$event->sheet->getDelegate()->setTitle($this->sheetName);
}
}
];
}
/**
* @return array
* [
* 'B' => 40,
* 'C' => 60
* ]
*/
public function setColumnWidth (array $columnwidth)
{
$this->columnWidth = array_change_key_case($columnwidth, CASE_UPPER);
}
/**
* @return array
* [
* 1 => 40,
* 2 => 60
* ]
*/
public function setRowHeight (array $rowHeight)
{
$this->rowHeight = $rowHeight;
}
/**
* @return array
* [
* A1:K7 => '宋体'
* ]
*/
public function setFont (array $font)
{
$this->font = array_change_key_case($font, CASE_UPPER);
}
/**
* @return array
* [
* A1:K7 => true
* ]
*/
public function setBold (array $bold)
{
$this->bold = array_change_key_case($bold, CASE_UPPER);
}
/**
* @return array
* [
* A1:K7 => F0FF0F
* ]
*/
public function setBackground (array $background)
{
$this->background = array_change_key_case($background, CASE_UPPER);
}
/**
* @return array
* [
* A1:K7
* ]
*/
public function setMergeCells (array $mergeCells)
{
$this->mergeCells = array_change_key_case($mergeCells, CASE_UPPER);
}
/**
* @return array
* [
* A1:K7 => 14
* ]
*/
public function setFontSize (array $fontSize)
{
$this->fontSize = array_change_key_case($fontSize, CASE_UPPER);
}
/**
* @return array
* [
* A1:K7 => #000000
* ]
*/
public function setBorders (array $borders)
{
$this->borders = array_change_key_case($borders, CASE_UPPER);
}
/**
* 导出
* @param Request $request
* @return \Symfony\Component\HttpFoundation\BinaryFileResponse
*/
public function doExport(Request $request){
//设置表头
$row = [[
"id"=>'ID',
"name"=>'敏感词',
"type_name"=>'类型',
"created_at_format"=>'创建时间'
]];
$h = new SensitiveWordsHandler();
$res = $h->doExport();
$data = $res;//要导入的数据
$header = $row;//导出表头
$excel = new ExportService($data, $header,'words');
$excel->setColumnWidth(['B' => 20, 'C' => 20,'D'=>40]);
$excel->setBold(['A1:D1' => true]);
return Excel::download($excel , 'words_'.date('Y-m-d').'.xlsx');
}
2、导入
class ImportService implements ToCollection
{
public $data;
protected $delTitle;
/**
*
* @param $title integer //去掉几行标题 默认一行
*/
public function __construct($delTitle = 1)
{
$this->delTitle = $delTitle;
}
/**
* @param Collection $rows
*/
public function collection(Collection $rows)
{
$this->delTitle($rows);
$this->data = $rows;
}
public function delTitle (&$rows) {
$rows = $rows->slice($this->delTitle)->values();
}
}
/**
* 导入
* @param Request $request
* @return \Illuminate\Http\JsonResponse
* @throws \App\Exceptions\ApiException
*/
public function doImport(Request $request){
$params = $request->only(
'excel_file'
);
SensitiveWordsValidator::validate('admin_import_file_rule',$params);
$file = $params['excel_file'];
$delTitle = 1;//指定头行数 删除它
$excel = new ImportService($delTitle);
Excel::import($excel, $file->getRealPath());
$h = new SensitiveWordsHandler();
$data = $excel->data->toArray();
if(empty($data)) apiError("导入excel 内容读取为空",3204);
$res = $h->doImport($data);
return $this->output($res);
}