PHP(PhpSpreadsheet)でエクセルの読み書きをした話

2018年1月29日

CSVは闇

最近お仕事でいわゆるマスタデータ管理画面をつくる機会がありました。

管理画面の要件として「CSVで一括更新できるようにしてほしい、エクセルで一気に編集したい」という話が持ち上がりました。あるあるですね。

「CSV?なるほど良いですよ」とは答えたものの、CSV入出力というのは実はとても面倒な部類に属する仕事です。複数行の文字列への対応はもちろん、文字エンコーディングがsjis(cp932)であるとか、日付を書き出したはずなのにエクセルで開くと変な数値になってしまったり、ゼロ詰めした数値が破壊されたり、ドットで始まる文字列とか、ああ、頭が・・・。

要は「エクセルで一気に編集したい」というところを満足すればいいわけなのですから、CSVではなく本当のエクセルファイルでも良いはずです。

昔PHPExcel 今PhpSpreadsheet

大丈夫。わたしは知っています。PHPにはとっても便利な『PHPExcel』というライブラリがあるということを! ということでPHPExcelの様子を見に行ってみましたところ、なんとPHPExcelは新装開店して PhpSpreadsheet になっていたのですね!

というわけで、実際にPhpSpreadsheetをお仕事でデータ入出力に使ってみましたので、その際に学習した使い方のTipsなどを紹介します。

PhpSpreadsheet のインストール

PhpSpreadsheetはPackagistから簡単にインストール可能で、composerコマンドでインストールするなら例えば以下のようになります。

$ composer require "phpoffice/phpspreadsheet"

composer.jsonは例えばこんな感じ。

{
  "require": {
    "phpoffice/phpspreadsheet": "^1.0"
  }
}

PhpSpreadsheet の使い方

公式ドキュメントにたくさんのサンプルがありますので、そちらを読むのが良いです。以下にはごく簡単な例を紹介します。

エクセルファイルを読むコード。

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$spreadsheet = $reader->load($xlsxFilepath); // ファイルをロードするとSpreadsheetが得られます。
$sheet = $spreadsheet->getSheet(0); // 最初のシートを得ています。Sheetが得られます。
$rowArray = $sheet->toArray(); // これで表の2次元配列が得られます。

エクセルファイルを書くコード。

$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet(); // Spreadsheetを作成。
$sheet = $spreadsheet->getSheet(0); // 最初のシートを得ています。Sheetが得られます。
$rowArray = [
  [1, '山田', '男'],
  [2, '市川', '女'],
  [3, '古橋', '男'],
];
$sheet->fromArray($rowArray); // 表の左上(A1)から2次元配列でべたっと貼り付ける例。
$row = [4, '宮本', '女'];
$sheet->fromArray($row, null, 'A4'); // 4行目から貼り付ける例。
$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
$writer->save($xlsxFilepath); // これで保存されます。

このとき書き込む文字はutf8のままで問題ありません。またセルの横幅や文字色の設定などもできます(これもCSVには真似出来ないことですね)。

実用Tips

ファイルダウンロード

“php://output”に対してsaveをすれば、そのままブラウザへの出力になります。もちろんこの以前に適切なダウンロード用HTTPヘッダ(Content-dispositionとか)を出力しておくことが必要です。

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
$writer->save('php://output');

エクセルの罠へ対処する

冒頭に書いたような『エクセルで開くと変な数値になってしまったり』という罠への対応です。これはセルの値と書式設定を文字列型にすることで対処します。こうするとエクセルシート上での演算で不都合が発生する場合がありますが、あくまでもCSVの代替(データ入出力用)と割り切るならアリだと思います。

// 値の型をstringにする
$row = [(string)4, '宮本', '女', '02/28'];
$sheet->fromArray($row, null, 'A4');
$sheet
  ->getStyle("A1:G2500") // 全データ範囲を選択
  ->getNumberFormat()
  ->setFormatCode(NumberFormat::FORMAT_TEXT); // 書式設定をテキストにする

まとめ

PhpSpreadsheetを使うとCSVで発生するような面倒事がだいぶ減るのでとても便利です。インターフェイスも直感的(まるでエクセル上でマウス操作をしているかのように簡単)ですので、管理画面のデータ入出力にCSVを使っている方はぜひ試してみてください。

久しぶりのPHPプログラムネタでした!でわでわ。

PHPPHP

Posted by らいあ