今回は PhpSpreadsheet で行う「ワークシート」の 「条件付き書式設定」 について説明したいと思います。
「条件付き書式設定」 はエクセルで実際に行う時には以下の様に、メニューの［ホーム］タブの［条件付き書式］-［新しいルール］の順にクリックします。
「条件付き書式」 は以下の6種類がありますが、例としてはリンクのある部分に付いて例を記します。
- セルの値に基づいてすべてのセルを書式設定
- 指定の値を含むセルだけを書式設定
- 上位または下位に入る値だけを書式設定
- 平均より上または下の値だけを書式設定
- 一意の値または重複する値だけを書式設定
- 数式を使用して、書式設定するセルを決定
■指定の値を含むセルだけを書式設定
セルに設定されている値によって書式を設定する方法です。
条件付き書式オブジェクトを生成し setConditionType() メソッドで「条件タイプ」を設定し setOperatorType() メソッドで「比較演算タイプ」を設定します。 さらに addCondition() メソッドで「比較対象の指定値」を設定し、最後に動作の書式を設定します。（今回はフォントオブジェクトを使って文字色を変更）
今回は1個のセルに対して2つの条件付き書式を設定する為に、2個の条件付き書式オブジェクトを生成し各設定を行った後で、 対象となるセルの条件付き書式を取得し、そこに作成した2つの条件付き書式を追加しています。
尚、最初の条件はセルの値がマイナスであれば文字色を「赤」に、2つ目の条件はセルの値が０以上であれば文字色を「緑」にします。
<?php
// ライブラリ読込
require '../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style;
use PhpOffice\PhpSpreadsheet\Style\Conditional;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
// Spreadsheetオブジェクト生成
$objSpreadsheet = new Spreadsheet();
// ワークシートオブジェクト
$objSheet = $objSpreadsheet->getActiveSheet();
// 条件付き書式オブジェクト１生成
$objConditional1 = new Conditional();
// 条件タイプ:「指定の値を含む」
$objConditional1->setConditionType(Conditional::CONDITION_CELLIS);
// 比較演算タイプ:「指定の値より小さい」
$objConditional1->setOperatorType(Conditional::OPERATOR_LESSTHAN);
// 指定値:「0」⇒ (指定値 ＜ 0)
$objConditional1->addCondition('0');
// フォント色:「赤」
$objConditional1->getStyle()->getFont()->getColor()->setARGB(Color::COLOR_RED);
// 条件付き書式オブジェクト２生成
$objConditional2 = new Conditional();
// 条件タイプ:「指定の値を含む」
$objConditional2->setConditionType(Conditional::CONDITION_CELLIS);
// 比較演算タイプ:「指定の値以上」
$objConditional2->setOperatorType(Conditional::OPERATOR_GREATERTHANOREQUAL);
// 指定値:「0」⇒ (指定値 ≧ 0)
$objConditional2->addCondition('0');
// フォント色:「緑」
$objConditional2->getStyle()->getFont()->getColor()->setARGB(Color::COLOR_GREEN);
// セル[B2:B3]の既存のスタイルの取得（結果は array で返る）
$arrConditionalStyles = $objSheet->getStyle('B2:B3')->getConditionalStyles();
// 条件付き書式オブジェクト１ array追加
array_push($arrConditionalStyles, $objConditional1);
// 条件付き書式オブジェクト２ array追加
array_push($arrConditionalStyles, $objConditional2);
// セル[B2:B3]にスタイルの設定
$objSheet->getStyle('B2:B3')->setConditionalStyles($arrConditionalStyles);
// [B2]セルにマイナス数値設定
$objSheet->getCell('B2')->setValue('-123');
// [B3]セルにプラス数値設定
$objSheet->getCell('B3')->setValue('1000');
// [test7-1.xlsx]:Excel2007形式で保存する
$objWriter = new Xlsx($objSpreadsheet);
$objWriter->save('test7-1.xlsx');
exit();
?>
出力されたエクセルファイルを見てみると以下の様になります。
PhpOffice\PhpSpreadsheet\Style\Conditional には以下の様に定数が宣言されています。
// Condition types
const CONDITION_NONE = 'none';
const CONDITION_CELLIS = 'cellIs'; // セルの値による設定
const CONDITION_CONTAINSTEXT = 'containsText';
const CONDITION_EXPRESSION = 'expression'; // 式による設定??
const CONDITION_CONTAINSBLANKS = 'containsBlanks';
// Operator types
const OPERATOR_NONE = '';
const OPERATOR_BEGINSWITH = 'beginsWith';
const OPERATOR_ENDSWITH = 'endsWith';
const OPERATOR_EQUAL = 'equal';
const OPERATOR_GREATERTHAN = 'greaterThan';
const OPERATOR_GREATERTHANOREQUAL = 'greaterThanOrEqual';
const OPERATOR_LESSTHAN = 'lessThan';
const OPERATOR_LESSTHANOREQUAL = 'lessThanOrEqual';
const OPERATOR_NOTEQUAL = 'notEqual';
const OPERATOR_CONTAINSTEXT = 'containsText';
const OPERATOR_NOTCONTAINS = 'notContains';
const OPERATOR_BETWEEN = 'between';
■数式を使用して、書式設定するセルを決定
数式を使用して書式設定する場合、上の例の様に以下の手順で行います。
条件付き書式オブジェクトを生成し setConditionType() メソッドで「条件タイプ:CONDITION_EXPRESSION」を設定し setOperatorType() メソッドで「比較演算タイプ:OPERATOR_CONTAINSTEXT」を設定します。 さらに addCondition() メソッドで「条件式:$C2="済"」を設定し、最後に動作の書式を設定します。（今回はフォントオブジェクトを使って文字色を変更）
条件付き書式オブジェクトを生成し各設定を行った後で、対象となるセルの条件付き書式を取得し、そこに作成した条件付き書式を追加しています。
尚、今回のやりたいことは、「C」列の値が”済”であれば「B」列を灰色にすることです。
「B2」セルにこの条件付き書式を設定し、その条件付き書式をさらに複製をするようにしました。 ワークシートオブジェクトの duplicateConditionalStyle() メソッドで複製処理を行います。 （後でこの方法では問題があることが発覚しました。）
<?php
// ライブラリ読込
require '../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style;
use PhpOffice\PhpSpreadsheet\Style\Conditional;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
// Spreadsheetオブジェクト生成
$objSpreadsheet = new Spreadsheet();
// ワークシートオブジェクト
$objSheet = $objSpreadsheet->getActiveSheet();
// 条件付き書式オブジェクト１生成
$objConditional1 = new Conditional();
// 条件タイプ:「数式を使用して、書式設定するセルを決定」
$objConditional1->setConditionType(Conditional::CONDITION_EXPRESSION);
// 比較演算タイプ:「数式の文字列」
$objConditional1->setOperatorType(Conditional::OPERATOR_CONTAINSTEXT);
// 指定値:「$C2="済"」
$objConditional1->addCondition('$C2="済"');
// フォント色:「灰色」
$objConditional1->getStyle()->getFont()->getColor()->setARGB('FF808080');
// セル[B2]の既存の条件付き書式スタイルの取得（結果は array で返る）
$arrConditionalStyles = $objSheet->getStyle('B2')->getConditionalStyles();
// 条件付き書式オブジェクト１ array追加
array_push($arrConditionalStyles, $objConditional1);
// セル[B2]に条件付き書式の設定
$objSheet->getStyle('B2')->setConditionalStyles($arrConditionalStyles);
// 書式設定オブジェクトをコピーする
$objSheet->duplicateConditionalStyle($objSheet->getConditionalStyles('B2'), 'B3:B4' );
// テストデータ書込み
// [B2]セルに値設定
$objSheet->getCell('B2')->setValue('案件001');
$objSheet->getCell('C2')->setValue('済');
// [B3]セルに値設定
$objSheet->getCell('B3')->setValue('案件002');
// [B4]セルに値設定
$objSheet->getCell('B4')->setValue('案件003');
$objSheet->getCell('C4')->setValue('済');
// [test7-6.xlsx]:Excel2007形式で保存する
$objWriter = new Xlsx($objSpreadsheet);
$objWriter->save('test7-6.xlsx');
exit();
?>
出力されたエクセルファイルを見てみると以下の様になります。
このエクセルファイルですが「B3」の所が灰色になっていて、意図したものになっていません。
エクセルで条件付き書式の内容を見てみると、条件式が「B2」セルのものと同じになっていました。
エクセル上で条件付き書式のコピーを行うと、条件式は行の番号に合わせてくれますが、上記のソースではそこまでできない様です。
そこで改良版（改悪版）かもしれませんが、以下に対応する様に変更したソースを載せます。
■数式を使用して、書式設定するセルを決定（改良版）
上記のソースでセル[B2]に対して条件付き書式の設定を行う部分を、各行ごとに繰り返し処理で行う様にします。
<?php
// ライブラリ読込
require '../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style;
use PhpOffice\PhpSpreadsheet\Style\Conditional;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
// Spreadsheetオブジェクト生成
$objSpreadsheet = new Spreadsheet();
// ワークシートオブジェクト
$objSheet = $objSpreadsheet->getActiveSheet();
for($i = 2; $i <=4; $i++) {
// 書式設定オブジェクト１生成
$objConditional1 = new Conditional();
// 書式タイプ:「数式を使用して、書式設定するセルを決定」
$objConditional1->setConditionType(Conditional::CONDITION_EXPRESSION);
// 比較演算タイプ:「数式の文字列」
$objConditional1->setOperatorType(Conditional::OPERATOR_CONTAINSTEXT);
// 指定値:「$C2="済"」
$objConditional1->addCondition('$C'.$i.'="済"');
// フォント色:「灰色」
$objConditional1->getStyle()->getFont()->getColor()->setARGB('FF808080');
// セル[B2]の既存のスタイルの取得（結果は array で返る）
$arrConditionalStyles = $objSheet->getStyle('B'.$i)->getConditionalStyles();
// 書式設定オブジェクト１ array追加
array_push($arrConditionalStyles, $objConditional1);
// セル[B2]にスタイルの設定
$objSheet->getStyle('B'.$i)->setConditionalStyles($arrConditionalStyles);
}
// テストデータ書込み
// [B2]セルに値設定
$objSheet->getCell('B2')->setValue('案件001');
$objSheet->getCell('C2')->setValue('済');
// [B3]セルに値設定
$objSheet->getCell('B3')->setValue('案件002');
// [B4]セルに値設定
$objSheet->getCell('B4')->setValue('案件003');
$objSheet->getCell('C4')->setValue('済');
// [test7-6-2.xlsx]:Excel2007形式で保存する
$objWriter = new Xlsx($objSpreadsheet);
$objWriter->save('test7-6-2.xlsx');
exit();
?>
出力されたエクセルファイルを見てみると以下の様になります。
エクセルで「B4」セルの条件付き書式の内容を見てみると意図したものになりました。
コメント