2024年4月19日

PhpSpreadsheet　tips #2

・文字列の書き込み
・数値の書き込み
・式の書き込み
・日付の書き込み
・日時、時間の書き込み

使用環境

・ PHP 8.2.9
・ phpoffice/phpspreadsheet　version 2.0.0　（Composerでインストール済み）

基本例

サンプルコード

土台になるファイルを開いて、値を書き込んで保存するサンプル。
ファイルを開いた後、シートのオブジェクトを取得して値を書き込んでから保存している。

<?php

require '../vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Reader\Xlsx as XlsxReader;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx as XlsxWriter;
use PhpOffice\PhpSpreadsheet\Cell\DataType;


// 元になるファイルをスプレッドシートに読み込む
$reader = new XlsxReader();
$spreadsheet = $reader->load( './template.xlsx' );


// シート「Sheet1」を取得
$sheet = $spreadsheet->getSheetByName( 'Sheet1' );

// A1セルに文字列を書き込む
$sheet->setCellValueExplicit( 'A1', 'Hello World!', DataType::TYPE_STRING );

// A2セルに数値を書き込む
$sheet->setCellValueExplicit( [ 1, 2 ], 3.141592, DataType::TYPE_NUMERIC );


// xlsx形式で保存する
$writer = new XlsxWriter( $spreadsheet );
$writer->save( './output.xlsx' );


// スプレッドシートを閉じてメモリを解放
$spreadsheet->disconnectWorksheets();
unset( $spreadsheet );

PHP

説明

シート名を指定して取得する例。

// シート「Sheet1」を取得
$sheet = $spreadsheet->getSheetByName( 'Sheet1' );

PHP

シートの取得はシート名での取得の他、シート番号での取得やアクティブシートでの取得も可。

// 最初のシートを取得（0始まり）
$sheet = $spreadsheet->getSheet(0);

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

PHP

続く以下の書き込み指示で実際に値を入れていく。

// A1セルに文字列を書き込む
$sheet->setCellValueExplicit( 'A1', 'Hello World!', DataType::TYPE_STRING );

// A2セルに数値を書き込む
$sheet->setCellValueExplicit( [ 1, 2 ], 3.141592, DataType::TYPE_NUMERIC );

PHP

setCellValueExplicit()の第1引数で、書き込むセルを指定。
A1、B2、のようにセル位置で指定するか、配列を使って列番号と行番号でも指定出来る。
（他にもセルのオブジェクトを使って指定する方法もあるが割愛）

第2引数が書き込む値。

第3引数が書き込む型。
いくつか種類があるが、ここではよく使われる3種をピックアップ。
（それぞれ後述の説明で使用している）

・ DataType::TYPE_STRING　：　文字列
・ DataType::TYPE_NUMERIC　：　数値
・ DataType::TYPE_FORMULA　：　式
etc…

なお、書き込みは以下の setCellValue() が使われている例が多い。
（公式ドキュメントでもsetCellValue()）

$sheet->setCellValue( 'A1', 'Hello World!' );

PHP

ただこれは、注意が必要。
書き込む値の型が明示されていないので、意図しない形に自動解釈されてしまうケースがある。

そのため、setCellValueExplicit()を使うことをお勧めする。

文字列の書き込み

$sheet->setCellValueExplicit( 'A1', 'Hello World!', DataType::TYPE_STRING );

PHP

文字列の書き込みは型を指定していればあまり深く考えなくて良い。
動的に出力していると書き込む値が null や空文字のケースもあるが、その際も問題なし。

なお、もし setCellValue() を使って書き込む場合は以下のように結果が変わってしまうので注意が必要。

// 数値として書き込まれる
$sheet->setCellValue( 'A1', '-1.11' );

// 文字列として書き込まれる
$sheet->setCellValue( 'A2', '0123' );

// 式として書き込まれる
$sheet->setCellValue( 'A3', '=A1+A2' );

PHP

実利用では固定値ではなく動的な値を出力するケースが多いはず。
そこに数値や計算式と誤認されるものが混ざりこむのは異常値（レアケース）寄りなことが多く、テスト時にも気付き漏れて不具合につながりやすい印象。

数値の書き込み

$sheet->setCellValueExplicit( 'A1', 3.141592, DataType::TYPE_NUMERIC );

PHP

固定の数値を書き込むだけなら深く考える必要はないが、動的な値を書き込む場合は値によって注意が必要。

// 数値として書き込まれる
$sheet->setCellValueExplicit( 'A1', '3.141592', DataType::TYPE_NUMERIC );

// 数値として書き込まれる（前後半角スペースは無視される）
$sheet->setCellValueExplicit( 'A2', '   3.141592', DataType::TYPE_NUMERIC );

// 数値として書き込まれる（前後半角スペースは無視される）
$sheet->setCellValueExplicit( 'A3', '3.141592   ', DataType::TYPE_NUMERIC );

// 数値として123が書き込まれる
$sheet->setCellValueExplicit( 'A4', '0123', DataType::TYPE_NUMERIC );

// 数値として83が書き込まれる（8進のままではなく10進数になる）
$sheet->setCellValueExplicit( 'A5', 0123, DataType::TYPE_NUMERIC );

// 数値として0が書き込まれる
$sheet->setCellValueExplicit( 'A6', null, DataType::TYPE_NUMERIC );

// Fatal error（空文字はダメ）
$sheet->setCellValueExplicit( 'A7', '', DataType::TYPE_NUMERIC );

// 数値として1が書き込まれる
$sheet->setCellValueExplicit( 'A8', true, DataType::TYPE_NUMERIC );

// 数値として0が書き込まれる
$sheet->setCellValueExplicit( 'A9', false, DataType::TYPE_NUMERIC );

// Fatal error（カンマ区切りはダメ）
$sheet->setCellValueExplicit( 'A10', '1,000', DataType::TYPE_NUMERIC );

// Fatal error（郵便番号は文字列でないとダメ）
$sheet->setCellValueExplicit( 'A11', '123-4567', DataType::TYPE_NUMERIC );

// Fatal error（文字が入るとダメ）
$sheet->setCellValueExplicit( 'A12', '100p', DataType::TYPE_NUMERIC );

// Fatal error（途中の半角スペースはダメ）
$sheet->setCellValueExplicit( 'A13', '3. 141592 ', DataType::TYPE_NUMERIC );

PHP

実利用では固定値ではなく動的な値を出力するケースが多いはず。
その際 null や空文字が混ざりやすいと思われるので、予め対処しておく必要がある。

その他、EXCEL側の書き込み先セルの書式にも注意が必要。

例えばパーセンテージを出力する場合。
EXCEL側でセルの書式がパーセンテージだと、100を書き込むとEXCEL上では10000%と表示される。

式の書き込み

$sheet->setCellValueExplicit( 'A1', '=B2+B3', DataType::TYPE_FORMULA );

PHP

正しい式を書き込む分には問題ない。
ただし、正しくない式を書いてしまった場合はその誤り方によって扱いが変わるので注意が必要。

// 「=BAD CASE」が書き込まれる（誤った計算式でもそのまま通る）
$sheet->setCellValueExplicit( 'A1', '=BAD CASE', DataType::TYPE_FORMULA );

// 「=ORIGINALFUNCTION(B3)」が書き込まれる（EXCEL側にしかない独自関数も大丈夫）
$sheet->setCellValueExplicit( 'A2', '=ORIGINALFUNCTION(B2)', DataType::TYPE_FORMULA );

// Fatal error
$sheet->setCellValueExplicit( 'A3', '=(B3+', DataType::TYPE_FORMULA );

// 「=AD CASE」が書き込まれる（先頭が=に変わる）
$sheet->setCellValueExplicit( 'A4', 'BAD CASE', DataType::TYPE_FORMULA );

// 「=0.141592」が書き込まれる　※再計算されるまで、表面上は文字列で「3.141592」になる
$sheet->setCellValueExplicit( 'A5', 3.141592, DataType::TYPE_FORMULA );

// 「=0.141592」が書き込まれる　※再計算されるまで、表面上は文字列で「3.141592」になる
$sheet->setCellValueExplicit( 'A6', '3.141592', DataType::TYPE_FORMULA );

// 何も書き込まれない
$sheet->setCellValueExplicit( 'A7', '', DataType::TYPE_FORMULA );

// 何も書き込まれない
$sheet->setCellValueExplicit( 'A8', null, DataType::TYPE_FORMULA );

// 何も書き込まれない
$sheet->setCellValueExplicit( 'A9', '', DataType::TYPE_FORMULA );

// 文字列として1が書き込まれる
$sheet->setCellValueExplicit( 'A10', true, DataType::TYPE_FORMULA );

// 何も書き込まれない
$sheet->setCellValueExplicit( 'A11', false, DataType::TYPE_FORMULA );

PHP

上記の通り、入力値がそのままEXCELに書き込まれるわけではない。

また、PhpSpreadsheetは計算式を加工、及び解釈している（又は、しようとしているが不完全）。
そのため、Fatal errorが出たり、その計算結果が記録されて表面上現れている（それが実際、式の結果とずれている）。

日付の書き込み

日付を文字列として書き込むでよければ、文字列の書き込みと同様。

$sheet->setCellValueExplicit( 'A1', '2024/04/19', DataType::TYPE_STRING );

PHP

ただし、これはEXCELでは文字列扱いになり日付情報として認識されない。

EXCELでは日付は数値情報で、それを書式として日付として見せている。
それに準ずる場合は数値の書き込みになるため、以下のような形。

// 数値として45401が書き込まれる
// Date::PHPToExcel に渡すのはGMTであることに注意
// strtotime()の2038年問題は64bitのOS/PHPが使われている前提
$time = strtotime( '2024/04/19 GMT' );
$sheet->setCellValueExplicit( 'A2', \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel( $time ), DataType::TYPE_NUMERIC );

PHP

これをEXCEL側で書式指定してやれば見た目も日付になる。

※EXCELは1900/1/1～9999/12/31の範囲でしか日付として扱えないので範囲によっては注意が必要。

日時、時間の書き込み

時間を含む場合も日付と同じ。

// 数値として45401.427430556が書き込まれる
// 小数点以下が時間を表している
// Date::PHPToExcel に渡すのはGMTであることに注意
// strtotime()の2038年問題は64bitのOS/PHPが使われている前提
$time = strtotime( '2024/04/19 10:15:30 GMT' );
$sheet->setCellValueExplicit( 'A3', \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel( $time ), DataType::TYPE_NUMERIC );

PHP

日付を出力せず、時間だけを書き込む場合は以下。
少し計算が必要。

// 数値として0.427430555555556が書き込まれる
// 計算のため、先に出力値を計算して整数部を消す
$excelTime = \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel( strtotime( '2024/04/19 10:15:30 GMT' ) );
$excelTime -= floor( $excelTime );
$sheet->setCellValueExplicit( 'A4', $excelTime, DataType::TYPE_NUMERIC );

PHP

時間の情報は小数部の丸め誤差を避けられないので注意が必要。

EXCELへの手入力で作った時間情報と上記で出力した時間情報は、日時書式指定後の見た目は同じになるが小数部の数値は完全には一致しない。
そのため、日時や時間の一致判定や大小判定、並び替えを行う際は注意が必要。

なお、これはEXCELに手入力した場合でも同じことが起きる。
例えば、「2024/4/19 10:15:30」と日付なしの「10:15:30」では、小数部の桁数が違う。

そもそも割り切れない小数で表すことに無理があり（1秒=1/86400）、EXCEL自体が桁を揃えられていないので、計算結果をEXCELと同値にするのは困難に思われる。

PhpSpreadsheet tips #2