2024年4月30日

PhpSpreadsheet　tips #3

・メモリの使用量について
・必要メモリの試算（1セルに使うメモリ量について）
・使用メモリの抑制（EXCELファイル読み込み時用）

使用環境

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

PhpSpreadsheetでEXCELファイルを操作するのに必要なメモリ量は、ファイル容量よりも遥かに大きくなる。

例えば、190KBのEXCELファイルをPhpSpreadsheetで開くと、その100倍近くのメモリが使われたりする。

なので、190KBのファイルなのだから、1MBもメモリあれば足りるだろう、、、
などと、安易に考えるのは危険。

必要なメモリ量を目安として試算する際は、ファイルの容量ではなく、セル数を元に試算した方が良い。
（それでも目安の域を出ないので、可能な限り実データに近いものでの事前検証が望ましい）

セル数を元に、必要メモリを試算していく。

上記ドキュメントに
『ワークシート内のセルごとに平均約 1KB（64 ビットPHPでは 1.6KB）を使用する』
との記述があるが、セルのデータ内容によるため必ずしもその通りではない。

検証した感触としては、半角英字1文字だけのセルで0.6KB程が使用されていて、1KBよりだいぶ少ない。

ただ、長いテキストなどはその内容に応じてメモリが使用されるので、2KB、3KB…と例示されている1KB（1.6KB）を超える。

ただ、実利用を考えると、リストとして出力しようなもので長めのもの、、、
例えば住所などで
「東京都○○○区○○○○○11-11-11○○○○○○○○○○1111」
といった文字列であれば0.6KB強程で済んでいる。

そのため、文章などの長文データばかりでなければ、分かりやすい1KBを試算値として使って問題はなさそう。

※検証は該当データを含むEXCELファイルの読み込みで検証。
　ファイルは新規作成したEXCELファイルにデータを入れただけ（書式などは初期状態）のもの。

（試算例　※行×列=セル数）
1,000行 × 20列 = 20,000セル、20,000セル × 1KB = 19.5MB。
10,000行 × 20列 = 200,000セル、 200,000セル × 1KB = 195.3MB。

仮にこれを1セル=0.6KBで試算すると、
1,000行 × 20列 = 20,000セル、20,000 × 0.6KB = 11.7MB。
10,000行 × 20列 = 200,000セル、200,000 × 0.6KB = 117.3MB。

そう考えると、
・1プロセスで使えるメモリが潤沢にある
・同時実行がない（又は、あっても許容出来るだけのメモリ量がある）
などの条件が揃わない限りは、ボリュームのあるリストファイルの読み書きは難しい。

ファイルの読み込みのみであれば、読み込む内容を絞ることでメモリ使用量を抑える事を検討出来る。
（残念ながら書き込みに使う場合は難しい）

// 値だけ読み込むよう指定
$reader->setReadDataOnly( true );

PHP

値だけを読み込むことでメモリ使用量を抑える事が出来る。
（フォントや書式などが読み込まれなくなる）

ただし、新規作成して値だけを書いたようなEXCELファイルの場合（値以外の情報がほぼない場合）は指定しても大差ない。

また、書式が入っていて値と表面上の見た目が異なる場合、書式が読まれないためデータの取得が難しくなるケースがある。
例を挙げると、EXCELの日付データは、値は数値で書式により日付に見えるようになっている。
そのため、これを指定すると日付の状態では値を読めなくなってしまう。

// 読み込むシートを指定
$reader->setLoadSheetsOnly( 'Sheet1' );

PHP

読み込むシートを指定することで、それ以外のシートを無視してメモリ使用量を抑える事が出来る。

ただし、いくつかの前提事項がある。
・シートが複数あり、データ量が各シートに分散している
・読み込むシートが1シートである
・読み込むシートのシート名が予め分かっている

なお、これを指定して開いたEXCELファイルを保存した場合、読み込んだシート以外はなくなってしまう。
ファイルを開いて加筆して保存する場合は注意が必要。