使用 Laravel Excel 实现 Excel/CSV 文件导入导出
简介
Laravel Excel 在 Laravel 5 中集成 PHPOffice 套件中的 PHPExcel，从而方便我们以优雅的、富有表现力的代码实现Excel/CSV文件的导入和导出。
该项目的GitHub地址是：https://github.com/SpartnerNL/Laravel-Excel。
本文我们将在Laravel中使用Laravel Excel简单实现Excel文件的导入和导出。
安装&配置
使用Composer安装依赖
首先在Laravel项目根目录下使用Composer安装依赖：
composer require maatwebsite/excel配置
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config执行成功后会在config目录下生成一个配置文件excel.php。
具体使用参考官方文档：https://docs.laravel-excel.com/3.1/getting-started/
Excel 导入教程
新建导入文件，导入导出业务代码尽量不要和原来业务耦合。我们拿官网 user 模块举例
# toModel 模式
php artisan make:import -m App\Models\Users UsersImport
# ToCollection 模式
php artisan make:import UsersImport会在 app 目录下创建 Exports 目录
.
├── app
│ ├── Imports
│ │ ├── UsersImport.php
│
└── composer.jsonUsersImport.php 代码内容
<?php
namespace App\Imports;
use App\User;
use Illuminate\Support\Facades\Hash;
use Maatwebsite\Excel\Concerns\ToModel;
class UsersImport implements ToModel
{
/**
* @param array $row
*
* @return User|null
*/
public function model(array $row)
{
return new User([
'name' => $row[0],
'email' => $row[1],
'password' => Hash::make($row[2]),
]);
}
}业务控制器中调用
use App\Imports\UsersImport;
use Maatwebsite\Excel\Facades\Excel;
use App\Http\Controllers\Controller;
class UsersController extends Controller
{
public function import()
{
Excel::import(new UsersImport, 'users.xlsx');
}
}需要说明的是，上面所用的模式是 toModel，不需要手动去调用 save 方法，如果需要手动控制存储过程，请使用下列方法。
<?php
namespace App\Imports;
use App\User;
use Illuminate\Support\Facades\Hash;
//替换 toModel
use Maatwebsite\Excel\Concerns\ToCollection;
class UsersImport implements ToCollection
{
/**
* @param Collection $collection
*/
public function collection(Collection $collection)
{
$data = $collection->toArray();
//如果需要去除表头
unset($data['0']);
if (count($data) > 1) {
$this->createData($data);
}
}
// 具体业务代码
public function createData($rows)
{
//todo
}
}Excel 导入基本功能到这基本完成，应该可以满足80%业务需求。如果有更多需求请继续阅读，下面将介绍分块导入、多表导入。
分块导入
如果 excel 数据量比较大，不适合一次性导入数据库，可以通过按量分块导入的方式节约内存。
按 1000 条为基准取出导入
namespace App\Imports;
use App\User;
use Maatwebsite\Excel\Concerns\ToModel;
//新增
use Maatwebsite\Excel\Concerns\WithBatchInserts;
use Maatwebsite\Excel\Concerns\WithChunkReading;
class UsersImport implements ToModel, WithBatchInserts, WithChunkReading
{
public function model(array $row)
{
return new User([
'name' => $row[0],
]);
}
//批量导入1000条
public function batchSize(): int
{
return 1000;
}
//以1000条数据基准切割数据
public function chunkSize(): int
{
return 1000;
}
}多 sheet 导入
和导出比较类似，需要两步操作，第一步读取整体 excel 结构，第二步完成对应表数据导入。
第一个文件 UsersImport.php
namespace App\Imports;
use Maatwebsite\Excel\Concerns\WithMultipleSheets;
class UsersImport implements WithMultipleSheets
{
public function sheets(): array
{
//这里需要注意的是键，这个键可以是sheet表的名称，比如 'sheet1'=> new FirstSheetImport()
return [
0 => new FirstSheetImport(),
1 => new SecondSheetImport(),
];
}
}这里我没有找到获取所有 sheet 的方法，所以只能一个个指定，如果你调用的方法是一致的，可以参考以下我的写法。如果你有更好的方式，欢迎交流。
public function sheets(): array
{
$sheet = [];
for ($i=1; $i<=26; $i++) {
$sheet[$i] = new CustomSheetImport();
}
return $sheet;
}第二个文件处理数据
namespace App\Imports;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
class FirstSheetImport implements ToCollection
{
public function collection(Collection $rows)
{
//todo
}
}其他比如数据验证之类的方法我觉得还是不要使用它提供的方式，大部分业务环境不需要这些额外繁琐的东西。
Excel 导出教程
新建导出文件，导入导出业务代码尽量不要和原来业务耦合。我们拿官网 user 模块举例
使用make:export命令在 p/Exports 创建导出类
php artisan make:export UsersExport --model=User会在 app 目录下创建 Exports 目录
.
├── app
│ ├── Exports
│ │ ├── UsersExport.php
│
└── composer.jsonUsersExport.php 代码内容
<?php
namespace App\Exports;
use App\User;
use Maatwebsite\Excel\Concerns\FromCollection;
class UsersExport implements FromCollection
{
public function collection()
{
return User::all();
}
}业务控制器中调用此导出
<?php
namespace App\Http\Controllers;
use App\Exports\UsersExport;
use Maatwebsite\Excel\Facades\Excel;
class UsersController extends Controller
{
public function export()
{
return Excel::download(new UsersExport, 'users.xlsx');
}
}很方便简单是不是。这样可以把 user 表中所有内容都导入 excel 。很显然你的业务不会如此简单，那就继续。
Laravel Excel 支持查询语句导出、数组导出、视图表格导出，这些可以具体查看文档。
我们通常情况下需要组装业务数据，集合导出可以作为通用的导出方案。
<?php
namespace App\Exports;
use App\User;
use Maatwebsite\Excel\Concerns\FromCollection;
class UsersExport implements FromCollection
{
protected $data;
//构造函数传值
public function __construct($data)
{
$this->data = $data;
}
//数组转集合
public function collection()
{
return new Collection($this->createData());
}
//业务代码
public function createData()
{
//todo 业务
}
}createData 方法返回的数据格式如下
return [
['编号', '姓名', '年龄']
[1, '小明', '18岁'],
[4, '小红', '17岁']
];需要注意的是，这里组装了 excel 的表头，这也是比较方便的地方。
如此，简单的业务导出就完成了，应该可以满足80%需求，接下来我们继续，比如单元格格式化、自动适应、设置宽高、导出图片、多 sheet 表等功能。
单元格格式化
有时候我们需要对单元格处理文本、数字、日期、金额等格式。
<?php
namespace App\Exports;
use App\User;
use Maatwebsite\Excel\Concerns\FromCollection;
//新增两个 use
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
//新增 WithColumnFormatting
class UsersExport implements FromCollection, WithColumnFormatting
{
public function collection()
{
return User::all();
}
}
/**
* @return array
*/
public function columnFormats(): array
{
return [
'B' => NumberFormat::FORMAT_DATE_DDMMYYYY, //日期
'C' => NumberFormat::FORMAT_NUMBER_00, //金额保留两位小数
];
}自动适应单元格宽
<?php
namespace App\Exports;
use App\User;
use Maatwebsite\Excel\Concerns\FromCollection;
//新增
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
//新增 ShouldAutoSize
class UsersExport implements FromCollection, ShouldAutoSize
{
public function collection()
{
return User::all();
}
}导出多 sheet
多表导出需要做两步操作，第一组装 sheet，第二生成对应的 sheet 表
<?php
namespace App\Exports;
use App\User;
//新增
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\WithMultipleSheets;
//新增 WithMultipleSheets
class UsersExport implements WithMultipleSheets
{
use Exportable;
protected $year;
public function __construct(int $year)
{
$this->year = $year;
}
/**
* @return array
*/
public function sheets(): array
{
$sheets = [];
for ($month = 1; $month <= 12; $month++) {
//不同的数据可以调用不同的方法
$sheets[] = new UserPerMonthSheet($this->year, $month);
}
return $sheets;
}
}然后新建 UserPerMonthSheet 类
namespace App\Exports;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithTitle;
class UserPerMonthSheet implements FromQuery, WithTitle
{
private $month;
private $year;
public function __construct(int $year, int $month)
{
$this->month = $month;
$this->year = $year;
}
/**
* @return Builder
*/
public function query()
{
return User
::query()
->whereYear('created_at', $this->year)
->whereMonth('created_at', $this->month);
}
/**
* sheet 表名称
* @return string
*/
public function title(): string
{
return 'Month ' . $this->month;
}
}设置单元格高度以及垂直居中，字体颜色、背景色等
这里需要用到 Laravel Excel 的事件模块
提供多种事件 BeforeExport、BeforeWriting、BeforeSheet，AfterSheet等等，也就是导出功能的生命周期，具体查看文档即可。修改单元格高度我们这里使用 AfterSheet
namespace App\Exports;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\BeforeExport;
use Maatwebsite\Excel\Events\BeforeWriting;
use Maatwebsite\Excel\Events\BeforeSheet;
class UserExport implements WithEvents
{
/**
* 注册事件
* @return array
*/
public function registerEvents(): array
{
return [
AfterSheet::class => function(AfterSheet $event) {
//设置作者
$event->writer->setCreator('Patrick');
//设置列宽
$event->sheet->getDelegate()->getColumnDimension('A')->setWidth(50);
//设置行高，$i为数据行数
for ($i = 0; $i<=1265; $i++) {
$event->sheet->getDelegate()->getRowDimension($i)->setRowHeight(50);
}
// 设置区域单元格自动换行
$event->sheet->getStyle('A1:Z1265')->getAlignment()->setWrapText(true);
// 设置指定单元格自动换行
$event->sheet->getStyle('A2')->getAlignment()->setWrapText(true);
//设置区域单元格垂直居中
$event->sheet->getDelegate()->getStyle('A1:K1265')->getAlignment()->setVertical('center');
//设置区域单元格字体、颜色、背景等，其他设置请查看 applyFromArray 方法，提供了注释
$event->sheet->getDelegate()->getStyle('A1:K6')->applyFromArray([
'font' => [
'name' => 'Arial',
'bold' => true,
'italic' => false,
'strikethrough' => false,
'color' => [
'rgb' => '808080'
]
],
'fill' => [
'fillType' => 'linear', //线性填充，类似渐变
'rotation' => 45, //渐变角度
'startColor' => [
'rgb' => '000000' //初始颜色
],
//结束颜色，如果需要单一背景色，请和初始颜色保持一致
'endColor' => [
'argb' => 'FFFFFF'
]
]
]);
//合并单元格
$event->sheet->getDelegate()->mergeCells('A1:B1');
}
];
}
}我没找到能全局处理的方式，如果你们知道请告诉我，万分感谢。
导出图片
<?php
namespace App\Exports;
//新增
use Maatwebsite\Excel\Concerns\WithDrawings;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
class UserExport implements WithDrawings
{
public function drawings()
{
$drawing = new Drawing();
$drawing->setName('Logo');
$drawing->setDescription('This is my logo');
$drawing->setPath(public_path('/img/logo.jpg'));
$drawing->setHeight(50);
$drawing->setCoordinates('B3');
$drawing2 = new Drawing();
$drawing2->setName('Other image');
$drawing2->setDescription('This is a second image');
$drawing2->setPath(public_path('/img/other.jpg'));
$drawing2->setHeight(120);
$drawing2->setCoordinates('G2');
return [$drawing, $drawing2];
}
}这是官方的例子，实际使用中我们不可能手写这么多方法块，我改写一下
public function drawings()
{
//这里的数据自己组装
$draw_arr = [1 =>'detail1.jpg', 2 => 'detail2.jpg'];
$result = [];
foreach ($draw_arr as $k => $v) {
${'drawing'.$k} = new Drawing();
${'drawing'.$k}->setName('Other image');
${'drawing'.$k}->setDescription('This is a second image');
//图片路径
${'drawing'.$k}->setPath(public_path($v));
${'drawing'.$k}->setHeight(50);
//设置图片列
${'drawing'.$k}->setCoordinates('U'.$k);
$result[] = ${'drawing'.$k};
}
return $result;
}引用链接
[1] PHPOffice: https://github.com/PHPOffice[2] PHPExcel: https://github.com/PHPOffice/PHPExcel[3] https://github.com/SpartnerNL/Laravel-Excel: https://github.com/SpartnerNL/Laravel-Excel[4] https://docs.laravel-excel.com/3.1/getting-started/: https://docs.laravel-excel.com/3.1/getting-started/