Zenn
📑

【Laravel-Excel】Laravelでエクセルファイルを操作する

2021/09/19に公開2件
Excel
Laravel
PHP
tech

背景

Laravelにはエクセルファイルを簡単に操作できるLaravel-Excelというライブラリがあります。

このライブラリの記事はたくさんありますが、Githubのリポジトリやテストなどのサンプルコードがあるものが少なかったのでメモ

すべてのコードはGithubのリポジトリにpushしてあります。

諸注意

サンプルコードは簡便のため、層の分離等は行っておらず若干ファットコントローラーになっています。

インストール

環境

  • Laravel 8.x
  • PHP 8

Laravelインストール

docker-laravelというLaravelアプリを簡単に用意できる環境があるのでこちらをcloneする。

READMEにしたがって環境を立ち上げる。

ライブラリの準備

以下のようにinfra/docker/php/Dockerfileを書き換える。

infra/docker/php/Dockerfile
FROM php:8.0-fpm-buster
LABEL maintainer="ucan-lab <yes@u-can.pro>"
SHELL ["/bin/bash", "-oeux", "pipefail", "-c"]

# timezone environment
ENV TZ=UTC \
  # locale
  LANG=en_US.UTF-8 \
  LANGUAGE=en_US:en \
  LC_ALL=en_US.UTF-8 \
  # composer environment
  COMPOSER_ALLOW_SUPERUSER=1 \
  COMPOSER_HOME=/composer

COPY --from=composer:2.0 /usr/bin/composer /usr/bin/composer

RUN apt-get update && \
  apt-get -y install git libicu-dev libonig-dev libfreetype6-dev libpng-dev libjpeg-dev libzip-dev unzip locales && \
  apt-get clean && \
  rm -rf /var/lib/apt/lists/* && \
  locale-gen en_US.UTF-8 && \
  localedef -f UTF-8 -i en_US en_US.UTF-8 && \
  mkdir /var/run/php-fpm && \
  docker-php-ext-install intl pdo_mysql zip bcmath && \
  docker-php-ext-configure gd --with-freetype --with-jpeg && \
  docker-php-ext-install -j$(nproc) gd && \
  composer config -g process-timeout 3600 && \
  composer config -g repos.packagist composer https://packagist.org

COPY ./infra/docker/php/php-fpm.d/zzz-www.conf /usr/local/etc/php-fpm.d/zzz-www.conf
COPY ./infra/docker/php/php.ini /usr/local/etc/php/php.ini

WORKDIR /work/backend

追記した処理としては

  • 画像処理系のライブラリをコンテナのOSにインストール
  • gdにフォントとjpegを使用できるように設定
  • PHPの画像処理ライブラリ「gd」をインストール

phpコンテナのビルドし直し

docker compose build app

php spread sheetのインストール

composer require phpoffice/phpspreadsheet

laravel excelのインストール

composer require maatwebsite/excel

念の為 service provider にライブラリを登録

config/app.php
'providers' => [
    /*
     * Package Service Providers...
     */
    Maatwebsite\Excel\ExcelServiceProvider::class,
]

念の為、Facadeも登録

app/config.php
'aliases' => [
    ...
    'Excel' => Maatwebsite\Excel\Facades\Excel::class,
]

configファイルをpublish

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

エクスポート

ここではUserモデルのデータをxlsx形式で書き出し、画面上でダウンロードできるようにします。

実装

Exportクラスを定義

php artisan make:export UsersExport --model=User
backend/app/Exports/UsersExport.php
<?php

namespace App\Exports;

use App\Models\User;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\FromCollection;

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

UserController.phpにAPIを追加

./app/Http/Controllers/UserController.php
    public function download(): BinaryFileResponse
    {
        return Excel::download(new UsersExport, UsersExport::FILE_NAME);
    }

APIを叩く画面を用意。

index.blade.php
@extends('layouts.app')

@section('content')
    <section>
        <h1>Laravel/Excel Sample app</h1>
        <div>
            <a href="{{ route('users.excel.export.download') }}">Export user models</a>
        </div>
    </section>
@endsection

Export user modelsのリンクをクリックすると、usersテーブルの全レコードがエクスポートされる。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/290859/2a1a918e-99d2-a1b7-2081-522da42c1a2d.png" width=100%>

大容量データエクスポート時の対策

テーブルが大量のレコードを持つ場合、エクスポートに時間がかかりユーザーの離脱やクレームに繋がりかねません。
そこでLaravelのQueues(キュー)を用いて、エクスポートを非同期で実行させます。
エクスポート完了後、S3にファイルをアップロードし、ダウンロードリンクをメール送信します。

実装

S3にアップロードするためのライブラリを追加

composer require --with-all-dependencies league/flysystem-aws-s3-v3 "^1.0"

以下のartisanコマンドを実行し、DBにキューを管理するテーブルを追加

php artisan queue:table
php artisan migrate

キューに入れるジョブを定義

php artisan make:job NotifyUserOfCompletedExport

エクスポート完了時に送信する通知(Notification)を定義

php artisan make:notification ExportCompleted

通知が送信されるようにジョブを編集する。

backend/app/Jobs/NotifyUserOfCompletedExport.php
<?php
declare(strict_types=1);

namespace App\Jobs;

use App\Models\User;
use App\Notifications\ExportCompleted;
use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;

/**
 * Class NotifyUserOfCompletedExport
 * @package App\Jobs
 */
class NotifyUserOfCompletedExport implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    /**
     * @var User
     */
    private User $user;

    /**
     * @var string
     */
    private string $fileName;

    /**
     * Create a new job instance.
     *
     * @param User $user
     * @param string $fileName
     */
    public function __construct(User $user, string $fileName)
    {
        $this->user = $user;
        $this->fileName = $fileName;
    }

    /**
     * Execute the job.
     *
     * @return void
     */
    public function handle()
    {
        $this->user->notify(new ExportCompleted($this->fileName));
    }
}

通知にS3のリンクを含めるように修正する。

backend/app/Notifications/ExportCompleted.php
<?php
declare(strict_types=1);

namespace App\Notifications;

use App\Http\Controllers\UserController;
use Illuminate\Bus\Queueable;
use Illuminate\Notifications\Messages\MailMessage;
use Illuminate\Notifications\Notification;
use Illuminate\Support\Facades\Storage;

/**
 * Class ExportCompleted
 * @package App\Notifications
 */
class ExportCompleted extends Notification
{
    use Queueable;

    /**
     * @var string
     */
    private string $fileName;

    /**
     * Create a new notification instance.
     *
     * @param string $fileName
     * @return void
     */
    public function __construct(string $fileName)
    {
        $this->fileName = $fileName;
    }

    /**
     * Get the notification's delivery channels.
     *
     * @param mixed $notifiable
     * @return array
     */
    public function via($notifiable): array
    {
        return ['mail'];
    }

    /**
     * Get the mail representation of the notification.
     *
     * @param mixed $notifiable
     * @return MailMessage
     */
    public function toMail($notifiable): MailMessage
    {
        $s3 = Storage::disk(UserController::STORAGE_S3);

        return (new MailMessage)
            ->line('Export has been completed.')
            ->line('Please click link to download a exported file.')
            ->action('Download ' . $this->fileName, $s3->url($this->fileName));
    }

    /**
     * Get the array representation of the notification.
     *
     * @param mixed $notifiable
     * @return array
     */
    public function toArray($notifiable): array
    {
        return [
            //
        ];
    }
}

動作確認

mailtrapに登録し、.envに必要な情報を入力する。

backend/.env
MAIL_MAILER=smtp
MAIL_HOST=smtp.mailtrap.io
MAIL_PORT=2525
MAIL_USERNAME=********
MAIL_PASSWORD=********
MAIL_ENCRYPTION=tls
MAIL_FROM_ADDRESS=test@test.com
MAIL_FROM_NAME="${APP_NAME}"

S3のバケットにアップロードするためAWSのクレデンシャル等を.envに追加

backend/.env
AWS_ACCESS_KEY_ID=********
AWS_SECRET_ACCESS_KEY=********
AWS_DEFAULT_REGION=********
AWS_BUCKET=********
AWS_USE_PATH_STYLE_ENDPOINT=false

backend/resources/views/excel/index.blade.phpにキューエクスポート用のリンクを追加

backend/resources/views/excel/index.blade.php
@extends('layouts.app')

@section('content')
    <section>
        <h1>Laravel/Excel Sample app</h1>
        <div>
            <a href="{{route('users.excel.download')}}">Export user models</a>
        </div>
        <div>
            <a href="{{route('users.excel.queue')}}">Add a job of export user models in queue</a>
        </div>
    </section>
@endsection

localhost/users/excelにアクセスし「Add a job of export user models in queue」リンクをクリック
画面上部に成功メッセージが表示されることを確認。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/290859/59a7f34b-9837-2682-d3c5-7db55f50d81b.png" width=100%>

mailtrapでファイルダウンロードリンク付きのメールが送信されることを確認する。
※ファイルダウンロードのためにはバケットの公開範囲を設定してください

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/290859/1dc89cfe-a14e-26a6-e115-d29df95c9b6f.png" width=100%>

テスト

ExcelファサードはExcel::fake()でモックしテストコードを書くことができます。

出力されたファイルの中身等も検証することが可能です。

backend/tests/Feature/UserControllerTest.php
    public function test_download()
    {
        Excel::fake();

        User::factory()->count(10)->create();

        $response = $this->get(route('users.excel.download'));

        $response->assertOk();

        Excel::assertDownloaded(UsersExport::FILE_NAME, function (UsersExport $export) {
            //出力されたExcelファイルが10件のModelを持つか検証
            return $export->collection()->count() === 10;
        });
    }

すべてのテストケースはUserControllerTest.phpに実装してあります。

インポート

エクセルに記載されたデータを取り込むことも可能。

実装

UserのImportクラスを追加

php artisan make:import UsersImport --model=User

UsersImportを下記のように編集する。

backend/app/Imports/UsersImport.php
<?php
declare(strict_types=1);

namespace App\Imports;

use App\Models\User;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\Hash;
use Maatwebsite\Excel\Concerns\ToModel;

class UsersImport implements ToModel
{
    /**
     * @param array $row
     *
     * @return Model|User|null
     */
    public function model(array $row): Model|User|null
    {
        return new User([
            'name' => $row[0],
            'email' => $row[1],
            'd_o_b' => $row[2],
            'sex' => $row[3],
            'password' => Hash::make($row[4]),
        ]);
    }
}

UserController.phpにAPIを追加

./app/Http/Controllers/UserController.php
    /**
     * @param UploadPost $request
     * @return View|RedirectResponse
     */
    public function upload(UploadPost $request): View|RedirectResponse
    {
        try {
            Excel::import(new UsersImport, $request->file('users'));
            $message = 'Successfully imported an excel file';
            return \view('excel.import', compact('message'));

        } catch (Exception $e) {
            logger()->error($e);
            return redirect(route('users.excel.import.import-form'))
                ->withErrors($e->getMessage());
        }
    }

APIを叩く画面を用意

import.blade.php
@extends('layouts.app')

@section('content')
    <section>
        <h1>Laravel/Excel Sample app</h1>
        <div>
            <p>Import</p>
            <form
                action="{{ route('users.excel.import.upload') }}"
                method="POST"
                enctype="multipart/form-data"
            >
                @csrf
                <div>
                    <input type="file" id="users" name="users">
                </div>
                <div>
                    <input type="submit" value="Import">
                </div>
            </form>
        </div>
    </section>
@endsection

ファイルを選択し、Importボタンを押すとインポートが実行される

大容量データインポート時の対策

エクスポートと同様に大容量エクセルファイルがインポートされた際は、インポートの終了をメール通知することができます。

実装

UsersExport.phpにWithChunkReading, ShouldQueueインターフェースをimplementsさせる。

./backend/app/Imports/UsersImport.php
<?php
declare(strict_types=1);

namespace App\Imports;

use App\Models\User;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\Hash;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithChunkReading;

class UsersImport implements ToModel, WithChunkReading, ShouldQueue
{
    /**
     * @param array $row
     *
     * @return Model|User|null
     */
    public function model(array $row): Model|User|null
    {
        return new User([
            'name' => $row[0],
            'email' => $row[1],
            'sex' => $row[2],
            'password' => Hash::make($row[3]),
        ]);
    }

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

キューに入れるジョブを定義

php artisan make:job NotifyUserOfCompletedImport

エクスポート完了時に送信する通知(Notification)を定義

php artisan make:notification ImportCompleted
ImportCompleted.php
<?php
declare(strict_types=1);

namespace App\Notifications;

use Illuminate\Bus\Queueable;
use Illuminate\Notifications\Messages\MailMessage;
use Illuminate\Notifications\Notification;

class ImportCompleted extends Notification
{
    use Queueable;

    /**
     * @var string
     */
    private string $fileName;

    /**
     * Create a new job instance.
     *
     * @param string $fileName
     */
    public function __construct(string $fileName)
    {
        $this->fileName = $fileName;
    }

    /**
     * Get the notification's delivery channels.
     *
     * @param mixed $notifiable
     * @return array
     */
    public function via($notifiable): array
    {
        return ['mail'];
    }

    /**
     * Get the mail representation of the notification.
     *
     * @param mixed $notifiable
     * @return \Illuminate\Notifications\Messages\MailMessage
     */
    public function toMail($notifiable): MailMessage
    {
        return (new MailMessage)
            ->line('Import has been completed.')
            ->action('Return to the app', route('users.excel.import.import-form'));
    }

    /**
     * Get the array representation of the notification.
     *
     * @param mixed $notifiable
     * @return array
     */
    public function toArray($notifiable): array
    {
        return [
            //
        ];
    }
}
NotifyUserOfCompletedImport.php
<?php
declare(strict_types=1);

namespace App\Jobs;

use App\Models\User;
use App\Notifications\ImportCompleted;
use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;

class NotifyUserOfCompletedImport implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    /**
     * @var User
     */
    private User $user;

    /**
     * @var string
     */
    private string $fileName;

    /**
     * Create a new job instance.
     *
     * @param User $user
     * @param string $fileName
     */
    public function __construct(User $user, string $fileName)
    {
        $this->user = $user;
        $this->fileName = $fileName;
    }

    /**
     * Execute the job.
     *
     * @return void
     */
    public function handle()
    {
        $this->user->notify(new ImportCompleted($this->fileName));
    }
}

UserController.phpにAPIを用意

UserController.php
    /**
     * @param QueueImportPost $request
     * @return View|RedirectResponse
     */
    public function queueImport(QueueImportPost $request): View|RedirectResponse
    {
        try {
            Excel::queueImport(
                new UsersImport,
                $file = $request->file('users'),
                self::STORAGE_S3
            )->chain([
                new NotifyUserOfCompletedImport(
                    request()->user() ?? User::factory()->create(),
                    $request->file('users')->getClientOriginalName()
                )
            ]);

            $message = 'Successfully queued job of import an excel file';
            return \view('excel.import', compact('message'));

        } catch (Exception $e) {
            logger()->error($e->getMessage());
            return redirect(route('users.excel.import.import-form'))
                ->withErrors($e->getMessage());
        }
    }

APIを叩く画面を用意

import.blade.php
@extends('layouts.app')

@section('content')
    <section>
        <h1>Laravel/Excel Sample app</h1>
        <div>
            <p>Import</p>
            <form
                action="{{ route('users.excel.import.upload') }}"
                method="POST"
                enctype="multipart/form-data"
            >
                @csrf
                <div>
                    <input type="file" id="users" name="users">
                </div>
                <div>
                    <input type="submit" value="Import">
                </div>
            </form>
        </div>
        <div>
            <p>Import by queue</p>
            <form
                action="{{ route('users.excel.import.queue') }}"
                method="POST"
                enctype="multipart/form-data"
            >
                @csrf
                <div>
                    <input type="file" id="users" name="users">
                </div>
                <div>
                    <input type="submit" value="Import by queue">
                </div>
            </form>
        </div>
    </section>
@endsection

ファイルを選択し、Import by queueボタンを押すとインポートがQueueに入る

image.png

インポート終了すると、メールが送信される。

image.png

テスト

エクスポートと同様にアサーションが用意されているのでテストをかける。
※インポートされた後にDBテストを行う際はExcel::fake()は使えないので注意

UserControllerTest.php（抜粋）
    public function test_import_success()
    {
        $response = $this->post(route('users.excel.import.upload'), [
            'users' => new UploadedFile(
                './tests/Feature/data/import_success.xlsx',
                'import_success.xlsx',
                'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
                null,
                true
            )
        ]);

        $response
            ->assertOk()
            ->assertViewHas('message');

        $this
            ->assertDatabaseHas('users', [
                'name' => 'Dr. Keaton Beahan DVM',
                'email' => 'myrtice.langosh@example.com',
            ])
            ->assertDatabaseHas('users', [
                'name' => 'Amelia Auer DDS',
                'email' => 'maximillian76@example.com',
            ])
            ->assertDatabaseHas('users', [
                'name' => 'Mariane Satterfield',
                'email' => 'cassin.brendon@example.net',
            ]);
    }

すべてのテストケースはUserControllerTest.phpに実装してあります。

所感

これであなたもエクセルマスター

補足

ドキュメントに設計思想書いてあるので、読んでみたら勉強になるぜよ。

※記事へのご指摘歓迎します。

GitHubで編集を提案

Discussion

MoguraMogura

php spread sheetのインストールを行っていますが、今回は使用されていないように見受けられます。