概要

EC-CUBE4の独自プラグイン開発で使い方を覚えたライブラリ第二弾。
PhpSpreadsheetでテンプレートのExcelに文字と画像を出力し、Excelファイルとしてダウンロードする方法について理解したことをまとめました。

ちなみに、私が実装したのはExcel 2007以降のExcelで、拡張子が.xlsxのものを指します。以降、Excel 2007のことを.xlsxと表現します。(PhpSpreadsheet内のクラスもそのような命名がされています)

私にとってはPhpSpreadsheetの使い方そのものよりも、

アスペクト比を維持したまま画像をリサイズするための計算処理
ラッパークラスを作ると再利用可能なコードができあがることを実感したこと

が重要な学びだったので、記事として記録することにしました。
今後も新たな使い方がわかれば更新していきます。

動作環境

PHP 7.3
Symfony 3.4
EC-CUBE 4.0.3
phpoffice/phpspreadsheet 1.12.0

PhpExcelよりもPhpSpreadsheetを推奨

いろんな記事で言われていることですが、
PhpSpreadsheetは、PhpExcelというライブラリの後継です。
新規でExcel出力に関する機能を実装するなら、PhpSpreadsheetを利用しましょう。
PhpSpreadsheetのgithubでも、PhpSpreadsheetを利用することを推奨しています。

PHPExcel vs PhpSpreadsheet ?
PhpSpreadsheet is the next version of PHPExcel. It breaks compatibility to dramatically improve the code base quality (namespaces, PSR compliance, use of latest PHP language features, etc.).

Because all efforts have shifted to PhpSpreadsheet, PHPExcel will no longer be maintained. All contributions for PHPExcel, patches and new features, should target PhpSpreadsheet master branch.

Do you need to migrate? There is an automated tool for that.

以下、ざっくり日本語訳

PHPExcelか、PhpSpreadsheetか?
PhpSpreadsheetはPHPExcelの次のバージョンです。コードベースの品質（名前空間、PSRへの準拠、最新のPHP言語機能の使用など）を劇的に向上させるために、互換性が失われます。

すべての取り組みがPhpSpreadsheetに移行したため、PHPExcelは維持されなくなります。パッチや新機能などPHPExcelへのすべての貢献は、PhpSpreadsheetのmasterブランチを対象とする必要があります。

移行したいですか？そのための自動化ツールがあります。

PhpExcelからPhpSpreadsheetへの自動移行ツールも存在するようなので、「古いシステムでPhpExcelを使っているがPhpSpreadsheetに変更したい...！」という人向けのサポートも探せばありそうです。
扱ったことがないので、本記事では説明しません。

使い方

少しずつコードを書きながら説明します。
\$spreadsheetはExcelブック(Excelファイルそのもの)、\$worksheetは現在選択中のシート、と捉えてください。
最後にサンプルコードを載せたので、コピーして試しに出力してみると使い方が分かると思います。

※一部コード例で$this->editorが呼ばれていますが、これはサンプルコードのPhpSpreadsheetWrapperクラスのことを指します。
途中から例を書くのが面倒になってPhpSpreadsheetWrapperの抜粋と化しています。

1. Excelファイルを読み込む

テンプレートとなるsample-invoice.xlsxを用意し、それを読み込みます。

<?php

use PhpOffice\PhpSpreadsheet\Reader\Xlsx as XlsxReader;    

class Xxx {

    public function yyyy() 
    {
        $filePath = ./xxx/xxx/sample-invoice.xlsx;
        $reader = new XlsxReader();
        $spreadsheet = $reader->load($filePath);
    }
}

ユーザーがアップロードしたファイルを読み取る必要があり、ユーザーがxlsファイルをアップロードしているか、xlsxファイルをアップロードしているかがわからない場合は、IOFactoryクラスを使えばよいようですが、

Note that automatic type resolving mode is slightly slower than explicit mode.

自動型解決モードは、明示的モードよりも少し遅いことに注意してください。

とあったので、Excel 2007固定ならば明示的にXlsxクラスを使ったほうがよさそうです。

2. シート名で編集するシートを選択する

テンプレートを読み込んだら、シート名を基に編集したいシートを選択します。
ここで選択したシートに対してセルの値入力や画像の出力を行います。

指定するシート名は、日本語でも問題ありませんでした。
\$spreadsheetはExcelブック(Excelファイルそのもの？)、\$worksheetは現在選択中のシート、と考えると分かりやすいと思います。

<?php

use PhpOffice\PhpSpreadsheet\Reader\Xlsx as XlsxReader;    

class Xxx {
    public function yyyy() 
    {
        $filePath = ./xxx/xxx/sample-invoice.xlsx;
        $reader = new XlsxReader();
        $spreadsheet = $reader->load($filePath);

        $worksheet = $spreadsheet->getSheetByName('請求書');
    }
}

3. 指定したセルに文字を入力する

A5形式で表記したセル番地と、入力したい文字列をsetCellValue()メソッドに渡すだけです。

<?php

use PhpOffice\PhpSpreadsheet\Reader\Xlsx as XlsxReader;    

class Xxx {
    public function yyyy() 
    {
        $filePath = ./xxx/xxx/sample-invoice.xlsx;
        $reader = new XlsxReader();
        $spreadsheet = $reader->load($filePath);

        $worksheet = $spreadsheet->getSheetByName('請求書');

        $worksheet->setCellValue('B3', '4/12');
        $worksheet->setCellValue('B5', 'テスト請求先');
        $worksheet->setCellValue('C9', 1000);
    }
}

4. 画像ファイルのパスを指定して画像を貼り付ける

画像を貼り付けるには、まずDrawingクラスのインスタンスを生成します。
Drawingインスタンスに、出力したい画像のパスや縦横何pxで出力するかなどを設定し、どのExcelシートに画像を出力するか指定します。

<?php

use PhpOffice\PhpSpreadsheet\Reader\Xlsx as XlsxReader;    
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;

class Xxx {
    public function yyyy() 
    {
        $filePath = './xxx/xxx/sample-invoice.xlsx';
        $reader = new XlsxReader();
        $spreadsheet = $reader->load($filePath);

        $worksheet = $spreadsheet->getSheetByName('請求書');

        $worksheet->setCellValue('B3', '4/12');
        $worksheet->setCellValue('B5', 'テスト請求先');
        $worksheet->setCellValue('C9', 1000);

        $path = './xxx/xxx/image.png';
        (new Drawing())
            ->setPath($path) // 画像のパス
            ->setCoordinates('E2') // ここで指定したセル番地が画像の一番左上になる
            ->setWidth(200) // 画像の幅を何pxで出力するか
            ->setHeight(100) // 画像の高さを何pxで出力するか
            ->setResizeProportional(false) // アスペクト比を維持するならfalse
            ->setWorksheet($worksheet); // どのExcelシートに画像を出力するか
    }
}

5. アスペクト比を維持したままリサイズした画像を貼り付ける

phpspreadsheetでは、
画像を出力するDrawingクラスにsetResizeProportional()「アスペクト比を維持したまま辺をリサイズする」や、 setWidth()「幅を指定する」、setHeight()「高さを指定する」というメソッドが用意されている。

これらを使えば、指定した最大高さ・最大幅に合わせて、かつアスペクト比を維持したまま画像をリサイズしてくれ・・・なかったので工夫した。
どういうことか数字を伴って説明すると、

画像の高さを最大100px、幅を最大100pxとなるようにアスペクト比を維持したままExcelに出力したい場合、
実際の画像が高さ:200px 幅:400pxなら、幅の最大値に合わせるためにそれぞれ1/4にして高さ:50px 幅:100px と出力したい。

ところが、phpspreadsheetの場合、先ほどのメソッドを使っても高さの最大値に対してのみリサイズするので、高さ:100px 幅:200pxの画像が出力されてしまう。

この現象を回避するため、自分で縮小・拡大する比率を求めて出力する画像の高さ・幅を計算することにした。

コード例

    public function setImage(string $path, string $coordinates, int $maxWidth, int $maxHeight, bool $changeAspectRatio = false): self
    {
        [$originWidth, $originHeight] = getimagesize($path);

        $widthReductionRate = $maxWidth / $originWidth; // 幅を縮小・拡大する比率
        $heightReductionRate = $maxHeight / $originHeight;　// 高さを縮小・拡大する比率

        // 画像縮小時は、より縮小率が大きいほう、つまり値が小さいほうを計算に使う。
        // 画像拡大時は、より拡大率が小さいほう、つまり値が小さいほうを計算に使う。
        $reductionRate = min($widthReductionRate, $heightReductionRate);

        $width = $originWidth * $reductionRate;
        $height = $originHeight * $reductionRate;

        (new Drawing())
            ->setPath($path) // 画像のパス
            ->setCoordinates($coordinates) // ここで指定したセル番地が画像の一番左上になる
            ->setWidth($width) // 画像の幅を何pxで出力するか
            ->setHeight($height) // 画像の高さを何pxで出力するか
            ->setResizeProportional($changeAspectRatio) // 比率を自分で求めたのでこの設定は不要かも。
            ->setWorksheet($worksheet); // どのExcelシートに画像を出力するか

        return $this;
    }

        // 画像縮小時は、より変化が大きいほう、つまり値が小さいほうを計算に使う。
        // 画像拡大時は、より変化が小さいほう、つまり値が小さいほうを計算に使う。
        $reductionRate = min($widthReductionRate, $heightReductionRate);

この部分が、あまり直感的でなくて自分でも混乱している・・・。
去年画像のトリミング機能を作ったときも、縮小率・拡大率を求めて、それを元画像の幅・高さにかけた値でリサイズする、という処理を行ったので、合っているはず。

6. blob型でデータベースに保存した画像をExcelに貼り付ける

PHPでblob型でデータベースに保存した画像を出力する

この記事との合わせ技です。
MySQLのデータベースにblob型で保存したpng画像をExcelに貼る方法を説明します。

画像ファイルを貼る場合は、先ほど説明した通り、phpspreadsheetのPhpOffice\PhpSpreadsheet\Worksheet\Drawingクラスを利用しました。

バイナリな画像を貼るには、PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawingを利用します。
使い方は、Drawingクラスとほぼ同じです。違いは、以下の2点です。

画像のパスではなくリソースを渡す
setRenderingFuntion()とsetMimeType()を呼び出し、画像の種類に応じて引数を変える

ここでも、画像のアスペクト比を維持したかったので、縮小比(拡大比)を求めて幅・高さを指定しました。

コード例

呼び出す側 (商品の画像をExcelに貼るイメージです。)
この記事でも説明したが、stream_get_contents() の offset を 0 にする必要があります。初期値の -1 だと、 getPng() を呼び出したときにポインタが移動したままになると推測され、2回目以降に呼び出したときに取得できず、MemoryDrawingのsetImageResource()で「文字列がないよ」とエラーになります。

    private function writeImageFromString(Product $product): void
    {
        $this->editor->setPngImageFromString(
            imagecreatefromstring(stream_get_contents($product->getPng(), -1, 0)),
            'M6',
            720,
            1080,
            false
        );
    }

呼び出される側
今回はpng固定だったのでこのような例になっていますが、png以外の画像を貼りたい場合は、MemoryDrawing::RENDERING_PNG MemoryDrawing::MIMETYPE_PNGの部分を他の定数に変更します。

    public function setPngImageFromString($resource, string $coordinates, int $maxWidth, int $maxHeight, bool $changeAspectRatio = false): self
    {
        $originWidth = imagesx($resource);
        $originHeight = imagesy($resource);

        $widthReductionRate = $maxWidth / $originWidth;
        $heightReductionRate = $maxHeight / $originHeight;
        $reductionRate = min($widthReductionRate, $heightReductionRate);

        $width = $originWidth * $reductionRate;
        $height = $originHeight * $reductionRate;

        (new MemoryDrawing())
            ->setImageResource($resource)
            ->setRenderingFunction(MemoryDrawing::RENDERING_PNG)
            ->setMimeType(MemoryDrawing::MIMETYPE_PNG)
            ->setCoordinates($coordinates)
            ->setWidth($width)
            ->setHeight($height)
            ->setResizeProportional($changeAspectRatio)
            ->setWorksheet($this->worksheet);

        return $this;
    }

7. セル内で文字列を改行する

.xlsx の場合、改行コードが含まれた長い文字列(ex:商品の説明文や備考欄の文章)をsetCellValue()で書き込みすると、勝手に改行コードを基に改行してくれます。

※.xlsの場合は未確認です。

8. (参考)文字列が長い場合に自動でセルの高さを大きくする

※サンプルコードでも触れていますが、この方法は条件によってはうまくいかない可能性があります。

金額や住所・氏名はほぼ一定の長さの文字列だと思いますが、商品名などは場合によっては長くなると考えられます。
もしセルに収まりきらないのなら、文字を折り返し表示してすべての文字列が見えるようにしたいものです。

正攻法かわからないのですが、次のような方法をとりました。
テンプレートである元のExcelに細工をするので、ユーザがアップロードしたExcelに対しては対処できないのが難点です。

自動でセルの高さを大きくする

まず、テンプレートのExcelで、折り返し表示したいセルで「折り返して全体を表示する」を有効にします。こうすることで、setCellValue()で文字を入力したとき、自動で行の高さが大きくなり、セル内のテキストもセルの幅に合わせて折り返し表示されます。これはExcelを通常通りアプリケーションで開いたときの挙動と同じですね。

※この方法では、行の高さが自動で変わるため、同じ行のセルに影響があることに注意。

自動で列幅を広くすることはできなかった

こうすることで自動で行の高さを変えることはできるものの、列幅を自動で広くすることはできませんでした。
しかし、セルの幅が変わらないとなると、列幅が狭いセルの中に何行も文字列が入力される・・・という不格好な状態になります。

一方、結合したセルにsetCellValue()で文字を入力すると、今度は列幅が15pxくらいになるという現象が発生しました。
最終的な解決方法は、予めセルの幅を広くしておくことでした。

※この方法も、列の幅を変えるため、同じ列のセルのレイアウトを考慮する必要がある。

私の場合、テンプレートの本来のレイアウトにおいて、入力セルはL10、長い文字列のときはL,M,N列の3列分に収めて折り返し表示したかったので、L列を3列分の幅(216px)に変更し、先ほど説明したようにL10セルで「折り返して全体を表示する」を有効にしました。
これで、L10に216px分以上の長い文字列が入力される場合は、自動で折り返し表示されます。列幅が狭いセルの中に10行くらい文字が入力される・・・ということにもなりませんでした。

9. 印刷範囲を設定する

phpspreadsheetでは、出力したExcelを印刷するときの範囲を指定できます。
改ページプレビューのときに表示される青い太枠内のことで、この画像の例ではA1セルから12セルまでの範囲です。

呼び出す側

$this->editor->setPrintArea('A1:S12');

呼び出される側

public function setPrintArea(string $range): self
{
    $this->worksheet->getPageSetup()->setPrintArea($range);

    return $this;
}

10. 印刷ページ(印刷グリッド)を設定する(次のページ数に合わせて印刷)

印刷範囲だけでなく、印刷グリッドも指定できます。
印刷グリッドというのは、簡単にいうと、印刷範囲を何ページとして印刷するか？を指定するためのグリッドです。

印刷範囲を1ページに収めたいとき

私が今回実装したのはこのパターンでした。呼び出すだけで1ページに収まる設定ができるようにsetPrintGridToOnePage()メソッドを作りました。

呼び出す側

$this->editor->setPrintGridToOnePage();

呼び出される側

public function setPrintGridToOnePage(): self
{
    $this->worksheet->getPageSetup()->setFitToPage(true);

    return $this;
}

印刷範囲を縦Nページ、横Nページに収めたいとき

「次のページ数に合わせて印刷」のように指定したいときはsetFitToWidth() setFitToHeight()を呼びます。

ただし、マウスでグリッドを指定するようにグリッド線の位置を正確に指定する方法は分かりませんでした。(方法が無いというより、使わなかったので深く調べていない)

呼び出す側
先ほどの画像のように、横は2ページ、縦は1ページに収めたい場合は、こんな風に呼び出します。

$this->editor->setPrintGridForWidth(2)
    ->setPrintGridForHeight(1);

呼び出される側

public function setPrintGridForWidth(int $page): self
{
    $this->worksheet->getPageSetup()->setFitToWidth($page);

    return $this;
}

public function setPrintGridForHeight(int $page): self
{
    $this->worksheet->getPageSetup()->setFitToHeight($page);

    return $this;
}

11. 書き込んだExcelを.xlsxとしてブラウザでダウンロードする

当初、.xlsxとして出力する方法がなかなか分からず、半日ほどかけて調べ、最終的にこのようになりました。

<?php

use PhpOffice\PhpSpreadsheet\Reader\Xlsx as XlsxReader;    
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;

class Xxx {

    public function yyyy() 
    {
        $filePath = './xxx/xxx/sample-invoice.xlsx';
        $reader = new XlsxReader();
        $spreadsheet = $reader->load($filePath);

        $worksheet = $spreadsheet->getSheetByName('請求書');

        $worksheet->setCellValue('B3', '4/12');
        $worksheet->setCellValue('B5', 'テスト請求先');
        $worksheet->setCellValue('C9', 1000);

        $path = './xxx/xxx/image.png';
        (new Drawing())
            ->setPath($path) // 画像のパス
            ->setCoordinates('E2') // ここで指定したセル番地が画像の一番左上になる
            ->setWidth(200) // 画像の幅を何pxで出力するか
            ->setHeight(100) // 画像の高さを何pxで出力するか
            ->setResizeProportional(false) // アスペクト比を維持するならfalse
            ->setWorksheet($worksheet); // どのExcelシートに画像を出力するか

        $fileName = '請求書_20200412.xlsx';

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;');
        header("Content-Disposition: attachment; filename=\"{$fileName}\"");
        header('Cache-Control: max-age=0');

        $writer = new XlsxWriter($spreadsheet);
        $writer->save('php://output');
        exit;
    }
}

半日かけて方法を突き止めたのはいいのですが、公式のRecipesに記載がありました。

出力したExcelファイルが破損する理由については、困ったときのTips事象①を参照してください。

MIME type、Content-Type

これはExcel 2007以降のExcel .xlsxのことを表す。

application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

.xlsとして出力したい場合は、以下を指定する。

application/vnd.ms-excel

参考

よくある MIME タイプ

Content-Disposition

attachment;を指定することで、ブラウザにファイルをダウンロードするように命じることができる。
filename={$fileName}では、文字通りダウンロードしたファイルの名前を指定できる。

Cache-Control

レスポンスの内容をキャッシュするかしないか、キャッシュをするなら有効期限はどれくらいか、キャッシュの再読み込みをどうするか、などを設定する。
phpspreadsheet公式でmax-age=0を指定しているので、常に新しいリソースとしてブラウザに認識されることがある・・・ということだろうか・・・。

Cache-Control 一般ヘッダーフィールドは、リクエストとレスポンスの両方のキャッシュ規則を指定するために用います。

max-age=<seconds>

リソースが新しいとみなされる最長の時間を指定します。 Expires とは対照的に、このディレクティブはリクエスト時刻からの相対時間です。

12. サーバ内の特定のパスにExcelを保存する

ブラウザでダウンロードするのではなく、サーバ側に保存する方法を説明します。
といっても、XlsxWriterクラスのsave()を呼ぶ際に、php://output→exit;するのではなく、save()に保存したいファイルのパスを絶対パスで渡すだけです。

使いどころとしては、私の場合、複数のファイルを圧縮して1つのZipをブラウザでダウンロードさせる際、一時的にファイルとして保存するためにこの方法を使いました。
Zip化については別の記事にまとめました。

コード例

呼び出す側

※抜粋のため、変数には適当な注文番号やらパスの文字列が入っていると思ってください。

$fileName = sprintf('請求書-%s-%s-%s.xlsx', $order->getOrderNo(), $orderItem->getProductCode(), $serialNumber)
$this->editor->saveFile(sprintf('%s/%s', $temporaryPath, $fileName));

呼び出される側

public function saveFile(string $fileName): void
{
    $writer = new XlsxWriter($this->spreadsheet);
    $writer->save($fileName);
}

サンプルコード

コピペしてすぐ試せるように、出力される文字列はべた書きにしています。
実際には、DBから取ってきたデータをセルに入力したり、商品の画像を出力したりしました。

テンプレートとなるsample-invoice.xlsxの内容はこんな感じです。Excelのオンラインテンプレートの「青色の請求書」を簡略化のため少し編集しました。

自社名部分はセルではなくテキストボックス内の文字で、phpspreadsheetでテキストボックスに文字を入力する方法は分からないのでこの記事では書きません。もし調べてわかったひとがいたら、ぜひ記事にしてください。

小計や 合計(税込)はセルの計算式で算出できるはずと思いましたが、計算式が記述されたセルがあると次のようなエラーが出ました。セルの計算式が使えないとなると、プログラム側で計算した値をphpspreadsheetで入力する必要がありそうです。

Error: An unexpected error occured" at Uncaught PHP Exception PhpOffice\PhpSpreadsheet\Calculation\Exception

※ファイルパスには各自で用意したテンプレート・画像を指定してください。

サンプルコード

<?php

namespace Xxx;

use Eccube\Controller\AbstractController;
use Xxx\Xxx\Service\Excel\OutputInvoiceService;
use Symfony\Component\Routing\Annotation\Route;

class OutputController extends AbstractController
{
    /**
     * @Route("/output/invoice", name="output_invoice")
     *
     * @param OutputInvoiceService $outputInvoiceService
     *
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     */
    public function output(OutputInvoiceService $outputInvoiceService): void
    {
        $outputInvoiceService->outputInvoice();
    }
}

<?php

namespace Xxx\Xxx\Service\Excel;

use Eccube\Common\EccubeConfig;
use Exception;

class OutputInvoiceService
{
    /** @var EccubeConfig */
    private $eccubeConfig;

    /** @var PhpSpreadsheetWrapper */
    private $editor;

    /**
     * @param EccubeConfig $eccubeConfig
     * @param PhpSpreadsheetWrapper $phpSpreadsheetWrapper
     */
    public function __construct(
        EccubeConfig $eccubeConfig,
        PhpSpreadsheetWrapper $phpSpreadsheetWrapper
    ) {
        $this->eccubeConfig = $eccubeConfig;
        $this->editor = $phpSpreadsheetWrapper;
    }

    /**
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws Exception
     */
    public function outputInvoice()
    {
        $this->editor->load($this->getTemplateFilePath())
            ->selectSheetByName('請求書');

        $this->writeImage();

        $this->writeIssueDate();

        $this->writeCustomerInformation();

        $this->writeOrderStatement();
        $this->writePrice();

        $this->editor->output($this->getFileName());
    }

    private function getTemplateFilePath(): string
    {
        return $this->eccubeConfig->get('plugin_realdir') . '/xxx/xxx/template/sample-invoice.xlsx';
    }

    /**
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     */
    private function writeImage()
    {
        $imagePath = sprintf('%s/%s', $this->eccubeConfig->get('eccube_save_image_dir'), 'egg.jpg');

        $this->editor->setImage($imagePath, 'F2', 400, 400, false);
    }

    private function writeIssueDate()
    {
        $this->editor->setCellValue('B3', '4月12日');
    }

    private function writeCustomerInformation()
    {
        $this->editor->setCellValue('B5', 'テスト太郎 A社');
        $this->editor->setCellValue('B6', '111-1111 こっち県そこ街');
        $this->editor->setCellValue('B7', '000-111-4444');
    }

    private function writeOrderStatement()
    {
        // 「折り返して全体を表示する」が有効なセルの例
        $this->editor->setCellValue('B9', '短い文字列');
        $this->editor->setCellValue('B10', '長い文字列あいうえおかきくけこさしすせそたちつてとなにぬねのはひふへほまみむめも');

        // 「折り返して全体を表示する」が無効なセルの例
        $this->editor->setCellValue('B11', '短い文字列');
        $this->editor->setCellValue('B12', '長い文字列あいうえおかきくけこさしすせそたちつてとなにぬねのはひふへほまみむめも');
    }

    private function writePrice()
    {
        $this->editor->setCellValue('C9', 100);
        $this->editor->setCellValue('C10', 200);
        $this->editor->setCellValue('C11', 300);
        $this->editor->setCellValue('C12', 400);
    }

    private function getFileName(): string
    {
        return sprintf('請求書_%s.xlsx', date('Ymd'));
    }

}

<?php

namespace Xxx\Xxx\Service\Excel;

use PhpOffice\PhpSpreadsheet\Exception;
use PhpOffice\PhpSpreadsheet\Reader\Exception as ReaderException;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx as XlsxReader;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Exception as WriterException;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx as XlsxWriter;

class PhpSpreadsheetWrapper
{
    private Spreadsheet $spreadsheet;

    private Worksheet $worksheet;

    public function load(string $filePath): self
    {
        $this->spreadsheet = (new XlsxReader())->load($filePath);

        return $this;
    }

    public function selectSheetByName(string $name): self
    {
        $this->worksheet = $this->spreadsheet->getSheetByName($name);

        return $this;
    }

    public function setImage(string $path, string $coordinates, int $maxWidth, int $maxHeight, bool $changeAspectRatio = false): self
    {
        [$originWidth, $originHeight] = getimagesize($path);

        $widthReductionRate = $maxWidth / $originWidth;
        $heightReductionRate = $maxHeight / $originHeight;
        $reductionRate = min($widthReductionRate, $heightReductionRate);

        $width = $originWidth * $reductionRate;
        $height = $originHeight * $reductionRate;

        (new Drawing())
            ->setPath($path)
            ->setCoordinates($coordinates)
            ->setWidth($width)
            ->setHeight($height)
            ->setResizeProportional($changeAspectRatio)
            ->setWorksheet($this->worksheet);

        return $this;
    }

    public function setPngImageFromString($resource, string $coordinates, int $maxWidth, int $maxHeight, bool $changeAspectRatio = false): self
    {
        $originWidth = imagesx($resource);
        $originHeight = imagesy($resource);

        $widthReductionRate = $maxWidth / $originWidth;
        $heightReductionRate = $maxHeight / $originHeight;
        $reductionRate = min($widthReductionRate, $heightReductionRate);

        $width = $originWidth * $reductionRate;
        $height = $originHeight * $reductionRate;

        (new MemoryDrawing())
            ->setImageResource($resource)
            ->setRenderingFunction(MemoryDrawing::RENDERING_PNG)
            ->setMimeType(MemoryDrawing::MIMETYPE_PNG)
            ->setCoordinates($coordinates)
            ->setWidth($width)
            ->setHeight($height)
            ->setResizeProportional($changeAspectRatio)
            ->setWorksheet($this->worksheet);

        return $this;
    }

    public function setCellValue(string $coordinate, string $value): self
    {
        $this->worksheet->setCellValue($coordinate, $value);

        return $this;
    }

    public function setPrintArea(string $range): self
    {
        $this->worksheet->getPageSetup()->setPrintArea($range);

        return $this;
    }

    public function setPrintGridToOnePage(): self
    {
        $this->worksheet->getPageSetup()->setFitToPage(true);

        return $this;
    }

    public function setPrintGridForWidth(int $page): self
    {
        $this->worksheet->getPageSetup()->setFitToWidth($page);

        return $this;
    }

    public function setPrintGridForHeight(int $page): self
    {
        $this->worksheet->getPageSetup()->setFitToHeight($page);

        return $this;
    }

    public function output(string $fileName): void
    {
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;');

        // attachment;とすると、ブラウザにダウンロードするように命令できる。
        // filenameには、ダウンロード時のファイル名を指定する。
        // filename=とfilename*=、2つあるのはIEでダウンロードすると、日本語ファイル名が文字化ける現象への対策。
        // filenameを併記すると、filename*=を優先し、filename*=に対応していないブラウザはfilename=を参照する。
        header("Content-Disposition: attachment; filename=\"{$zipFileName}\"; filename*=utf-8''" . rawurlencode($zipFileName));

        header('Cache-Control: max-age=0');

        $writer = new XlsxWriter($this->spreadsheet);
        $writer->save('php://output');
        exit;
    }

    public function saveFile(string $fileName): void
    {
        $writer = new XlsxWriter($this->spreadsheet);
        $writer->save($fileName);
    }
}

このサンプルコードから、生成されたExcelファイルがこちらです。

元のテンプレートファイルでは、B9・B10のセルは「折り返して全体を表示する」を有効に、B11・B12のセルは「折り返して全体を表示する」を無効にしてあります。
これで文字が折り返され・・・てはいますが、行の高さが自動で変わらないですね・・・。セルの設定によっては効かないのかもしれません。わかったら追記します。

ラッパークラスを作った

「ライブラリをラップしたラッパークラスを作ったほうが、ライブラリが変更したときに対応しやすいし、何かと都合がよいものだよ」と、人に言われはするのですが、
去年LaravelでInterventionImageライブラリを使って画像のトリミング機能を作った際はあまり実感がわかないままでした。

実際に実装した際は、「テンプレート.xlsxを読み込んで文字や画像を書き込みExcelとして出力する」という機能を複数作る必要がありました。なので、複数の機能で再利用できるようにクラスを作る必要があると考えました。

そうしてできたのがPhpSpreadsheetWrapperクラスです。
こちらの記事、およびgithubを参考にしました。
PhpSpreadsheetのwrapperをとりあえず整備しました　 github

ControllerからServiceクラスを呼び出し、ServiceクラスからPhpSpreadsheetWrapperクラスのメソッドを呼ぶことで、
「処理の流れ」
「データをExcelのどこにどう書き込むか指定することで、どんなExcelを出力するのか決める」
「Excelに書き込み、Excelとして出力する」
の3つに分けられました。まだまだ改善の余地があるでしょう。

名前に日本語を含むファイルをIEでダウンロードしたとき文字化けを防ぐ

ヘッダーのContent-Dispositionについて、別の記事で参考記事とともに説明しているのでそちらを参照ください。
PHPで複数ファイル(blob型のファイル含む)をZipダウンロードする方法

php

// attachment;とすると、ブラウザにダウンロードするように命令できる。
// filenameには、ダウンロード時のファイル名を指定する。
// filename=とfilename*=、2つあるのはIEでダウンロードすると、日本語ファイル名が文字化ける現象への対策。
// filenameを併記すると、filename*=を優先し、filename*=に対応していないブラウザはfilename=を参照する。
header("Content-Disposition: attachment; filename=\"{$zipFileName}\"; filename*=utf-8''" . rawurlencode($zipFileName));

困ったときのTips

事象①　ファイルが破損している

Excelファイルをダウンロードできた、と思ったら拡張子なしでダウンロードされた。

拡張子を.xlsxに変更して無理矢理閲覧すると、こんなことを言われてしまう。

この現象は、PHPのエラーが出ているためにファイルが破損しているために起きている可能性がある。私の場合はそうだった。
サクラエディタでこの破損したファイルを開くと...

ExcelはバイナリデータのはずなのにHTMLやPHPのエラーまで出力されている。これが、ファイルが破損していますよー、といわれる原因。出力されているHTMLやPHPのエラーは、単にこの変数は存在しません、という内容のsymfonyのException。

なので、ExcelをダウンロードするページやエラーページのHTMLがExcel上に出力されないように、exit;で処理を止める必要がある。

いろんな記事で、Excelファイルを出力する際には exit;が書かれていました。これがなぜなのか、どうしてこう書くとExcelファイルを出力できるのか分からなかったのですが、公式のRecipesに次のような記述があります。

Make sure not to include any echo statements or output any other contents than the Excel file.

(中略)

Failing to follow the above guidelines may result in corrupt Excel files arriving at the client browser, and/or that headers cannot be set by PHP (resulting in warning messages).

エコーステートメントを含めたり、Excelファイル以外の内容を出力したりしないでください。

(中略)

上記のガイドラインに従わないと、Excelのファイルが破損してクライアントのブラウザーに到達したり、PHPでヘッダーを設定できなかったりする可能性があります（警告メッセージが表示されます）。

これに従うためにExcel書き込み・出力が終わったらすぐexit;する必要がある・・・ということらしい。

事象②　出力したExcelに拡張子がない

事象①をクリアして破損はなくなるも、拡張子が無いまま・・・。
とおもったらファイル名を指定するときに.xlsxをつけ忘れていただけだった。
これを読んでいるひとも気を付けて...

参考記事

PhpSpreadsheetのwrapperをとりあえず整備しました
- github 記事内にもリンクありラッパークラスを作ってみようと思ったものの、どう書けばいいのかわからなかったので大変参考になりました。
MDN web docs HTTP ヘッダー
- phpspreadsheetに関する記事はたくさん存在するが、存在しないレスポンスヘッダーを使用していたり、誤った使い方をしている例もある。一度意味を調べたほうが、自分のためにもコードのためにもいい。
- application/force-downloadなんて使ってはいけない。
【PHP】正しいダウンロード処理の書き方
PHPExcelでエクセル出力入門 Excelをダウンロードできるようにする
- PHPExcel(古いので非推奨なライブラリ)の記事ですが、Excelをファイルとしてダウンロードする際のレスポンスヘッダーの設定内容を調べているときに参考になりました。
PhpSpreadsheetのTIPSあれこれ
- 本記事よりも高度な操作をしたい人は、こちらの記事のほうが参考になるかもしれません。他の記事も紹介されています。
PhpSpreadsheetで数値、文字列、金額、日付、画像をシートに書き込むサンプル
phpspreadsheetがチョー便利！phpからExcelの読み書きプログラミング
PhpSpreadsheet Documentation
PhpSpreadsheet API
PHPExcel と PhpSpreadsheet の比較
- 印刷ページの指定方法が分からず、行き着いたページです。また良い記事を見つけてしまった。
PHPExcelで印刷用の改ページ設定について
- PHPExcelの記事ですが、この記事のおかげでsetFitToPage()の存在に気づけました。
幅を指定したページ数に収める
- 他のページは確認していないけど、いろいろやり方が載っていそう・・・？

PhpSpreadsheetでExcelを読み書きしてExcelとしてダウンロードする

概要

動作環境

PhpExcelよりもPhpSpreadsheetを推奨

使い方

1. Excelファイルを読み込む

2. シート名で編集するシートを選択する

3. 指定したセルに文字を入力する

4. 画像ファイルのパスを指定して画像を貼り付ける

5. アスペクト比を維持したままリサイズした画像を貼り付ける

コード例

6. blob型でデータベースに保存した画像をExcelに貼り付ける

コード例

7. セル内で文字列を改行する

8. (参考)文字列が長い場合に自動でセルの高さを大きくする

自動でセルの高さを大きくする

自動で列幅を広くすることはできなかった

9. 印刷範囲を設定する

10. 印刷ページ(印刷グリッド)を設定する(次のページ数に合わせて印刷)

印刷範囲を1ページに収めたいとき

印刷範囲を縦Nページ、横Nページに収めたいとき

11. 書き込んだExcelを.xlsxとしてブラウザでダウンロードする

MIME type、Content-Type

参考

Content-Disposition

Cache-Control

12. サーバ内の特定のパスにExcelを保存する

コード例

サンプルコード

ラッパークラスを作った

名前に日本語を含むファイルをIEでダウンロードしたとき文字化けを防ぐ

困ったときのTips

事象① ファイルが破損している

事象② 出力したExcelに拡張子がない

参考記事

事象①　ファイルが破損している

事象②　出力したExcelに拡張子がない