PHP项目Laravel Excel导入导出库

wen PHP项目 2026-08-12 145

本文目录导读：

安装与配置
基础用法
导出类详解
导入类详解
高级功能
性能优化建议
错误处理与队列
常用技巧
完整示例：用户导入导出

在Laravel项目中，最流行的Excel导入导出库是 maatwebsite/excel（目前最新的3.1版本）,以下是该库的详细使用指南：

安装与配置

安装包

composer require maatwebsite/excel

生成配置文件（可选）

php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"

配置说明（config/excel.php）

默认导出路径
磁盘配置
缓存设置
CSV设置等

基础用法

简单导出

use Maatwebsite\Excel\Facades\Excel;
use App\Exports\UsersExport;
// 直接下载
return Excel::download(new UsersExport, 'users.xlsx');
// 存储到服务器
Excel::store(new UsersExport, 'users.xlsx', 's3');
// 存储为CSV
Excel::store(new UsersExport, 'users.csv', 'local', \Maatwebsite\Excel\Excel::CSV);

简单导入

use Maatwebsite\Excel\Facades\Excel;
use App\Imports\UsersImport;
// 从上传文件导入
Excel::import(new UsersImport, request()->file('file'));
// 从指定路径导入
Excel::import(new UsersImport, 'users.xlsx', 's3');

导出类详解

基本导出类

<?php
namespace App\Exports;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
class UsersExport implements FromCollection, WithHeadings, WithMapping, WithStyles
{
    public function collection()
    {
        return User::select('id', 'name', 'email', 'created_at')->get();
    }
    // 表头
    public function headings(): array
    {
        return [
            'ID',
            '姓名',
            '邮箱',
            '创建时间'
        ];
    }
    // 数据映射（格式化数据）
    public function map($user): array
    {
        return [
            $user->id,
            $user->name,
            $user->email,
            $user->created_at->format('Y-m-d H:i:s'),
        ];
    }
    // 样式设置
    public function styles(Worksheet $sheet)
    {
        return [
            // 表头样式
            1 => ['font' => ['bold' => true]],
            // 列宽自动调整
            'A' => ['width' => 10],
            'B' => ['width' => 15],
            'C' => ['width' => 25],
        ];
    }
}

从数组导出

use Maatwebsite\Excel\Concerns\FromArray;
use Maatwebsite\Excel\Concerns\WithHeadings;
class ArrayExport implements FromArray, WithHeadings
{
    public function array(): array
    {
        return [
            [1, '张三', 'zhangsan@example.com'],
            [2, '李四', 'lisi@example.com'],
        ];
    }
    public function headings(): array
    {
        return ['ID', '姓名', '邮箱'];
    }
}

从查询导出（分块）

use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithChunkReading;
class UsersExport implements FromQuery, WithChunkReading
{
    public function query()
    {
        return User::query();
    }
    public function chunkSize(): int
    {
        return 1000; // 每1000条写入一次
    }
}

多表合并导出

use Maatwebsite\Excel\Concerns\WithMultipleSheets;
class MultiSheetExport implements WithMultipleSheets
{
    public function sheets(): array
    {
        return [
            new UsersSheet(),
            new OrdersSheet(),
            new ProductsSheet(),
        ];
    }
}

导入类详解

基本导入类

<?php
namespace App\Imports;
use App\Models\User;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Concerns\WithValidation;
use Maatwebsite\Excel\Concerns\WithBatchInserts;
use Maatwebsite\Excel\Concerns\WithChunkReading;
class UsersImport implements ToModel, WithHeadingRow, WithValidation, WithBatchInserts, WithChunkReading
{
    // 将每一行转换为模型
    public function model(array $row)
    {
        return new User([
            'name'     => $row['name'],
            'email'    => $row['email'],
            'password' => bcrypt('password123'),
        ]);
    }
    // 使用表头作为键名（第一行为表头）
    public function headingRow(): int
    {
        return 1;
    }
    // 验证规则
    public function rules(): array
    {
        return [
            'email' => 'required|email|unique:users,email',
            'name'  => 'required|string|max:255',
        ];
    }
    // 批量插入
    public function batchSize(): int
    {
        return 1000;
    }
    // 分块读取
    public function chunkSize(): int
    {
        return 1000;
    }
    // 自定义验证消息
    public function customValidationMessages()
    {
        return [
            'email.required' => '邮箱不能为空',
            'email.email'    => '邮箱格式不正确',
        ];
    }
}

导入到集合（不创建模型）

use Maatwebsite\Excel\Concerns\ToCollection;
use Illuminate\Support\Collection;
class DataImport implements ToCollection
{
    public function collection(Collection $rows)
    {
        foreach ($rows as $row) {
            // 处理每一行数据
        }
    }
}

导入数组

use Maatwebsite\Excel\Concerns\ToArray;
class ArrayImport implements ToArray
{
    public function array(array $array)
    {
        // 处理数组数据
    }
}

带进度回调的导入

use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\BeforeImport;
use Maatwebsite\Excel\Events\AfterImport;
use Maatwebsite\Excel\Events\BeforeSheet;
use Maatwebsite\Excel\Events\AfterSheet;
class EventImport implements WithEvents
{
    public function registerEvents(): array
    {
        return [
            BeforeImport::class => function(BeforeImport $event) {
                // 导入前处理
            },
            AfterImport::class => function(AfterImport $event) {
                // 导入后处理
            },
            BeforeSheet::class => function(BeforeSheet $event) {
                if ($event->getSheet()->getTitle() === 'Sheet1') {
                    // 特定sheet处理
                }
            },
            AfterSheet::class => function(AfterSheet $event) {
                // 单元格操作等
            },
        ];
    }
}

高级功能

导出公式

public function map($row): array
{
    return [
        $row->id,
        $row->name,
        '=SUM(A2:A10)', // 公式
    ];
}

图片导出

use Maatwebsite\Excel\Concerns\WithDrawings;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
class ImageExport implements WithDrawings
{
    public function drawings()
    {
        $drawing = new Drawing();
        $drawing->setName('Logo');
        $drawing->setDescription('This is my logo');
        $drawing->setPath(public_path('/img/logo.png'));
        $drawing->setHeight(90);
        $drawing->setCoordinates('B3');
        return $drawing;
    }
}

日期格式化

use Maatwebsite\Excel\Concerns\WithColumnFormatting;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
class UsersExport implements WithColumnFormatting
{
    public function columnFormats(): array
    {
        return [
            'A' => NumberFormat::FORMAT_DATE_YYYYMMDD,
            'B' => NumberFormat::FORMAT_NUMBER,
        ];
    }
}

CSV导出特殊配置

Excel::download(new UsersExport, 'users.csv', \Maatwebsite\Excel\Excel::CSV, [
    'Content-Type' => 'text/csv',
    'charset' => 'UTF-8',
    'delimiter' => ',',
    'enclosure' => '"',
]);

性能优化建议

大文件导出处理

use Maatwebsite\Excel\Concerns\WithMultipleSheets;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithChunkReading;
class LargeDataExport implements FromQuery, WithChunkReading
{
    public function query()
    {
        return User::query();
    }
    public function chunkSize(): int
    {
        return 5000;
    }
}

内存优化

// 在导入大文件时使用
'import' => [
    'chunk_size' => 1000, // 每次读取的行数
    'batch_size' => 500,  // 每次写入的条数
],

错误处理与队列

错误收集

use Maatwebsite\Excel\Validators\Failure;
class UsersImport implements ToModel, WithValidation
{
    public function onFailure(Failure ...$failures)
    {
        foreach ($failures as $failure) {
            $failure->row();       // 失败行号
            $failure->attribute(); // 失败的属性
            $failure->errors();    // 错误信息
        }
    }
}

队列化导入

<?php
namespace App\Jobs;
use Illuminate\Bus\Queueable;
use Illuminate\Queue\SerializesModels;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Maatwebsite\Excel\Facades\Excel;
use App\Imports\UsersImport;
class ImportUsersJob implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;
    protected $file;
    public function __construct($file)
    {
        $this->file = $file;
    }
    public function handle()
    {
        Excel::import(new UsersImport, $this->file);
    }
}

常用技巧

设置默认时区

// config/excel.php
'timezone' => 'Asia/Shanghai',

自动识别文件格式

// 根据扩展名自动识别
Excel::import(new UsersImport, 'file.xlsx');
Excel::import(new UsersImport, 'file.csv');
Excel::import(new UsersImport, 'file.xls');

下载时设置文件名编码

return Excel::download(new UsersExport, 
    rawurlencode('用户数据') . '.xlsx' // 解决中文文件名问题
);

格式化导出数据为空值

public function map($row): array
{
    return [
        $row->name ?? '暂无',
        $row->email ?: '未填写',
    ];
}

完整示例：用户导入导出

// app/Exports/UsersExport.php
<?php
namespace App\Exports;
use App\Models\User;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
class UsersExport implements FromQuery, WithHeadings, WithMapping, ShouldAutoSize, WithStyles
{
    public function query()
    {
        return User::query()->select('id', 'name', 'email', 'phone', 'status', 'created_at');
    }
    public function headings(): array
    {
        return ['ID', '姓名', '邮箱', '电话', '状态', '注册时间'];
    }
    public function map($user): array
    {
        return [
            $user->id,
            $user->name,
            $user->email,
            $user->phone,
            $user->status === 1 ? '正常' : '禁用',
            $user->created_at->format('Y-m-d H:i:s'),
        ];
    }
    public function styles(Worksheet $sheet)
    {
        return [
            1 => ['font' => ['bold' => true, 'size' => 12]],
            'A1:F1' => ['fill' => ['fillType' => 'solid', 'startColor' => ['rgb' => 'CCCCCC']]],
        ];
    }
}

// app/Imports/UsersImport.php
<?php
namespace App\Imports;
use App\Models\User;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Concerns\WithValidation;
use Maatwebsite\Excel\Concerns\SkipsOnFailure;
use Maatwebsite\Excel\Validators\Failure;
use Illuminate\Support\Facades\Hash;
class UsersImport implements ToModel, WithHeadingRow, WithValidation, SkipsOnFailure
{
    private $successCount = 0;
    private $failures = [];
    public function model(array $row)
    {
        $this->successCount++;
        return new User([
            'name'     => $row['name'],
            'email'    => $row['email'],
            'password' => Hash::make('default123'),
            'phone'    => $row['phone'] ?? '未指定',
            'status'   => 1,
        ]);
    }
    public function rules(): array
    {
        return [
            'name'   => 'required|string|max:255',
            'email'  => 'required|email|unique:users,email',
            'phone'  => 'nullable|string|max:11',
        ];
    }
    public function onFailure(Failure ...$failures)
    {
        foreach ($failures as $failure) {
            $row = $failure->row();
            $error = $failure->errors()[0] ?? '';
            $this->failures[] = [
                '行号' => $row,
                '错误' => $error
            ];
        }
    }
    public function getSuccessCount()
    {
        return $this->successCount;
    }
    public function getFailures()
    {
        return $this->failures;
    }
}

// 控制器中使用
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Maatwebsite\Excel\Facades\Excel;
use App\Exports\UsersExport;
use App\Imports\UsersImport;
class UserImportExportController extends Controller
{
    // 导出
    public function export()
    {
        return Excel::download(new UsersExport(), '用户数据_' . date('Y-m-d') . '.xlsx');
    }
    // 导入页面
    public function importView()
    {
        return view('users.import');
    }
    // 处理导入
    public function import(Request $request)
    {
        $request->validate([
            'file' => 'required|file|mimes:xlsx,xls,csv|max:10240',
        ]);
        $import = new UsersImport();
        try {
            Excel::import($import, $request->file('file'));
            return response()->json([
                'success' => true,
                'message' => "导入成功！成功导入 {$import->getSuccessCount()} 条数据",
                'failures' => $import->getFailures()
            ]);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => '导入失败：' . $e->getMessage()
            ], 500);
        }
    }
}

这些就是Laravel中使用maatwebsite/excel库进行Excel导入导出的主要功能和使用方法,根据项目需求选择合适的功能组合即可。

本文地址： https://www.bmjup.com/post/44306.html