【PHP(Laravel)】MySQLのデータを整形してExcelに出力する簡単な方法

2020.10.12
php備忘録
Excel出力, Laravel, MySQL, PHP, PhpSpreadsheet, xlsx, スクリプト備忘録, スプレッドシート, 企画書, 商品情報, 変換, 提案書, 自動処理

1. PHP(Laravel)でMySQLのデータを整形してExcelに出力する簡単な方法

PHP(Laravel)でMySQLのデータを整形してExcelに出力する簡単な方法

Laravel Excel というキーワードで検索している人って結構いるんだなぁ～と思い私が採用している簡単な方法を記載しておこうと思います。

Excel出力の方法は大別して2つ

LaravelでMySQLのテーブルを整形してExcelに出力する方法は2系統あります。
どんな道具を使うかにも関わる大切な所なので「自分のやりたいことにはどちらがマッチするか」を先に考えておきます。

事前準備したテンプレートの加工
ゼロからのExcelファイル作成

前者は「データの入っていないExcelファイルを用意しその上にデータを張り付けていく方法」で後者は文字通り「何もない所からExcelファイルを構築する方法」です。

それぞれのメリット/デメリット

それぞれに利点と欠点がります。簡単にまとめると下の通りです。

▼事前準備したテンプレートの加工

[利点]

出力に合わせた形に形成しやすい
型が決まっているので構築が楽

[欠点]

リストのような行数が可変する場合の対応が面倒
型が決まっているので、欠損情報があると目立つ

▼ゼロからのExcelファイル作成

[利点]

データの数によって幅や高さの調整を行えるので自由度が高い
出力情報に合わせてレイアウトを変更できるため出力ごとの最適化が出来る

[欠点]

高い自由度の分、コントロールする項目が多く構築が面倒
PDFや用紙への出力を検討する場合、変動する行数のコントロールが必要になる

ちなみに、私が作成するのは【提案書】【企画書】といった出力を伴う定型ファイルが多いのですが、これは超簡単に構築できます。

Excel出力が簡単なツールは何か

テンプレートを作成しておき、そこに値を書き込むスタイルであれば【PhpSpreadsheet】が一番簡単です。
Laravel-Excelも利用してみましたが、ごくごく単純に出来るのはphpspreadsheetの方でした。

インストールもcomposerに指示して一発。

composer require phpoffice/phpspreadsheet

1	composer require phpoffice/phpspreadsheet

phpspreadsheetの簡単な使い方

段取りはこんな感じです。

出力用のコードをまとめたディレクトリ Exports を用意する
読込むテンプレートファイル(XLSX)の位置と名前を固定し準備する
出力用コードの中で PhpSpreadsheet の5つのスクリプトをUSEする
どのセルに何の値を入れるか出力用のコード内で指示する
出力されたファイル(xlsx)の扱いを指示する(ダウンロードかメール送信かディレクトリ保存か)

簡単なテストコード

<?php
namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromCollection;
use App\Models\Item;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx as ReaderXlsx;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx as WriterXlsx;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing\Shadow;

class OneItemExportExcel
  public static function kikakusho($item_id){
    #テンプレートパスを指示
    $excel_file = storage_path('app/excel_format/OneItem.xlsx');
    $spreadsheet = self::createBase($excel_file,$item_id);
    #一時保存
    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, "Xlsx");
    $savepath = storage_path('app/excel_format/thumb/').$saleitemid.'_'.$customer_id.'.xlsx';
    $writer->save($savepath);

    return $savepath;
  }
  public static function createBase($excel_file,$item_id){
    #商品抽出
    $item = Item::find($item_id);
    #テンプレートファイル取得
    $reader = new ReaderXlsx();
    $spreadsheet = $reader->load($excel_file);
    #アクティブシートの取得
    $sheet = $spreadsheet->getActiveSheet();
    
    #QRコードを作成
    $qrcode = \QrCode::format('png')->size(78)->margin(0)->generate('生成したいURL');
    $QRpath =storage_path('app/excel_format/thumb/').$item->id.'.png';
    //保存
    file_put_contents($QRpath,$qrcode);
    #QRポジションに挿入
    self::InsertQRPict($sheet,$QRpath);
  }
  public static function InsertQRPict($sheet,$imagePath){
    #縦横サイズを決定
    $reductionRate = PictureFileControl::reductionRate($imagePath,63,63);
    #画像挿入
    (new Drawing())
    ->setPath($imagePath)
    ->setWorksheet($sheet)
    ->setResizeProportional(true)
    ->setWidth($reductionRate[0])
    ->setHeight($reductionRate[1])
    //記載セル設定
    ->setCoordinates('S9');
    return ;
  }

<?php

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromCollection;

use App\Models\Item;

use PhpOffice\PhpSpreadsheet\Spreadsheet;

use PhpOffice\PhpSpreadsheet\Reader\Xlsx as ReaderXlsx;

use PhpOffice\PhpSpreadsheet\Writer\Xlsx as WriterXlsx;

use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;

use PhpOffice\PhpSpreadsheet\Worksheet\Drawing\Shadow;

class OneItemExportExcel

public static function kikakusho($item_id){

#テンプレートパスを指示

$excel_file = storage_path('app/excel_format/OneItem.xlsx');

$spreadsheet = self::createBase($excel_file,$item_id);

#一時保存

$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, "Xlsx");

$savepath = storage_path('app/excel_format/thumb/').$saleitemid.'_'.$customer_id.'.xlsx';

$writer->save($savepath);

return $savepath;

}

public static function createBase($excel_file,$item_id){

#商品抽出

$item = Item::find($item_id);

#テンプレートファイル取得

$reader = new ReaderXlsx();

$spreadsheet = $reader->load($excel_file);

#アクティブシートの取得

$sheet = $spreadsheet->getActiveSheet();

#QRコードを作成

$qrcode = \QrCode::format('png')->size(78)->margin(0)->generate('生成したいURL');

$QRpath =storage_path('app/excel_format/thumb/').$item->id.'.png';

//保存

file_put_contents($QRpath,$qrcode);

#QRポジションに挿入

self::InsertQRPict($sheet,$QRpath);

}

public static function InsertQRPict($sheet,$imagePath){

#縦横サイズを決定

$reductionRate = PictureFileControl::reductionRate($imagePath,63,63);

#画像挿入

(new Drawing())

->setPath($imagePath)

->setWorksheet($sheet)

->setResizeProportional(true)

->setWidth($reductionRate[0])

->setHeight($reductionRate[1])

//記載セル設定

->setCoordinates('S9');

return ;

}

上のコードは用意したExcelファイルの『S列9行目』に作成したQRコードの画像を貼り付けます。

貼付け指示はたったこれだけ

#テンプレートファイル取得
$reader = new ReaderXlsx();
$spreadsheet = $reader->load(”Excelファイルへのパス”);

#アクティブシートの取得
$sheet = $spreadsheet->getActiveSheet();

/*画像貼り付けの場合*/
(new Drawing())
  ->setPath(’画像へのパス’)
  ->setWorksheet($sheet)
  ->setResizeProportional(true)
  ->setWidth(’画像の横幅’)
  ->setHeight(’画像の縦幅’)
  //記載セル設定
  ->setCoordinates('S9');

/*文字列貼付けの場合*/
$sheet->setCellValue('記載場所(列行)','記載テキスト');

#テンプレートファイル取得

$reader = new ReaderXlsx();

$spreadsheet = $reader->load(”Excelファイルへのパス”);

#アクティブシートの取得

$sheet = $spreadsheet->getActiveSheet();

/*画像貼り付けの場合*/

(new Drawing())

->setPath(’画像へのパス’)

->setWorksheet($sheet)

->setResizeProportional(true)

->setWidth(’画像の横幅’)

->setHeight(’画像の縦幅’)

//記載セル設定

->setCoordinates('S9');

/*文字列貼付けの場合*/

$sheet->setCellValue('記載場所(列行)','記載テキスト');

PhpSpreadsheetはxlsxだけでなくCSV出力にも使えます

このプラグインはcsv出力にも使えるから使い勝手はとてもいいです。

例えば、モールの出力形式に合わせたCSVを作成する時などに十分使えます。

まぁ私はAPIで出品してしまった方が楽なのでそんな風に仕掛けてますけど、手元にCSVが欲しい方は結構いらっしゃる様なので、今後PHPスプレッドシートを使って出力を組もうかなと考えてます。

APIで出品できない(しにくい)モールもあるみたいですしね。

まとめ

このブログにもLaravel Excelと言うキーワードで訪問してくださる方がいまして、その回答をどこにも記してないので申し訳ないなと思いこんな記事を書いてみました。

私が色々と作ってきた中で『簡単』だったり『当たり前』だったりと判断して記載していない内容は結構あります。
今後、そんな情報も随時更新していこうかなと思ってます。

さぁ、今日もお仕事頑張りましょう！

【PHP(Laravel)】MySQLのデータを整形してExcelに出力する簡単な方法

PHP(Laravel)でMySQLのデータを整形してExcelに出力する簡単な方法

Excel出力の方法は大別して2つ

それぞれのメリット/デメリット

Excel出力が簡単なツールは何か

phpspreadsheetの簡単な使い方

簡単なテストコード

貼付け指示はたったこれだけ

PhpSpreadsheetはxlsxだけでなくCSV出力にも使えます

まとめ

関連

SEO対策でHTMLヘッダーに施すogp等の内容と書き方について 2020.10.09

【PHP(Laravel)】JavaScriptの記載場所と登録方法について(jQuery含め) 2020.10.12

【PHP(Laravel)】MySQLのデータを整形してExcelに出力する簡単な方法

PHP(Laravel)でMySQLのデータを整形してExcelに出力する簡単な方法

Excel出力の方法は大別して2つ

それぞれのメリット/デメリット

Excel出力が簡単なツールは何か

phpspreadsheetの簡単な使い方

簡単なテストコード

貼付け指示はたったこれだけ

PhpSpreadsheetはxlsxだけでなくCSV出力にも使えます

まとめ

共有:

関連

SEO対策でHTMLヘッダーに施すogp等の内容と書き方について 2020.10.09

【PHP(Laravel)】JavaScriptの記載場所と登録方法について(jQuery含め) 2020.10.12