Help us understand the problem. What is going on with this article?

PhpSpreadsheetのTIPSあれこれ

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をインストールする

これでいちおうcomposerからのrequireコマンドは受け付けるようになるのですが、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もインストール必須になります。

このページを参考にして、エラーメッセージから対象ファイルの行を追跡し、該当箇所に書き加えると使用できるようになります。

当ページのルールです

目を通しておくと、読み解きやすくなります。

PHP
use PhpOffice\PhpSpreadsheet\Spreadsheet as Spread; //公式APIは別名を定義して使っています
$spread = new Spread(); //スプレッドシート(ブック)は$spreadと略しています
$sheet = $spread -> getActivesheet(); //ワークシートは$sheetと略しています

1:任意のセルに対し、上寄せ、左寄せなどを行う(+エイリアスの別名設定)

公式APIの一つ、AlignmentAPIから色々設定できます(あえてインスタンスを作らなくても直接スコープできます)。また、名前空間のエイリアスは別名定義を指定できるので、別名を作っておくと冗長な記述を省略できます(PHPの名前空間を参照)。
※外部ファイルで使う場合はインスタンスを作成してください。エイリアスはそのページでしか使えません。

PHPSpreadsheet公式のAlignment一覧

PHP
 use PhpOffice\PhpSpreadsheet\Style\Alignment as Alignment; //Alignmentのエイリアスを別名で再定義

//左右を制御する(HORIZONTAL …水平のこと)
$sheet -> getStyle("A1") -> getAlignment() -> setHorizontal(Alignment::HORIZONTAL_LEFT); //左寄せ
$sheet -> getStyle("A2") -> getAlignment() -> setHorizontal(Alignment::HORIZONTAL_CENTER); //中央寄せ
$sheet -> getStyle("A3") -> getAlignment() -> setHorizontal(Alignment::HORIZONTAL_RIGHT); //右寄せ
$sheet -> getStyle("A4") -> getAlignment() -> setHorizontal(Alignment::HORIZONTAL_DISTRIBUTED); //均等割付

//上下を制御する(VERTICAL …垂直のこと。中央寄せはMIDDLEじゃないので注意してください)
$sheet -> getStyle("B1") -> getAlignment() -> setVertical(Alignment::VERTICAL_TOP); //上寄せ
$sheet -> getStyle("B2") -> getAlignment() -> setVertical(Alignment::VERTICAL_CENTER); //中央寄せ
$sheet -> getStyle("B3") -> getAlignment() -> setVertical(Alignment::VERTICAL_BOTTOM); //下寄せ
$sheet -> getStyle("B4") -> getAlignment() -> setHorizontal(Alignment::VERTICAL_DISTRIBUTED); //均等割付

$align = new Alignment(); //インスタンスを作成(インスタンス名は任意)
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); //均等割付

文字寄せは結合したセルでも対応可能ですが、その際開始位置のセルアドレスを指定してください。

PHP
$sheet -> mergeCells("B1:C5"); //指定範囲のセル結合
$sheet -> getStyle("B1") -> getAlignment() -> setVertical(Alignment::VERTICAL_TOP); //開始アドレスと合わせると無難です。

2:セル内に対し、自動改行、縮小、回転、縦文字、インデントなどを制御する

getAlignment()メソッドから色々設定できます。

イ:自動改行

setWrapText(bool)メソッドのプロパティをtrueに設定することで、対応できます。もし、幅を広くしたい場合は最初から列幅を決めてください(19章に自動列幅指定の方法も載せています)。

PHP
//デフォルトのセル幅に対し、文字列を自動改行して表示させます。
$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にします。

PHP
$sheet -> getStyle("A1") -> getAlignment() -> setShrinkToFit(true); //縮小して表示
$sheet -> getStyle("B1") -> getAlignment() -> setShrinkToFit(false); //縮小して表示を解除

ハ:テキストを回転表示

setTextRotation(n)メソッドを使用します。nは任意の変数で、-90を下限、90を上限に、15の倍数にしないと例外エラーが発生することがあります。

PHP
$sheet -> getStyle("A1") -> getAlignment() -> setTextRotation(30); // nは15の倍数にしないと例外エラーが発生することがある

ニ:縦文字(縦書き)

縦書きも、setTextRotation(n)メソッドを使用し、xlsならnに255、xlsxならnに-165を設定することで有効になるという特殊な仕様が仕組まれています。かなだけの場合なら文字と改行コードを組み合わせるだけでもできますが、長音を対応させるにはこれしかないです。

PHP
$sheet -> setCellValue("A1","オホーツク"); //長音の場合は改行だけでは対応できない
$sheet -> getStyle("A1") -> getAlignment() -> setTextRotation(-165); //縦書きになる

公式ページのAlignment.phpファイルソースの282行目、setTextRotation()メソッドのプログラムに意味深な数値が設定されており、これを打ち込んでみたら案の定でしたが、なぜ255と-165なのかはわかりません。

Alignment.php
// 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)で指定できます。

PHP
$sheet -> getStyle("A1") -> getAlignment() -> setIndent(1); //1文字分インデントがかかる

※結合したセルに対してAlignmentを制御する場合

結合したセルを制御する場合は結合したアドレス範囲を指定する必要があります。

PHP
$sheet -> mergeCells("B2:D4"); //結合したい範囲を指定
$sheet -> getStyle("B2:D4") -> getAlingnment() -> .... ; //範囲を合わせてから設定する

3:特定のセルに固定しておく

freezePane()メソッドを使うことで、特定のセルを固定しておくことができます(要はExcel機能にあるウィンドウの固定と同じ働きなので、必ず行列いずれかのマージンを空けておいてください)。また、unfreezePane()メソッドを利用することで、セルの固定を解除できるほか、指定のセル位置にカーソルを合わせたまま保存することもできます。

PHP
//現在のシートに対し、A列と1行を固定しておく場合
$spread -> getActiveSheet() -> freezePane('B2'); 

※注意
必ず真上あるいは真左に最低1つマージンをとってください。ファイル読込時に破損し、Excelファイルから以下のメッセージが表示されることになります。

  • 開いた直後… hogehoge.xlsxには読み取れない内容が含まれています。このブックの内容を回復しますか?ブックの発行元が信頼できる場合は、[はい]をクリックしてください。

  • 読込後…ファイル レベルの検証と修復が完了しました。このブックの一部が修復されたか、破棄された可能性があります。削除された機能: /xl/worksheets/sheet1.xml パーツ内のビュー

この質問文の回答を読むと、マージンをあけろとありますね。
phpexcel freeze row and columns

4:ファイル書き込み前に、セル位置を先頭に設定しておく 20200616改訂

以前はファイル固定のメソッドを使う方法を説明したのですが、これだと複数シートの場合に対応していなかったので、もっとリスクが少なく簡単な方法が公式ページに記載してあり、setActiveSheetIndex(n)(nはシート番号)かsetActiveSheetIndexByName('シート名')で簡単に設定できます。ただ、これだとセル位置までは元通りにならないので、その場合は上記に挙げたunmergeCells('アドレス名')で指定するといいでしょう。

PHP
//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'); //書き込んだ名前を保存

Worksheets

5:データの最終行、最終列を取得する

$spreadオブジェクトから以下のメソッドを実行することで、任意の変数に格納できます。これを使うと動的な範囲の取得が楽になります。

PHP
$sheet = $spread -> getActiveSheet(); //処理したいシートを取得
$max_row = $sheet -> getHighestRow(); //最終行(最下段)の取得
$max_col = $sheet -> getHighestColumn(); //最終列(右端)の取得
$maxCellAddress = $max_col.$max_row; //最終セルのアドレスを格納する変数
$ary_data = $sheet -> rangeToArray("B2:{$maxCellAddress}"); //指定の位置から最終セルまで取得する

ただし、これだとファイルから最終列がうまく取得できないことが多いので、ループをかけて文字データが取得できなくなったタイミングを指定する方法がおすすめです。

PHP
$max_row_tmp = $sheet -> getHighestRow(); //暫定的な最下段の列アドレス
while($sheet -> getCell("A{$max_row_tmp}") -> getValue() == NULL) $max_row_tmp--; //値が取得できるまで、デクリメントする

6:数字の文字列が指数化される場合の対応

数値は12桁までしか正常に表示されないのは、PhpSpreadsheetの標準仕様だそうです。ですので、設定を手動で変えてやる必要があるのですが、セル範囲に対し文字列と認識させるだけで、桁数を考慮する必要もなく、ゼロが先頭になった場合でもゼロパディング処理の必要もなく、しっかりと表示させることができます(ただし、14桁までしか正しく表示できないようで、15桁以上になると下4桁が勝手に丸められてしまいます)。

PHP
$sheet = $spread -> getActiveSheet(); //処理したいシートを取得
$max_row = $sheet -> getHighestRow(); //最下段
$sheet -> getStyle("A1:D{$max_row}") -> getNumberFormat() -> setFormatCode('0'); //文字列と認識させる

7: データをまるごと書き込む

データの書き込みはPHPExcelよりかなり便利になっておりfromArray()メソッドを使うと、一度にオブジェクト化された配列データを書き込むことができます。また、引数を指定することによって、開始位置のアドレスなどを指定することができます。

PHP
$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と記述すると、数値以外の文字が出力されなくなるので、特定の行を集計したい場合などには使えるかも知れません。例で言うと、行政区数における藤沢市の値が空白となります)。

8: ループ式を使ってデータを挿入する(アルファベット列のインクリメントと関数の埋め込み)

たとえば、最終行にそれぞれ合計値を出す関数を埋め込みたいとします。行アドレスは数字(1,2,3…)なので、普通にインクリメントすればいいですが、列(A,B,C…)はどう処理すればいいでしょうか?

実は、++はPHPの標準仕様として英文字に対応しているため、同様に列アドレスも普通にインクリメント可能です(Coordinateでインデックスを割り出す必要ありません)。

また、シートに関数を埋め込みたい場合はExcelと同様、=を付けることで関数と認識させることができます。

PHP
$limitRow = $sheet -> getHighestRow(); //集計を行う最終行
$sumRow = $limitRow + 1; //集計を出力する行
$limitCol = $sheet -> getHighestColumn(); //最終列
$currentCol = "B"; //開始列
//最終列に達するまで関数を埋め込む
while( $currentCol != $limitCol){
    //合計式を埋め込む(集計の最終行と集計関数を出力する行を一緒にしないように!)
  $sheet
    -> setCellValue("{$currentCol}{$sumRow}","=counta({$currentCol}2:{$currentCol}{$limitRow})");
  $currentCol++; //列アドレスも数値と同じようにそのままインクリメント可能
}

9: Excelファイルのテンプレートを複写して使う

テンプレートとして既存のxlsxを使うには、setReadDataOnly(false)としてからファイルを開くのですが、そのままだと既存のテンプレートに上書きされてしまいます。それを回避するためにはcloneという便利なオブジェクトがあります。

PHP
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を使った場合は上書き保存せずに、別ファイル保存した方がいい場合が多数発生します(いずれ、この部分も体系的に分析したいところです)。

10: 行や列を削除する

任意のシートから対象の行や列を削除することができます。文法は以下の通りです。また、列の場合はインデックスで指定する方法もあります。第二引数は省略可なので、単行や単列を処理したい場合は第二引数は空白にしてください。

  • removeRow(削除したい先頭の行番号, 削除したい行数);
  • removeColumn(削除したい先頭の列記号,削除したい列数);
  • removeColumnByIndex(削除したい先頭の列番号,削除したい列数)

【警告】結合したセルを行や列削除したりするとデータが壊れます。

PHP
$sheet = $spread -> getActivesheet();
$sheet -> removeRow(10,5);  //対象シートの10行めを先頭に、5行分削除する
$sheet -> removeCoulumnByIndex(3,4); //対象シートの3列(C)から4列削除する

11: 複数にまたがるシート内のデータ取得

getActiveSheet()だと現在選択しているシートしか取得できませんが、getSheet($i)だと任意のシートを取得することができます。それとシート数を数えるgetSheetCount()を活用した方法。

PHP
$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にデータがシート順に代入されていきます。

12: Excelのセルに埋め込んだ数式を反映させる

rangeToArray()メソッドを使えば、ワークシートの指定したエリアを取得することができますが、何も指定しないままだと、数式を埋め込んだ場合、そのまま読み込んでしまいます。そこで、それを制御する便利な引数があります。

  • 第1引数:読み込みたいセルの範囲
  • 第2引数:空白セルの場合に表示させる値
  • 第3引数:数式を反映させるかどうかの判定(TRUEではい,FALSEでいいえ)
  • 第4引数:Excelの各セルに設定したフォーマットを反映させるかどうかの判定(TRUEではい、FALSEでいいえ)。なお、フォーマットを反映させてしまうと、日付を読み込むとき海外スタイルdd/mm/yyyyになってしまうので、FALSEにした方がいいような気がします。
  • 第5引数:セルのアドレス読み取り式をインデックス形式にするか?(TRUEでインデックス、FALSEでアドレス)
PHP
$ar_contents = $sheet -> rangeToArray("A1:E30",NULL,TRUE,TRUE,FALSE,FALSE);

ところが、このままだと、数式を埋め込んだ場合に値が反映されないとundefined index XXというエラーが表示されてしまうので、これを制御するためにエラー制御演算子@を埋め込みます(実はインスタンスにも使えます)。

PHP
$ar_contents = @$sheet -> rangeToArray("A1:E30",NULL,TRUE,TRUE,FALSE,FALSE);

こうすれば、数式の計算結果のまま読み込むことができ、その計算結果が空白の場合でもエラーを表示させることありません。また、toArray()関数でも使用できます(参考サイト参照、その場合は引数は4つです)。

参考にしたサイト

※エラーが解消されない場合は32章を参照してください。

13:シートを解除/保護してファイル出力する

保護されたシートをテンプレートにすると、出力されたファイルのセルは保護されたままとなり、都合が悪いことがあります。それを解除するメソッドにgetProtectionがあります。

PHP
$sheet -> fromArray($data); //$dataは任意の値
$sheet -> getProtection() -> setSheet(false);//保護を解除してくれます。

逆にシートを保護して出力することもできます。setPassword()メソッドを使えば、任意のパスワードも設定できます。
また、任意の場所だけ保護する場合はgetStyleメソッドを適用します。

PHP
$sheet -> fromArray($data); //$dataは任意の値
$sheet -> getProtection() -> setSheet(true) -> setPassword('xxxx'); //xxxxが解除用のパスワードとなります。
$sheet -> getStyle('A1:D4') -> getProtection() -> setSheet(true); //A1~D4が保護対象になります。

14:行や列を非表示にする

よく、計算式部分など、表示させておきたくない行や列があると思いますが、それを非表示にするメソッドがsetVisible(false)です。

PHP
$sheet -> getColumnDimension('D') -> setVisible(false); //非表示にしたい列
$sheet -> getRowDimension('5') -> setVisible(false); //非表示にしておきたい行

こうしておけば、出力されたExcelにはD列と5行目が非表示になります。逆にsetVisible(true)にすれば、非表示になっている行や列を再表示させることもできます。

15:印刷範囲を調整する

setPrintArea()メソッドを使用すると、印刷範囲を設定できます。ファイルを呼び出して、複写して保存したりすると本来想定していた印刷範囲がずれたりすることがありますが、それを再調整する便利なメソッドがあります。

PHP
$sheet -> getPageSetup() -> setPrintArea('A:Z'); //AからZまで印刷する場合
$sheet -> getPageSetup() -> setPrintArea('A1:Z30'); //A1からZ30まで印刷する場合

ほかにも色々できるみたいで、マージン、ヘッダ、フッタなどを設定できます。また、WorksheetAPIを使用すれば印刷の向き、印刷の用紙サイズなども設定できます。

イ:印刷の倍率

倍率を指定するにはsetScale(n)メソッドを使用します。

PHP
$sheet -> getPageSetup() -> setScale(80); // 80%になる

ロ:印刷の向き

印刷の向きはWorksheetAPIのPageSetup()属性から設定できます。

PHP
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup as PageSetup;
$sheet -> getPageSetup() -> setOrientation(PageSetup::ORIENTATION_PORTRAIT); //縦向き
$sheet -> getPageSetup() -> setOrientation(PageSetup::ORIENTATION_LANDSCAPE); //横向き

ハ:用紙サイズ

用紙サイズはWorksheetAPIのPageSetup()属性から設定できます。例ではA4ですが、他にも色々用意されています。

PHP
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup as PageSetup;
$sheet -> getPageSetup() -> setPaperSize(PageSetup::PAPERSIZE_A4); //A4サイズ
  • 公式のAPI

PageSetup

ニ:センタリングの解除

印刷プレビューのセンタリングを解除するにはそれぞれ、setVerticalCentered(bool)メソッド、setHorizontalCentered(bool)メソッドを使用します。

PHP
$sheet -> getPageSetup() -> setVerticalCentered(false); //上下の中央揃え解除
$sheet -> getPageSetup() -> setHorizontalCentered(false); //左右の中央揃え解除

ホ:マージン指定

印刷のマージンはsetPageMargins()メソッド内から制御します。注意点として数値はインチ指定で入力して下さい。

PHP
$sheet -> getPageMargins() -> setTop(1); //上
$sheet -> getPageMargins() -> setBottom(1); //下
$sheet -> getPageMargins() -> setLeft(1); //左
$sheet -> getPageMargins() -> setRight(1); //右
$sheet -> getPageMargins() -> setHeader(1); //ヘッダ
$sheet -> getPageMargins() -> setFooter(1); //フッタ

ヘ:ページ送り

一定の行でページ送りを行うにはWorksheetAPIとsetBreak()メソッドを使用します。注意点として、一度このメソッドで設定してしまうと、再設定しないとファイルに設定が適用されたままとなります。

PHP
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet as Worksheet;
$sheet -> setBreak("A20",Worksheet::BREAK_ROW); //行で送る
$sheet -> setBreak("D1",Worksheet::BREAK_COLUMN); //列で送る

ト:セル枠、印刷範囲のグリッドの表示、非表示

印刷時にExcelのセル枠を非表示にするにはsetShowGridlines(bool)メソッドで制御し、setShowGridlines(false)で制御可能です。

PHP
$sheet -> setShowGridlines(false); //印刷時にセル枠が非表示になる(ボーダー指定は対象外)

また、setPringGrilines(true);で印刷範囲のグリッドを表示できます(Excelの仕様と同様、一度印刷プレビューに行かないと表示されないようです)。

チ:繰り返し行(見出し)を設定する

見出しを設定して、繰り返し行を設定する場合はsetRowsToRepeatAtTopByStartAndEnd(開始行,終了行)を使用します。

PHP
$sheet -> getPageSetup()-> setRowsToRepeatAtTopByStartAndEnd(1, 2); //1行目を先頭に2行目までが見出しとして繰り返される

リ:ヘッダ、フッタにタイトルやページ番号を設定する

ヘッダ、フッタを設定するにはgetHeaderFooter()メソッドを活用します。また、&の記号を用いた各種コードを使用することで、さまざまな設定ができます。

PHP
$sheet -> getHeaderFooter()-> setOddHeader("&C これはタイトルです"); //&Cはセンタリング
$sheet -> getHeaderFooter()-> setOddFooter("&P/&N"); //&Nは全ページ数、&Pは現在ページの付与

Setting the print header and footer of a worksheet

16:テキストの一部分に特定フォント(太字、色文字、サイズ変更など)を用いる

任意のセルに対して、全文字のスタイルを変更するのは簡単です。ところが、これが一部分への使用となると、かなり情報が少なく苦労しましたが、RichTextAPIを使って設定できます。流れとしてはRichTextAPIで特定のフォントを埋め込んだ文字列をcreateTextRun()メソッドで、そうでない文字列をcreateText()メソッドで作り、それをセルに反映させるという行程になります。

実はこの2つのメソッド、文字列の追記ができます。

PHP
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()メソッドのオブジェクト変数を作成するといいでしょう。

PHP
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("ここを");
$specialFont = $RichText7 -> createTextRun("太字") -> getFont() -> setBold(true); //太字制御
$RichText7 -> createText("に、ここも");
$RichText7 -> createTextRun("また太字") -> getFont() -> setBold(true); //太字制御
$RichText7 -> createText("にできます");
$sheet -> getCell("A7") -> setValue($RichText7); 

richtext.jpg

17:シートの表示ズームサイズを調節する

setZoomScale(n)というメソッドがあり、それを使います。EXCELと同様10~400まで変更が可能です。また、シート単位で設定できます。

PHP
$Sheet -> getSheetVlew() -> setZoomScale(70); //ズームを70%にする

18:シートに画像を貼り付ける

画像を貼り付けるにはDrawingAPIを使用します。ですが、注意しなければいけないのは、仕様なのかバグなのかわかりませんが、複写する際は、各データを入力する前に画像を貼って下さい。文字を入力した後で画像を貼るとエラーが起きることがあります。

PHP
    use PhpOffice\PhpSpreadsheet\Worksheet\Drawing as Draw; //Drawing属性を使用
    $Draw = new  Draw(); //インスタンスの作成
    $Draw -> setPath('画像のパス'); //画像のパス
    $Draw -> setCoordinates("B2"); //画像貼り付けの場所。例ではB2
    $Draw -> setResizeProportional(true); //画像をリサイズするかどうか
    $Draw -> setWidth(400); //画像サイズ
    //引き続きデータを入力する
    $sheet -> setCellValue("A1","写真タイトル");

19:日本語入力したセル列幅を自動リサイズする(20200421修正)

このPHPExcelでのページを参考に色々テストを重ねてみました。ポイントは予めsetAutoSize(true)を使用する必要があり、Windows(Xamppで検証)ならそれだけで行けるみたいですが、Linux(ceotOSで検証)で列を指定する場合は再度setAutoSize(false)で値をリセットし、一定サイズを掛ける作業が必須のようです。

なお、単純にsetAutoSize(true)だけではフォントの文字幅を読み取れないので意味がなく、公式ページを読む限り、あくまでこのメソッドは幅のリサイズを有効にする命令を与えるもののようで、半角英数字でも値がずれたりします。なので、実際に列幅をカウントして、その値を取得してから明示的に指定する必要があります。

それから、(1)~(7)の順番厳守でこの順番以外だと列幅は-1しか返しません(つまり、値は無効)。また、2バイト文字に対応させるために幅に1.7から2.0を掛けておくとよさそうです(自動調整とほぼほぼ変わりませんでした)。

PHP
$sheet -> setCellValue("A1","鹿児島県志布志市志布志町志布志"); //文字を設定 (1)
$dim = $sheet -> getColumnDimension("A"); //対象行のオブジェクト作成 (2)
$dim -> setAutoSize(true); //リサイズを有効にする (3)
$sheet -> calculateColumnWidths(); //行幅を数える (4)
$dim -> setAutoSize(false); //リサイズの初期化(5)…Windowsの場合は不要かも
$col_width = $dim -> getWidth(); //列幅を取得 (6)
$dim -> setWidth($col_width * 1.7); //列幅を指定。取得した値に1.7を掛けると理想的な幅となる。 (7)

autosize.jpg

※半角文字と全角文字が混淆している場合
もし、長さで半角文字のみが上回っていると、上記のような決め打ちだとマージン幅がおかしくなってしまいます。なので、全角か半角かを判別して、それに適した長さを調節するといいでしょう。

PHP
$col_width = $dim -> getWidth(); //行幅を取得 (6)
$value = $sheet -> getCell("A1") -> getValue(); //対象の値を取得
$margin = (strlen($value) > mb_strlen($value) )? 2:0.98; //全角か半角かで倍率を任意で変更
$dim -> setWidth($col_width * $margin );

20:行や列に値をコピーする

メソッドを隈なく調べてみたのですが、コピーした行を挿入という直接的な記述はないようです。その代わり、範囲を取得、その範囲を貼り付け、もし行を挿入する場合は、新たに行を挿入するというプロセスを経ると同様の操作が可能になります。

PHP
$range = $sheet -> rangeToArray("A1:A10"); //指定の範囲を取得
$sheet -> insertNewColumnBefore("C"); //C行の前に行を新規挿入する
$sheet -> fromArray($range,NULL,"C1"); //指定の位置に貼り付ける

新規挿入しない場合は、既存のセルに上書きされます。

なお、新規に行や列を挿入するメソッドは4種類あります。column(行)なのかrow(列)なのか、そしてbefore(前)で覚えるといいでしょう。また、それぞれ引数を2つ持つことができ、2つめの引数に対象となる行数、列数を指定することができます。

PHP
$sheet -> insertNewColumnBefore("A"); //A行の前に挿入
$sheet -> insertNewRowBefore(1); //1列の前に挿入
$sheet -> insertNewColumnBefore("A",3); //A行の前に3行挿入
$sheet -> insertNewRowBefore(1,4); //1列の前に4列挿入

※行から列を入れ替えてペーストする場合
行から列に入れ替える場合はarray_column関数が役立ちます。

PHP
$v_range = $sheet -> rangeToArray("A2:A11"); //範囲の取得
$h_range = array_column($v_range,0); //インデックス0を基準に配列を組み直すと行列が入れ替わる
$sheet -> fromArray($h_range,NULL,"B1"); //その配列を貼り付けると行列入れ替えで貼り付けと同じになる

※列から行に入れ替えてペーストする場合
列から行に入れ替える場合はarray_chunk関数が役立ちます。

PHP
$h_range = $sheet -> rangeToArray("B1:K1"); //範囲の取得
$v_range = array_chunk($v_range,1); //array_chunkで1個ずつ配列にする
$sheet -> fromArray($v_range,NULL,"A2"); //その配列を貼り付けると列行入れ替えで貼り付けと同じになる

21:ワークシートそのものを挿入、複写、削除、隠蔽する

ワークシートを操作するにはWorksheetAPIを活用します。注意点として複写する場合は同じ名称を使わず、事前に名称を指定しておかないとエラーが出ます。

イ:新規作成のシートを挿入する

新たに指定する場合です。何も指定しない場合はcreateSheet()メソッドだけで作れ、引数を何も指定していない場合は末尾にシートが作成されます。

PHP
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('シート名')によるシート名による指定、いずれも使用可能です。

PHP
$cloned_sheet = clone $spread -> getSheetbyName("newsheet") //複写元のシート
$cloned_sheet -> setTitle("copiedsheet"); //必ず事前にタイトル名を決めておくこと
$spread -> addSheet($cloned_sheet); //2つ目の引数を省略した場合は最後尾に追加される。

ハ:シートを削除する

シートを削除する場合はシート番号を指定します。そのために公式ページでは、getIndex()メソッドを使ってシート番号を取得する方法を推奨しているようです。Excel操作と違って、削除の際の警告は出ませんので、操作は慎重に行ってください。

PHP
$sel_index = $spread->getIndex(  $spread->getSheetByName('newsheet') ); //シート番号を取得
$spread ->removeSheetByIndex($sel_index);

※応用:雛型からデータを複写する

これらのメソッドを駆使すると、同一ブックの複数シートに雛型を複写することができます。ポイントは複製元にデータを挿入しないことです。

PHP
$cloned = clone $spread -> getSheet(0); //シートを複製
for($i = 1;$i <= $max ;$i++ ){
    $newsheet = $cloned -> setTitle("Sheet".$i); //タイトルを付与
    $spread -> addSheet($newsheet); //シートを複製
    //データの処理を行う
    $spread -> getSheet($i) -> fromArray(...); //データを入力していく
}
$spread -> removeSheetByIndex(0); //複製元の雛型を削除する


ニ:シートを非表示・再表示する

ワークシートを非表示にする場合はAPI内のメソッドを活用することになります。

PHP
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet as Worksheet; //worksheet属性を使用

$spread -> getSheet(1) -> setSheetState(Worksheet::SETSTATE_HIDDEN); //任意のシート(例だと2つ目)を非表示にする
$spread -> getSheet(2) -> setSheetState(Worksheet::SETSTATE_VISIBLE); //任意のシート(例だと3つ目)を再表示

※ シートに着色する場合
シートのタブを着色する場合は以下のコマンドで対処します。対応していない色もあるので注意。

PHP
$sheet->getTabColor()->setRGB('FF0000');

22:セルに入力規則を設定する

けっこうややこしい記述法ですが、CellAPI内のDataValidation属性で制御できます。共通の流れとしては

  • validation属性を使用
  • 指定のセルを選択
  • どのタイプを適用するか?(範囲指定型orリスト指定型)
  • どのような表示形式にするか?
  • メッセージ制御(指定しない場合はExcelのデフォルト表示になります)
  • ルール指定

となります。

A:指定範囲内で入力制御する

たとえば、数値を入力する場合0~100までに制限する場合です。

PHP
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を複写します。

PHP
$sheet -> getCell("B2") -> setDataValidation( clone $validation );

B:指定の値で入力制御する

リスト形式にして、「はい、いいえ」で答えてもらう場合です。ドロップダウンリストを使わない指定もできます。なお、分析してみたところ、setAllowBlankメソッドを使用する場合はsetShowErrorMessage()、setShowInputMessage()メソッドの指定もしないと、バリデーション制御ができないようです。

PHP
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

23:オートフィルターを使う

オートフィルターを使うにはsetAutoFilter()メソッドを用います。

A:行すべてに指定する

このようにcalculateWorksheetDimension()メソッドを使用することで、全行に対しオートフィルタを適用することができます。

PHP
$sheet = $spread -> getActivesheet();
$sheet -> setAutoFilter( $sheet -> calculateWorksheetDimension() );

B:部分的に指定する

フィルターを適用したいアドレス範囲を指定するとオートフィルターをかけたい行を指定できます。注意点としてフィルターが表示されるのは指定した先頭行だけですが、以下のように範囲を指定しないと、範囲外の値はフィルタリングされません

PHP
$sheet = $spread -> getActivesheet();
$sheet -> setAutoFilter("A1:C20"); //A1からC20までが適用されるが、A21からの値は適用外となる

C:ルール指定して対象の値だけ表示させる

setAutoFilter()だけだとルール指定ができないので、ルール指定する場合はAutofilterAPIを活用します。なお、この情報は英語サイトにも公式サイト以外全くといっていいほど記載例がないので、本当に動作するか検証を繰り返しています。

イ:完全一致

完全一致でフィルタリングしたい場合は以下のような記述を行います。例ではA行に東京都、横浜市、名古屋市、大阪市、神戸市、広島市、福岡市が入っており、B行に区名(中央区・中区)が入っています。その結果、対象都市すべての中央区(東京都、大阪市、神戸市、福岡市)だけが返されることになります。

PHP
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,"中央区"); //不完全一致の場合
`

xls_autofill.jpg

ロ:部分一致

部分一致条件を適用するには2つの属性を駆使し、ワイルドカード(*)を使って設定します。記述ルールとしてsetFilterType()でフィルターの型を指定してから、createRule()の後にsetRuleType()と指定しないと機能しません。

PHP
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と同じく正規表現は非対応です。

PHP
$column -> createRule() -> setRule(Rule::AUTOFILTER_COLUMN_RULE_EQUAL,'山*'); //~で始まる
$column -> createRule() -> setRule(Rule::AUTOFILTER_COLUMN_RULE_EQUAL,'*山*'); //~を含む
$column -> createRule() -> setRule(Rule::AUTOFILTER_COLUMN_RULE_EQUAL,'*山'); //~で終わる

ハ:未満・以下・以上・超

数値に対して条件を指定するには2つの属性を駆使します。こちらは当然、数値しか効果がありません。

PHP
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)の組み合わせを押さえておけば混同しないでしょう。

PHP
$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内でセル属性を数値としないと、期待通りの操作となりませんので、24章を読んで、事前に数値指定してください。

ニ:複合条件

甲:OR検索

OR条件の場合はデフォルト指定なので、そのまま追記します。以下の記述で30未満または60超の条件が適用されます。

PHP
$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のソースを調査しました)。

PHP
$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%を昇順で取る、ということになります。

PHP
//setRule(方式,範囲とする数値,順序)
setRule(Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT,5,Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP)
PHP
$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_TOPではなくTOPTEN_BY_VALUEになります。後の記述は変わりません。なお、値が少ない順で取得したい場合は、TOPTEN_BOTTOMとなります。例なら下位5件を取る、ということになります。

PHP
$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....

もし、このフィルターを試すとこのエラーが出ると思います。これはこういう理由によるもので、ならばソースコードを編集し、変数を作って代入します。

AutoFilter.php
//ここが原因
return array_pop( array_slice($dataValues, 0, $ruleValue));
//このように書き換えるとnoticeメッセージが出ない
$val = array_slice($dataValues, 0, $ruleValue);
return array_pop($val);

※オートフィルターはかなり複雑なので、公式ページでもかなり詳しく載っています。
AutoFilter Reference

24:セルの書式設定を変更する

23章に絡んでいるのですが、Excelで書式設定を数値にしないとオートフィルターで不具合が発生すると記述しました。では、どうやって書式設定を変えるのかを調査したところCellAPIのDatatype属性が必要になります。そしてsetValueExplicit()メソッドに書式を入力します。注意点として、あくまで入力値に対して書式設定を行うため、一旦数値を取得する必要があります。したがって、Excelのように空値に書式だけ設定することはできないようです(設定したあとsetValue()で空値を入れたりしたら、書式が元の標準に戻ってしまいました)。

PHP
use PhpOffice\PhpSpreadsheet\Cell\DataType as Datatype; //Datatype属性を使用
$value = $sheet -> getCell('D2') -> getValue(); //値を一旦取得
$sheet -> getCell('D2') -> setValueExplicit($value,Datatype::TYPE_NUMERIC); //書式設定が数値となる

でも、これだと一つのセルしか適用されないので、一定範囲を指定したい場合はこのようにループさせるといいでしょう。

PHP
//指定したい範囲をループさせる
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上での文字入力制御)なので、双方設定する必要があります。

PHP
use PhpOffice\PhpSpreadsheet\Cell\DataType as Datatype; 
$sheet -> setCellValueExplicit("A1",$row_receipt['od_unitprice'],Datatype::TYPE_NUMERIC); //書式を変える
$sheet -> getStyle("A1") -> getNumberFormat() -> setFormatCode('#,##0'); //カンマ付与に対応する

なお、Numberformat属性のFORMAT_NUMBER_COMMA_SEPARATED1を指定すると、強制的に下二桁の小数点が付与されてしまいます。

25 :イテレータを活用し、全データを読み込みながら特定の処理を行う

普通ならばtoArray()メソッドで十分なのですが、もし、その取得した値に対して、特定の処理(税率を掛ける、あるいは特定の値を対象外とするなど)を行いたい場合には各種のイテレータが役立ちます。

イ:特定の処理を行う

array_mapのように、全データに対し特定の処理を行う場合です。この場合はgetCellIterator()を使うと、全データを取得することができます。そのデータに対し特定の処理を行う、いわばPHPのforeach式などと同じことができます()。また、アドレスを取得したい場合はgetCoordinate()メソッドを使います。

PHP
$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; //そのまま
        }
    }
}

ロ:特定の行列だけを抽出する

特定の行列だけを取得したい場合は、以下の方法で行や列を取得し、チェックしてフィルタリングします。

PHP
$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;
}

あとは、この$rowidx$colidxの値でフィルタリングしてみてください。

26:セルに単線(垂直線、水平線、斜線)を引く

単純に境界線を引く場合はgetBorders()メソッドを使います(章を設けたのは斜線の引き方の解説のためです)。ちなみに、applyFromArray()メソッドを使用した方法は別の人が紹介しているので、この章では上下左右だけや外周だけといった、単純に単線の引き方を紹介します。

A:垂直線・水平線

まずはおさらいです。垂直線や水平線を引く場合は以下の公式があり、getBorders() -> getHoge()(Hogeは任意の命令)として、方向を定めてからスタイルを適用します(スタイルの紹介はリンク先を参照してください)。

PHP
use PhpOffice\PhpSpreadsheet\Style\Border as Border;
$sheet -> getStyle("アドレス範囲") -> getBorders() -> getTop() -> setBorderStyle(Border::BORDER_DASHED); //上方向

方向を示すメソッドは以下の通りです(斜線は後述)。

PHP
getTop(); //上方向
getBottom(); //下方向
getLeft(); //左方向
getRight(); //右方向
getOutline(); //外周
getInside(); //内周(外周以外)
getAllBorders(); //すべての垂直・水平直線
getVertical(); //垂直線すべて
getHorizontal(); //水平線すべて

B:斜線

それを踏まえて、斜線の説明に入ります。公式マニュアルではgetDiagonal()メソッドを使用するようにありますが、それだけでは引けません。ソースの分析を行いテストしてみた結果、先にsetDiagonalDirection(n)メソッドを使って、どの方向に引くかを指定する必要があります。

PHP
use PhpOffice\PhpSpreadsheet\Style\Border as Border;
$sheet -> getStyle("A1") -> getBorders() -> setDiagonalDirection(1) -> getDiagonal() -> setBorderStyle(Border::BORDER_THIN);

※setDiagonalDirection(n)の引数は次のようになっているようです。

PHP
setDiagonalDirection(1); //上り斜線(左下から右上)
setDiagonalDirection(2); //下り斜線(左上から右下)
setDiagonalDirection(3); //双方向(クロス)
setDiagonalDirection(0); //斜線の取り消し

27:セルに日付を出力する

タイムスタンプを取ってそれをそのままExcelに貼り付けたりすると、Excelの書式が合わないために、全く的外れな値になってしまいます。それを防ぐためには書式を設定してから、かつ日付出力用に再計算するSharedAPI内のPHPtoExcel()メソッドで処理が必要です。また、書式設定にはStyleAPIのNumberFormat属性を使用します。

PHP
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関数を日本時間に合わせる(標準時から9時間の時差)
$xlsdateval = Date::PHPToExcel( $timestamp ); //Excel出力用に再計算を行う
$sheet -> setCellValue("A1",$xlsdateval); //再計算した値を代入

出力形式の設定は以下のように変更できます。

PHP
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まである。

※時間設定には色々種類があります。公式のソースに定数が設定されているので、参考にするといいでしょう。

NumberFormat.php

また、日付の表示形式を任意指定するにはsetValueExplicit()を使う方法もあります。

28:ハイパーリンクを埋め込む

リンクを埋め込むにはgetHyperlink()メソッドのsetUrl()メソッドを使用します。

A:シート内のリンクを貼る

シート内のリンクを貼るには以下のようにシート名と転送先アドレスを設定します。

PHP
$sheet -> getCell("A1") -> getHyperlink() -> setUrl("sheet://'Sheet1'!A10"); //シート名称Sheet1のアドレスA10にジャンプする

B:外部リンクを貼る

外部リンクを貼るには以下のようにリンク先を設定します。

PHP
$sheet -> getCell("A1") -> getHyperlink() -> setUrl("http://qiita.com");

C:セル内のテキストだけをハイパーリンクにする

今までのやり方だとセル全体がリンクになってしまいましたが、テキストのみハイパーリンクにするには、CellAPIのHyperlink属性を活用し、setHyperlink()メソッドを使用します。

PHP
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

29:セルの背景を塗りつぶす

セルのパターンを塗りつぶすのは前述したapplyFromArray()メソッドを使う方法と、getFill()メソッドを使って一つ一つ指定していく方法があります。ここでは基本、getFill()メソッドの方法で説明します。また、共通でStyleAPIのFill属性を使用します。

A:全色塗りつぶし

まずは基本です。全色を塗りつぶすにはsetFillType()メソッドを使います。

PHP
use PhpOffice\PhpSpreadsheet\Style\Fill as Fill;
$sheet -> getFill() -> setFillType(Fill::FILL_SOLID) -> getStartColor() -> setARGB('00ff7f50');

B:グラデーションで塗りつぶし

グラデーションを適用するには、先にsetRotation(n)メソッドで、色の向きを指定する必要があります。また、getStartColor()メソッドとgetEndColor()メソッドで開始色と終了色を指定する必要があります。

PHP
$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()メソッドで指定します。

PHP
$fill -> setRotation(45);//右上対角線
$fill -> setRotation(90);//グラデーションの向き。90は横
$fill -> setRotation(135);//右下対角線

また角から指定する場合はsetFillType()`メソッドを変更します。

PHP
$color = $fill -> setFillType(Fill::FILL_GRADIENT_PATH); //グラデーションの種類
$color -> getStartColor() -> setARGB('00ff7f50'); //開始色
$color -> getEndColor() -> setARGB('00FFFFFF'); //終了色

※なお、グラデーションを中央から指定する場合は変数で設定されていないため、現在方法を探索中。

C:パターンで塗りつぶし

これがかなり苦戦しました。ポイントはパターンだからといってgetStartColor()メソッドだけでは黒くなってしまうだけで、getEndColor()メソッドに白色(白の場合は単色パターンとなる)など、2色を織り交ぜる必要があります。

PHP
$color = $sheet -> getStyle('B2') ->getFill()->setFillType(Fill::FILL_PATTERN_LIGHTDOWN);
$color -> getStartColor() -> setARGB('00fdeff2');
$color -> getEndColor() -> setARGB('00FFFFFF');

※パターンの種類は以下のようなものがあります(一部)

PHP
setFillType(Fill::FILL_PATTERN_DARKDOWN); //右下がり斜線 縞
setFillType(Fill::FILL_PATTERN_DARKGRAY); //左下がり斜線 格子
setFillType(Fill::FILL_PATTERN_GRAY0625); //6.25%灰色
setFillType(Fill::FILL_PATTERN_LIGHTGRID); //横 格子

xls_gd.jpg

※この質問記事に塗りつぶしのリストがあり、名称は継承しています。
PHPExcel pattern style

30:スタイルを複数のセルで繰り返す

今までは一つ一つアドレスに対し、処理を繰り返してきましたが、正直面倒な場合もあります。そんなときにはduplicateStyle()メソッド(duplicateは重複の意)を使うと便利です。

※以下のサンプルを見れば、行、列及びブロックでの複写は有効である反面、対角線のみの複写はできないことがわかると思います。

PHP
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");

xls.jpg

31:ExcelファイルをPDF出力する(+日本語出力への対応)

実はPhpSpreadsheetで出力できるものはExcelファイルだけじゃなく、csvやpdfにも対応しています。ところが、pdfファイルを日本語出力しようしても、なかなかうまく行かず苦戦している人も多いようですが、次の方法で対応できます。注意点としては、今まで使用していなかったWriterAPIのIOFactory属性とTCPDFを使用します(WriterAPIではフォント指定できません)。

また、使用できるフォントは限られています。

※tcPDFを事前にダウンロードして、日本語にも対応させておいてください。

PHP
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"); //保存する

30章のサンプルをPDF化してみました。体裁はまだ整える必要はありますが、日本語もしっかり表示されています。
SnapCrab_No-0000.jpg

  • 参考にしたサイト

PHPでxlsxファイルをPDFに変換するならLibreOfficeしかないな

32: 関数を埋め込んだ空白セルを読み込み、書き込みを行う

Excelファイルから関数を埋め込んだだけの空白セルを読み込むと次のようなエラーが出ます。

Notice: Undefined index: A in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/LookupRef.php on line 624

これはインデックスが読み取れないというエラーなので、これをエラーコードからディレクトリを追跡して、無理矢理Phpspreadsheetのソースを書き換えて対応します。

LookupRef.php
//624行目をこのように書き換える
$tmp_value = (isset($arrayValues[$columnNum]))?$arrayValues[$columnNum]: NULL;
return $tmp_value;
//return $arrayValues[$columnNum]; //こっちをコメントアウト

こうすれば、関数を埋め込んだ部分を読み込んでもエラーが出なくなり、出力時にも埋め込んだ関数がそのまま適用されます。ただし、メモリをけっこう消費するので、このケースに対応させる場合はほどほどの行数にしておきましょう。

33: 複数の結合セルを再結合する(unmergeCellsメソッドの正しい使い方)

たとえば、A1とA2が結合されてあり、B1とB2が結合されているとします。これをA1からB2まで再結合したい場合、このような書き方が思いつくはずです。

case1.php
$sheet -> mergeCells("A1:B2");
case2.php
$sheet -> unmergeCells("A1:B2");
$sheet -> mergeCells("A1:B2");
case3.php
$sheet -> unmergeCells("A1:A2");
$sheet -> unmergeCells("B1:B2");
$sheet -> mergeCells("A1:B2");

上記の方法はいずれも失敗します。case1はファイル処理できますが、いざファイルを開いたときにデータ破損と表示され、修復すると結合処理はリセットされます。case2とcase3に至ってはエラーメッセージが表示され、処理が止まってしまいます。

では、どのようにするかというと、このunmergeCellsメソッドはかなり曲者であり一度結合処理したセルを解除する場合、一度mergeCellsメソッドでなぞって結合を解除させるセルを記憶させないといけないようです。

したがって、A1からB2までを再結合する場合は、お互いA行とB行を解除してから、再結合を行う必要があります。

PHP
//A行の結合解除
$sheet -> mergeCells("A1:A2"); //既に指定した結合セルをなぞって、メソッドに解除させるセルを記憶させる。
$sheet -> unmergeCells("A1:A2"); //結合を解除
//B行の結合解除
$sheet -> mergeCells("B1:B2"); //既に指定した結合セルをなぞって、メソッドに解除させるセルを記憶させる。
$sheet -> unmergeCells("B1:B2"); //結合を解除
//A行とB行の再結合
$sheet -> mergeCells("A1:B2"); //これで再結合できる

unmergeCellsメソッドの正しい使用方法を探してみたところ、公式ソースの113行目にunmergeCellsメソッドを使用している場面がありますが、この直前に同じ範囲を結合している動作があり、これをヒントにテストしたところ、ようやく使用方法がわかりました。

補 :こんなメッセージが出たら…

開発中にこのようなメッセージに頻繁に遭遇すると思います。
Warning: unlink ...Resource temporarily unavailable in ...

これが出る原因は、保存しようとしているファイルを開きっぱなしにしている場合です。これをtry&catchできる方法は現在探索中です。

それから、WebサーバですがApacheの使用を推奨します。Nginxを用いるとリソースを圧迫してしまい、処理のループに限界(メモリ解放でも追いつけませんでした)があります。あと、別ページにて考察しているLitespeedもlibzipに対応していないため書き込みができません…。

追記+リンク

以降、随時業務をこなしがてら追記していきます

PhpSpreadSheetについて高度な操作方法を紹介したサイトを探しているとこんなページがあります。ここでは、記事が被るものは極力載せない予定です。

BRSF
職業、PG・SE・DBエンジニア。オープン環境のwebプログラムをメインにシステム構築担当。使用言語はPHP(cakePHP、Laravel含)jQuery、JavaScript、ExcelVBA、Perl、Ruby、Python。現在Vue、React、Angular強化中。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした