LaravelでExcelを操作する(インポート・エクスポート/ダウンロードから分割、バッチ処理etc)
- 公開:
- 更新:
- カテゴリ: PHP Laravel
- タグ: Laravel,CSV,5.7,Excel,TSV
Webアプリケーションの機能としてファイルからデータをインポート(登録)したりエクスポート(出力)したりしますが、扱うファイルとしてもっともポピュラーなのはCSVファイルでしょう。しかしながら、時にはエクセルファイルで行いたいといった時もあったりします。
今回は、LaravelでのExcelファイルのインポートやエクスポートを行っていきます。
- アジェンダ
開発環境
今回の開発環境は以下の通りです。
- 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ドライバを使用するかをここで設定します。
- 利用可能なオプション: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');
// Excel2000~2003
(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');
// Excel2000~2003
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ではこの他にも、イベントやキューを用いる事が出来たり、エクスポートしたエクセルに装飾を行ったりとまだまだ色々な機能があるので是非試してみてください。