laravel下的excle使用
安装
1、composer.json文件下require属性添加 "maatwebsite/excel": "^3.1"
2、composr update
3、配置文件app.php下providers数组中添加 ：Maatwebsite\Excel\ExcelServiceProvider::class,
aliases数组添加：'Excel' => Maatwebsite\Excel\Facades\Excel::class,
使用
-
导出使用
-
新建（封装）一个导出类
-
<?php namespace App\Exports; //use App\Invoice; use Maatwebsite\Excel\Concerns\FromArray; use Maatwebsite\Excel\Concerns\WithHeadings; //设置表头 use Maatwebsite\Excel\Concerns\ShouldAutoSize; //自适应长宽，只需要在类里面集成就可以 use Maatwebsite\Excel\Concerns\WithColumnWidths; //设置列宽 use Maatwebsite\Excel\Events\AfterSheet; use PhpOffice\PhpSpreadsheet\Style\Border; use Maatwebsite\Excel\Concerns\WithEvents; class Export implements FromArray,WithHeadings,WithColumnWidths,ShouldAutoSize,WithEvents { protected $invoices; //数组形式传递 protected $header; //设置头 protected $columnWidth; //设置列宽 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($invoices,$headers) { $this->invoices = $invoices; $this->header = $headers; } //定义数组格式内容 public function array(): array { return $this->invoices; } //设置表头 public function Headings(): array { return $this->header; } //设置列宽 public function columnWidths(): array { return [ 'A' => 30, 'B' => 30, 'C' => 30, ]; } 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->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->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 * [ * A1:K7 => '宋体' * ] */ public function setFont(array $font) { $this->font = array_change_key_case($font, CASE_UPPER); } /** * @return array * @2020/3/22 10:33 * [ * A1:K7 => true * ] */ public function setBold(array $bold) { $this->bold = array_change_key_case($bold, 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); } }
-
-
导入
-
新建（封装）导入类
-
<?php namespace App\Imports; use Illuminate\Support\Collection; use Maatwebsite\Excel\Concerns\ToCollection; class UsersImport implements ToCollection { public $data; protected $delTitle; //删除的行 /** * * @param $title integer //去掉几行标题 默认一行 */ public function __construct($delTitle = 1) { $this->delTitle = $delTitle; } /** * @param Collection $rows * @2020/3/23 9:53 */ public function collection(Collection $rows) { $this->delTitle($rows); //$rows 是数组格式 $this->data = $rows; } public function delTitle (&$rows) { $rows = $rows->slice($this->delTitle)->values(); } }
控制器调用
<?php namespace App\Http\Controllers; use Maatwebsite\Excel\Facades\Excel; use App\Exports\Export; use App\Models\User as userModel; use Illuminate\Http\Request; use App\Imports\UsersImport; class ExcleC extends Controller { //导出 public function user_export(Excel $excel){ $a = 111111111111111111111; $ex = new Export([['yi','er','san'],[(string)$a,'11123123adsadasd3eqdqdqdadadasda','我是大哥哈哈哈啊哈哈哈哈你是帅哥'],[date('Y-m-d H:i:s',time()),5,time()]],['测试表']); $ex->setFont(['A1:Z1265' => '宋体']); //设置字体 $ex->setFontSize(['A1:I1' => 24,'A2:Z1265' => 14]); //设置字体大小 $ex->setBold(['A1:C1' => true]); //加粗 $ex->setBold(['A2:C2' => true]); $ex->setMergeCells(['A1:C1']); //合并单元格 $ex->setBorders(['A1:C4'=>'#333333']); //设置边框颜色 return Excel::download($ex, 'test.xlsx'); } //导入，前提自己去新建一个文件上传，然后把路由定义到这里来就可以了 public function imports(Request $request) { $path = $request->file('file'); $delTitle = 1;//指定头行数 删除它 $excel = new UsersImport($delTitle); //Excel::import($excel, $path->getRealPath()); Excel::import($excel, 'D:\安装包\test (28).xlsx'); foreach ($excel->data->toArray() as $k => &$v){ foreach ($v as $key => $value){ if(empty($v[$key])){ unset($v[$key]); } } } unset($v); dd($excel->data->toArray()); //这个地方就可以获取到文件内的集合了 } } -
1651
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
