RitoLabo

LaravelでExcelを操作する(インポート・エクスポート/ダウンロードから分割、バッチ処理etc)

  • 公開:
  • 更新:
  • カテゴリ: PHP Laravel
  • タグ: Laravel,CSV,5.7,Excel,TSV

Webアプリケーションの機能としてファイルからデータをインポート(登録)したりエクスポート(出力)したりしますが、扱うファイルとしてもっともポピュラーなのはCSVファイルでしょう。しかしながら、時にはエクセルファイルで行いたいといった時もあったりします。

今回は、LaravelでのExcelファイルのインポートやエクスポートを行っていきます。

アジェンダ
  1. 開発環境
  2. Laravel Excel
    1. システム要件
  3. インストール
  4. 使用するデータとDBの準備
    1. マイグレーション
  5. モデル作成
  6. エクスポートクラスとインポートクラス
  7. インポート
    1. コントローラからインポート
    2. ファサード
    3. ファサードなし
    4. 色々なファイルのインポート
    5. ヘッダー行が存在する場合
    6. 見出し行がカラム名の場合
    7. 分割(チャンク)
    8. アップロードされたファイルをインポート
    9. バッチでのインポート
  8. エクスポート
    1. ダウンロード
    2. ファサード無し
    3. ディスクに保存
    4. エクセルファイル以外のエクスポート
    5. bladeビューからのエクスポート

開発環境

今回の開発環境は以下の通りです。

  • Linux CentOS 7
  • Apache 2.4
  • MySQL 8.0
  • PHP 7.2
  • Laravel 5.7

laravelのルートディレクトリを「laravel/」としています。

Laravel Excel

今回は、エクセルファイルの操作に「Laravel Excel」というパッケージを導入します。

Laravel Excelとは、PhpSpreadsheet(Excelなどのさまざまなスプレッドシートファイル形式の読み書きを可能にする一連のクラスを提供するPHP製のライブラリ)を基にしたLaravel特化のエクセル操作ライブラリです。

Laravel Excel
https://laravel-excel.maatwebsite.nl/

ちなみにエクセルファイル以外にもCSVファイルはもちろん、色々なファイルを扱う事が出来ます。

尚、今回はバージョン3.1を使用していきます。

システム要件

  • PHP: ^7.0
  • Laravel: ^5.5
  • PhpSpreadsheet: ^1.4
  • PHP拡張モジュール:php_zip php_xml php_gd2

インストール

Laravel Excelをcomposerを使ってインストールします。Laravelのルートディレクトリで以下を叩きます。

# Laravelルートディレクトリへ移動
cd /path/to/laravel

# Laravel Execlインストール
composer require maatwebsite/excel

# 実行結果
[demo@localhost laravel]$ composer require maatwebsite/excel
Using version
^3.1 for maatwebsite/excel
./composer.json has been updated
Loading composer repositories with package information
Updating dependencies (including require-dev)
Package operations: 4 installs, 0 updates, 0 removals

- Installing
markbaker/matrix (1.1.4): Downloading (100%)
- Installing
markbaker/complex (1.4.7): Downloading (100%)
- Installing
phpoffice/phpspreadsheet (1.6.0): Downloading (100%)
- Installing
maatwebsite/excel (3.1.3): Downloading (100%)
phpoffice/phpspreadsheet suggests installing mpdf/mpdf (Option for
rendering PDF with PDF Writer)
phpoffice/phpspreadsheet suggests installing dompdf/dompdf (Option for
rendering PDF with PDF Writer)
phpoffice/phpspreadsheet suggests installing tecnickcom/tcpdf (Option for
rendering PDF with PDF Writer)
phpoffice/phpspreadsheet suggests installing jpgraph/jpgraph (Option for
rendering charts, or including charts with PDF or HTML Writers)

次に、設定ファイルにサービスプロパイダとファサードを登録します。

laravel/config/app.php
'providers' => [

Maatwebsite\Excel\ExcelServiceProvider::class,

],
'aliases' => [

'Excel' => Maatwebsite\Excel\Facades\Excel::class,

],

設定ファイルを公開します。以下のartisanコマンドを叩きます。

# Laravelルートディレクトリへ移動
cd /path/to/laravel

# 設定ファイルを出力する
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"

# 実行結果
[demo@localhost laravel]$ php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"
Copied File [/vendor/maatwebsite/excel/config/excel.php] To [/config/excel.php]
Publishing complete.

laravel/config 配下に execl.php が生成されます。

laravel/config/execl.php
<?php

use Maatwebsite\Excel\Excel;

return [
'exports' => [
'chunk_size' => 1000,
'temp_path' => sys_get_temp_dir(),
'pre_calculate_formulas' => false,
'csv' => [
'delimiter' => ',',
'enclosure' => '"',
'line_ending' => PHP_EOL,
'use_bom' => false,
'include_separator_line' => false,
'excel_compatibility' => false,
],
],

'imports' => [
'read_only' => true,
'heading_row' => [
'formatter' => 'slug',
],
],
'extension_detector' => [
'xlsx' => Excel::XLSX,
'xlsm' => Excel::XLSX,
'xltx' => Excel::XLSX,
'xltm' => Excel::XLSX,
'xls' => Excel::XLS,
'xlt' => Excel::XLS,
'ods' => Excel::ODS,
'ots' => Excel::ODS,
'slk' => Excel::SLK,
'xml' => Excel::XML,
'gnumeric' => Excel::GNUMERIC,
'htm' => Excel::HTML,
'html' => Excel::HTML,
'csv' => Excel::CSV,
'tsv' => Excel::TSV,
'pdf' => Excel::DOMPDF,
],
];
エクスポート設定
chunk_size
ここでチャンクの大きさを指定できます。FromQueryを使用すると、クエリはここの値に従って自動的にチャンクされます。
temp_path
ファイルをエクスポートするときは、保存またはダウンロードする前に一時ファイルを使用します。ここでそのパスをカスタマイズできます。
csv
CSVエクスポートの区切り文字、囲み、および行末を設定します。
インポート設定
read_only
ファイルを読み込み専用として扱います。
formatter
見出し行フォーマッターを構成します。
利用可能なオプション:none|slug|custom
拡張設定
pdf
デフォルトでどのPdfドライバを使用するかをここで設定します。
利用可能なオプション:Excel :: MPDF | Excel :: TCPDF | Excel :: DOMPDF

今回はデフォルトのまま進めます。

使用するデータとDBの準備

今回は、サンプルとして住所jpが公開している住所データを使用してエクセルファイルを作成し、それらをインポートしていきます。

尚、ダウンロード可能なデータに.xlsxがないので、CSVファイルから作成するなど、適宜作成してください。

また、作成するファイルによってはサイズがとても大きくなってしまうので、自身のサーバースペックに応じて作成してください。

マイグレーション

まずはテーブルを作成します。マイグレーションファイルを作成するために、以下のartisanコマンドを叩きます。

# Laravelルートディレクトリへ移動
cd /path/to/laravel

# マイグレーションファイルを生成する
php artisan make:migration create_address_table --create=address

# 実行結果
[demo@localhost laravel]$ php artisan make:migration create_address_table --create=address
Created Migration: XXXX_create_address_table

laravel/database/migrations 配下に XXXX_create_address_table.phpが生成されます。(XXXXの部分は日時が入ります)

laravel
├─database
│ ├─ migrations
│ │   └─
XXXX_create_address_table.php

マイグレーションを実行してテーブルを作成します。以下のartisanコマンドを叩きます。

# Laravelルートディレクトリへ移動
cd /path/to/laravel

# マイグレーション実行
php artisan migrate

# 実行結果
[demo@localhost laravel]$ php artisan migrate
Migrating: xxxx_create_address_table
Migrated: xxxx_create_address_table

MySQLにログインして確認すると、以下のテーブルが作成されます。

mysql> SHOW COLUMNS FROM `address`;
+------------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| address_cd | int(11) | NO | | NULL | |
| prefectures_cd | int(11) | NO | | NULL | |
| city_cd | int(11) | NO | | NULL | |
| town_area_cd | int(11) | NO | | NULL | |
| postal_code | char(8) | NO | | NULL | |
| office_flag | tinyint(4) | YES | | 0 | |
| deprecated_flag | tinyint(4) | YES | | 0 | |
| prefectures | varchar(4) | NO | | NULL | |
| prefecture_kana | varchar(255) | NO | | NULL | |
| city | varchar(255) | NO | | NULL | |
| city_kana | varchar(255) | NO | | NULL | |
| town_area | varchar(255) | YES | | NULL | |
| town_area_kana | varchar(255) | YES | | NULL | |
| town_area_supplement | varchar(255) | YES | | NULL | |
| kyoto_street_name | varchar(255) | YES | | NULL | |
| character_chome | varchar(255) | YES | | NULL | |
| character_chapter_kana | varchar(255) | YES | | NULL | |
| supplement | varchar(255) | YES | | NULL | |
| office_name | varchar(255) | YES | | NULL | |
| office_name_kana | varchar(255) | YES | | NULL | |
| office_address | varchar(255) | YES | | NULL | |
| new_address_cd | int(11) | YES | | 0 | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
+------------------------+------------------+------+-----+---------+----------------+

モデル作成

Addressテーブルのモデルを作成します。以下のartisnコマンドを叩きます。

# Laravelルートディレクトリへ移動
cd /path/to/laravel

# モデル作成
php artisan make:model Address

laravel/app 配下に Address.phpが作成されるので、これを定義します。

laravel/app/Models/Address.php
<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Address extends Model
{
protected $table = 'address';

protected $fillable = [
'address_cd',
'prefectures_cd',
'city_cd',
'town_area_cd',
'postal_code',
'office_flag',
'deprecated_flag',
'prefectures',
'prefecture_kana',
'city',
'city_kana',
'town_area',
'town_area_kana',
'town_area_supplement',
'kyoto_street_name',
'character_chome',
'character_chapter_kana',
'supplement',
'office_name',
'office_name_kana',
'office_address',
'new_address_cd'
];

}

エクスポートクラスとインポートクラス

Laravel Excelでは「エクスポートクラス」と「インポートクラス」というものを作成し、そこで具体的な処理方法などを定義していきます。

以下のartisanコマンドを叩いて、エクスポートクラスとインポートクラスを生成します。

# Laravelルートディレクトリへ移動
cd /path/to/laravel

# エクスポート&インポートクラスを生成
php artisan make:export AddressExport --model=App\\Address
php artisan make:import AddressImport --model=App\\Address

# 実行結果
[demo@localhost laravel]$ php artisan make:export AddressExport --model=App\\Address
Export created successfully.
[demo@localhost laravel]$ php artisan make:import AddressImport --model=App\\Address
Import created successfully.

app/Imports 配下に AddressImport.phpが、app/Exports 配下に AddressExport.php が生成されます。

laravel
├─ app
│   ├─ Exports
│   │   └─
AddressExport.php
│   ├─ Imports
│   │   └─
AddressImport.php
app/Imports/AddressImport.php
<?php

namespace App\Imports;

use App\Address;
use Maatwebsite\Excel\Concerns\ToModel;

class AddressImport implements ToModel
{
/**
* @param array $row
*
* @return \Illuminate\Database\Eloquent\Model|null
*/
public function model(array $row)
{
return new Address([
//
]);
}
}

これを以下のように定義します。

<?php

namespace App\Imports;

use App\Address;
use Maatwebsite\Excel\Concerns\ToModel;

class AddressImport implements ToModel
{
public function model(array $row)
{
return new Address([
'address_cd' => $row[0],
'prefectures_cd' => $row[1],
'city_cd' => $row[2],
'town_area_cd' => $row[3],
'postal_code' => $row[4],
'office_flag' => $row[5],
'deprecated_flag' => $row[6],
'prefectures' => $row[7],
'prefecture_kana' => $row[8],
'city' => $row[9],
'city_kana' => $row[10],
'town_area' => $row[11],
'town_area_kana' => $row[12],
'town_area_supplement' => $row[13],
'kyoto_street_name' => $row[14],
'character_chome' => $row[15],
'character_chapter_kana' => $row[16],
'supplement' => $row[17],
'office_name' => $row[18],
'office_name_kana' => $row[19],
'office_address' => $row[20],
'new_address_cd' => $row[21]
]);
}
}

テーブルカラムとデータのマッピングを行っています。

app/Exports/AddressExport.php
<?php

namespace App\Exports;

use App\Address;
use Maatwebsite\Excel\Concerns\FromCollection;

class AddressExport implements FromCollection
{
/**
* @return \Illuminate\Support\Collection
*/
public function collection()
{
return Address::all();
}
}

ここまでで前段の準備は完了です。次から、これらを使ってしてエクセルファイルを操作していきます。

インポート

Excelファイルをインポートしていきます。

コントローラからインポート

コントローラからエクセルファイルをインポートします。コントローラを作成します。

# コントローラを生成
php artisan make:controller AddressController

ファサード

コントローラを以下のように定義します。

laravel/app/Http/Controllers/AddressController.php
<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Imports\AddressImport;
use Maatwebsite\Excel\Facades\Excel;

class AddressController extends Controller
{
public function import()
{
$file_name = "address.xlsx";
Excel::import(new AddressImport, $file_name);
}
}

上から解説します。

use App\Imports\AddressImport;
use Maatwebsite\Excel\Facades\Excel;

インポートクラスとファサードをuseしています。Excelファサードはエイリアス登録しているので、

use Excel;

のようにしてもOKです。

public function import()
{
$file_name = "address.xlsx";
Excel::import(new AddressImport, $file_name);
}

importアクションを定義しています。Excelファサードのimportメソッドを利用する事でインポートを行えますが、第一引数にインポートクラスのインスタンス、第二引数にファイルパスを指定しています。

尚、ファイルパスについてはFilesystem Diskを用いてファイルを取得する為、ディスク指定が local の場合、起点は laravel/storage/app ディレクトリになります。

$file_name = "address.xlsx";
Excel::import(new AddressImport, $file_name);
// => laravel/storage/app/address.xlsx を取得する

ディスク指定について、デフォルトでは「local」になっていますが、設定は laravel/config/filesystems.php で確認・指定が行えます。

また、オプションで第三引数にディスクの指定、第四引数にリーダーの種類を指定できます。

Excel::import(new AddressImport, $file_name, 'local', \Maatwebsite\Excel\Excel::XLSX);

あとはルーティングを行って実行すれば、インポートが行われます。

laravel/routes/web.php
Route::get('address/import', 'AddressController@import');

ファサードなし

ファサードを使わなくてもインポートが可能です。その場合はImportableトレイトを利用します。インポートクラスを以下のように定義します。

laravel/app/Imports/AddressImport.php
<?php

namespace App\Imports;

use App\Address;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\Importable;

class AddressImport implements ToModel
{
use Importable;

public function model(array $row)
{
return new Address([
'address_cd' => $row[0],
'prefectures_cd' => $row[1],
'city_cd' => $row[2],
'town_area_cd' => $row[3],
'postal_code' => $row[4],
'office_flag' => $row[5],
'deprecated_flag' => $row[6],
'prefectures' => $row[7],
'prefecture_kana' => $row[8],
'city' => $row[9],
'city_kana' => $row[10],
'town_area' => $row[11],
'town_area_kana' => $row[12],
'town_area_supplement' => $row[13],
'kyoto_street_name' => $row[14],
'character_chome' => $row[15],
'character_chapter_kana' => $row[16],
'supplement' => $row[17],
'office_name' => $row[18],
'office_name_kana' => $row[19],
'office_address' => $row[20],
'new_address_cd' => $row[21]
]);
}
}

コントローラは以下のようになります。

laravel/app/Http/Controllers/AddressController.php
<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Imports\AddressImport;

class AddressController extends Controller
{
public function import()
{
$file_name = "address.xlsx";
(new AddressImport)->import($file_name);
}
}

色々なファイルのインポート

Laravel Excelでは、エクセル以外のファイルにも対応しています。

  • Excel2007以降(.xlsx)
  • Excel2000/2002/2003(.xls)
  • CSV
  • TSV
  • OpenDocument スプレッドシート
  • SLK
  • XML
  • html
  • gnumeric

これらのファイルは、ファイル名を指定すれば拡張子から判定されてインポートが行われます。

// Excel2007
(new AddressImport)->import('file.xlsx');

// CSV
(new AddressImport)->import('file.csv');

// TSV
(new AddressImport)->import('file.tsv');

// Excel20002003
(new AddressImport)->import('file.xls');

// OpenDocument スプレッドシート
(new AddressImport)->import('file.ods');

// SLK
(new AddressImport)->import('file.slk');

// XML
(new AddressImport)->import('file.xml');

// html
(new AddressImport)->import('file.html');

// gnumeric
(new AddressImport)->import('file.gnumeric');

ヘッダー行が存在する場合

エクセルファイルのデータには大抵ヘッダ行が先頭に存在していたりします。インポートするデータにヘッダ行が無い場合はこれまでの方法でインポートが行われますが、今回のデータの場合は数値指定のカラムも多いので失敗してしまいます。除外したい行がある場合は、インポートクラスに定義します。

laravel/app/Imports/AddressImport.php
public function model(array $row)
{
// マルチバイト文字が含まれている場合はその行をインポートから除外する
if(strlen($row[0]) !== mb_strlen($row[0],'utf8')) {
return null;
}

return new Address([
.
.
.

今回はヘッダ行が日本語の想定で、住所CDの値にマルチバイト文字が含まれている場合はその行を除外する指定を行っています。このようにして、何らかの条件でnullを返す事で、その行をスキップする事が出来ます。

見出し行がカラム名の場合

ファイルにヘッダ行があるがそれらがカラム名の場合は、これを利用してインポートを行う事も出来ます。こんな感じのやつ

ヘッダ行がカラム名のエクセルファイル

インポートクラスにWithHeadingRowを実装します。

laravel/app/Imports/AddressImport.php
<?php

namespace App\Imports;

use App\Address;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\WithHeadingRow;

class AddressImport implements ToModel, WithHeadingRow
{
use Importable;

public function model(array $row)
{
return new Address([
'address_cd' => $row['address_cd'],
'prefectures_cd' => $row['prefectures_cd'],
'city_cd' => $row['city_cd'],
'town_area_cd' => $row['town_area_cd'],
'postal_code' => $row['postal_code'],
'office_flag' => $row['office_flag'],
'deprecated_flag' => $row['deprecated_flag'],
'prefectures' => $row['prefectures'],
'prefecture_kana' => $row['prefecture_kana'],
'city' => $row['city'],
'city_kana' => $row['city_kana'],
'town_area' => $row['town_area'],
'town_area_kana' => $row['town_area_kana'],
'town_area_supplement' => $row['town_area_supplement'],
'kyoto_street_name' => $row['kyoto_street_name'],
'character_chome' => $row['character_chome'],
'character_chapter_kana' => $row['character_chapter_kana'],
'supplement' => $row['supplement'],
'office_name' => $row['office_name'],
'office_name_kana' => $row['office_name_kana'],
'office_address' => $row['office_address'],
'new_address_cd' => $row['new_address_cd']
]);
}

implementsにWithHeadingRowを追加したら、変数 $row のキー名を、対応する文字列キーにします。

コントローラには変更はありません。

laravel/app/Http/Controllers/AddressController.php
<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Imports\AddressImport;

class AddressController extends Controller
{
public function import()
{
$file_name = "address_with_header_column.xlsx";

(new AddressImport)->import($file_name);
}
}

これでヘッダ行の名称に対応させた状態でインポートを行う事が出来ます。

尚、今回の例ではヘッダがカラム名でしたが、変数 $row のキー名とエクセルファイルのヘッダの文字列が一致していれば良い(全角は×)ので、カラム名でなくてもインポート可能です。

ちなみにレアケースですが、ヘッダ行が先頭に無い場合でも、インポートクラスに指定する事で対応させる事が可能です。インポートクラスにheadingRowメソッドを定義します。

laravel/app/Imports/AddressImport.php
public function model(array $row)
{
return new Address([
'address_cd' => $row['address_cd'],
'prefectures_cd' => $row['prefectures_cd'],
'city_cd' => $row['city_cd'],
'town_area_cd' => $row['town_area_cd'],
'postal_code' => $row['postal_code'],
'office_flag' => $row['office_flag'],
'deprecated_flag' => $row['deprecated_flag'],
'prefectures' => $row['prefectures'],
'prefecture_kana' => $row['prefecture_kana'],
'city' => $row['city'],
'city_kana' => $row['city_kana'],
'town_area' => $row['town_area'],
'town_area_kana' => $row['town_area_kana'],
'town_area_supplement' => $row['town_area_supplement'],
'kyoto_street_name' => $row['kyoto_street_name'],
'character_chome' => $row['character_chome'],
'character_chapter_kana' => $row['character_chapter_kana'],
'supplement' => $row['supplement'],
'office_name' => $row['office_name'],
'office_name_kana' => $row['office_name_kana'],
'office_address' => $row['office_address'],
'new_address_cd' => $row['new_address_cd']
]);
}

public function headingRow(): int
{
return 3;
}

ただしこの場合、ヘッダ行より上の行は取り込まれないので注意が必要です。ヘッダ行の上にデータとは関係ない情報を含んでいる場合などには良いかもしれません。

分割(チャンク)

Laravel Excelは、デフォルトではエクセルファイル内の全てのデータを一括でインサートしようとします。この場合、もちろんデータ量が多ければその分メモリを消費し、メモリ不足で処理が停止してしまう事もあります。それを防止する為に、チャンクを設定できます。

インポートクラスにchunkSizeメソッドを定義します。

laravel/app/Imports/AddressImport.php
<?php

namespace App\Imports;

use App\Address;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\WithHeadingRow;

class AddressImport implements ToModel, WithHeadingRow
{
use Importable;

public function model(array $row)
{
return new Address([
'address_cd' => $row['address_cd'],
'prefectures_cd' => $row['prefectures_cd'],
'city_cd' => $row['city_cd'],
'town_area_cd' => $row['town_area_cd'],
'postal_code' => $row['postal_code'],
'office_flag' => $row['office_flag'],
'deprecated_flag' => $row['deprecated_flag'],
'prefectures' => $row['prefectures'],
'prefecture_kana' => $row['prefecture_kana'],
'city' => $row['city'],
'city_kana' => $row['city_kana'],
'town_area' => $row['town_area'],
'town_area_kana' => $row['town_area_kana'],
'town_area_supplement' => $row['town_area_supplement'],
'kyoto_street_name' => $row['kyoto_street_name'],
'character_chome' => $row['character_chome'],
'character_chapter_kana' => $row['character_chapter_kana'],
'supplement' => $row['supplement'],
'office_name' => $row['office_name'],
'office_name_kana' => $row['office_name_kana'],
'office_address' => $row['office_address'],
'new_address_cd' => $row['new_address_cd']
]);
}

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

上記では100を返していますが、この場合は「100件ずつインサートする」という意味になります。自身の環境やインポートするデータによって、適切な値をセットしてください。

アップロードされたファイルをインポート

フォームからアップロードされたエクセルファイルをインポートする事も可能です。

laravel/app/Http/Controllers/AddressController.php
<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Imports\AddressImport;

class AddressController extends Controller
{
public function index()
{
return view('address.index');
}

public function import(Request $request)
{
(new AddressImport)->import($request->excel_file);
}
}

これまでの記述にリクエストデータを渡してあげるだけです。

laravel/resources/views/address/index.blade.php
<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="UTF-8">
<title>Laravel Excel Form Upload</title>
</head>
<body>
<form method="post" action="/address/import">
@csrf
<input type="file" name="excel_file"><br>
<input type="submit" value="send">
</form>
</body>
</html>
laravel/routes/web.php
Route::get('address/import', 'AddressController@index');
Route::post('address/import', 'AddressController@import');

簡単に書きましたが、フォームから受け取る際はバリデーションを必ず通すようにしましょう。

バッチでのインポート

次に、バッチインポートも実装してみます。

まずはCommandsクラスを生成します。以下のartisnコマンドを叩きます。

# Laravelのルートディレクトリへ移動
cd /path/to/laravel

# Commandクラスを生成
php artisan make:command ImportExcelCommand --command="import:excel"

# 実行結果
[demo@localhost laravel]$ php artisan make:command ImportExcelCommand --command="import:excel"
Console command created successfully.

laravel/app/Console/Commands 配下に ImportExcelCommand.php が生成されます。

laravel
│   ├─ app
│   ├─ Console
│   │   ├─ Commands
│   │   │   ├─
ImportExcelCommand.php

Commandsクラスを定義します。

laravel/app/Console/Commands/ImportExcelCommand.php
<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use App\Imports\AddressImport;

class ImportXlsxCommand extends Command
{
protected $signature = 'import:excel {file}';

protected $description = 'Import Excel File From Storage app.';

public function __construct()
{
parent::__construct();
}

public function handle()
{
$this->output->title('Starting import');
$file = $this->argument('file');
(new AddressImport)->withOutput($this->output)->import($file);
$this->output->success('Import successful');
}
}

今回は、コマンド+ファイル名で実行されるようにしています。handleアクションを解説します。

$this->output->title('Starting import');

プログレスバーをスタートさせます。手動で実行する場合に視覚的にわかりやすくする為なので、無くても動作します。

$file = $this->argument('file');

コマンドで入力したファイル名を変数$fileに格納しています。

(new AddressImport)->withOutput($this->output)->import($file);

変数$fileをimportメソッドに渡してインポートを行っています。尚、プログレスバーを用いない場合はwithOutputメソッドをチェーンさせずに

(new AddressImport)->import($file);

とします。

$this->output->success('Import successful');

プログレスバーを終了させます。

今回はプログレスバーを表示させるので、インポートクラスを以下に定義します。

laravel/app/Imports/AddressImport.php
<?php

namespace App\Imports;

use App\Address;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Concerns\WithBatchInserts;
use Maatwebsite\Excel\Concerns\WithChunkReading;
use Maatwebsite\Excel\Concerns\WithProgressBar;

class AddressImport implements ToModel, WithHeadingRow, WithBatchInserts, WithChunkReading, WithProgressBar
{
use Importable;

public function model(array $row)
{
return new Address([
'address_cd' => $row['address_cd'],
'prefectures_cd' => $row['prefectures_cd'],
'city_cd' => $row['city_cd'],
'town_area_cd' => $row['town_area_cd'],
'postal_code' => $row['postal_code'],
'office_flag' => $row['office_flag'],
'deprecated_flag' => $row['deprecated_flag'],
'prefectures' => $row['prefectures'],
'prefecture_kana' => $row['prefecture_kana'],
'city' => $row['city'],
'city_kana' => $row['city_kana'],
'town_area' => $row['town_area'],
'town_area_kana' => $row['town_area_kana'],
'town_area_supplement' => $row['town_area_supplement'],
'kyoto_street_name' => $row['kyoto_street_name'],
'character_chome' => $row['character_chome'],
'character_chapter_kana' => $row['character_chapter_kana'],
'supplement' => $row['supplement'],
'office_name' => $row['office_name'],
'office_name_kana' => $row['office_name_kana'],
'office_address' => $row['office_address'],
'new_address_cd' => $row['new_address_cd']
]);
}

public function batchSize(): int
{
return 100;
}
public function chunkSize(): int
{
return 100;
}
}

WithBatchInserts・WithChunkReading・WithProgressBar を実装しています。さらに、batchSizeメソッドでチャンクサイズを100に設定しています。

実装が完了しました。これをartisanコマンドから使用する事が出来ます。

# コマンドの確認
[demo@localhost laravel]$ php artisan
Available commands:
import
import:excel Import Excel File From Storage app.

実行すると、以下のように表示されます。

# インポート実行
php artisan import:excel address.xlsx

# 実行結果
[demo@localhost laravel]$ php artisan import:excel address.xlsx

Starting import
===============


1000/1000 [▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓] 100%

[OK] Import successful

エクスポート

DBのデータをエクセルファイルへエクスポートします。

最初に生成したエクスポートクラスです。

laravel/app/Exports/AddressExport.php
<?php

namespace App\Exports;

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

class AddressExport implements FromCollection
{
/**
* @return \Illuminate\Support\Collection
*/
public function collection()
{
return Address::all();
}
}

ルーティングです。

laravel/routes/web.php
Route::get('address/export', 'AddressController@export');

ダウンロード

エクスポートしたファイルをダウンロードします。

コントローラにエクセル出力を定義します。

laravel/app/Http/Controllers/AddressController.php
<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Exports\AddressExport;
use Maatwebsite\Excel\Facades\Excel;

class AddressController extends Controller
{
public function export()
{
return Excel::download(new AddressExport, 'output.xlsx');
}
}

アクセスすると、エクスポート後、ダウンロードが行われます。

ファサード無し

インポート同様、こちらもファサード無しでも利用できます。Exportableトレイトを利用します。エクスポートクラスを以下のように定義します。

laravel/app/Exports/AddressExport.php
<?php

namespace App\Exports;

use App\Address;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\Exportable;

class AddressExport implements FromCollection
{
use Exportable;

/**
* @return \Illuminate\Support\Collection
*/
public function collection()
{
return Address::all();
}
}
laravel/app/Http/Controllers/AddressController.php
public function export()
{
return (new AddressExport)->download('output.xlsx');
}

ディスクに保存

ダウンロードではなく、ディスクに保存する場合は、store()メソッドを使います。

public function export()
{
Excel::store(new AddressExport, 'output.xlsx', 'local');
}

第三引数にはディスク名を指定していますが、指定しない場合はデフォルトのディスクがセットされます。

エクセルファイル以外のエクスポート

エクセル以外のファイル形式でエクスポートする場合は、ファイル名を渡す時にその拡張子で渡します。

// Excel2007
return (new AddressExport)->download('output.xlsx');

// CSV
return (new AddressExport)->download('output.csv');

// TSV
return (new AddressExport)->download('output.tsv');

// Excel20002003
return (new AddressExport)->download('output.xls');

// OpenDocument スプレッドシート
return (new AddressExport)->download('output.ods');

// html
return (new AddressExport)->download('output.html');

尚、PDFへの変換も行えます。

// TCPDF
return (new AddressExport)->download('output.pdf', \Maatwebsite\Excel\Excel::TCPDF);

// DOMPDF
return (new AddressExport)->download('output.pdf', \Maatwebsite\Excel\Excel::DOMPDF);

// MPDF
return (new AddressExport)->download('output.pdf', \Maatwebsite\Excel\Excel::MPDF);

3種類のPDF作成ライブラリに対応しており、使用にはそれぞれのライブラリのインストールが必要です。

# TCPDF
composer require tecnickcom/tcpdf

# DOMPDF
composer require mpdf/mpdf

# MPDF
composer require dompdf/dompdf

ただし、ただデータを出力するだけでは綺麗に描画されないなど一定のカスタマイズが必要になります。
https://phpspreadsheet.readthedocs.io/en/develop/topics/reading-and-writing-to-file/#pdf

次に紹介する、ビューからのエクスポートの場合は形が整って出力されます。

また、PDF出力はデータに日本語を含む場合は別途フォントをセットする必要があります。

bladeビューからのエクスポート

これまではデータそのものをエクスポートしてきましたが、Bladeビューを使って出力の形を定義する事もできます。

例えば、こんな感じのビューを定義します。

laravel/resources/views/address/export.blade.php
<table>
<thead>
<tr>
<th>住所CD</th>
<th>都道府県CD</th>
<th>市区町村CD</th>
<th>町域CD</th>
<th>郵便番号</th>
<th>都道府県</th>
<th>都道府県カナ</th>
<th>市区町村</th>
<th>市区町村カナ</th>
<th>町域</th>
</tr>
</thead>
<tbody>
@foreach($address as $a)
<tr>
<td>{{ $a->address_cd }}</td>
<td>{{ $a->prefectures_cd }}</td>
<td>{{ $a->city_cd }}</td>
<td>{{ $a->town_area_cd }}</td>
<td>{{ $a->postal_code }}</td>
<td>{{ $a->prefectures }}</td>
<td>{{ $a->prefecture_kana }}</td>
<td>{{ $a->city }}</td>
<td>{{ $a->city_kana }}</td>
<td>{{ $a->town_area }}</td>
</tr>
@endforeach
</tbody>
</table>

そして、コントローラは以下に定義します。

laravel/app/Http/Controllers/AddressController.php
<?php

namespace App\Http\Controllers;

use App\Address;
use Illuminate\Http\Request;

class AddressController extends Controller
{
public function export()
{
return view('address.export', [
'address' => Address::all()
]);
}
}

ブラウザからアクセスすると、以下が表示されます。

ビュー画面

これをそのままエクスポートします。まずはエクスポートクラスを以下に定義します。

laravel/app/Exports/AddressExport.php
<?php

namespace App\Exports;

use App\Address;
use Illuminate\Contracts\View\View;
use Maatwebsite\Excel\Concerns\FromView;

class AddressExport implements FromView
{
public function view(): View
{
return view('address.export', [
'address' => Address::all()
]);
}
}

これまではFromCollectionでしたが、FromViewを実装する形に変更しています。そして、viewメソッドの中でbladeテンプレートの指定とデータを渡しています。

そして、コントローラは以下にします。

laravel/app/Http/Controllers/AddressController.php
<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Exports\AddressExport;
use Maatwebsite\Excel\Facades\Excel;

class AddressController extends Controller
{
public function export()
{
return Excel::download(new AddressExport, 'output.xlsx');
}
}

これでブラウザからアクセスすると、ビューの形式で書き出されたエクセルファイルがダウンロードされます。

エクセル出力画面

まとめ

以上で作業は完了です。Laravel Excelではこの他にも、イベントやキューを用いる事が出来たり、エクスポートしたエクセルに装飾を行ったりとまだまだ色々な機能があるので是非試してみてください。