目次
はじめに
ユーザーにエクセルで入力してもらい、それをアップロードしてシステムに登録するという要件が発生しました。
この時に問題なのは、ユーザーに自由に入力されてしまうとバリデーションが大変になるということです。
なのでユーザーの入力値を固定するためにドロップダウンリストから選択させる方式にしました。
なんとPhpSpreadsheetはドロップダウンリストを設定することができるのです！（ドーン
環境
PhpSpreadsheet: 1.6
エクセルのドロップダウンリスト
エクセル本体でドロップダウンリストを設定するには、「データの入力規則」から設定することができます。
PhpSpreadsheetでは、どのように設定したら良いでしょうか？
PhpSpreadsheetでドロップダウンリストを設定する
エクセルを準備
まず以下の画像のようなSheetが２つあるエクセルを準備します。
Sheet１
Sheet2
PHPソース
まずPhpSpreadsheetの公式ドキュメントの下記を参考にします。
Setting data validation on a cell
PhpSpreadsheetでドロップダウンリストを設定するには以下のようにします。
use PhpOffice\PhpSpreadsheet\NamedRange;
// 「Sheet1」シートを取得
$sheet = $this->spreadsheet->getSheet(0);
// セルB3を取得
$CellB = $sheet->getCell('B3')->getDataValidation();
$CellB->setType( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST );
$CellB->setErrorStyle( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_INFORMATION );
$CellB->setAllowBlank(false);
$CellB->setShowInputMessage(true);
$CellB->setShowErrorMessage(true);
$CellB->setShowDropDown(true);
$CellB->setErrorTitle('入力エラー');
$CellB->setError('値がリストの中にありません');
$CellB->setPromptTitle('選択してください。');
// 「Sheet2」シートのC列のC2以降の値を持つセルの一覧を取得する
$CellB->setFormula1("=OFFSET('Sheet2'!\$C\$2,0,0,COUNTA('Sheet2'!\$C:\$C)-1,1)");
解説
今回の重要なところは setFormula1 の値です。
setFormula1の値
サンプルの値は以下になります。
=OFFSET('Sheet2'!\$C\$2,0,0,COUNTA('Sheet2'!\$C:\$C)-1,1)
上記は「＄」をエスケープしてるので、エクセル上では以下になります。
=OFFSET('Sheet2'!$C$2,0,0,COUNTA('Sheet2'!$C:$C)-1,1)
まず処理されるのはCOUNT関数です。
COUNTA('Sheet2'!$C:$C)-1
COUNTA関数を使って「Sheet２」シートのC列の値の入っている数を数え、見出しの一行文を差し引いています。
今回は「４」の値になります。
つまりプログラム上は以下になります。
OFFSET('Sheet2'!$C$2,0,0,4,1)
OFFSET関数は範囲を値とする関数です。
つまりプログラム上は以下の値になります。
C2:C5
よってドロップダウンリストには「C2:C5」が表示されます。
まとめ
PhpSpreadsheetの記述よりも関数で範囲を指定するのに苦労しました。
今回はエクセル関数で範囲を指定しましたが、範囲に名前を定義するという方法もあります。
https://www.kabanoki.net/2984
時間があったら、ドロップダウンリストで選んだ内容で、別のドロップダウンリストをフィルターする方法を書きたいと思います。
今日はこの辺でー