PhpSpreadsheet 便利帳！すぐに使える小ネタ集-スケ郎のお話

PhpSpreadsheet 便利帳すぐに使える小ネタ集

PHPで Excelを出力するなら「PhpSpreadsheet」というライブラリを使うのが便利です。

この記事では、「PhpSpreadsheet」で EXCEL出力する時に役立つ小ネタ集です。

インストール

composerを使ってインストールします。

composer installコマンドの場合

composer require phpoffice/phpspreadsheet

「composer.json」を使う場合

    "require": {
        "phpoffice/phpspreadsheet": "^1.23"
    },

ファイルを編集後、composer install または composer update でインストールする。

Dockerを使っている場合の注意点

Dockerで開発している人の場合、必要なライブラリやPHPの拡張機能を有効にしておかないと、上のインストールコマンドはエラーになる。

dockerfile に必要なライブラリと PHPの拡張機能をインストールするスクリプトを組んでおく必要があります。

RUN apt-get update \
    && apt-get -y install libfreetype6-dev libjpeg62-turbo-dev libpng-dev libzip-dev\
    && docker-php-ext-configure gd --with-freetype --with-jpeg \
    && docker-php-ext-install -j$(nproc) gd \
    && docker-php-ext-install zip

基本のエクセル操作

新しいブックを作って値を出力

新しくブックを作成し、セルに値を出力してみる。

use PhpOffice\PhpSpreadsheet\Spreadsheet;

// 新しいスプレッドシート作成する
$spreadSheet = new Spreadsheet;

// [A1] と [B2]セルへの値の書き込み
$spreadSheet->getActiveSheet()->setCellValue('A1', 'A1のセルの値');
$spreadSheet->getActiveSheet()->setCellValue('B2', 'B2の値');

既存のファイルを編集する

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;

// ファイルの読みこみ
$sprSheet = IOFactory::load('./sample.xlsx');

// セルの値を読み出す
$value = $sprSheet->getActiveSheet()->getCell("A1")->getValue();

// セルへの値の書き込み
$spreadSheet->getActiveSheet()->setCellValue('A1', 'A1のセルの値');

// 保存
$writer->save('./sample.xlsx');

ちなみに、読み込んだファイル名と違う名前で保存すれば、別名でファイルを保存することができる。（テンプレートファイル的なことができる）

いろんな操作（シート編）

シートを取得する

// アクティブシート
$sheet = $spreadSheet->getActiveSheet();

// シート名を指定
$sheet = $spreadSheet->getSheetByName("sheet1");

// インデックスで取得 (０〜）
$sheet = $spreadSheet->getSheet(0);

シートのタイトルを変更

$sheet->setTitle("New Sheet Name");

セルの値を取得・設定

セルへの参照はシートオブジェクトの getCell 関数を使用する。

引数に指定するセルの参照方法として、A1などのセルアドレスで指定する方法や、行・列インデックスを指定してセルを参照する方法などが用意されている。

セルのアドレスで値を取得・設定する

$sheet = $spreadSheet->getActiveSheet();

/////////////////////
// A1セルの値を取得
$value = $sheet->getCell("A1")->getValue();
print($value);

/////////////////////
// A2セルの値を変更
$sheet->getCell("A2")->setValue("変更する値");

インデックスを指定してセルの値を取得・設定する

getCell 関数の引数に、配列（[列インデックス, 行インデックス]）を指定するとでセルの値を参照・設定できる。

$sheet = $spreadSheet->getActiveSheet();

/////////////////////
// A1セルの値を取得
$columnIndex = 0;
$rowIndex = 0;
$value = $sheet->getCell([$columnIndex, $rowIndex])->getValue();
print($value);

/////////////////////
// A2セルの値を変更
$columnIndex = 0;
$rowIndex = 1;
$sheet->getCell([$columnIndex, $rowIndex])->setValue("変更する値");

書式付きでセルの値を取得

セルに設定された表示書式で値を取得する場合は getFormattedValue を使用する。

$sheet->getCell("A2")->getFormattedValue();

数式があるセルの値を取得

数式が入っているセルの場合、getValue だと =A1+B1 のような数式そのものが返る。数式が評価された値を取得する場合は getCalculatedValue を使う。

$sheet->getCell("A2")->getCalculatedValue();

「getCalculatedValue」の数式計算はメッチャ遅い

getCalculatedValue 使用すると、PHPSpreadsheet内部の数式エンジンで数式が評価（再計算）されるが、複雑な計算式や、VLOOKUP などの評価すべき対象のセルが広範囲に及ぶような数式の場合、極端にこの関数の実行が遅くなる。

読み取るセルが数個ならまだよいが、大量の数式セルから値を読み出すプログラムのような場合は、getCalculatedValue は遅すぎて運用にまったく耐えられない。

上記の対策として、getOldCalculatedValue で最後に保存された数式の計算結果から値を取得する方法もある。getOldCalculatedValue はキャッシュや Excelで最後に数式が計算された値を返す関数であり、これにより内部で数式が評価（再計算）されることがないため、大幅な性能改善が見込める。

$sheet->getCell("A2")->getOldCalculatedValue();

値を設定するだけなら、setCellValueByColumnAndRow でシンプルに

セルに値を設定するだけであれば、わざわざセルのインスタンスを取得しなくても、WorkSheet の setCellValueByColumnAndRow 関数で値を設定できる。

// A3セルに値を設定する
$columnIndex = 0;
$rowIndex = 3;
$sheet->setCellValueByColumnAndRow($columnIndex, $rowIndex, "値");

プルダウン（入力規則）の設定

use PhpOffice\PhpSpreadsheet\Cell\DataValidation;

//入力規則オブジェクト取得
$validation = $sheet->getCell("A5")->getDataValidation();
//入力規則タイプをリストに設定
$validation->setType(DataValidation::TYPE_LIST);
//空白の選択可否（trueを選択すると空白選択可)
$validation->setAllowBlank(false);
//ドロップダウンの表示設定
$validation->setShowDropDown(true);
//リストに表示する内容
$validation->setFormula1('"リスト１,リスト２,リスト２"');

複数の範囲にプルダウン（入力規則）を設定する

複数の範囲に同じプルダウンの設定を反映する場合は、入力規則オブジェクトの setSqref 関数で指定した範囲に同じ入力規則を適用できる。

// A5〜A100の範囲に入力規則を設定
$validation->setSqref("A5:A100");

最終行・最終列の取得

最終行を取得

Excelで、データが入力されている最終行を取得する場合は、getHighestRow または getHighestDataRow 関数を使います。

$last_row = $spreadSheet->getActiveSheet()->getHighestRow();
//-> 100

$last_data_row = $spreadSheet->getActiveSheet()->getHighestDataRow();
//-> 100

最終列を取得

データが入力されている最終列を取得する場合は、getHighestColumn または getHighestDataColumn 関数を使います。

$last_column = $spreadSheet->getActiveSheet()->getHighestColumn();
//-> P

$last_data_column = $spreadSheet->getActiveSheet()->getHighestDataColumn();
//-> P

この関数は、最終列を列インデックスではなく、A〜から始まる列記号で返すので要注意

最終行と最終列をまとめて取得

最終行と最終列をまとめて取得したい時は getHighestRowAndColumn 関数が便利です。この関数は、配列で最終行と列を返します。

$last_column = $spreadSheet->getActiveSheet()->getHighestRowAndColumn();
// => array(2) {  
//      ["row"]=>  
//      int(100)  
//      ["column"]=>  
//      string(1) "P"  
//    }

便利な関数

列の幅を設定する

$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(12);

列幅の自動調節をする場合は setAutoSize 関数を使用する。

$spreadsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);

２次元配列で一気にデータを設定する

２次元配列のデータを作成し、fromArray 関数に渡すことで配列の中身を一気に Excelに出力できる。

$data = [
	["A", "B", "C"],
	["D", "E", "F"],
	["G", "H", "I"],
]

// A1セルから２次元配列で指定された行・列数分データを設定する
$spreadsheet->getActiveSheet()->fromArray($data, NULL, 'A1', true);

セルの範囲のデータを２次元配列で取得する

$data = $spreadsheet->getActiveSheet()->$sheet->rangeToArray("A1:E10");

列番号から列名（A,B,C,…などのアルファベット記号）を取得

$colname = Coordinate::stringFromColumnIndex(1);

//=> A

まとめ

PhpSpreadsheetでよく使う関数などを紹介しました。

今後も、この記事は随時メンテしていこうと思います。

PhpSpreadsheet 便利帳！すぐに使える小ネタ集