こんにちは。フリーランス・コンサルタント＆エンジニアの さてさて、この間DBデータをCSVでインポートする機能をつくるという記事を公開しましたが、実はいつもより少しだけアクセスが多かったです😊
そして、せっかくインポート（アップロード）を紹介したので、逆のエクスポート（ダウンロード）も開発してみたくなったので、今回はこの話題をお届けします。
DBデータをCSV or Excel形式でエクスポートする
です。
もちろん業務内容で変わってくるとは思いますが、よくある形式として「ウェブ上で顧客や商品データを管理し、必要なときにそれらのデータを取り出してローカル環境で加工＆プリントアウトする」という流れがあったりします。
その際に、いちいちMySQLなどからデータをエクスポートするよりはサイトにログインしてクリックするだけでCSVやExcelファイルがダウンロードできたら、とても便利かと思います。
ぜひ参考にしてみてくださいね😊✨
【追記：2020.9.26】「１行目に項目名を入れたい場合」を追加しました
開発環境： Laravel 6.x
やりたいこと
各モデルに独自のメソッドを追加してもいいのですが、そうなるとそれ以外のモデルからはエクスポートができませんので、より汎用的に使えるよう今回はCollection::macro()を使ってコレクションを拡張する形式でDBデータをエクスポートできるようにします。
※ Collection::macro()を使うと、Laravelのコレクションに独自のメソッドを追加することができます。
なお、Collection::macro()はapp/Providers/AppServiceProvider.phpのboot()内に記述するとどこからでも呼び出すことができます。
では、実際にやっていきましょう！
準備をする
今回CSVとExcelファイルは、以下２つのパッケージで取り扱います。
以下のコマンドでインストールしておいてください。
composer require sukohi/fluent-csv:3.*
composer require phpoffice/phpspreadsheet
※ なお、PhpSpreadsheetでもCSVでダウンロードが可能ですがShift_JISに対応していないのであえてfluent-csvを使っています。
CSVでエクスポート
実装コード
では、まずはCSVのエクスポートです。
app/Providers/AppServiceProvider.phpのboot()に次のように追加してください。
<?php
namespace App\Providers;
use Illuminate\Database\Eloquent\Collection;
use Illuminate\Support\ServiceProvider;
class AppServiceProvider extends ServiceProvider
{
// 省略
public function boot()
{
Collection::macro('exportCsv', function($filename = '', $filters = [], $encoding = 'UTF-8') {
if(empty($filename)) {
$filename = $this->first()->getTable() .'_'. date('Ymd_His') .'.csv';
}
$fluent = \FluentCsv::setEncoding($encoding);
$this->each(function($item) use($fluent, $filters) {
$row_data = [];
if(empty($filters)) {
$row_data = array_values($item->toArray());
} else {
foreach($filters as $filter) {
if(is_callable($filter)) {
$row_data[] = $filter($item);
}
}
}
$fluent->addData($row_data);
});
return $fluent->download($filename);
});
}
}
この中では、基本的にfluent-csvにデータを追加してreturnしているだけですが、重要なのがfiltersです。これは、例えば以下のようにデータを加工したい場合に使うコールバック関数になっています。
- 名前に「様」をつけたい
- 西暦を和暦に変換したい
- 郵便番号、都道府県、市区町村などを結合して住所をつくる
使い方
シンプルな使い方
コントローラー内で以下のようにして使います。（もちろんUserモデルだけでなく、他のモデルでも同じように使えます）
$users = \App\User::get(); return $users->exportCsv();
※ この場合、ファイル名は「テーブル名 + 日時.csv」となります。
取得データを指定する
以下のようにすることで、取得する内容を絞り込むことができます。
$users = \App\User::select('id', 'email', 'name')->get();
return $users->exportCsv();
実際にエクスポートするとこうなります。
ファイル名を指定する
ファイル名を指定する場合は第１引数で指定します。
return $users->exportCsv('test.csv');
データを加工する
第２引数にコールバック関数を追加するとデータを加工することができます。（DataTable.jsに似た感じです）
$users = \App\User::get();
$filters = [
function($user) {
return $user->name .'様';
},
function($user) {
return $user->created_at->format('Y年m月d日 H時i分s秒');
}
];
return $users->exportCsv('test.csv', $filters);
実際にエクスポートしたものがこちらです。
文字コードを指定する
文字コードは第３引数になります。
return $users->exportCsv('test.csv', [], 'sjis-win');
１行目に項目名を入れたい場合
訪問ユーザーさんから「１行目に以下のような項目名を入れたい場合はどうすればいいでしょうか？」とご質問をいただいたので、Collection::macro()を拡張してみました😊👍
- ID
- メールアドレス
- 名前
- 登録日時
まずはコードからです。
app/Providers/AppServiceProvider.php
// 👇 $headings を追加しました
Collection::macro('exportCsv', function($filename = '', $headings = [], $filters = [], $encoding = 'UTF-8') {
if(empty($filename)) {
$filename = $this->first()->getTable() .'_'. date('Ymd_His') .'.csv';
}
$fluent = \FluentCsv::setEncoding($encoding);
// 👇 ここも追加しました
if(!empty($headings)) {
$fluent->addData($headings);
}
$this->each(function($item) use($fluent, $filters) {
$row_data = [];
if(empty($filters)) {
$row_data = array_values($item->toArray());
} else {
foreach($filters as $filter) {
if(is_callable($filter)) {
$row_data[] = $filter($item);
}
}
}
$fluent->addData($row_data);
});
return $fluent->download($filename);
});
拡張と言いましたが、実際には引数に$headingsを追加し、もし空ではない場合にaddData()を実行しているだけです。
そのため、使い方としては、以下のようになります。
$users = User::select('id', 'email', 'name', 'created_at')->get();
$filename = 'test.csv';
$headings = [
'ID',
'メールアドレス',
'名前',
'登録日時'
];
return $users->exportCsv($filename, $headings);
実際にエクスポートするとこのようになります。
Excelでエクスポート
続いてExcelのエクスポートです。
実装コード
こちらもapp/Providers/AppServiceProvider.phpのboot()の中に以下のコードを追加してください。
<?php
namespace App\Providers;
use Illuminate\Database\Eloquent\Collection;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Symfony\Component\HttpFoundation\StreamedResponse;
use Illuminate\Support\ServiceProvider;
class AppServiceProvider extends ServiceProvider
{
// 省略
public function boot()
{
Collection::macro('exportExcel', function($filename = '', $filters = []) {
if(empty($filename)) {
$filename = $this->first()->getTable() .'_'. date('Ymd_His') .'.xlsx';
}
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$excel_data = [];
$this->each(function($item) use(&$excel_data, $filters) {
$row_data = [];
if(empty($filters)) {
$row_data = array_values($item->toArray());
} else {
foreach($filters as $filter) {
if(is_callable($filter)) {
$row_data[] = $filter($item);
}
}
}
$excel_data[] = $row_data;
});
$sheet->fromArray($excel_data, null, 'A1');
$callback = function() use($spreadsheet) {
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
};
$status = 200;
$headers = [
'Content-Type' => 'application/vnd.ms-excel',
'Content-Disposition' => 'attachment;filename="'. $filename .'"',
'Cache-Control' => 'max-age=0',
];
return new StreamedResponse($callback, $status, $headers);
});
}
}
使い方
使い方は基本的にCSVのエクスポートと同じですが、文字コードの指定はできません。（UTF-8で固定です）
パッケージをつくりました
今回の機能は、再利用する可能性が高いと感じましたのでパッケージ化してGitHubで公開することにしました。
https://github.com/SUKOHI/EloquentExport
インストールはコマンド一発です。
composer require sukohi/eloquent-export:1.*
使い方は基本的に同じですが、メソッド名がexport()に変更になっていて、ファイル名だけが必須になっています。
エクセルファイルの場合
return $users->export('test.xlsx');
// or
return $users->export('test.xlsx', $filters);
CSVファイルの場合
return $users->export('test.csv');
// or
return $users->export('test.csv', $filters);
// or
return $users->export('test.csv', $filters, 'sjis-win');
おわりに
ということで今回はデータベースの内容をCSVとExcelのファイルでエクスポートする機能を作ってみました。
ちなみにですが、今回使ったPhpSpreadsheetのライセンスが数日前にLGPLからMITに変更になったようです。（偶然ですがタイミングがちょうどでした😊✨）
もちろんLGPLでも内容によってはクライアント様のために使うことはできますが、開発者としてはMITと明言されている方が使いやすい印象はありますね。
また、私が公開しているFluent-CSVの方もMITライセンスですので、フレキシブルに利用できるかと思います。
ぜひ今回の内容を活用してみてくださいね。
ではでは〜！
