PhpSpreadSheetについてまとめてみた #PHP

はじめに

CakePHPといったフレームワークで開発するに当たって、Excel系のファイルを操作できるようにするのに、ライブラリーを導入する必要があります。

PhpSpreadSheetやSimpleExcel様々なライブラリーが存在していますが、今回一番使われているPhpSpreadSheet（汎用バージョン）を紹介させていただきます。

ネットでインストール方法や利用方法関にする内容は多数存在しているので、この部分は簡略に紹介します。本編は普段きづかれにくいところを説明します。

基礎
1. 対応できるExcel系ファイルのバージョン
2. インストール方法
3. 利用方法
効率アップ
1. Fluent interfacesで記述するべき
2. 計算結果のキャッシュ
3. memoryの内容をcacheに移行
注意点
1. バージョンサポート
2. 出力できない問題--メモリエラー

インストールと利用方法

対応できるExcel系ファイルバージョン(２０１９.３)

Format	Reading	Writing
Open Document Format/OASIS (.ods)	✓	✓
Office Open XML (.xlsx) Excel 2007 and above	✓	✓
BIFF 8 (.xls) Excel 97 and above	✓	✓
BIFF 5 (.xls) Excel 95	✓
SpreadsheetML (.xml) Excel 2003	✓

composerでインストール

composer require phpoffice/phpspreadsheet

手動でもいけますがちょっと手間かかるので、composerを推奨します。

利用方法

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet(); 
$sheet = $spreadsheet->getActiveSheet(); //括弧の中に操作するsheet名を自由に指定できます。
$sheet->setCellValue('A1', 'Hello World !'); //cellに内容をインサートする

$writer = new Xlsx($spreadsheet);
$writer->save('hello world.xlsx');

計算やセルフォーマット用のファンクションも用意されています。OFFICIALSITEを参照してください。
OfficialSite

効率アップ

Fluent interfacesで記述するべき

$spreadsheet->getProperties()->setCreator("wrecking ball");
$spreadsheet->getProperties()->setLastModifiedBy("wrecking ball");

↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓

$spreadsheet->getProperties()
    ->setCreator("wrecking ball")
    ->setLastModifiedBy("wrecking ball");

強制ではないですが、Fluent interfacesを利用しない場合、getPropertiesファンクションは複数回呼ばれます。効率面を考慮するとしたら、上の様な書き方は避けたほうがいいです。

計算結果キャッシュ

EXCELのセルは２つのタイプがあります、valueとformula。
A11=SUM（A1:10）といった形式はformulaタイプです。
PhpSpreadSheetは、formulaタイプを計算して、キャッシュに保存します。
なので、以下の方法で直接値を取得できます、再計算する必要はありません。

$spreadsheet->getActiveSheet()->getCell('A11')->getCalculatedValue();

計算結果のキャッシュを削除することも可能です。

Calculation::getInstance($spreadsheet)->clearCalculationCache();

キャッシュ機能を無効する

Calculation::getInstance($spreadsheet)->disableCalculationCache();

時々FORMULAはうまく計算されない場合があります、これはコードの問題ではないかもしれません。
以下の対照表をチェックした上で、コードをチェックしてください。
Excel・PhpSpreadSheetファンクション対照表

memoryの内容をcacheに移行

PhpSpreadSheetの仕組みはcellごとに1kのメモリを使います。データ量が多い場合やconcurrentが多い場合にサーバが処理できない可能性が高いです。サーバのSPECをあげる方法以外は、memoryの内容をcacheに移行と言う方法もあります。処理スピードはもちろん遅くなります。

上記機能を有効にする

$cache = new MyCustomPsr16Implementation();
\PhpOffice\PhpSpreadsheet\Settings::setCache($cache);

注意:デフォルトはworksheetごとキャッシュします。ここはcellのキャッシュ機能の有効化です。すでにworksheetなどを作り始めたら途中で変換することはできません

注意点

バージョンサポート

Phpダウンロード用ライブラリーを検索すると、PhpExcelという結果がよくでてきます。これはPhpSpreadSheetの前バージョンなので、すでにサポートされていません。
PhpSpreadSheetは該当PHPバージョンのサポートが停止する半年前にサポートを停止します。

出力できない問題--メモリエラー

Fatal error: Allowed memory size of xxx bytes exhausted (tried to allocate yyy bytes) in zzz on line aaa

php.iniのメモリ制限によっては上記のエラーが出るため、下記のように一時的に拡張することも可能です。

ini_set('memory_limit', '128M')

まとめ

PhpSpreadSheetについては日本語バージョンのOffical Documentがないので、こちらの記事が参考になれば幸いです。