PHPExcelの後継ライブラリ、PHPSpreadsheetもだいぶ情報が広まってきているところですが、とりわけに日本語サイトでの説明が少ない、またはないものを採り上げたものです（随時追加・更新）。一部、海外サイトにも情報がほとんどない、あるいは公式サイトが間違っているものを検証、分析したものもあります。
尚、PHPExcelはPHP7.1までしか対応していないため、公式サイトでもPHPSpreadsheetへの切り替えを推奨しているようです。
0 :Phpspreadsheetを使うための準備（linuxの場合）
この段階から情報が錯綜しているので、方法をまとめました。当然、PHPは事前にインストールしておいてください。また、このセクションでは、ローカルインストールの場合も記述しています。
1.composerのインストール
Phpspreadsheetを使うためにはPhpspreadsheetライブラリが必要となりますが、手動で入手するのは面倒なのでComposerというパッケージ管理ソフトを使います（公式推奨）。また、rootアカウントでのインストールは推奨されていませんので、ユーザーアカウントを変更してください。
#su - hogehoge //アカウントhogehogeでログイン
hogehoge$ php -r "copy('https://getcomposer.org/installer', 'composer-setup.php');"
hogehoge$ php composer-setup.php
hogehoge# ./composer.phar -v //バージョン情報の確認。アスキーのロゴが出てくれば成功です。
/*インストールはこれで終わりですが、今度はcomposerコマンドだけで実行できるようにします*/
hogehoge$ mv composer.phar /usr/local/bin/composer.phar //composer.pharの移動
hogehoge$ alias composer="php /usr/local/bin/composer.phar" //エイリアスの設定
hogehoge$ composer -v //composerコマンドだけで実行できるか確認
これで、composerの操作準備は完了ですが、インストールのためにcomposer.jsonが必要になります。
2.composer.jsonの設定
composer.jsonの設定を行います。直接編集するよりrequireコマンドを実行した方が確実だと連絡をいただきましたので、記述を修正しています。
hogehoge$ composer init //これの実行で、ほとんど命令通りに進み、作成を完了させます。
hogehoge$ composer require phpoffice/phpspreadsheet
3. zip-archiveをインストールする
これでxlsファイルなら大丈夫ですが、xlsxファイルを読み書きする場合はPHP上でXML処理が必要になり、、php-zipあるいはphp-pecl-zip、それとlibzip5がないと読込、書込の際にエラーが発生し、PhpSpreadsheetを使用することができませんので、以下の手順でインストールしておきます。
※xxは任意のPHPのバージョン（7.2ならば72として、リポジトリを設定してください）
/*PHP7.1以前はこちら*/
hogehoge$ yum --enablerepo=remi-phpxx -y install php-zip libzip5
/*PHP7.2以降はこちら*/
hogehoge$ yum --enablerepo=remi-phpxx -y install php-pecl-zip libzip5
hogehoge$ systemctl restart httpd //Apacheの場合、Nginxならhttpdをnginxに書き替える
hogehoge$ php --ri zip//zip関連のアーカイブがインストールされているか確認
/*このように表示されていれば確実*/
zip
Zip => enabled
Zip version => x.x.x
Libzip headers version => x.x.x
Libzip library version => x.x.x
4. php-xmlのインストール
引き続き、php-xmlも忘れずにインストールしておきましょう。これをインストールしないとxlsxファイルへの書き込み処理がうまくいかず、xmlWriterが読み取れませんというエラーがxlsxファイル保存時に発生し、場合によっては後で厄介なことになります（読み込むだけなら問題ないですが、一応インストールしておいた方が無難です）。
hogehoge$yum list installed | grep -i php //php-xmlがインストールされているか確認
hogehoge$yum --enablerepo=remi-phpxx -y install php-xml
5. Phpspreadsheetのインストール
hogehoge$chmod -R 777 /var/www/html //インストールしたいディレクトリに書き込み権限を付与
hogehoge$cd /var/www/html //Phpspreadsheetをインストールしたいディレクトリへ移動
hogehoge html$composer install
これでエラーが表示されなかったらようやく準備OKです。なお、一度ライブラリを作ってしまうと、vendorフォルダと内部ファイルだけで使用可能になります。ですので、vendorフォルダを保存しておけば、使いたいプログラムにそのまま移動するだけで、使えるようになります(Linux環境で作成したフォルダをWindowsのXAMPP環境に移植しても普通に動かせました）。
※ 手動の換装などで、Phpspreadsheetでエラーが表示される場合
zip-archiveやphp-xmlのインストールを忘れているとエラーが発生します。オンライン接続可能だったらそのまま上記のphp-zipないしはphp-pecl-zip、libzip5、php-xmlをインストールすれば解決しますが、オフラインだった場合はリポジトリを作成して、それらをローカルインストールする必要があります（下記のように、うまくリポジトリを作れない場合は、不運にもcreaterepoもインストールしなければいけない場合もあります。その場合はオンライン環境からcreaterepoもインストールして、先にcreaterepoだけrpmコマンドで解凍しておけば、yumコマンドで作れるようになります）。
もし、PHPのバージョンアップデートが必要になった場合は一通りのPHP関連ファイルを全てアップロードしないといけない場合もありますので、以下に必要なライブラリを挙げておきます（PHP7.2.xxの場合）
- アップデートに必要なライブラリ一覧→
php-cli,php-common,php-devel,php-json,php-mbstring,php-mysqlnd,php-pdo,php-pgsql,php-xml,php-intl,php-gd
yumを使ったローカルインストールはけっこう曲者なので、参考になるリンクを置いておきます。
※ PHP5.6で操作する場合
PhpSpreadSheetはPHP5.6から使用可能ですが、zip-Archiveの仕様がPHP7と異なっているため、ライブラリ内の関数を一部書き変える必要があります。また、php-gdもインストール必須になります。
このページを参考にして、エラーメッセージから対象ファイルの行を追跡し、該当箇所に書き加えると使用できるようになります。
当ページのルールです
目を通しておくと、読み解きやすくなります。
use PhpOffice\PhpSpreadsheet\Spreadsheet as Spread; //公式APIは別名を定義して使っています。ちなみに、使用メソッドと別名が同じ場合は、asは省略しても問題ないです(\hoge = as hogeの場合)。
$spread = new Spread(); //スプレッドシート(ブック）は$spreadと略しています
$sheet = $spread -> getActivesheet(); //ワークシートは$sheetと略しています
１:任意のセルに対し、上寄せ、左寄せなどを行う（＋エイリアスの別名設定）
公式APIの一つ、AlignmentAPIから色々設定できます(あえてインスタンスを作らなくても直接スコープできます）。また、名前空間のエイリアスは別名定義を指定できるので、別名を作っておくと冗長な記述を省略できます（PHPの名前空間を参照）。
※外部ファイルで使う場合はインスタンスを作成してください。エイリアスはそのページでしか使えません。
use PhpOffice\PhpSpreadsheet\Style\Alignment as Align; //Alignmentのエイリアスを別名で再定義（別名が同じ場合は別名指定は不要です）
//左右を制御する(HORIZONTAL …水平のこと)
$sheet -> getStyle("A1") -> getAlignment() -> setHorizontal(Align::HORIZONTAL_LEFT); //左寄せ
$sheet -> getStyle("A2") -> getAlignment() -> setHorizontal(Align::HORIZONTAL_CENTER); //中央寄せ
$sheet -> getStyle("A3") -> getAlignment() -> setHorizontal(Align::HORIZONTAL_RIGHT); //右寄せ
$sheet -> getStyle("A4") -> getAlignment() -> setHorizontal(Align::HORIZONTAL_DISTRIBUTED); //均等割付
//上下を制御する(VERTICAL …垂直のこと。中央寄せはMIDDLEじゃないので注意してください)
$sheet -> getStyle("B1") -> getAlignment() -> setVertical(Align::VERTICAL_TOP); //上寄せ
$sheet -> getStyle("B2") -> getAlignment() -> setVertical(Align::VERTICAL_CENTER); //中央寄せ
$sheet -> getStyle("B3") -> getAlignment() -> setVertical(Align::VERTICAL_BOTTOM); //下寄せ
$sheet -> getStyle("B4") -> getAlignment() -> setHorizontal(Align::VERTICAL_DISTRIBUTED); //均等割付
$align = new Align(); //インスタンスを作成(インスタンス名は任意)
require_once("hogehoge.php"); //外部ファイルを呼び出す
```hogehoge.php
/*外部ファイルで用いる場合は、インスタンスからメソッドを呼び出す*/
//左右を制御する(HORIZONTAL …水平のこと)
$sheet -> getStyle("C1") -> getAlignment() -> setHorizontal($align::HORIZONTAL_LEFT); //左寄せ
$sheet -> getStyle("C2") -> getAlignment() -> setHorizontal($align::HORIZONTAL_CENTER); //中央寄せ
$sheet -> getStyle("C3") -> getAlignment() -> setHorizontal($align::HORIZONTAL_RIGHT); //右寄せ
$sheet -> getStyle("C4") -> getAlignment() -> setHorizontal($align::HORIZONTAL_DISTRIBUTED); //均等割付
//上下を制御する(VERTICAL …垂直のこと。中央寄せはMIDDLEじゃないので注意してください)
$sheet -> getStyle("D1") -> getAlignment() -> setVertical($align::VERTICAL_TOP); //上寄せ
$sheet -> getStyle("D2") -> getAlignment() -> setVertical($align::VERTICAL_CENTER); //中央寄せ
$sheet -> getStyle("D3") -> getAlignment() -> setVertical($align::VERTICAL_BOTTOM); //下寄せ
$sheet -> getStyle("D4") -> getAlignment() -> setHorizontal($align::VERTICAL_DISTRIBUTED); //均等割付
文字寄せは結合したセルでも対応可能ですが、その際開始位置のセルアドレスを指定してください。
$sheet -> mergeCells("B1:C5"); //指定範囲のセル結合
$sheet -> getStyle("B1") -> getAlignment() -> setVertical(Align::VERTICAL_TOP); //開始アドレスと合わせる
２:セル内に対し、自動改行、縮小、回転、縦文字、インデントなどを制御する
getAlignment()メソッドから色々設定できます。
イ：自動改行
setWrapText(bool)メソッドのプロパティをtrueに設定することで、対応できます。もし、幅を広くしたい場合は最初から列幅を決めてください(19章に自動列幅指定の方法も載せています)。
//デフォルトのセル幅に対し、文字列を自動改行して表示させます。
$sheet -> getStyle("A1") -> getAlignment()-> setWrapText(true);
//任意のセル幅に対するセルに対し、文字列を自動改行して表示させます。例の場合はA列の幅を20に設定。
$sheet -> getActivesheet() -> getColumnDimension('A') -> setWidth(20);
$sheet -> getStyle("A1") -> getAlignment()-> setWrapText(true);
なお、改行コードを用いる場合はsetWrapText(true)を指定した場合のみ**\r**で対応しています。
参考になったページ
ロ：縮小して表示
setShrinkToFit(bool)メソッドを使用します。fit（収納）するためにshrink(縮小）すると覚えればいいでしょう。また、縮小表示を解除するには引数をfalseにします。
$sheet -> getStyle("A1") -> getAlignment() -> setShrinkToFit(true); //縮小して表示
$sheet -> getStyle("B1") -> getAlignment() -> setShrinkToFit(false); //縮小して表示を解除
ハ：テキストを回転表示
setTextRotation(n)メソッドを使用します。nは任意の変数で、-90を下限、90を上限に、15の倍数にしないと例外エラーが発生することがあります。
$sheet -> getStyle("A1") -> getAlignment() -> setTextRotation(30); // nは15の倍数にしないと例外エラーが発生することがある
ニ：縦文字（縦書き）
縦書きも、setTextRotation(n)メソッドを使用し、xlsならnに255、xlsxならnに-165を設定することで有効になるという特殊な仕様が仕組まれています。かなだけの場合なら文字と改行コードを組み合わせるだけでもできますが、長音を対応させるにはこれしかないです。
$sheet -> setCellValue("A1","オホーツク"); //長音の場合は改行だけでは対応できない
$sheet -> getStyle("A1") -> getAlignment() -> setTextRotation(-165); //縦書きになる
公式ページのAlignment.phpファイルソースの282行目、setTextRotation()メソッドのプログラムに意味深な数値が設定されており、これを打ち込んでみたら案の定でしたが、なぜ255と-165なのかはわかりません。
// Excel2007 value 255 => PhpSpreadsheet value -165
if ($pValue == 255) {
$pValue = -165;
}
/* 公式のソースに-165という意味深な数値設定がある */
if (($pValue >= -90 && $pValue <= 90) || $pValue == -165) {
if ($this->isSupervisor) {
$styleArray = $this->getStyleArray(['textRotation' => $pValue]);
$this->getActiveSheet()->getStyle($this->getSelectedCells())->applyFromArray($styleArray);
} else {
$this->textRotation = $pValue;
}
} else {
throw new PhpSpreadsheetException('Text rotation should be a value between -90 and 90.');
}
PhpSpreadsheet/src/PhpSpreadsheet/Style/Alignment.php
ホ：インデント
インデントはsetIndent(n)で指定できます。
$sheet -> getStyle("A1") -> getAlignment() -> setIndent(1); //1文字分インデントがかかる
※結合したセルに対してAlignmentを制御する場合
結合したセルに対してAlignment制御する場合は結合したアドレス範囲を指定する必要があります。
$sheet -> mergeCells("B2:D4"); //結合したい範囲を指定
$sheet -> getStyle("B2:D4") -> getAlingnment() -> .... ; //範囲を合わせてから設定する
３:特定のセルに固定しておく
freezePane()メソッドを使うことで、特定のセルを固定しておくことができます（要はExcel機能にあるウィンドウの固定と同じ働きなので、必ず行列双方のマージンを空けておいてください）。また、unfreezePane()メソッドを利用することで、セルの固定を解除できるほか、指定のセル位置にカーソルを合わせたまま保存することもできます。
//現在のシートに対し、A列と1行を固定しておく場合
$spread -> getActiveSheet() -> freezePane('B2');
注意
必ず行列に最低１つマージンをとってください。
マージンがなかった場合にファイル読込時に破損し、Excelファイルから以下のメッセージが表示されることになります。
-
開いた直後… hogehoge.xlsxには読み取れない内容が含まれています。このブックの内容を回復しますか？ブックの発行元が信頼できる場合は、[はい]をクリックしてください。
-
読込後…ファイル レベルの検証と修復が完了しました。このブックの一部が修復されたか、破棄された可能性があります。削除された機能: /xl/worksheets/sheet1.xml パーツ内のビュー
この質問文の回答を読むと、マージンをあけろとあります。
phpexcel freeze row and columns
４:ファイル書き込み前に、セル位置を先頭に設定しておく
以前はファイル固定のメソッドを使う方法を説明したのですが、これだと複数シートの場合に対応していなかったので、もっとリスクが少なく簡単な方法が公式ページに記載してあり、setActiveSheetIndex(n)(nはシート番号)かsetActiveSheetIndexByName('シート名')で簡単に設定できます。ただ、これだとセル位置までは元通りにならないので、その場合は上記に挙げたunFreezePane('アドレス名')で指定するといいでしょう。
//sheet1というシートの先頭に合わせる。
use PhpOffice\PhpSpreadsheet\Writer\XlsX as XWriter;
$spread -> setActiveSheetIndex(0); //シートを先頭に合わせる
$spread -> getSheet(0) -> unfreezePane('A1'); //アドレスA1に合わせる
$writer = new XWriter($spread); //定義した別名を使って、シートを書き込む
$writer -> save('hogehoge.xlsx'); //書き込んだ名前を保存
参考にした公式API
５:データの最終行、最終列を取得する
$spreadオブジェクトから以下のメソッドを実行することで、任意の変数に格納できます。これを使うと動的な範囲の取得が楽になります。
$sheet = $spread -> getActiveSheet(); //処理したいシートを取得
$max_row = $sheet -> getHighestRow(); //最終行（最下段）の取得
$max_col = $sheet -> getHighestColumn(); //最終列（右端）の取得
$maxCellAddress = $max_col.$max_row; //最終セルのアドレスを格納する変数
$ary_data = $sheet -> rangeToArray("B2:{$maxCellAddress}"); //指定の位置から最終セルまで取得する
ただし、これだとファイルから最終行がうまく取得できないことが多いので、ループをかけて文字データが取得できなくなったタイミングを指定する方法がおすすめです。
$max_row_tmp = $sheet -> getHighestRow(); //暫定的な最下段の列アドレス
while($sheet -> getCell("A{$max_row_tmp}") -> getValue() == NULL) $max_row_tmp--; //値が取得できるまで、デクリメントする
６:数字の文字列が指数化される場合の対応
数値は12桁までしか正常に表示されないのは、PhpSpreadsheetの標準仕様だそうです。ですので、設定を手動で変えてやる必要があるのですが、setFormatCode('0')と記述して、セル範囲に対し文字列と認識させるだけで解決します。しかも、以下に挙げるような利点があります。
- 桁数は14桁まで自由に表示できる
- ゼロが先頭になった場合でもゼロパディング処理が不要
※なお、15桁以上になると下4桁が勝手に丸められてしまいます。
$sheet = $spread -> getActiveSheet(); //処理したいシートを取得
$max_row = $sheet -> getHighestRow(); //最下段
$sheet -> getStyle("A1:D{$max_row}") -> getNumberFormat() -> setFormatCode('0'); //文字列と認識させる
７： データをまるごと書き込む
データの書き込みはPHPExcelよりかなり便利になっておりfromArray()メソッドを使うと、一度にオブジェクト化された配列データを書き込むことができます。また、引数を指定することによって、開始位置のアドレスなどを指定することができます。
$sheet = $spread -> getActiveSheet(); //処理したいシートを取得
//テスト用オブジェクト。概要は[都市名,人口,行政区数]となっている。
$rows = [
["横浜市",3740000,18],
["川崎市",1520000,7],
["相模原市",730000,3],
["藤沢市",431000,"-"]
];
$sheet -> fromArray($rows); //指定なしだと先頭セル（A1）から順番に貼り付けられます。
//第3引数は、出力開始位置のアドレスを指定できます(省略した場合はA1)
$sheet -> fromArray($rows,NULL,"A2"); //A2から順番に出力される。
※第2引数はあまり使う機会はないかも知れません。公式サイトには
Array values with this value will not be set
と記述されています。直訳すると「この値を伴う配列の値は設定されません」とあり、対象のセルに対して値を設定したくない場合に用いるようです。（いちおう、0と記述すると、数値以外の文字が出力されなくなるので、特定の行を集計したい場合などには使えるかも知れません。例で言うと、行政区数における藤沢市の値が空白となります）。
８： ループ式を使ってデータを挿入する（英字列のインクリメントと関数の埋め込み）
たとえば、最終行にそれぞれ合計値を出す関数を埋め込みたいとします。行アドレスは数字(1,2,3…)なので、普通にインクリメントすればいいですが、列（A,B,C…）はどう処理すればいいでしょうか？
実は、PHPの標準仕様として++は英文字に対応しているため、同様に列アドレスも普通にインクリメント可能です（Coordinateでインデックスを割り出す必要ありません）。
また、シートに関数を埋め込みたい場合はExcelと同様、=を付けることで関数と認識させることができます。
$limitRow = $sheet -> getHighestRow(); //集計を行う最終行
$sumRow = $limitRow + 1; //集計を出力する行
$limitCol = $sheet -> getHighestColumn(); //最終列
$currentCol = "B"; //開始列
//最終列に達するまで関数を埋め込む
while( $currentCol != $limitCol){
//合計式を埋め込む（集計の最終行と集計関数を出力する行を一緒にしないように！）
$sheet
-> setCellValue("{$currentCol}{$sumRow}","=counta({$currentCol}2:{$currentCol}{$limitRow})");
$currentCol++; //列アドレスも数値と同じようにそのままインクリメント可能
}
※EXCELの関数に絶対参照のアドレスを埋め込む場合
PHPの変数に絶対参照のアドレスを埋め込む場合は、絶対参照記号の$に対し、エスケープを忘れないでください。
$address = "=MATCH(A1,\$B1:\$B10,FALSE)"; //エスケープを忘れると、PHPが変数と認識したり、エラーが起きたりすることがある。
他に、空白を挿入する場合のダブルクォートなど、ついついエスケープを忘れがちになりますが、SQL文などと同様、あくまでPHPファイル上で処理されるのは文字列に過ぎません。
９： Excelファイルのテンプレートを複写して使う
テンプレートとして既存のxlsxを使うには、setReadDataOnly(false)としてからファイルを開くのですが、そのままだと既存のテンプレートに上書きされてしまいます。それを回避するためにはcloneという便利なオブジェクトがあります。
use PhpOffice\PhpSpreadsheet\Reader\Xlsx as XReader;
$xreader = new XReader();
use PhpOffice\PhpSpreadsheet\Writer\Xlsx as XWriter;
$template = $_SERVER['DOCUMENT_ROOT']."/tmp/template.xlsx"; //任意のテンプレート
$filepath = $_SERVER['DOCUMENT_ROOT']."/data/";
$xreader -> setReadDataOnly(false); //これをfalseにしないと複写できない
$spread = $xreader -> load($tmplate); //テンプレートをロードする
//データとファイル名を並行でループさせる($ary_dataは任意のデータ配列、$filenamesはファイル名の配列)
foreach(array_map(NULL,$ary_data,$filenames) as list($data,$filename) ){
$cloned_spread = clone $spread; //テンプレートの複写
$cloned_spread -> getActiveSheet() -> fromArray($data,NULL,"A2"); //任意のデータの代入
$xwriter = new Xwriter($cloned_spread); //データを書き込む
$xwriter -> save($filepath.$filename); //書き込んだxlsxファイルを保存する
}
※参考になったページ(Teratailより）
ただし、cloneはPHP自体が用意しているオブジェクトであり、シートだけを複写する場合は推奨していないそうで、sheet -> copy()メソッドを使用するように指定しているようです。ところが、このcopy()メソッドもあまり期待した動作にならないことが多く、勝手にエンコードが差し替わったりします（そもそも任意のシートを複写するものですので、用途が異なります）。
また、背景色、太字などを制御するには、clone前のファイルを処理するようにしてください。
また、このcloneを使わずに、別ファイル保存した方がいい場合が多数発生します（特にAPIなどはすべて無効化されてしまったりします。いずれ、この部分も体系的に分析したいところです）。
１０： 行や列を削除する
任意のシートから対象の行や列を削除することができます。文法は以下の通りです。また、列の場合はインデックスで指定する方法もあります。第二引数は省略可なので、単行や単列を処理したい場合は第二引数は空白にしてください。
-
removeRow(削除したい先頭の行番号, 削除したい行数); -
removeColumn(削除したい先頭の列記号,削除したい列数); removeColumnByIndex(削除したい先頭の列番号,削除したい列数)
警告
結合したセルを行や列削除したりするとデータが壊れます
$sheet = $spread -> getActivesheet();
$sheet -> removeRow(10,5); //対象シートの10行めを先頭に、5行分削除する
$sheet -> removeCoulumnByIndex(3,4); //対象シートの3列（C）から4列削除する
全部の行が消える場合
古いバージョンのPhpspreadsheetだと全行が消えてしまうバグを持っています。なので、その場合は以下のように書き換えてください（2113行目のremoveRowメソッド）。
public function removeRow($pRow, $pNumRows = 1)
{
if ($pRow >= 1) {
$highestRow = $this->getHighestDataRow();
$objReferenceHelper = ReferenceHelper::getInstance();
$objReferenceHelper->insertNewBefore('A' . ($pRow + $pNumRows), 0, -$pNumRows, $this);
//$r=0から$r=$pRowに入れ替え
for ($r = $pRow; $r < $pNumRows; ++$r) {
$this->getCellCollection()->removeRow($highestRow);
--$highestRow;
}
} else {
throw new Exception('Rows to be deleted should at least start from row 1.');
}
return $this;
}
※これを発見したとき、目が点になりました...。
１１： 複数にまたがるシート内のデータ取得
getActiveSheet()だと現在選択しているシートしか取得できませんが、getSheet($i)だと任意のシートを取得することができます。それとシート数を数えるgetSheetCount()を活用した方法することで、複数のシートからデータを入出力することができます。
$spread = $xReader -> load($loadfile); //任意のExcelファイルを読み込む
$cnt_sheet = $spread -> getSheetCount(); //ファイル内のシート数を数える
$ar_contents = [];
//シート数だけループする
for($i = 0 ;$i < $cnt_sheet ; $i++ ){
$ar_contents[] = $spread -> getSheet($i) -> toArray(); //シート内全データの取得
}
こうすれば、配列$ar_contentsにデータがシート順に代入されていきます。
１２： Excelのセルに埋め込んだ数式を反映させる
rangeToArray()メソッドを使えば、ワークシートの指定したエリアを取得することができますが、何も指定しないままだと、数式を埋め込んだ場合、そのまま読み込んでしまいます。そこで、それを制御する便利な引数があります。
- 第1引数：読み込みたいセルの範囲
- 第2引数：空白セルの場合に表示させる値
- 第3引数：数式を反映させるかどうかの判定（TRUEではい,FALSEでいいえ）
- 第4引数：Excelの各セルに設定したフォーマットを反映させるかどうかの判定（TRUEではい、FALSEでいいえ）。なお、フォーマットを反映させてしまうと、日付を読み込むとき海外スタイルdd/mm/yyyyになってしまうので、FALSEにした方がいいような気がします。
- 第5引数：セルのアドレス読み取り式をインデックス形式にするか？(TRUEでインデックス、FALSEでアドレス）
$ar_contents = $sheet -> rangeToArray("A1:E30",NULL,TRUE,TRUE,FALSE,FALSE);
ところが、このままだと、数式を埋め込んだ場合に値が反映されないとundefined index XXというエラーが表示されてしまうので、これを制御するためにエラー制御演算子@を埋め込みます（実はインスタンスにも使えます）。
$ar_contents = @$sheet -> rangeToArray("A1:E30",NULL,TRUE,TRUE,FALSE,FALSE);
こうすれば、数式の計算結果のまま読み込むことができ、その計算結果が空白の場合でもエラーを表示させることありません。また、toArray()関数でも使用できます（参考サイト参照、その場合は引数は4つです）。
参考にしたサイト
※エラーが解消されない場合は３２章を参照してください。
１３：シートを解除/保護してファイル出力する
保護されたシートをテンプレートにすると、出力されたファイルのセルは保護されたままとなり、都合が悪いことがあります。それを解除するメソッドにgetProtectionがあります。
$sheet -> fromArray($data); //$dataは任意の値
$sheet -> getProtection() -> setSheet(false);//保護を解除してくれます。
逆にシートを保護して出力することもできます。setPassword()メソッドを使えば、任意のパスワードも設定できます。
また、任意の場所だけ保護する場合はgetStyleメソッドを適用します。
$sheet -> fromArray($data); //$dataは任意の値
$sheet -> getProtection() -> setSheet(true) -> setPassword('xxxx'); //xxxxが解除用のパスワードとなります。
$sheet -> getStyle('A1:D4') -> getProtection() -> setSheet(true); //A1~D4が保護対象になります。
１４：行や列を非表示にする
よく、計算式部分など、表示させておきたくない行や列があると思いますが、それを非表示にするメソッドがsetVisible(false)です。
$sheet -> getColumnDimension('D') -> setVisible(false); //非表示にしたい列
$sheet -> getRowDimension('5') -> setVisible(false); //非表示にしておきたい行
こうしておけば、出力されたExcelにはD列と５行目が非表示になります。逆にsetVisible(true)にすれば、非表示になっている行や列を再表示させることもできます。
１５：印刷範囲を調整する
ファイルを呼び出して、複写して保存したりすると本来想定していた印刷範囲がずれたりすることがありますが、それを再調整する便利なメソッドがあります。
そこでsetPrintArea()メソッドを使用すると、随時印刷範囲を設定できます。
$sheet -> getPageSetup() -> setPrintArea('A:Z'); //AからZまで印刷する場合
$sheet -> getPageSetup() -> setPrintArea('A1:Z30'); //A1からZ30まで印刷する場合
ほかにも色々できるみたいで、マージン、ヘッダ、フッタなどを設定できます。また、WorksheetAPIを使用すれば印刷の向き、印刷の用紙サイズなども設定できます。
イ：印刷の倍率
倍率を指定するにはsetScale(n)メソッドを使用します。
$sheet -> getPageSetup() -> setScale(80); // 80%になる
ロ：印刷の向き
印刷の向きはWorksheetAPIのPageSetup()属性から設定できます。そしてsetOrientation()メソッドで向きを決定させます（デフォルトは縦向き）。
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup as PageSetup;
$sheet -> getPageSetup() -> setOrientation(PageSetup::ORIENTATION_PORTRAIT); //縦向き
$sheet -> getPageSetup() -> setOrientation(PageSetup::ORIENTATION_LANDSCAPE); //横向き
ハ：用紙サイズ
用紙サイズはWorksheetAPIのPageSetup()属性から設定できます。そしてsetPaperSizeメソッドで様式を決定させます。例ではA4ですが、他にも色々用意されています。
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup as PageSetup;
$sheet -> getPageSetup() -> setPaperSize(PageSetup::PAPERSIZE_A4); //A4サイズ
- 公式のAPI
ニ：センタリングの解除
印刷プレビューのセンタリングを解除するにはそれぞれ、setVerticalCentered(bool)メソッド、setHorizontalCentered(bool)メソッドを使用します。
$sheet -> getPageSetup() -> setVerticalCentered(false); //上下の中央揃え解除
$sheet -> getPageSetup() -> setHorizontalCentered(false); //左右の中央揃え解除
ホ：マージン指定
印刷のマージンはgetPageMargins()メソッド内から制御します。引っかかりがちな注意点として数値はインチ指定となっているので、cmで入力するとおかしな結果を招きます。1インチは2.54cmなので、計算しておくといいでしょう。
$inch = round($margin / 2.54,-2); //Excelのインチ指定は小数点2桁まで対応している
$sheet -> getPageMargins() -> setTop($inch); //上
$sheet -> getPageMargins() -> setBottom($inch); //下
$sheet -> getPageMargins() -> setLeft($inch); //左
$sheet -> getPageMargins() -> setRight($inch); //右
$sheet -> getPageMargins() -> setHeader($inch); //ヘッダ
$sheet -> getPageMargins() -> setFooter($inch); //フッタ
ヘ：ページ送り
一定の行でページ送りを行うにはWorksheetAPIとsetBreak()メソッドを使用します。注意点として、一度このメソッドで設定してしまうと、再設定しないとファイルに設定が適用されたままとなります。
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet as Worksheet;
$sheet -> setBreak("A20",Worksheet::BREAK_ROW); //行で送る
$sheet -> setBreak("D1",Worksheet::BREAK_COLUMN); //列で送る
ト：セル枠、印刷範囲のグリッドの表示、非表示
印刷時にExcelのセル枠を非表示にするにはsetShowGridlines(bool)メソッドで制御し、setShowGridlines(false)で制御可能です。
$sheet -> setShowGridlines(false); //印刷時にセル枠が非表示になる（ボーダー指定は対象外）
また、setPrintGridlines(true)で印刷範囲のグリッドを表示できます（Excelの仕様と同様、一度印刷プレビューに行かないと表示されないようです）。
チ：繰り返し行(見出し)を設定する
見出しを設定して、繰り返し行を設定する場合はsetRowsToRepeatAtTopByStartAndEnd(開始行,終了行)を使用します。
$sheet -> getPageSetup()-> setRowsToRepeatAtTopByStartAndEnd(1, 2); //1行目を先頭に2行目までが見出しとして繰り返される
リ：ヘッダ、フッタにタイトルやページ番号を設定する
ヘッダ、フッタを設定するにはgetHeaderFooter()メソッドを活用します。また、&の記号を用いた各種コードを使用することで、さまざまな設定ができます。
$sheet -> getHeaderFooter()-> setOddHeader("&C これはタイトルです"); //&Cはセンタリング
$sheet -> getHeaderFooter()-> setOddFooter("&P/&N"); //&Nは全ページ数、&Pは現在ページの付与
Setting the print header and footer of a worksheet
１６：テキストの一部分に特定フォント（太字、色文字、サイズ変更など）を用いる
任意のセルに対して、全文字のスタイルを変更するのは簡単です。ところが、これが一部分への使用となると、かなり情報が少なく苦労しましたが、RichTextAPIを使って設定できます。流れとしてはRichTextAPIで特定のフォントを埋め込んだ文字列をcreateTextRun()メソッドで、そうでない文字列をcreateText()メソッドで作り、それをセルに反映させるという行程になります。
実はこの２つのメソッド、文字列の追記ができます。
use PhpOffice\PhpSpreadsheet\RichText\RichText as RichText; //RichTextAPIを使用
$RichText = new RichText();
$RichText -> createText("以下の文章はこの部分だけ");
//以下は特定のフォントを埋め込む作業
$specialFont = $RichText -> createTextRun("太字") -> getFont(); //特殊フォント用のオブジェクトを作ると複合条件に対応できる
$specialFont -> setBold(true); //太字制御
/* 以下は参考までに
$specialFont -> setName('HG丸ｺﾞｼｯｸM-PRO'); //フォントの種類
$specialFont -> setSize(20); //文字サイズ
*/
//引き続き、追記ができる。
$RichText -> createText("になります");
//テキストをセルに反映
$sheet -> getCell("A1") -> setValue($RichText);
こうすれば、
以下の文章はこの部分だけ太字になります
とセル内に表示させることができます。
- 参考サイト(PHPExcelでの解説ですが、PhpSpreadSheetでも反映させることができます）
PHPExcel - How to make part of the text bold
※応用
注意点としてフォント色を変える場合、StyleAPIのColor属性が必要になります。また、一つのフォントに複数条件適用したい場合は、getFont()メソッドのオブジェクト変数を作成するといいでしょう。
use PhpOffice\PhpSpreadsheet\RichText\RichText as RichText;
use PhpOffice\PhpSpreadsheet\Style\Color as Color;
$sheet = $spread -> getActivesheet();
//太字にする
$RichText = new RichText();
$RichText -> createText("この部分だけ");
$RichText -> createTextRun("太字") -> getFont() -> setBold(true); //太字制御
$RichText -> createText("になります");
$sheet -> getCell("A1") -> setValue($RichText);
//フォントを変更する
$RichText2 = new RichText();
$RichText2 -> createText("この部分だけ");
$RichText2 -> createTextRun("フォント") -> getFont() -> setName('HG丸ｺﾞｼｯｸM-PRO'); //フォントの種類
$RichText2 -> createText("が変更されます");
$sheet -> getCell("A2") -> setValue($RichText2);
//文字サイズを変更する
$RichText3 = new RichText();
$RichText3 -> createText("この部分だけ");
$RichText3 -> createTextRun("文字サイズ") -> getFont() -> setSize(20); //文字サイズ
$RichText3 -> createText("が変更されます");
$sheet -> getCell("A3") -> setValue($RichText3);
//文字色を変更する
$RichText4 = new RichText();
$color = new Color();
$color-> setRGB('FF0000'); //フォント色のインスタンスを作っておく
$RichText4 -> createText("この部分だけ");
$RichText4 -> createTextRun("配色") -> getFont() -> setColor($color);//文字色
$RichText4 -> createText("が変更されます");
$sheet -> getCell("A4") -> setValue($RichText4);
//全部適用する
$RichText5 = new RichText();
$RichText5 -> createText("この部分だけ");
$specialFont = $RichText5 -> createTextRun("全部") -> getFont(); //特殊フォント用のオブジェクトを作ると複合条件に対応できる
$specialFont -> setBold(true); //太字制御
$specialFont -> setName('HG丸ｺﾞｼｯｸM-PRO'); //フォントの種類
$specialFont -> setSize(20); //文字サイズ
$specialFont -> setColor($color);//文字色
$RichText5 -> createText("が変更されます");
$sheet -> getCell("A5") -> setValue($RichText5);
//改行混じりのテキストを処理する
$RichText6 = new RichText();
$sheet -> getStyle('A6') -> getAlignment() -> setWraptext(true);
$RichText6 -> createText("改行混じりも\n");
$RichText6 -> createTextRun("適用") -> getFont() -> setItalic(true); //斜体制御
$RichText6 -> createText("\nできます");
$sheet -> getCell("A6") -> setValue($RichText6);
//複数回、リッチテキストを追記する
$RichText7 = new RichText();
$RichText7 -> createText("ここを");
$RichText7 -> createTextRun("太字") -> getFont() -> setBold(true); //太字制御
$RichText7 -> createText("に、ここも");
$RichText7 -> createTextRun("また太字") -> getFont() -> setBold(true); //太字制御
$RichText7 -> createText("にできます");
$sheet -> getCell("A7") -> setValue($RichText7);
１７：シートの表示ズームサイズを調節する
setZoomScale(n)というメソッドがあり、それを使います。EXCELと同様10～400まで変更が可能です。また、シート単位で設定できます。
$Sheet -> getSheetVlew() -> setZoomScale(70); //ズームを70％にする
１８：シートに画像を貼り付ける
画像を貼り付けるにはDrawingAPIを使用します。ですが、注意しなければいけないのは、仕様なのかバグなのかわかりませんが、複写する際は、各データを入力する前に画像を貼って下さい。文字を入力した後で画像を貼るとエラーが起きることがあります。
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing as Draw; //Drawing属性を使用
$Draw = new Draw(); //インスタンスの作成
$Draw -> setPath('画像のパス'); //画像のパス
$Draw -> setCoordinates("B2"); //画像貼り付けの場所。例ではB2
$Draw -> setResizeProportional(true); //画像をリサイズするかどうか
$Draw -> setWidth(400); //画像サイズ
$Draw -> setOffsetX(10); //X軸マージン
$Draw -> setOffsetY(10); //Y軸マージン
//引き続きデータを入力する
$sheet -> setCellValue("A1","写真タイトル");
※なお、標準ではjpgしか対応していません。gifやpngを貼付したい場合はMemoryDrawingAPIを用います。詳しくは以下の記事を参照してください（画像処理に関して自分よりずっと詳しく解説されています）。
１９：日本語入力したセル列幅を自動リサイズする
このPHPExcelでのページを参考に色々テストを重ねてみました。ポイントは予めsetAutoSize(true)を使用する必要があり、Windows（Xamppで検証）ならそれだけで行けるみたいですが、Linux（ceotOSで検証）で列を指定する場合は再度setAutoSize(false)で値をリセットし、一定サイズを掛ける作業が必須のようです。
なお、単純にsetAutoSize(true)だけではフォントの文字幅を読み取れないので意味がなく、公式ページを読む限り、あくまでこのメソッドは幅のリサイズを有効にする命令を与えるもののようで、半角英数字でも値がずれたりします。なので、実際に列幅をカウントして、その値を取得してから明示的に指定する必要があります。
それから、（１）～（７）の順番厳守でこの順番以外だと列幅は-1しか返しません（つまり、値は無効）。また、2バイト文字に対応させるために幅に1.7から2.0を掛けておくとよさそうです(自動調整とほぼほぼ変わりませんでした)。
$sheet -> setCellValue("A1","鹿児島県志布志市志布志町志布志"); //文字を設定 （１）
$dim = $sheet -> getColumnDimension("A"); //対象列のオブジェクト作成 （２）
$dim -> setAutoSize(true); //リサイズを有効にする （３）
$sheet -> calculateColumnWidths(); //列幅を数える （４）
$dim -> setAutoSize(false); //リサイズの初期化（５）…Windowsの場合は不要かも
$col_width = $dim -> getWidth(); //列幅を取得 （６）
$dim -> setWidth($col_width * 1.7); //列幅を指定。取得した値に1.7を掛けると理想的な幅となる。 （７）
※半角文字と全角文字が混淆している場合
もし、長さで半角文字のみが上回っていると、上記のような決め打ちだとマージン幅がおかしくなってしまいます。なので、全角か半角かを判別して、それに適した長さを調節するといいでしょう。
$col_width = $dim -> getWidth(); //列幅を取得 （６）
$value = $sheet -> getCell("A1") -> getValue(); //対象の値を取得
$margin = (strlen($value) > mb_strlen($value) )? 2:0.98; //全角か半角かで倍率を任意で変更
$dim -> setWidth($col_width * $margin );
２０：行や列に値をコピーする
メソッドを隈なく調べてみたのですが、コピーした行や列を挿入という直接的な記述はないようです。その代わり、範囲を取得、その範囲を貼り付け、もし列を挿入する場合は、新たに列を挿入するというプロセスを経ると同様の操作が可能になります。
$range = $sheet -> rangeToArray("A1:A10"); //指定の範囲を取得
$sheet -> insertNewColumnBefore("C"); //C列の前に列を新規挿入する
$sheet -> fromArray($range,NULL,"C1"); //指定の位置に貼り付ける
新規挿入しない場合は、既存のセルに上書きされます。
なお、新規に行や列を挿入するメソッドは4種類あります。column（列）なのかrow（行）なのか、そしてbefore（前）かで覚えるといいでしょう。また、それぞれ引数を2つ持つことができ、2つめの引数に対象となる行数、列数を指定することができます。
$sheet -> insertNewColumnBefore("A"); //A列の前に挿入
$sheet -> insertNewRowBefore(1); //1行の前に挿入
$sheet -> insertNewColumnBefore("A",3); //A列の前に3列挿入
$sheet -> insertNewRowBefore(1,4); //1行の前に4行挿入
行から列を入れ替えてペーストする場合
行から列に入れ替える場合はarray_column関数が役立ちます。array_column関数は指定のカラムを抽出して配列を組み直す関数なので、第2引数に先頭行のインデックスを入れます。
$v_range = $sheet -> rangeToArray("A2:A11"); //範囲の取得
$h_range = array_column($v_range,0); //インデックス0を基準に配列を組み直すと行列が入れ替わる
$sheet -> fromArray($h_range,NULL,"B1"); //その配列を貼り付けると行列入れ替えで貼り付けと同じになる
列から行に入れ替えてペーストする場合
列から行に入れ替える場合はarray_chunk関数が役立ちます。array_chunk関数は配列を指定した要素数ごとに配列を分割する関数なので、第2引数に入れ替えたい行数を入れます。
$h_range = $sheet -> rangeToArray("B1:K1"); //範囲の取得
$v_range = array_chunk($v_range,1); //array_chunkで1個ずつ配列にする
$sheet -> fromArray($v_range,NULL,"A2"); //その配列を貼り付けると列行入れ替えで貼り付けと同じになる
２１：ワークシートそのものを挿入、複写、削除、隠蔽する
ワークシートを操作するにはWorksheetAPIを活用します。注意点として複写する場合は同じ名称を使わず、事前に名称を指定しておかないとエラーが出ます。
イ：新規作成のシートを挿入する
新たに指定する場合です。何も指定しない場合はcreateSheet()メソッドだけで作れ、引数を何も指定していない場合は末尾にシートが作成されます。
use PhpOffice\PhpSpreadsheet\Reader\XlsX as XReader;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet as Worksheet; //worksheet属性を使用
$xReader = new XReader();
$spread = $xReader -> load("hogehoge.xlsx"); //任意のファイルを読み込み
$newsheet = new Worksheet($spread,"newsheet"); //ここで任意のシートを新規作成
$spread -> addSheet($newsheet, 0); //任意のシート追加。0は挿入する位置
ロ：既存のシートを複写する
既存のシートを複写する場合はcloneメソッドでシートを複写し、予めタイトルを付与しておいてからaddSheet()メソッドを使います。シートの指定はgetSheet(n)でのシート番号による指定、getSheetbyName('シート名')によるシート名による指定、いずれも使用可能です。
$cloned_sheet = clone $spread -> getSheetbyName("newsheet") //複写元のシート
$cloned_sheet -> setTitle("copiedsheet"); //必ず事前にタイトル名を決めておくこと
$spread -> addSheet($cloned_sheet); //２つ目の引数を省略した場合は最後尾に追加される。
ハ：シートを削除する
シートを削除する場合はシート番号を指定します。そのために公式ページでは、getIndex()メソッドを使ってシート番号を取得する方法を推奨しているようです。Excel操作と違って、削除の際の警告は出ませんので、操作は慎重に行ってください。
$sel_index = $spread->getIndex( $spread->getSheetByName('newsheet') ); //シート番号を取得
$spread ->removeSheetByIndex($sel_index);
※応用：雛型からデータを複写する
これらのメソッドを駆使すると、同一ブックの複数シートに雛型を複写することができます。ポイントは複製元にデータを挿入しないことです。またSheet番号をリナンバリングする場合は、必ず複製元の雛形をSheet1から別名に変えておいて下さい（同一シート名を設定しようとするとエラーが発生します）。
$base_sheet = $spread -> getSheet(0); //先頭のシートを雛形とする
$base_sheet -> setTitle("basesheet"); //リナンバリングするので元のSheet1を別名に変更
$cloned = clone $base_sheet; //シートを複製
for($i = 1;$i <= $max ;$i++ ){
$newsheet = $cloned -> setTitle("Sheet".$i); //タイトルを付与
$spread -> addSheet($newsheet); //シートを複製
//データの処理を行う
$spread -> getSheet($i) -> fromArray(...); //データを入力していく
}
$spread -> removeSheetByIndex(0); //複製元の雛型を削除する
ニ：シートを非表示・再表示する
ワークシートを非表示にする場合はAPI内のメソッドを活用することになります。
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet as Worksheet; //worksheet属性を使用
…
$spread -> getSheet(1) -> setSheetState(Worksheet::SHEETSTATE_HIDDEN); //任意のシート(例だと2つ目)を非表示にする
$spread -> getSheet(2) -> setSheetState(Worksheet::SHEETSTATE_VISIBLE); //任意のシート(例だと3つ目)を再表示
※ シートタブを着色する場合
シートタブを着色する場合は以下のコマンドで対処します。対応していない色もあるので注意。
$sheet->getTabColor()->setRGB('FF0000');
２２：セルに入力規則を設定する
けっこうややこしい記述法ですが、CellAPI内のDataValidation属性で制御できます。共通の流れとしては
- validation属性を使用
- 指定のセルを選択
- どのタイプを適用するか？（範囲指定型orリスト指定型）
- どのような表示形式にするか？
- メッセージ制御（指定しない場合はExcelのデフォルト表示になります）
- ルール指定
となります。
A:指定範囲内で入力制御する
たとえば、数値を入力する場合0～100までに制限する場合です。
use PhpOffice\PhpSpreadsheet\Cell\DataValidation as validation; //バリデーションの設定
$validation = $spread -> getActivesheet() -> getCell('B1') -> getDataValidation();
$validation -> setType(validation::TYPE_WHOLE); //範囲指定したい場合。WHOLEは全体という意味
$validation -> setErrorStyle(validation::STYLE_STOP); //警告スタイル適用
$validation -> setErrorTitle("エラー");
$validation -> setError("指定以外の値が入力されています！"); //エラーメッセージ
$validation -> setpromptTitle("注意");
$validation -> setPrompt("0~100の数値を入力してください"); //セルにフォーカスされたときの注意コメント表示
$validation -> setFormula1(0); //ルール1（下限値）
$validation -> setFormula2(100); //ルール2（上限値）
※なお、他のセルにも適用する場合は$validationを複写します。
$sheet -> getCell("B2") -> setDataValidation( clone $validation );
B:指定の値で入力制御する
リスト形式にして、「はい、いいえ」で答えてもらう場合です。ドロップダウンリストを使わない指定もできます。なお、分析してみたところ、setAllowBlankメソッドを使用する場合はsetShowErrorMessage()、setShowInputMessage()メソッドの指定もしないと、バリデーション制御ができないようです。
use PhpOffice\PhpSpreadsheet\Cell\DataValidation as validation; //バリデーションの設定
$validation = $spread -> getActivesheet() -> getCell('C1') -> getDataValidation();
$validation -> setType(validation::TYPE_LIST); //リスト指定したい場合
$validation -> setErrorStyle(validation::STYLE_INFORMATION); //メッセージスタイル適用
$validation -> setAllowBlank(false); //空白を不許可にする
$validation->setShowInputMessage(true); //setAllowBlank()メソッドを適用した場合は指定必須
$validation->setShowErrorMessage(true); //setAllowBlank()メソッドを適用した場合は指定必須
$validation -> setShowDropDown(true); //ドロップダウン方式にする
$validation -> setError("指定以外の値での回答はできません"); //エラーメッセージ
$validation -> setPrompt("はいorいいえで回答してください"); //セルにフォーカスされたときの注意コメント表示
$validation -> setFormula1('"はい,いいえ"'); //ルール(入力許可される値)
Setting data validation on a cell
また、３４章ではプルダウンを掘り下げ、連動プルダウンの作成について触れています。
２３：オートフィルターを使う
オートフィルターを使うにはsetAutoFilter()メソッドを用います。
A：行すべてに指定する
このようにcalculateWorksheetDimension()メソッドを使用することで、全行に対しオートフィルタを適用することができます。
$sheet = $spread -> getActivesheet();
$sheet -> setAutoFilter( $sheet -> calculateWorksheetDimension() );
B：部分的に指定する
フィルターを適用したいアドレス範囲を指定するとオートフィルターをかけたい行を指定できます。注意点としてフィルターが表示されるのは指定した先頭行だけですが、以下のように範囲を指定しないと、範囲外の値はフィルタリングされません。
$sheet = $spread -> getActivesheet();
$sheet -> setAutoFilter("A1:C20"); //A1からC20までが適用されるが、A21からの値は適用外となる
C：ルール指定して対象の値だけ表示させる
setAutoFilter()だけだとルール指定ができないので、ルール指定する場合はAutofilterAPIを活用します。なお、この情報は英語サイトにも公式サイト以外全くといっていいほど記載例がないので、本当に動作するか検証を繰り返しています。
イ：完全一致
完全一致でフィルタリングしたい場合は以下のような記述を行います。例ではA列に東京都、横浜市、名古屋市、大阪市、神戸市、広島市、福岡市が入っており、B列に区名（中央区・中区）が入っています。その結果、対象都市すべての中央区（東京都、大阪市、神戸市、福岡市）だけが返されることになります。
use PhpOffice\PhpSpreadsheet\Worksheet\Autofilter\Column\Rule as Rule;
$sheet -> setAutoFilter("A1:B20");
$column = $sheet -> getAutoFilter()-> getColumn("B");
$column -> createRule() -> setRule(Rule::AUTOFILTER_COLUMN_RULE_EQUAL,"中央区");
$column -> createRule() -> setRule(Rule::AUTOFILTER_COLUMN_RULE_NOTEQUAL,"中央区"); //不完全一致の場合
`
ロ：部分一致
部分一致条件を適用するには２つの属性を駆使し、ワイルドカード(*)を使って設定します。記述ルールとしてsetFilterType()でフィルターの型を指定してから、createRule()の後にsetRuleType()と指定しないと機能しません。
use PhpOffice\PhpSpreadsheet\Worksheet\Autofilter\Column as Column;
use PhpOffice\PhpSpreadsheet\Worksheet\Autofilter\Column\Rule as Rule;
$column = $sheet -> getAutoFilter()-> getColumn("C");
$column -> setFilterType(Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER);
$column -> createRule() -> setRule(Rule::AUTOFILTER_COLUMN_RULE_EQUAL,'*山*') -> setRuleType(Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER);
setRule()メソッドには以下の記述ができます。Excelと同じく正規表現は非対応です。
$column -> createRule() -> setRule(Rule::AUTOFILTER_COLUMN_RULE_EQUAL,'山*'); //～で始まる
$column -> createRule() -> setRule(Rule::AUTOFILTER_COLUMN_RULE_EQUAL,'*山*'); //～を含む
$column -> createRule() -> setRule(Rule::AUTOFILTER_COLUMN_RULE_EQUAL,'*山'); //～で終わる
ハ：未満・以下・以上・超
数値に対して条件を指定するには２つの属性を駆使します。こちらは当然、数値しか効果がありません。
use PhpOffice\PhpSpreadsheet\Worksheet\Autofilter\Column as Column; //カラムを制御
use PhpOffice\PhpSpreadsheet\Worksheet\Autofilter\Column\Rule as Rule; //カラム内のルール制御
$sheet -> setAutoFilter("A1:D20");
$column = $sheet -> getAutoFilter()-> getColumn("D");
$column -> setFilterType(Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER); //予め宣言する
$column -> createRule() -> setRule(Rule::AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL,50) -> setRuleType(Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER); //50以下の値を抽出
また、範囲の制御は以下の通りです。LESSTHAN(未満)、GREATERTHAN(超)の法則とOREQUAL(or equal)の組み合わせを押さえておけば混同しないでしょう。
$column -> createRule() -> setRule(Rule::AUTOFILTER_COLUMN_RULE_LESSTHAN,50) -> setRuleType(Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER); //50未満
$column -> createRule() -> setRule(Rule::AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL,50) -> setRuleType(Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER); //50以下
$column -> createRule() -> setRule(Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL,50) -> setRuleType(Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER); //50以上
$column -> createRule() -> setRule(Rule::AUTOFILTER_COLUMN_RULE_GREATERTHAN,50) -> setRuleType(Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER); //50超
※但し、数値を予めExcel内でセル属性を数値としないと、期待通りの操作となりませんので、２４章を読んで、事前に数値指定してください。
ニ：複合条件
甲：OR検索
OR条件の場合はデフォルト指定なので、そのまま追記します。以下の記述で30未満または60超の条件が適用されます。
$column -> createRule() -> setRule(Rule::AUTOFILTER_COLUMN_RULE_LESSTHAN,30) -> setRuleType(Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER); //30未満
$column -> createRule() -> setRule(Rule::AUTOFILTER_COLUMN_RULE_GREATERTHAN,60) -> setRuleType(Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER); //60超
乙：AND検索
AND条件の場合は明示的に命令を記述します。公式だとsetAndOr()メソッドを書いていましたが、そんなものはPHPExcelまでで廃止されており、setJoin("And")メソッドを使えばいけます(公式サイトが間違えていたので、APIのソースを調査しました)。
$column -> createRule() -> setRule(Rule::AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL,40) -> setRuleType(Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER); //40以上
$column -> setJoin( "And" ); //AND条件という指示。Orも使えます。
$column -> createRule() -> setRule(Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL,60) -> setRuleType(Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER); //60以下
ホ：トップテン方式（ランキングとパーセントによるフィルター）
警告
このフィルターを適用する場合、必ず値を数値化しておいて、一度データをバックアップをとってから試してください。値を数値化していない場合、ファイルそのものが高い確率で破損します。
甲：パーセントで取得する
パーセンテージで取得する場合です。フィルター形式をAUTOFILTER_FILTERTYPE_TOPTENFILTERとし、setRule()メソッドで、以下のように記述します。つまり、例ならパーセント方式で5%を昇順で取る、ということになります。
//setRule(方式,範囲とする数値,順序)
setRule(Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT,5,Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP)
$sheet = $spread -> getActivesheet();
$sheet -> setAutoFilter($sheet -> calculateWorksheetDimension() );
$sheet -> getAutoFilter();
$sheet -> setAutoFilter("A1:D20");
$column = $sheet -> getAutoFilter()-> getColumn("D");
$column -> setFilterType(Column::AUTOFILTER_FILTERTYPE_TOPTENFILTER); //トップ10フィルターの設定
$column -> createRule() -> setRule(Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT,5,Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP) -> setRuleType(Rule::AUTOFILTER_RULETYPE_TOPTENFILTER); //ルールタイプの適用
乙：個数で取得する
個数で取得する場合はTOPTEN_PERCENTではなくTOPTEN_BY_VALUEになります。後の記述は変わりません。なお、値が少ない順で取得したい場合は、TOPTEN_BOTTOMとなります。例なら下位5件を取る、ということになります。
$sheet = $spread -> getActivesheet();
$sheet -> setAutoFilter($sheet -> calculateWorksheetDimension() );
$sheet -> getAutoFilter();
$sheet -> setAutoFilter("A1:D20");
$column = $sheet -> getAutoFilter()-> getColumn("D");
$column -> setFilterType(Column::AUTOFILTER_FILTERTYPE_TOPTENFILTER); //トップ10フィルターの設定
$column -> createRule() -> setRule(Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_BY_VALUE,5,Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_BOTTOM) -> setRuleType(Rule::AUTOFILTER_RULETYPE_TOPTENFILTER); //ルールタイプの適用
※Only variables should be passed by reference....
もし、このフィルターを試すとこのエラーが出ると思います。これはこういう理由によるもので、ならばソースコードを編集し、変数を作って代入します。
//ここが原因
return array_pop( array_slice($dataValues, 0, $ruleValue));
//このように書き換えるとnoticeメッセージが出ない
$val = array_slice($dataValues, 0, $ruleValue);
return array_pop($val);
※オートフィルターはかなり複雑なので、公式ページでもかなり詳しく載っています。
AutoFilter Reference
２４：セルの書式設定を変更する
２３章に絡んでいるのですが、Excelで書式設定を数値にしないとオートフィルターで不具合が発生すると記述しました。では、どうやって書式設定を変えるのかを調査したところCellAPIのDatatype属性が必要になります。そしてsetValueExplicit()メソッドに書式を入力します。注意点として、あくまで入力値に対して書式設定を行うため、一旦数値を取得する必要があります。したがって、Excelのように空値に書式だけ設定することはできないようです（設定したあとsetValue()で空値を入れたりしたら、書式が元の標準に戻ってしまいました）。
use PhpOffice\PhpSpreadsheet\Cell\DataType as Datatype; //Datatype属性を使用
$value = $sheet -> getCell('D2') -> getValue(); //値を一旦取得
$sheet -> getCell('D2') -> setValueExplicit($value,Datatype::TYPE_NUMERIC); //書式設定が数値となる
でも、これだと一つのセルしか適用されないので、一定範囲を指定したい場合はこのようにループさせるといいでしょう。
//指定したい範囲をループさせる
for($row = 2; $row < 21 ; $row++){
$cell = "D{$row}";
$value = $sheet -> getCell($cell) -> getValue();
$sheet -> getCell($cell) -> setValueExplicit($value,Datatype::TYPE_NUMERIC);
}
応用：セルの書式設定を行った値にカンマを付与する
書式設定にはsetCellValueExplicit("アドレス","値","書式")という一度に全部指定できるメソッドもあります。ここで紛らわしいのは**Excel上でのセルの数式設定と、入力値の書式設定は別物(PhpSpreadsheet上での文字入力制御)**なので、双方設定する必要があります。
use PhpOffice\PhpSpreadsheet\Cell\DataType as Datatype;
$sheet -> setCellValueExplicit("A1",$price,Datatype::TYPE_NUMERIC); //書式を変える
$sheet -> getStyle("A1") -> getNumberFormat() -> setFormatCode('#,##0'); //カンマ付与に対応する
なお、Numberformat属性のFORMAT_NUMBER_COMMA_SEPARATED1を指定すると、強制的に下二桁の小数点が付与されてしまいます。
２５ ：イテレータを活用し、全データを読み込みながら特定の処理を行う
普通ならばtoArray()メソッドで十分なのですが、もし、その取得した値に対して、特定の処理（税率を掛ける、あるいは特定の値を対象外とするなど）を行いたい場合には各種のイテレータが役立ちます。
イ：特定の処理を行う
array_mapのように、全データに対し特定の処理を行う場合です。この場合はgetCellIterator()を使うと、全データを取得することができます。そのデータに対し特定の処理を行う、いわばPHPのforeach式などと同じことができます（）。また、アドレスを取得したい場合はgetCoordinate()メソッドを使います。
$sheet = $this -> spread -> getActivesheet();
foreach( $sheet -> getRowIterator() as $row){
$cells = $row -> getCellIterator();
foreach( $cells as $cell){
$val = $cell -> getValue(); //値を取得
$addr = $cell -> getCoordinate(); //アドレスを取得
if(is_numeric($val) ){
echo $val * 1.1; //税率をかける
}elseif($addr != "A1"){
echo $val; //そのまま
}
}
}
ロ：特定の行列だけを抽出する
特定の行列だけを取得したい場合は、以下の方法で行アドレスや列アドレスを取得し、チェックしてフィルタリングします。
$sheet = $this -> spread -> getActivesheet();
$ar_rows = [];
foreach( $sheet -> getRowIterator() as $row){
$rowidx = $row -> getRowIndex(); //行アドレスを取得
if($rowidx == 10 ) continue; //10行目ならスキップ
$ar_cols = [];
foreach( $sheet -> getColumnIterator() as $col ){
$colidx = $col -> getColumnIndex(); //列アドレスを取得
//D列は対象外
if($colidx != "D"){
$ar_cols[] = $sheet -> getCell($colidx.$rowidx) -> getValue();
}
}
$ar_rows[] = $ar_cols;
}
２６：セルに単線（垂直線、水平線、斜線）を引く+アドレスを数値で取得する
単純に境界線を引く場合はgetBorders()メソッドを使います（章を設けたのは斜線の引き方の解説のためです）。ちなみに、applyFromArray()メソッドを使用した方法は別の人が紹介しているので、この章では上下左右だけや外周だけといった、単純に単線の引き方を紹介します。
A:垂直線・水平線
まずはおさらいです。垂直線や水平線を引く場合は以下の公式があり、getBorders() -> getHoge()(Hogeは任意の命令)として、方向を定めてからスタイルを適用します（スタイルの紹介はリンク先を参照してください）。
use PhpOffice\PhpSpreadsheet\Style\Border as Border;
$sheet -> getStyle("アドレス範囲") -> getBorders() -> getTop() -> setBorderStyle(Border::BORDER_DASHED); //上方向
方向を示すメソッドは以下の通りです（斜線は後述）。
getTop(); //上方向
getBottom(); //下方向
getLeft(); //左方向
getRight(); //右方向
getOutline(); //外周
getInside(); //内周(外周以外)
getAllBorders(); //すべての垂直・水平直線
getVertical(); //垂直線すべて
getHorizontal(); //水平線すべて
B:斜線
それを踏まえて、斜線の説明に入ります。公式マニュアルではgetDiagonal()メソッドを使用するようにありますが、それだけでは引けません。ソースの分析を行いテストしてみた結果、先にsetDiagonalDirection(n)メソッドを使って、どの方向に引くかを指定する必要があります。
use PhpOffice\PhpSpreadsheet\Style\Border as Border;
$sheet -> getStyle("A1") -> getBorders() -> setDiagonalDirection(1) -> getDiagonal() -> setBorderStyle(Border::BORDER_THIN);
※setDiagonalDirection(n)の引数は次のようになっているようです。
setDiagonalDirection(1); //上り斜線（左下から右上）
setDiagonalDirection(2); //下り斜線（左上から右下）
setDiagonalDirection(3); //双方向（クロス）
setDiagonalDirection(0); //斜線の取り消し
※実際に対角線を引いてトーナメント表を作成する
では、実際に斜線を使った実例を挙げていきます。ここではトーナメント表を作成します。
use PhpOffice\PhpSpreadsheet\Writer\Xlsx as XWriter;
use PhpOffice\PhpSpreadsheet\Spreadsheet as Spread;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate as Coord;
use PhpOffice\PhpSpreadsheet\Style\Border as Border;
$spread = new Spread();
$sheet = $spread -> getActivesheet();
$max_row = $sheet -> getHighestRow();
$ar_list = ["L","H","E","M","F","B"]; //リスト表
$ar_list_col[] = $ar_list;
$sheet -> fromArray($ar_list_col,NULL,"C1"); //X軸
$ar_list_row = array_chunk($ar_list,1); //列行入れ替え
$sheet -> fromArray($ar_list_row,NULL,"B2"); //Y軸
$max_row = $sheet -> getHighestRow();
$max_col = $sheet -> getHighestColumn();
$max_colvalue = Coord::columnIndexFromString($max_col);
for($coord = 1; $coord < $max_colvalue; $coord++){
$coord_c = $coord + 1;
$coord_r = $coord;
$cur_addr = $sheet -> getCell([$coord_c,$coord_r)]) -> getCoordinate();
$sheet -> getStyle($cur_addr) -> getBorders() -> setDiagonalDirection(2) -> getDiagonal() -> setBorderStyle(Border::BORDER_THIN);
}
$max_addr = $sheet -> getCell($max_col.$max_row) -> getCoordinate();
$sheet -> getStyle("B1:{$max_addr}") -> getBorders() -> getInside() -> setBorderStyle(Border::BORDER_THIN);
$sheet -> getStyle("B1:{$max_col}1") -> getBorders() -> getBottom() -> setBorderStyle(Border::BORDER_THICK);
$sheet -> getStyle("B1:B{$max_row}") -> getBorders() -> getRight() -> setBorderStyle(Border::BORDER_THICK);
$Xw = new XWriter($spread);
$Xw -> save("xls/test.xlsx");
このようにすれば対角線上に下り斜線を引けました。そして、ここでは新たに列アドレスを数値で取得するAPIとメソッドを利用しています。Coord::columnIndexFromString(列アドレス)とすれば、Excelのカラムを英字ではなく、数値で取得することができます。
use PhpOffice\PhpSpreadsheet\Cell\Coordinate as Coord; //座標取得のAPI
$max_col = $sheet -> getHighestColumn(); //最大列の取得
$max_colvalue = Coord::columnIndexFromString($max_col); //最大列を数値で取得
なぜ、数値が必要だったかというと、次の式で最大列を上限値にしてループしているのと、その値を元にインクリメントをかけてアドレスを再計算しているためです（英字のインクリメントはできますが、英字に数値を足すことはできません）。そして、アドレスを数値から取得している部分がgetCell([列アドレスの数値、行アドレスの数値])メソッドで、これを使うことで均等にアドレスの座標値を追加させています（※ver2からはcolumnandrow関係の関数は全部廃止されました。また数値アドレスでセル位置を取得する場合、引数は１つ（行アドレスと列アドレスを格納した配列）となります）。
for($coord = 1; $coord < $max_colvalue; $coord++){
$coord_c = $coord + 1;
$coord_r = $coord;
//ver2から
$cur_addr = $sheet -> getCell([$coord_c,$coord_r])-> getCoordinate();
//それ以前
$cur_addr = $sheet -> getCellByColumnAndRow($coord_c,$coord_r) -> getCoordinate();
$sheet -> getStyle($cur_addr) -> getBorders() -> setDiagonalDirection(2) -> getDiagonal() -> setBorderStyle(Border::BORDER_THIN);
}
２７：セルに日付を出力する
タイムスタンプを取ってそれをそのままExcelに貼り付けたりすると、Excelの書式が合わないために、全く的外れな値になってしまいます。それを防ぐためには書式を設定してから、かつ日付出力用に再計算するSharedAPI内のPHPtoExcel()メソッドで処理が必要です。また、書式設定にはStyleAPIのNumberFormat属性を使用します。
use PhpOffice\PhpSpreadsheet\Shared\Date as Date; //Date属性を使用
use PhpOffice\PhpSpreadsheet\Style\NumberFormat as NF; //StyleAPIからNumberFormat属性を使用
$sheet -> getStyle("A1") -> getNumberFormat() -> setFormatCode(NF::FORMAT_DATE_YYYYMMDDSLASH); //書式設定(後述)
$timestamp = time() + (60 * 60 * 9 ); //PHPのtime関数を日本時間に合わせる（標準時から９時間の時差）
$xlsdateval = Date::PHPToExcel( $timestamp ); //Excel出力用に再計算を行う
$sheet -> setCellValue("A1",$xlsdateval); //再計算した値を代入
出力形式の設定は以下のように変更できます。
setFormatCode(NF::FORMAT_DATETIME); //タイムスタンプを出力(YYYY-MM-DD h:i:s)
setFormatCode(NF::FORMAT_DATE_YYYYMMDD); //ハイフンで年月日を出力(YYYY-MM-DD)
setFormatCode(NF::FORMAT_DATE_YYYYMMDDSLASH); //スラッシュで年月日を出力(YYYY/MM/DD)
setFormatCode(NF::FORMAT_DATE_TIME5); //時間を取得（H:i:s）。TIMEの表示バリエーションは1~8まである。
※時間設定には色々種類があります。公式のソースに定数が設定されているので、参考にするといいでしょう。
また、日付の表示形式を任意指定するにはsetValueExplicit()を使う方法もあります。
２８：ハイパーリンクを埋め込む
リンクを埋め込むにはgetHyperlink()メソッドのsetUrl()メソッドを使用します。
Ａ：シート内のリンクを貼る
シート内のリンクを貼るには以下のようにシート名と転送先アドレスを設定します。
$sheet -> getCell("A1") -> getHyperlink() -> setUrl("sheet://'Sheet1'!A10"); //シート名称Sheet1のアドレスA10にジャンプする
Ｂ：外部リンクを貼る
外部リンクを貼るには以下のようにリンク先を設定します。
$sheet -> getCell("A1") -> getHyperlink() -> setUrl("http://qiita.com");
Ｃ：セル内のテキストだけをハイパーリンクにする
今までのやり方だとセル全体がリンクになってしまいましたが、テキストのみハイパーリンクにするには、CellAPIのHyperlink属性を活用し、setHyperlink()メソッドを使用します。
use PhpOffice\PhpSpreadsheet\Cell\Hyperlink as Hyperlink;
$hyperlink = new Hyperlink("http://qiita.com","ここをリンク");
$cell = $sheet -> getCell("A1");
$cell -> setHyperlink($hyperlink); //リンク設定
$font = $sheet -> getStyle("A1") -> getFont();
$font -> getcolor() -> setARGB('000000FF'); //リンク箇所を青色にする
$font -> setUnderline('single'); //リンク箇所にアンダーラインを付与
参考にしたページ(GitHub内のフォーラム)
How to insert clickable hyperlink in excel? #926
２９：セルの背景を塗りつぶす
セルのパターンを塗りつぶすのは前述したapplyFromArray()メソッドを使う方法と、getFill()メソッドを使って一つ一つ指定していく方法があります。ここでは基本、getFill()メソッドの方法で説明します。また、共通でStyleAPIのFill属性を使用します。
Ａ：全色塗りつぶし
まずは基本です。全色を塗りつぶすにはsetFillType()メソッドを使います。
use PhpOffice\PhpSpreadsheet\Style\Fill as Fill;
$sheet -> getFill() -> setFillType(Fill::FILL_SOLID) -> getStartColor() -> setARGB('00ff7f50');
Ｂ：グラデーションで塗りつぶし
グラデーションを適用するには、先にsetRotation(n)メソッドで、色の向きを指定する必要があります。また、getStartColor()メソッドとgetEndColor()メソッドで開始色と終了色を指定する必要があります。
$fill = $sheet -> getStyle('A1') ->getFill();
$color = $fill -> setFillType(Fill::FILL_GRADIENT_LINEAR); //グラデーションの種類
$fill -> setRotation(0);//グラデーションの向き。0は縦
$color -> getStartColor() -> setARGB('00ff7f50'); //開始色
$color -> getEndColor() -> setARGB('00FFFFFF'); //終了色
※グラデーションの向きはsetRotation()メソッドで指定します。
$fill -> setRotation(45);//右上対角線
$fill -> setRotation(90);//グラデーションの向き。90は横
$fill -> setRotation(135);//右下対角線
また角から指定する場合はsetFillType()`メソッドを変更します。
$color = $fill -> setFillType(Fill::FILL_GRADIENT_PATH); //グラデーションの種類
$color -> getStartColor() -> setARGB('00ff7f50'); //開始色
$color -> getEndColor() -> setARGB('00FFFFFF'); //終了色
※なお、グラデーションを中央から指定する場合は変数で設定されていないため、現在方法を探索中。
Ｃ：パターンで塗りつぶし
これがかなり苦戦しました。ポイントはパターンだからといってgetStartColor()メソッドだけでは単色になってしまいます。そのため、セル上にパターンを浮き上がらせるためには、getEndColor()メソッドに白色（白の場合は単色パターンとなる）など、2色を織り交ぜる必要があります。
$color = $sheet -> getStyle('B2') ->getFill()->setFillType(Fill::FILL_PATTERN_LIGHTDOWN);
$color -> getStartColor() -> setARGB('00fdeff2');
$color -> getEndColor() -> setARGB('00FFFFFF'); //2色目を選定しないとパターンが浮き上がらない。
※パターンの種類は以下のようなものがあります（一部）
setFillType(Fill::FILL_PATTERN_DARKDOWN); //右下がり斜線 縞
setFillType(Fill::FILL_PATTERN_DARKGRAY); //左下がり斜線 格子
setFillType(Fill::FILL_PATTERN_GRAY0625); //6.25％灰色
setFillType(Fill::FILL_PATTERN_LIGHTGRID); //横 格子
※この質問記事に塗りつぶしのリストがあり、名称は継承しています。
PHPExcel pattern style
３０：スタイルを複数のセルで繰り返す
今までは一つ一つアドレスに対し、処理を繰り返してきましたが、正直面倒な場合もあります。そんなときにはduplicateStyle()メソッド（duplicateは重複の意）を使うと便利です。
※以下のサンプルを見れば、行、列及びブロックでの複写は有効である反面、対角線のみの複写はできないことがわかると思います。
use PhpOffice\PhpSpreadsheet\Reader\Xlsx as XReader;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx as XWriter;
use PhpOffice\PhpSpreadsheet\Style\Alignment as Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border as Border;
$xr = new XReader();
$spread = $xr -> load($_SERVER['DOCUMENT_ROOT']."/doc4.xlsx");
$sheet = $spread -> getActivesheet();
$sheet -> setCellValue("A2","宗谷"); //
$sheet -> setCellValue("A3","オホーツク"); //
$sheet -> setCellValue("A4","上川"); //
$sheet -> setCellValue("A5","留萌"); //
//行で指定できる
$style_r = $sheet -> getStyle("A2");
$style_r -> getAlignment() -> setHorizontal(Alignment::HORIZONTAL_DISTRIBUTED);
$sheet -> duplicateStyle($style_r,"A3:A5"); //他の行にも適用
//列で指定することもできる
$v_range = $sheet -> rangeToArray("A2:A5");
$h_range = array_column($v_range,0);
$sheet -> fromArray($h_range,NULL,"B1");
$style_c = $sheet -> getStyle("B1");
$style_c -> getAlignment() -> setTextRotation(-165);
$sheet -> duplicateStyle($style_c,"B1:E1"); //他の列にも適用
//対角線で指定するとブロックで指定されてしまう
$style_b = $sheet -> getStyle("A1");
$style_b -> getBorders() -> setDiagonalDirection(2) -> getDiagonal() -> setBorderStyle(Border::BORDER_THIN);
$sheet -> duplicateStyle($style_b,"B2:E5"); //ブロック単位の適用になる
$Xw = new XWriter($spread);
$Xw -> save($_SERVER['DOCUMENT_ROOT']."/doc4.xlsx");
３１：ExcelファイルをPDF出力する（+日本語出力への対応）
実はPhpSpreadsheetで出力できるものはExcelファイルだけじゃなく、csvやpdfにも対応しています。ところが、pdfファイルを日本語出力しようしても、なかなかうまく行かず苦戦している人も多いようですが、次の方法で対応できます。注意点としては、今まで使用していなかったWriterAPIのIOFactory属性とTCPDFを使用します（WriterAPIではフォント指定できません）。
また、使用できるフォントは限られています。
※tcPDFを事前にダウンロードして、日本語にも対応させておいてください。
require_once("{$_SERVER['DOCUMENT_ROOT']}/tcpdf/tcpdf.php");
use PhpOffice\PhpSpreadsheet\Reader\Xlsx as XReader;
use PhpOffice\PhpSpreadsheet\Writer\Pdf\Tcpdf as Tcpdf;
use PhpOffice\PhpSpreadsheet\IOFactory as IOF;
$xr = new XReader();
$spread = $xr -> load($_SERVER['DOCUMENT_ROOT']."/hogehoge.xlsx");
…中略…
$Xw = IOF::createWriter($spread,'Tcpdf'); //IOFactoryで作成する
$Xw -> setFont("kozgopromedium"); //作成したオブジェクトにフォント設定をする(小塚ゴシック)
$Xw -> save("hogehoge.pdf"); //保存する
３０章のサンプルをPDF化してみました。体裁はまだ整える必要はありますが、日本語もしっかり表示されています。
- 参考にしたサイト
PHPでxlsxファイルをPDFに変換するならLibreOfficeしかないな
３２： 関数を埋め込んだ空白セルを読み込み、書き込みを行う
Excelファイルから関数を埋め込んだだけの空白セルを読み込むと次のようなエラーが出ます。
Notice: Undefined index: A in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/LookupRef.php on line 624
これはインデックスを読み取れないというエラーなので、これをエラーコードからディレクトリを追跡して、無理矢理Phpspreadsheetのソースを書き換えて対応します。
//624行目をこのように書き換える
$tmp_value = (isset($arrayValues[$columnNum]))?$arrayValues[$columnNum]: NULL;
return $tmp_value;
//return $arrayValues[$columnNum]; //こっちをコメントアウト
こうすれば、関数を埋め込んだ部分を読み込んでもエラーが出なくなり、出力時にも予め埋め込んだ関数がそのまま適用できます。
ただし、既存のExcelから読み取る場合はメモリをけっこう消費するので、このケースに対応させる場合はほどほどの行数にしておきましょう。書き込む方は数千行単位でも大丈夫だったので、関数を直接書き込む（8章参照）方がいいです。
３３： 複数の結合セルを再結合する（unmergeCellsメソッドの正しい使い方）
たとえば、読み込んだファイルに対しA1とA2が結合されてあり、B1とB2が結合されているとします。これをA1からB2まで再結合して出力したい場合、このような書き方が思いつくはずです。
$sheet -> mergeCells("A1:B2");
$sheet -> unmergeCells("A1:B2");
$sheet -> mergeCells("A1:B2");
$sheet -> unmergeCells("A1:A2");
$sheet -> unmergeCells("B1:B2");
$sheet -> mergeCells("A1:B2");
上記の方法はいずれも失敗します。case1はファイル処理できますが、いざファイルを開いたときにデータ破損と表示され、修復すると結合処理はリセットされます。case2とcase3に至ってはエラーメッセージが表示され、処理が止まってしまいます。
では、どのようにするかというと、このunmergeCellsメソッドはかなり曲者であり読み込んだ時点で結合処理されているセルを解除したい場合、事前にmergeCellsメソッドで結合範囲をなぞって結合を解除させるセルを記憶させないといけないようです。
したがって、A1からB2までを再結合する場合は、お互いA列とB列を結合、解除してから、再結合を行う必要があります。
//A列の結合解除
$sheet -> mergeCells("A1:A2"); //既に指定した結合セルをなぞって、メソッドに解除させるセルを記憶させる。
$sheet -> unmergeCells("A1:A2"); //結合を解除
//B列の結合解除
$sheet -> mergeCells("B1:B2"); //既に指定した結合セルをなぞって、メソッドに解除させるセルを記憶させる。
$sheet -> unmergeCells("B1:B2"); //結合を解除
//A列と列の再結合
$sheet -> mergeCells("A1:B2"); //これで再結合できる
unmergeCellsメソッドの正しい使用方法を探してみたところ、公式ソースの113行目にunmergeCellsメソッドを使用している場面がありますが、この直前に同じ範囲を結合している動作があり、これをヒントにテストしたところ、ようやく使用方法がわかりました。
３４：連動プルダウンリストを作る
２２章では入力規則設定やプルダウン（ドロップダウン）リストの方法を解説していますが、それに下記リンクにあるようなリスト名定義の方法と合わせると、次のような連動プルダウンを作ることができます。その前に、まず、Excelで連動プルダウンの作り方を知っておいた方がいいので、どのように作るか知りたい方はこちらを参照してください。
それを踏まえた上での作成プロセスは以下の手順になります。
- ①データを読み込み、リストを範囲指定し、名称を定義していく。
- ②定義した名称をリスト化する。
- ③先ほど作成した定義リストに対し、INDIRECT関数でデータを連動させる。
つまり、EXCELで連動プルダウンを作成する方法をそのままPhpSpreadsheetの関数に置き換えただけです。
なお、データを読み込むonsen.xlsxにはA1:E6のデータ部分は初めから記載されています。もし、プログラムから出力する場合はfromArray()メソッドなどで一度データをExcelのシートに貼り付けてください。
use PhpOffice\PhpSpreadsheet\Reader\Xlsx as XReader;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx as XWriter;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation as vali; //バリデーションの設定
use PhpOffice\PhpSpreadsheet\NamedRange as Range; //範囲選択の設定
$Xr = new XReader();
$spread = $Xr -> load("onsen.xlsx");
$sheet = $spread -> getActivesheet();
$ar_rowsdata = $sheet -> rangeToArray("A1:E1"); //リスト名称の範囲
//リスト作成
$rowaddr = "A";
foreach($ar_rowdata as $rowdata){
$spread -> addNamedRange(new Range($rowdata,$sheet,"{$rowaddr}2:{$rowaddr}6"));
$rowaddr++;
}
//プルダウンの設定
$data = "\"".implode(",",$ar_rowdata)."\"";
$validation = $sheet -> getCell('H1') -> getDataValidation();
$validation -> setType(vali::TYPE_LIST); //リスト指定したい場合
$validation -> setShowDropDown(true); //ドロップダウン方式にする
$validation -> setFormula1($data); //ルール(入力許可される値)
//連動させるプルダウン
$validation2 = $sheet -> getCell('H2') -> getDataValidation();
$validation2 -> setType(vali::TYPE_LIST); //リスト指定したい場合
$validation2 -> setShowDropDown(true); //ドロップダウン方式にする
$validation2 -> setFormula1("=INDIRECT(H1)"); //ルール(入力許可される値)
$Xw = new XWriter($spread);
$Xw -> save("xls/bind.xlsx");
では、これは何をしているのかを解説していきます。
①リストに対し、名称を定義
リストを作成して、リスト名を定義している部分がこの部分になります。NamedRangeAPIを用いてそれぞれリストを定義し、addNameRange()メソッドによって、新しいリストを追加していきます（繰り返し動作になるので工程をループ化しました）。なお、リスト作成の方法は以下のリンクに詳しい説明が載っています。
ここで工程の肝となるNamedRangeAPIの定義は以下のようになります
new NamedRange("定義名",対象のシート,データ範囲)
use PhpOffice\PhpSpreadsheet\NamedRange as Range;
…中略
$rowaddr = "A";
foreach($ar_rowdata as $rowdata){
$spread -> addNamedRange(new Range($rowdata,$sheet,"{$rowaddr}2:{$rowaddr}6")); //リスト名定義。2行目～6行目がリスト範囲になる
$rowaddr++; //8章で触れた通り、アドレス列はインクリメント可能
}
②リストをプルダウン化する
２２章の記述ルール設定のときに軽く触れましたが、プルダウン化は以下の部分になります。
$ar_rowdata = $sheet -> rangeToArray("A1:E1");
/*中略*/
$data = "\"".implode(",",$ar_rowdata)."\""; //指定形式の文字列にする
$validation = $sheet -> getCell('H1') -> getDataValidation();
$validation -> setType(validation::TYPE_LIST); //リスト指定したい場合
$validation -> setShowDropDown(true); //ドロップダウン方式にする
$validation -> setFormula1($data); //ルール(入力許可される値)
ここでかなり苦戦したのがsetFormula1()メソッドへの配列の代入ですが、以下のような形式でないと受け付けてくれず、Excel出力時にエラーとなります。
"要素1,要素2,要素3,…"
つまり、PHPだと文字列を作成する場合ダブルクォートは文字列に対してのデリミタとして認識されますが、setFormula1()メソッドに代入する場合は、そのデリミタ（ダブルクォート）も付与しないといけないわけです（Excelの文字列記述ルールに則っているわけなので、落ち着いて考えれば理屈はわかりますが）。そして、連動させる配列の値は、先程定義したリスト名称になります。
追記、もっと楽な方法がありました。シート上にリストが存在する場合、setFormula1(アドレス範囲)とすれば、面倒な配列への代入が不要になります。
$validation -> setFormula1("A1:E1"); //ルール(入力許可されるアドレス範囲)
③リストを連動させる
リストを連動させるにはExcelのINDIRECT(アドレス)関数を使用します。なので、アドレスにリスト名が表示されると、そのリストに従って、プルダウンメニューの値が連動されることになります。ですが、工程2との違いは、最後のsetFormula1()メソッドに代入する値だけで、そこに上記のINDIRECT関数を埋め込むだけです。
$validation2 = $sheet -> getCell('H2') -> getDataValidation(); //ここに連動プルダウンを設定
$validation2 -> setType(validation::TYPE_LIST); //リスト指定したい場合
$validation2 -> setShowDropDown(true); //プルダウン方式にする
$validation2 -> setFormula1("=INDIRECT(H1)"); //ルール(入力許可される値)
なお、INDIRECT関数は直接アドレスを参照するExcelの関数であり、ここではH1に入力された値を見ることになります。しかし、それがH2の記述ルールに設定されているので、H1で選択された値と定義してきたリスト名称が一致しているため、H2には個々で定義されたプルダウンメニューが表示されることになります。
３５位置情報を取得して、オブジェクトを制御してみようとした（仮）
これは、他の章とは異なり、あくまでテスト分析となります。目的はExcelの任意のセルに対し、位置情報を取得して、オブジェクトを出力するというものです。ただしPhpSpreadsheetの仕様に以下のものがあります。
- テキストボックスなどの図形オブジェクトは一切サポートされていない
- getRowHeight()で行の高さは取得できない（メソッドが機能していない）
この辺りはGithubあたりで散々質問が投げかけられていますが、誰もきちんと答えられていない上に、オブジェクトのことに関しては公式サイトが「not support」と記載しています。ただ、例外的にコメントオブジェクトと画像オブジェクトだけはサポートされているので、擬似的にコメントをテキストオブジェクトっぽく見せかけてみました。
なお、コメントの制御の仕方は以下のサイトを参考にしています（公式サイトより詳しいです）。
ただ、このサイトにも紹介されていないメソッドがあったので、それも採用しました。
$comment = $sheet -> getComment('B2');
$comment -> getText()-> createTextRun("テキストボックスは全く対応していないので、コメントをそれらしく見せて代用");
$comment -> getText()-> createTextRun("\r\n");
$comment -> getText()-> createTextRun("\r\n");
$comment -> getText()-> createTextRun("しょうがない。");
$comment -> setVisible(true);
$comment -> setWidth("300px"); //コメント幅
$comment -> setheight("100px"); //コメント高
$comment -> setMarginTop($mg_top); //コメントの位置関係、絶対参照で上から
$comment -> setMarginLeft($mg_left); //コメントの位置関係、絶対参照で左から
$comment ->getFillColor() -> setRGB('FFFFFF'); //コメントの背景を白くする
$comment -> setAlignment(Align::HORIZONTAL_RIGHT); //位置関係の調整
$Xw = new XWriter($spread);
$Xw -> save("xls/comment.xlsx");
さて、ここで本題になるのがsetMarginTopとsetMarginLeftに指定する値で、これを自動取得しようとしてみたのですが、これがかなり大変でした。厄介なのが、getWidthメソッドは正しく値を取得できない、Excelではポイントで取得しているが、それをsetWidthで指定する値に換算しないといけない、そしてwidthとheightではポイントの単位が異なっている、この3点に注意しないといけません。
$sheet -> setCellValue("A1","コメントテスト");
$comment = $sheet -> getComment('B2');
$width = calc_width("A",$sheet);
$width += calc_width("B",$sheet);
function calc_width($rowaddr,$sheet){
$dim = $sheet -> getColumnDimension($rowaddr);
$dim -> setAutoSize(true);
$sheet -> calculateColumnWidths();
$dim -> setAutoSize(false);
$width = $dim -> getWidth();
$width = $width * 1.7;
$width = ($width == -1 || $width < 7.8 )? 7.8 : $width * 1.1;
$dim -> setWidth($width);
$width = $width * 3.8;
return $width;
}
$mg_left = $width * 2;
$mg_top = 30 * 1 + 0.1;
セル幅自動取得は１９章でも触れたのですが、無記入のセルの場合、値は（-1）を返してしまいます。これは自動制御を意味するそうですが、それだと幅の合計計算ができないので、無記入の場合は、デフォルトの幅を取得するようにします。
そして、セル幅を設定した後で、その幅に対し、3.8を掛けていますが、ここがsetWidth()とsetMarginLeft()の差分調整になります。setMargin系のメソッドは引数の指定値がピクセルなので、これを施さないと明らかにおかしな場所にコメントが表示されてしまいます。
そして、もっと厄介なのが高さですが、前述した通りsetRowHeightメソッドは仕様上のバグにより値を取得できません。なので、最初は幅を取得してから１文字あたりの幅を計算して、それを高さの基準にしてみたのですが、widthとheightは同じ数値でも、基準単位が違うため、大きくずれます。なので、決め打ちで書いた方がいいでしょう（11ピクセルを30として、その比率で計算してみてもいいかも知れませんが、そこに労力を使う必要はないとは思います）。
補 :こんなメッセージが出たら…
開発中にこのようなメッセージに頻繁に遭遇すると思います。
Warning: unlink ...Resource temporarily unavailable in ...
これが出る原因は、保存しようとしているファイルを開きっぱなしにしている場合です。これをtry&catchできる方法は現在探索中です。
推奨するWEBサーバについて
WebサーバですがApacheの使用を推奨します。単純操作だと問題ないですが、複雑で過重な操作にNginxを用いるとリソースを圧迫してしまい、処理のループに限界（メモリ解放でも追いつけませんでした）があります。あと、別ページにて考察しているOpenLiteSpeedもlibzipに対応していないため書き込みができません。
また、操作のためにこの記事を知っておいてください。これを知っていると知っていないで、システムの能率が全く違います。