Laravel 13 Excel 导入导出完全指南

Excel 导入导出是企业应用中的常见需求。本文将深入探讨 Laravel 13 中 Excel 数据处理的各种方法和最佳实践。

安装配置

安装 Laravel Excel

1
composer require maatwebsite/excel

发布配置

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

配置选项

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
// config/excel.php
return [
    'exports' => [
        'chunk_size' => 1000,
        'pre_calculate_formulas' => false,
        'strict_null_comparison' => false,
        'csv' => [
            'delimiter' => ',',
            'enclosure' => '"',
            'line_ending' => PHP_EOL,
        ],
    ],
    'imports' => [
        'read_only' => true,
        'heading_row' => true,
        'chunk_size' => 1000,
    ],
];

导出基础

创建导出类

1
php artisan make:export UsersExport --model=User

简单导出

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<?php

namespace App\Exports;

use App\Models\User;
use Maatwebsite\Excel\Concerns\FromCollection;

class UsersExport implements FromCollection
{
    public function collection()
    {
        return User::all();
    }
}

使用导出

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
use App\Exports\UsersExport;
use Maatwebsite\Excel\Facades\Excel;

public function export()
{
    return Excel::download(new UsersExport, 'users.xlsx');
}

public function exportToCsv()
{
    return Excel::download(new UsersExport, 'users.csv');
}

public function storeExport()
{
    Excel::store(new UsersExport, 'users.xlsx', 's3');
}

高级导出

自定义表头

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
<?php

namespace App\Exports;

use App\Models\User;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;

class UsersExport implements FromCollection, WithHeadings, WithMapping
{
    public function collection()
    {
        return User::with('roles')->get();
    }

    public function headings(): array
    {
        return [
            'ID',
            '姓名',
            '邮箱',
            '角色',
            '注册时间',
        ];
    }

    public function map($user): array
    {
        return [
            $user->id,
            $user->name,
            $user->email,
            $user->roles->pluck('name')->join(', '),
            $user->created_at->format('Y-m-d H:i:s'),
        ];
    }
}

格式化单元格

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
<?php

namespace App\Exports;

use App\Models\Order;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
use Maatwebsite\Excel\Concerns\WithMapping;
use PhpOffice\PhpSpreadsheet\Shared\Date;

class OrdersExport implements FromCollection, WithColumnFormatting, WithMapping
{
    public function collection()
    {
        return Order::all();
    }

    public function map($order): array
    {
        return [
            $order->id,
            $order->customer_name,
            Date::dateTimeToExcel($order->created_at),
            $order->total,
            $order->status,
        ];
    }

    public function columnFormats(): array
    {
        return [
            'C' => 'yyyy-mm-dd hh:mm:ss',
            'D' => '#,##0.00',
        ];
    }
}

样式设置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?php

namespace App\Exports;

use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class UsersExport implements WithStyles
{
    public function styles(Worksheet $sheet)
    {
        return [
            1 => ['font' => ['bold' => true, 'size' => 12]],
            'A' => ['font' => ['color' => ['rgb' => 'FF0000']]],
            'B2' => ['font' => ['italic' => true]],
        ];
    }
}

多工作表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
<?php

namespace App\Exports;

use App\Models\User;
use App\Models\Order;
use Maatwebsite\Excel\Concerns\WithMultipleSheets;

class ReportExport implements WithMultipleSheets
{
    protected $startDate;
    protected $endDate;

    public function __construct($startDate, $endDate)
    {
        $this->startDate = $startDate;
        $this->endDate = $endDate;
    }

    public function sheets(): array
    {
        return [
            new UsersSheet($this->startDate, $this->endDate),
            new OrdersSheet($this->startDate, $this->endDate),
            new SummarySheet($this->startDate, $this->endDate),
        ];
    }
}

class UsersSheet implements FromQuery, WithHeadings, WithTitle
{
    public function query()
    {
        return User::whereBetween('created_at', [$this->startDate, $this->endDate]);
    }

    public function title(): string
    {
        return '用户';
    }

    public function headings(): array
    {
        return ['ID', '姓名', '邮箱', '注册时间'];
    }
}

导入基础

创建导入类

1
php artisan make:import UsersImport --model=User

简单导入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?php

namespace App\Imports;

use App\Models\User;
use Maatwebsite\Excel\Concerns\ToModel;

class UsersImport implements ToModel
{
    public function model(array $row)
    {
        return new User([
            'name' => $row[0],
            'email' => $row[1],
            'password' => bcrypt($row[2]),
        ]);
    }
}

使用导入

1
2
3
4
5
6
7
8
9
use App\Imports\UsersImport;
use Maatwebsite\Excel\Facades\Excel;

public function import(Request $request)
{
    Excel::import(new UsersImport, $request->file('file'));

    return redirect()->back()->with('success', '导入成功');
}

高级导入

表头行处理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<?php

namespace App\Imports;

use App\Models\User;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;

class UsersImport implements ToModel, WithHeadingRow
{
    public function model(array $row)
    {
        return new User([
            'name' => $row['name'],
            'email' => $row['email'],
            'password' => bcrypt($row['password']),
        ]);
    }
}

数据验证

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
<?php

namespace App\Imports;

use App\Models\User;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithValidation;

class UsersImport implements ToModel, WithValidation
{
    public function model(array $row)
    {
        return new User([
            'name' => $row['name'],
            'email' => $row['email'],
            'password' => bcrypt($row['password']),
        ]);
    }

    public function rules(): array
    {
        return [
            'name' => 'required|string|max:255',
            'email' => 'required|email|unique:users,email',
            'password' => 'required|string|min:8',
        ];
    }

    public function customValidationMessages()
    {
        return [
            'email.unique' => '邮箱 :input 已存在',
            'password.min' => '密码至少需要 :min 个字符',
        ];
    }
}

批量插入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
<?php

namespace App\Imports;

use App\Models\User;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithBatchInserts;
use Maatwebsite\Excel\Concerns\WithChunkReading;
use Illuminate\Support\Collection;

class UsersImport implements ToCollection, WithBatchInserts, WithChunkReading
{
    public function collection(Collection $rows)
    {
        foreach ($rows as $row) {
            User::create([
                'name' => $row['name'],
                'email' => $row['email'],
                'password' => bcrypt($row['password']),
            ]);
        }
    }

    public function batchSize(): int
    {
        return 1000;
    }

    public function chunkSize(): int
    {
        return 1000;
    }
}

导入事件处理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
<?php

namespace App\Imports;

use App\Models\User;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\AfterImport;
use Maatwebsite\Excel\Events\BeforeImport;

class UsersImport implements ToModel, WithEvents
{
    protected $importId;

    public function __construct($importId)
    {
        $this->importId = $importId;
    }

    public function model(array $row)
    {
        return new User([
            'name' => $row['name'],
            'email' => $row['email'],
            'password' => bcrypt($row['password']),
        ]);
    }

    public function registerEvents(): array
    {
        return [
            BeforeImport::class => function (BeforeImport $event) {
                $totalRows = $event->getReader()->getTotalRows();
                Import::find($this->importId)->update([
                    'total_rows' => $totalRows,
                    'status' => 'processing',
                ]);
            },

            AfterImport::class => function (AfterImport $event) {
                Import::find($this->importId)->update([
                    'status' => 'completed',
                    'completed_at' => now(),
                ]);
            },
        ];
    }
}

导入导出服务

完整服务类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
<?php

namespace App\Services;

use App\Exports\DynamicExport;
use App\Imports\DynamicImport;
use Maatwebsite\Excel\Facades\Excel;
use Illuminate\Support\Facades\Storage;

class ExcelService
{
    public function export(array $data, string $filename, array $options = [])
    {
        $export = new DynamicExport($data, $options);

        $format = $options['format'] ?? 'xlsx';
        $disk = $options['disk'] ?? 'local';

        $path = "exports/{$filename}.{$format}";

        Excel::store($export, $path, $disk);

        return $path;
    }

    public function import(string $path, string $modelClass, array $options = [])
    {
        $import = new DynamicImport($modelClass, $options);

        $disk = $options['disk'] ?? 'local';

        Excel::import($import, $path, $disk);

        return [
            'total' => $import->getTotalRows(),
            'success' => $import->getSuccessCount(),
            'failed' => $import->getFailedCount(),
            'errors' => $import->getErrors(),
        ];
    }

    public function exportModel(string $modelClass, array $columns = ['*'], array $options = [])
    {
        $query = $modelClass::query()->select($columns);

        if (isset($options['filters'])) {
            foreach ($options['filters'] as $column => $value) {
                $query->where($column, $value);
            }
        }

        $data = $query->get()->map(function ($item) use ($columns) {
            return $item->only($columns);
        });

        return $this->export($data->toArray(), $modelClass . '_export', $options);
    }
}

队列处理

异步导出

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
<?php

namespace App\Jobs;

use App\Exports\ReportExport;
use App\Models\User;
use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Support\Facades\Notification;
use App\Notifications\ExportReady;

class ExportReport implements ShouldQueue
{
    use Queueable;

    public function __construct(
        protected User $user,
        protected array $filters
    ) {}

    public function handle(): void
    {
        $export = new ReportExport($this->filters);

        $filename = 'report_' . now()->format('Y-m-d_His') . '.xlsx';
        $path = "exports/{$filename}";

        Excel::store($export, $path, 's3');

        $this->user->notify(new ExportReady($path));
    }
}

总结

Laravel 13 的 Excel 导入导出提供了：

  • 灵活的导出类定义
  • 自定义表头和格式化
  • 多工作表支持
  • 数据验证导入
  • 批量处理优化
  • 事件钩子支持
  • 异步队列处理

掌握 Excel 导入导出技巧可以高效处理大量数据交换需求。