条件に該当するレコードをCSVで出力する
検索条件に該当するレコードを一括でCSV出力してダウンロードさせる方法について解説。
ライブラリ「Laravel-Excel」を使う。
今回は、Customers(顧客)テーブルのデータをダウンロードすることを想定して解説。
ジャンプできる目次
Laravel-Excelのインストールと設定ファイル生成
composer require maatwebsite/excel
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"
クラスを作成
php artisan make:export CustomerExport
プロジェクトディレクトリ\app\Exports\CustomerExport.php
が生成される。
作ったクラスは後で編集する。
コントローラーからクラスを呼び出せるようにする
//プロジェクトディレクトリ\app\Http\Controllers\CustomerController.php
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Models\Customer;//モデル読み込み
use Carbon\Carbon;//日時取得
//csv関係
use App\Exports\CustomerExport;//追記
use Maatwebsite\Excel\Facades\Excel;//追記
use Maatwebsite\Excel\Validators\ValidationException;//追記
//中略
class CustomerController extends Controller
{
public function exportCustomerToCsv(Request $request){
//$request = {key:'あいうえお'}
return Excel::download(new CustomerExport($request), Carbon::now()->format('Y-m-d-h-m-s').'customers.csv');
}
}メソッドexportCustomerToCsv()が呼ばれたら 、
さっき作ったクラスCustomerExportにリクエスト（検索条件）を渡す。
クラス内のにおける処理の実行結果を現在の日時.customer.csvというファイル名で保存する
今回は、$request = {key:'あいうえお'}
が条件だったことにする。
CustomerExportクラスを編集
<?php
//プロジェクトディレクトリ\app\Exports\CustomerExport.php
namespace App\Exports;
use App\Models\Customer;//モデル名
use Illuminate\Support\Facades\Schema;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;
class CustomerExport implements FromCollection, WithHeadings, WithMapping
{
private $table = "customers";//テーブル名
private $columns;
private $searchCondition;//これから使いまわす変数を宣言
public function __construct($request)//コントローラーから(request)検索条件を受け取る
{
$this->searchCondition = $request;//コントローラーから受け取った検索条件をcollectionメソッドで呼び出せる様にする
$this->columns = Schema::getColumnListing($this->table);
}
/**
* @return \Illuminate\Support\Collection
*/
public function collection()
{
$searchCondition = $this->searchCondition;
$CustomerQuery = Customer::query();
if($searchCondition->key){
$key = '%' . addcslashes($searchCondition->key, '%_\\') . '%';//部分一致
$CustomerQuery->where(function ($query) use($key){
$query->where('answer','LIKE',$key);
});
}
return $CustomerQuery->orderBy('id', 'desc')->get();
}
public function headings(): array
{
return $this->columns;
}
public function map($row): array
{
$array = [];
$columns = $this->columns;
foreach ($columns as $column) {
$array[] = $row->{$column};
}
return $array;
}
}コントローラーから送信したリクエスト（検索条件）は、まず __construct()メソッドで受け取られる。
ややこしいけどここが結構重要。
__construct()内で、リクエスト（検索条件）を他のメソッド内でも $this->searchConditionという名称で呼び出せるように調整している。
で、今回の例だとcustomersテーブルのanswerカラムに「あいうえお」という文字列を含むレコードを全件取得して、
CSVでダウンロードさせることになる。
ちなみに条件に該当するレコードの全カラムが出力される。
一部だけに限定することもできるので、下記のサイトを参考にしてほしい
参考にしたサイト
どうやってメソッドを呼び出すん？
例えばAPIでメソッドを呼び出したいとする。
<?php
//api.php
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Route;;
use App\Http\Controllers\CustomerController;
/*
|--------------------------------------------------------------------------
| API Routes
|--------------------------------------------------------------------------
|
| Here is where you can register API routes for your application. These
| routes are loaded by the RouteServiceProvider within a group which
| is assigned the "api" middleware group. Enjoy building your API!
|
*/
Route::get('/customers/export', [CustomerController::class, 'exportCustomerToCsv']);あとは、aタグなどでリンクして下記のURLを呼び出せばダウンロードが始まる。
/api/customers/export?key=a
検索機能に付け加えるなら、aタグ内のリンクを必要に応じて動的に変化させればいい。
↓
<a :href="downloadUrl">CSVでダウンロード</a>
downloadUrlの内容を動的に変化させるわけやな。
リンクをクリックしたらメソッドが発火していきなりダウンロードが始まる。
文字化けさせない方法
通常文字コードはUTF-8で書き出されるので、Windowsのメモ帳やMicrosoft「Excel」で開くと文字化けする。
対処法として、プロジェクトディレクトリ/config/excel.phpを編集する必要がある。
先に言っとくとShift-JISで書き出さなくても大丈夫。
該当ファイルがなければ、
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"
を叩くと生成される。
別にすでに存在する場合も上書きされるわけじゃないから叩いても大丈夫らしい。
36行目付近に追記。
/*
|--------------------------------------------------------------------------
| CSV Settings
|--------------------------------------------------------------------------
|
| Configure e.g. delimiter, enclosure and line ending for CSV exports.
|
*/
'csv' => [
'delimiter' => ',',
'enclosure' => '"',
'escape_character' => '\\',
'contiguous' => false,
'input_encoding' => 'UTF-8',
'use_bom' => true, //追記
],この処理で解決した。
Configure e.g. delimiter, enclosure and line ending for CSV exports.
と
Configure e.g. delimiter, enclosure and line ending for CSV imports.
の設定があって紛らわしいが、
exports
の方に追記すること。
全体のコードとしては下記のような感じになった。(僕が考える最強のLaravel-Excelの設定)
<?php
use Maatwebsite\Excel\Excel;
return [
'exports' => [
/*
|--------------------------------------------------------------------------
| Chunk size
|--------------------------------------------------------------------------
|
| When using FromQuery, the query is automatically chunked.
| Here you can specify how big the chunk should be.
|
*/
'chunk_size' => 1000,
/*
|--------------------------------------------------------------------------
| Pre-calculate formulas during export
|--------------------------------------------------------------------------
*/
'pre_calculate_formulas' => false,
/*
|--------------------------------------------------------------------------
| Enable strict null comparison
|--------------------------------------------------------------------------
|
| When enabling strict null comparison empty cells ('') will
| be added to the sheet.
*/
'strict_null_comparison' => false,
/*
|--------------------------------------------------------------------------
| CSV Settings
|--------------------------------------------------------------------------
|
| Configure e.g. delimiter, enclosure and line ending for CSV exports.
|
*/
'csv' => [
'delimiter' => ',',
'enclosure' => '"',
'escape_character' => '\\',
'contiguous' => false,
'input_encoding' => 'UTF-8',
'use_bom' => true, //excelで開いても文字化けしないようにする
],
/*
|--------------------------------------------------------------------------
| Worksheet properties
|--------------------------------------------------------------------------
|
| Configure e.g. default title, creator, subject,...
|
*/
'properties' => [
'creator' => '',
'lastModifiedBy' => '',
'title' => '',
'description' => '',
'subject' => '',
'keywords' => '',
'category' => '',
'manager' => '',
'company' => '',
],
],
'imports' => [
/*
|--------------------------------------------------------------------------
| Read Only
|--------------------------------------------------------------------------
|
| When dealing with imports, you might only be interested in the
| data that the sheet exists. By default we ignore all styles,
| however if you want to do some logic based on style data
| you can enable it by setting read_only to false.
|
*/
'read_only' => true,
/*
|--------------------------------------------------------------------------
| Ignore Empty
|--------------------------------------------------------------------------
|
| When dealing with imports, you might be interested in ignoring
| rows that have null values or empty strings. By default rows
| containing empty strings or empty values are not ignored but can be
| ignored by enabling the setting ignore_empty to true.
|
*/
'ignore_empty' => false,
/*
|--------------------------------------------------------------------------
| Heading Row Formatter
|--------------------------------------------------------------------------
|
| Configure the heading row formatter.
| Available options: none|slug|custom
|
*/
'heading_row' => [
'formatter' => 'slug',
],
/*
|--------------------------------------------------------------------------
| CSV Settings
|--------------------------------------------------------------------------
|
| Configure e.g. delimiter, enclosure and line ending for CSV imports.
|
*/
'csv' => [
'delimiter' => null,
'enclosure' => '"',
'escape_character' => '\\',
'contiguous' => false,
'input_encoding' => 'UTF-8',
],
/*
|--------------------------------------------------------------------------
| Worksheet properties
|--------------------------------------------------------------------------
|
| Configure e.g. default title, creator, subject,...
|
*/
'properties' => [
'creator' => '',
'lastModifiedBy' => '',
'title' => '',
'description' => '',
'subject' => '',
'keywords' => '',
'category' => '',
'manager' => '',
'company' => '',
],
],
/*
|--------------------------------------------------------------------------
| Extension detector
|--------------------------------------------------------------------------
|
| Configure here which writer/reader type should be used when the package
| needs to guess the correct type based on the extension alone.
|
*/
'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 Extension
|--------------------------------------------------------------------------
|
| Configure here which Pdf driver should be used by default.
| Available options: Excel::MPDF | Excel::TCPDF | Excel::DOMPDF
|
*/
'pdf' => Excel::DOMPDF,
],
/*
|--------------------------------------------------------------------------
| Value Binder
|--------------------------------------------------------------------------
|
| PhpSpreadsheet offers a way to hook into the process of a value being
| written to a cell. In there some assumptions are made on how the
| value should be formatted. If you want to change those defaults,
| you can implement your own default value binder.
|
| Possible value binders:
|
| [x] Maatwebsite\Excel\DefaultValueBinder::class
| [x] PhpOffice\PhpSpreadsheet\Cell\StringValueBinder::class
| [x] PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder::class
|
*/
'value_binder' => [
'default' => Maatwebsite\Excel\DefaultValueBinder::class,
],
'cache' => [
/*
|--------------------------------------------------------------------------
| Default cell caching driver
|--------------------------------------------------------------------------
|
| By default PhpSpreadsheet keeps all cell values in memory, however when
| dealing with large files, this might result into memory issues. If you
| want to mitigate that, you can configure a cell caching driver here.
| When using the illuminate driver, it will store each value in the
| cache store. This can slow down the process, because it needs to
| store each value. You can use the "batch" store if you want to
| only persist to the store when the memory limit is reached.
|
| Drivers: memory|illuminate|batch
|
*/
'driver' => 'memory',
/*
|--------------------------------------------------------------------------
| Batch memory caching
|--------------------------------------------------------------------------
|
| When dealing with the "batch" caching driver, it will only
| persist to the store when the memory limit is reached.
| Here you can tweak the memory limit to your liking.
|
*/
'batch' => [
'memory_limit' => 60000,
],
/*
|--------------------------------------------------------------------------
| Illuminate cache
|--------------------------------------------------------------------------
|
| When using the "illuminate" caching driver, it will automatically use
| your default cache store. However if you prefer to have the cell
| cache on a separate store, you can configure the store name here.
| You can use any store defined in your cache config. When leaving
| at "null" it will use the default store.
|
*/
'illuminate' => [
'store' => null,
],
],
/*
|--------------------------------------------------------------------------
| Transaction Handler
|--------------------------------------------------------------------------
|
| By default the import is wrapped in a transaction. This is useful
| for when an import may fail and you want to retry it. With the
| transactions, the previous import gets rolled-back.
|
| You can disable the transaction handler by setting this to null.
| Or you can choose a custom made transaction handler here.
|
| Supported handlers: null|db
|
*/
'transactions' => [
'handler' => 'db',
'db' => [
'connection' => null,
],
],
'temporary_files' => [
/*
|--------------------------------------------------------------------------
| Local Temporary Path
|--------------------------------------------------------------------------
|
| When exporting and importing files, we use a temporary file, before
| storing reading or downloading. Here you can customize that path.
|
*/
'local_path' => storage_path('framework/cache/laravel-excel'),
/*
|--------------------------------------------------------------------------
| Remote Temporary Disk
|--------------------------------------------------------------------------
|
| When dealing with a multi server setup with queues in which you
| cannot rely on having a shared local temporary path, you might
| want to store the temporary file on a shared disk. During the
| queue executing, we'll retrieve the temporary file from that
| location instead. When left to null, it will always use
| the local path. This setting only has effect when using
| in conjunction with queued imports and exports.
|
*/
'remote_disk' => null,
'remote_prefix' => null,
/*
|--------------------------------------------------------------------------
| Force Resync
|--------------------------------------------------------------------------
|
| When dealing with a multi server setup as above, it's possible
| for the clean up that occurs after entire queue has been run to only
| cleanup the server that the last AfterImportJob runs on. The rest of the server
| would still have the local temporary file stored on it. In this case your
| local storage limits can be exceeded and future imports won't be processed.
| To mitigate this you can set this config value to be true, so that after every
| queued chunk is processed the local temporary file is deleted on the server that
| processed it.
|
*/
'force_resync_remote' => null,
],
];Byte Order Mark (BOM):
- UTF-8 with BOMは、ファイルの先頭に特殊なバイトシーケンス（EF BB BF）を含んでいます。このバイトシーケンスは、ファイルがUTF-8エンコーディングであることを示しています。
- 一方、標準的なUTF-8エンコーディングはBOMを含まないため、エンコーディングの自動検出が難しく、特にMicrosoft Excelのようなソフトウェアでは文字化けの問題が発生する可能性があります。
