PHPでエクセル操作を自動化する②
前回は「PhpSpreadsheet」をインストールして、サンプルコードが動作するところまで解説しました。今回は実例を交えて、エクセル操作の基本を解説していきます。
筆者の開発環境
PC：DELL Inspiron 15 3511
OS：Windows 11 Home バージョン22H2
PHP：8.0.1
Office：Microsoft Office Home and Business 2021
下準備
エクセルのワークブックを保存する専用フォルダをプロジェクトフォルダに作りましょう。コマンドプロンプトを起動し、下記のコマンドを実行してプロジェクトフォルダに移動してください。
cd C:\MAMP\htdocs\office保存用フォルダを作成します。下記のコマンドを実行してください。
mkdir data確認します。下記のコマンドを実行してください。
dir | find "data"下記のように表示されれば成功です。
2023/10/09 14:23 <DIR> dataワークブックの新規作成
新しいPHPファイルを作成します。下記のコマンドを実行してください。（今後ファイルの新規作成の説明は省略しますので、下記のコマンドのファイル名を変更して実行してください。）
type nul > new_workbook.phpnew_workbook.phpを下記のように編集してください。
<?php
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
require 'vendor/autoload.php';
$spreadsheet = new Spreadsheet();
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('data/book1.xlsx');編集出来たら保存してください。下記のコマンドを実行してください。（今後PHPの実行コマンドの説明は省略しますので、下記のコマンドのファイル名を変更して実行してください。）
php new_workbook.phpdataフォルダに「book1.xlsx」が作成されているか確認しましょう。
dir data下記のように表示されれば成功です。Windowsの方は実際にエクセルで開いてみてもOKです。
C:\MAMP\htdocs\office\data のディレクトリ
2023/10/09 14:50 <DIR> .
2023/10/09 14:48 <DIR> ..
2023/10/09 14:50 6,113 book1.xlsxセルへの書き込み
先ほどは何もデータがない空っぽのワークブックでしたので、今度はセルにデータを書き込んで新規作成してみます。
ファイル名を「set_cell_value.php」で新規作成してください。下記のように編集してください。編集出来たらphpコマンドで実行してください。
<?php
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
require 'vendor/autoload.php';
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
// 文字列書き込み
$sheet->setCellValue('A1', 'PhpSpreadsheet');
// 数値書き込み
$sheet->setCellValue('A2', 12345.6789);
// 真偽値書き込み
$sheet->setCellValue('A3', TRUE);
// 数式書き込み
$sheet->setCellValue(
'A4',
'=IF(A3, CONCATENATE(A1, " ", A2), CONCATENATE(A2, " ", A1))'
);
// 別の書き方
$sheet->getCell('A5')->setValue('Some value');
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
// ファイル名注意
$writer->save('data/book2.xlsx');書式を指定してセルに書き込み
例えば「012345」のように数値だけど文字列として書き込みたいという場合には、setCellValueExplicit()メソッドを用いて下記のデータタイプを指定します。
\PhpOffice\PhpSpreadsheet\Cell\DataType::Type_STRING・・・文字列
ファイル名「set_cell_value_explicit.php」
<?php
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
require 'vendor/autoload.php';
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
// 文字列として書き込む
$sheet->setCellValueExplicit('A1', '012345', DataType::TYPE_STRING);
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
// ファイル名注意
$writer->save('data/book3.xlsx');その他のデータタイプ
\PhpOffice\PhpSpreadsheet\Cell\DataType::Type_NUMERIC・・・数値
\PhpOffice\PhpSpreadsheet\Cell\DataType::Type_BOOL・・・真偽値
\PhpOffice\PhpSpreadsheet\Cell\DataType::Type_NULL・・・空白
\PhpOffice\PhpSpreadsheet\Cell\DataType::Type_FORMULA・・・数式
日時を書き込む
ファイル名「set_cell_datetime.php」
<?php
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
require 'vendor/autoload.php';
// タイムゾーンを設定
date_default_timezone_set('Asia/Tokyo');
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
// 日付フォーマットを設定
$sheet->getStyle('A1')->getNumberFormat()->setFormatCode('yyyy-mm-dd h:mm:ss');
// 現在時刻を書き込む
$sheet->setCellValue('A1', Date::PHPToExcel(new DateTime()));
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
// ファイル名注意
$writer->save('data/book4.xlsx');URLを書き込む
クリックするとブラウザでURLを開くリンクを作ります。
ファイル名「set_cell_url.php」
<?php
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
require 'vendor/autoload.php';
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
// ラベルを書き込む
$sheet->setCellValue('A1', 'Yahoo Japan');
// URLをラベルにセット
$sheet->getCell('A1')->getHyperlink()->setUrl('https://www.yahoo.co.jp');
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
// ファイル名注意
$writer->save('data/book5.xlsx');一括書き込み
複数データがある場合事前にデータを配列に詰めておくと一括で書き込みすることができます。
ポイントは配列を入れ子にすることです。親の配列の要素が行を表し、子の配列の要素がセルを表します。視覚的にも分かりやすいのではないかと思います。
formArrayメソッドの第３引数に開始セルを指定します。今回の場合、A１セルに「ヘッダー1」が書き込まれ、そこから右方向に値を埋めていき、値がなくなったら１行下がり（A2セルへ）、また右方向に値を埋めていくという動きになります。
ファイル名「bulk_values.php」
<?php
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
require 'vendor/autoload.php';
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->getStyle('B2')->getNumberFormat()->setFormatCode('yyyy-mm-dd h:mm:ss');
$sheet->getStyle('B3')->getNumberFormat()->setFormatCode('yyyy-mm-dd h:mm:ss');
$sheet->getStyle('B4')->getNumberFormat()->setFormatCode('yyyy-mm-dd h:mm:ss');
// ポイント
// 配列を入れ子にする
// 親の配列の要素が行を表す
// 子の配列の要素がセルを表す
$data = [
['ヘッダー1', 'ヘッダー2', 'ヘッダー3'], // １行目 => ['A列', 'B列', 'C列']
['データ1-1', Date::PHPToExcel(new DateTime('2023-01-01 09:00:00')), 12345], // ２行目 => ['A列', 'B列', 'C列']
['データ2-1', Date::PHPToExcel(new DateTime('2023-01-02 09:00:00')), 6789], // ３行目 => ['A列', 'B列', 'C列']
['データ3-1', Date::PHPToExcel(new DateTime('2023-01-03 09:00:00')), '=sum(C2:C3)'], // ４行目 => ['A列', 'B列', 'C列']
];
// 第３引数に開始セル, 第4引数にtrueを指定すると厳密な型指定になる
$sheet->fromArray($data, null, 'A1');
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
// ファイル名注意
$writer->save('data/book6.xlsx');ワークシートの新規作成
ファイル名「new_worksheet.php」
<?php
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
require 'vendor/autoload.php';
$spreadsheet = new Spreadsheet();
// 末尾に追加
$spreadsheet->createSheet();
// シート名を設定して先頭に追加
$newWorkSheet = new Worksheet($spreadsheet, 'My Data');
$spreadsheet->addSheet($newWorkSheet, 0);
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
// ファイル名注意
$writer->save('data/book7.xlsx');ワークシートを選択する
ファイル名「get_worksheet.php」
<?php
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
require 'vendor/autoload.php';
$spreadsheet = new Spreadsheet();
// 末尾に追加
$spreadsheet->createSheet();
// シート名を設定して先頭に追加
$newWorkSheet = new Worksheet($spreadsheet, 'My Data');
$spreadsheet->addSheet($newWorkSheet, 0);
// アクティブなワークシートを取得
$activeWorkSheet = $spreadsheet->getActiveSheet();
echo 'アクティブ：' . $activeWorkSheet->getTitle() . PHP_EOL;
// ワークシート名で取得
$worksheet1 = $spreadsheet->getSheetByName('Worksheet 1');
echo 'ワークシート名で取得：' . $worksheet1->getTitle() . PHP_EOL;
// 先頭のワークシートを取得
$spreadsheet->setActiveSheetIndex(0);
$firstWorksheet = $spreadsheet->getActiveSheet();
echo '先頭のワークシート' . $firstWorksheet->getTitle() . PHP_EOL;ワークシートのコピー
ファイル名「copy_worksheet.php」
<?php
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
require 'vendor/autoload.php';
$spreadsheet = new Spreadsheet();
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
// 同一ワークブック内でコピー
$sheet = $spreadsheet->getActiveSheet(); // アクティブなワークシートを取得
$clonedWorksheet = clone $sheet; // クローンを作る
$clonedWorksheet->setTitle('Copied Worksheet'); // シート名を設定
$spreadsheet->addSheet($clonedWorksheet); // 末尾にコピーを追加
// ファイル名注意（新規作成）
$writer->save('data/book8.xlsx');
// 別のワークブックへコピー
$anotherSpreadsheet = new Spreadsheet(); // もう一つワークブックを作成
$newWorksheet = new Worksheet($anotherSpreadsheet, 'My Data'); // シート名を設定してワークシートを作成
$anotherSpreadsheet->addSheet($newWorksheet, 0); // 先頭に追加
$clonedWorksheet = clone $anotherSpreadsheet->getSheetByName('My Data'); // シート名で先頭のワークシートを取得してクローンを作成
$spreadsheet->addExternalSheet($clonedWorksheet, 0); // 最初に作ったワークブックの先頭にコピーを追加
// 上書き保存
$writer->save('data/book8.xlsx');ワークシートの削除
ファイル名「remove_worksheet.php」
<?php
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
require 'vendor/autoload.php';
$spreadsheet = new Spreadsheet();
// 末尾に２シート追加
$spreadsheet->createSheet();
$spreadsheet->createSheet();
echo 'シート数：' . $spreadsheet->getSheetCount() . '個' . PHP_EOL;
print_r($spreadsheet->getSheetNames());
// 先頭のシートを削除
$spreadsheet->removeSheetByIndex(0);
echo 'シート数：' . $spreadsheet->getSheetCount() . '個' . PHP_EOL;
print_r($spreadsheet->getSheetNames());
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
// ファイル名注意
$writer->save('data/book9.xlsx');ワークブックの読み込み／セルデータの取得
先ほど作成した「data/book6.xlsx」を読み込み、全てのセルデータを出力してみます。全て出力し終わったら、範囲指定で同じデータを配列で一括取得してみます。
ファイル名「load_workbook.php」
<?php
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Shared\Date;
require 'vendor/autoload.php';
$reader = IOFactory::createReader('Xlsx');
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load('data/book6.xlsx');
$sheet = $spreadsheet->getActiveSheet();
// １段目のループで行を走査
foreach ($sheet->getRowIterator() as $row) {
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false);
// ２段目のループでセルを走査してデータを表示
foreach ($cellIterator as $cell) {
// 数式の場合
if ($cell->getDataType() === 'f') {
// getCalculatedValue()メソッドで数式の結果を表示
echo $cell->getCoordinate() . '：' . $cell->getCalculatedValue() . PHP_EOL;
continue;
}
// 数値かつB列（日時）の場合
if ($cell->getDataType() === 'n' && $cell->getColumn() === 'B') {
$strValue = $cell->getValue();
// PHPのDateTimeオブジェクトを返す
$dt = Date::excelToDateTimeObject((float)$strValue);
// フォーマットして表示
echo $cell->getCoordinate() . '：' . $dt->format('Y-m-d H:i:s') . PHP_EOL;
continue;
}
echo $cell->getCoordinate() . '：' . $cell->getValue() . PHP_EOL;
}
}
// 配列で範囲取得
$data = $sheet->rangeToArray('A1:C4');
print_r($data);通常の取得
echo $sheet->getCell('A1')->getValue();数式評価後に取得
セルの中身が「=sum(C2:C3)」の場合、sum関数の結果を取得します。数式そのものを取得する場合は上記のgetValue()メソッドを使用してください。
echo $sheet->getCell('C4')->getCalculatedValue();表示フォーマットで取得
公式ドキュメントによればgetFormattedValue()メソッドで取得可能とのことですが、うまくいかなかったので下記のようにしました。シリアル値をそのまま取得し、\PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject()メソッドを通してPHPのDateTimeオブジェクトに変換しています。引数に渡す際、string型からfloat型へ型変換を行っています。
echo $sheet->getCell('B2')->getFormattedValue();
↓
$strValue = $sheet->getCell('B2')->getValue();
$dt = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject((float)$strValue);
echo $dt->format('Y-m-d H:i:s')範囲指定で一括取得
入れ子になった配列が取得できます。親の配列の要素が行を表し、子の配列の要素がセルを表します。
$data = $sheet->rangeToArray('A1:C4');
print_r($data);
>>>
Array
(
[0] => Array
(
[0] => ヘッダー1
[1] => ヘッダー2
[2] => ヘッダー3
)
[1] => Array
(
[0] => データ1-1
[1] => 44927.375
[2] => 12345
)
[2] => Array
(
[0] => データ2-1
[1] => 44928.375
[2] => 6789
)
[3] => Array
(
[0] => データ3-1
[1] => 44929.375
[2] => 19134
)
)よくあるエラーについて
phpコマンドで下記のエラーが出た場合、エクセルのブックを閉じてから再度実行してください。
fopen(data/book4.xlsx): Failed to open stream: Resource temporarily unavailable解説は以上です。おつかれさまでした。
PHP／Laravelのシステム開発は株式会社パパグラムへぜひご相談ください。