|||||||||||||||||||||

なんぶ電子

- 更新:　

PHPでエクセルファイルを生成

PhpSpreadsheet

筆者はJavaからエクセルファイルを読み込んだり生成したりする場合にAPACHE POIを利用していたのですが、Webサービス経由でエクセルファイルを生成したい案件がありPHPで同様の事ができないかと探してみたらPhpSpreadsheetというものがあったので、今回はこの利用方法の覚書を作ります。例示する環境はDebian11+Php7.4ですが、Windowsの場合でもほぼ同様の操作だと思います。

要件

PhpSpreadsheetのページによればこのサポートは本家のPHPの各バージョンのサポート終了の後6カ月はめんどうを見てくれるようです。

Debian11で利用したいと考えたため、筆者の環境はPHP7.4となっています。

PHP本体のほか、PHPのパッケージ管理アプリであるComposerが必要です。

Debian11でPHP7.4を使う場合は、ComposerもAPTでインストールできます。Windowsなら公式サイトからダウンロードしてインストールします。 

# apt update
...
# apt install composer

プロジェクトの初期化

プロジェクトのディレクトリを作成します。ここではWeb経由での操作を考えているので、apacheサーバーの公開ディレクトリに作成しました。

$ mkdir phpspreadsheet
...
$ cd phpspreadsheet

プロジェクトのディレクトリで「composer init」コマンドを使ってプロジェクトを初期化します。テスト用のアプリなので初期化時に聞かれる問はすべてデフォルトのまま進めました。

$ composer init

「compsoer require」コマンドで、PhpSpreadsheetをプロジェクトに加えます。筆者の場合、気づかすに古いバージョンのphpで実行したのですが、サポートが終了すると、インストールもできなくなるようでした。 

$ composer require phpoffice/phpspreadsheet

この時PHPの拡張パッケージの有無により、次のようなエラーがでる事があります。

your requirements could not be resolved to an installable set of packages. Problem 1 - phpoffice/phpspreadsheet 1.24.1 requires ext-dom * -> the requested PHP extension dom is missing from your system.

Problemに提示されたパッケージをAPTでインストールします。筆者が遭遇した例でいうと次のようなものがありました。PHPのバージョンの部分は環境により読み替えてください。

  • php7.4-xml
    ... requires ext-dom * -> the requested PHP extension dom is missing from your system.

    DOM解析用のライブラリです。

  • php7.4-gd
    ... requires ext-gd * -> the requested PHP extension gd is missing from your system.

    画像処理用のライブラリです

  • php7.4-zip
    ... requires ext-zip * -> the requested PHP extension zip is missing from your system.

    ZIP圧縮用のライブラリです。

不足していたライブラリをAPTで取得します。Windowsの場合は、php.iniに拡張ライブラリの設定をします。

# apt install php7.4-xml php7.4-gd php7.4-zip

もし、開発環境と本番環境が違っている場合は、インストール前にcomposer.jsonを次のようにしして、「composer install」コマンドを実行します。

phpspreadsheetやphpのバージョンは環境に応じて変更してください。 

{
  "require": {
    "phpoffice/phpspreadsheet": "^1.24"
  },
  "config": {
    "platform": {
      "php": "7.3"
    }
  }
}

この記述中にある^はバージョンの範囲を指定するものです。

他composer.jsonでは、バージョンの範囲を等号不等号、ワイルドカード(*)、チルダ(~)で表すことがあります。

Hello World!

公式ページにある、もっともシンプルな使い方を借用します。

test.php

<?php

require 'vendor/autoload.php';

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

$book = new Spreadsheet();
$sheet = $book->getActiveSheet();
$sheet->setCellValue('A1','Hello World!');

$writer = new Xlsx($book);
$writer->save('hello_world.xlsx');

冒頭「vendor/autoload.php」をrequireすることで、composerが依存しているライブラリをロードします。

その後、PhpSpreadsheet本体から名前空間の設定をしてSpreadsheetとXlsxクラスを利用可能にしています。

設定したSpreadsheet(エクセル風に言うならワークブックに当たります)を定義し、そこからワークシートを選択しセルに文字を代入します。

書き込みはSpreadsheetのインスタンスをコンストラクタに渡して書き込み用のインスタンスを生成します。

書き込み用インスタンスから、ファイル名を引数にsaveメソッドを呼ぶことでファイルが保存されます。

このコードを実行する(php test.php)と、カレントディレクトリにエクセルファイルが生成されます。トップ画像はそのファイルをExcel2019で開いたものです。

ドキュメントとサンプルコード

先に紹介した公式サイトにドキュメントは存在するので、この後はそれを見ながらコーディングをしていくのですが、もう少しサンプルが欲しいという人向けにそれも提供されています。

composer requireにソースから取得する--prefer-sourceというオプションがあります。先のインストール時にこちらのオプションを付与することでサンプル付のライブラリがインストールされます。

ここでは先にインストールしてしまったのでいちどremoveコマンドで除去します。

$ composer remove phpoffice/phpspreadsheet
...
$ composr require phpoffice/phpspreadsheet --prefer-source
...

PHPのビルトインWebサーバー機能を使ってサンプルを稼働させます。

このコマンドは「php -S アドレス:ポート」とすることでカレントディレクトリを公開します。ここではさらに-tオプションを使って公開ディレクトリを指定しています。

外部のPCからアクセスしたい場合はlocalhostの部分を192.168.1.1等の外部からビルドインWebサーバーにアクセスできるアドレスを使って待ち受ける必要があります。

php -S localhost:8000 -t vendor/phpoffice/phpspreadsheet/samples
PHPSpreadsheet Sample

上部のタブから、実行したいサンプルを選択すると下段に表示される仕組みです。

実行されるPHPファイルはアドレスバーに表示されます。たとえば「http://localhost:8000/Calculations/Database/DAVERAGE.php」なら「vendor/phpoffice/phpspreadsheet/samples/Calculations/Database/DAVERAGE.php」に実体はあります。

またサンプル実行時にファイルが生成される場合は「/tmp/phpspreadsheet」に出力されるようです。

値関連の設定

実際筆者が利用したファイル出力の設定例をいくつか紹介しておきます。

まず、エクセルの文字コードはSJIS(CP932)ですが、PHP内の文字コードはUTF-8で問題ないようです。

$bookにはSpreadsheetのインスタンス(ワークブック)が、$sheetにはそこから取得したシートが設定されているものとします。設定のしかたは先ほど紹介したHello worldのコードを参照して下さい。

  • デフォルトのフォントの設定

    デフォルトフォントはSpreadsheet(ワークブック)に対して指定します。 

    $book->getDefaultStyle()
  ->getFont()
  ->setName('ＭＳ ゴシック')
  ->setSize(14);
  • 文字の折り返し

    文字の折り返しは、文字列中のLF(¥n)で設定できますが、折り返しを有効にする必要があります。

    getStyleメソッドにセルのアドレスを渡して得られたオブジェクトから、アライメントを取得し折り返しを有効にします。

    $sheet->getStyle('A1')
  ->getAlignment()
  ->setWrapText(true);
  
$sheet->setCellValue('A1',"あいうえお¥nかきくけこ");
  • セルの文字の装飾

    文字の装飾、太字(ボールド)やイタリック、色などを変更したい場合はセルからgetStyleを呼びます。色を指定する場合はColorクラス使を使えるようにしておいたうえでその定数を利用するか、16進数の文字列で直接指定します。

    ...
use PhpOffice¥PhpSpreadsheet¥Style¥Color;
...
$sheet->getCell('B1')->getStyle()->getFont()->setBold(true);
$sheet->getCell('B2')->getStyle()->getFont()->setItalic(true);
$sheet->getCell('B3')->getStyle()->getFont()->getColor()->setARGB(Color::COLOR_RED);
$sheet->getCell('B4')->getStyle()->getFont()->getColor()->setARGB('FFFFCCCC');

    setARGBに渡す値は透明度、赤、緑、青の4要素を2桁16進数であらわして連結させた文字列です。

  • 文字の一部を装飾

    文字の一部を装飾したい場合はRichTextクラスを用います。

    RichTextクラスのインスタンスからcreateTextメソッドを呼ぶと文字列が追加されていきます。

    セルの中の一部の文字だけを装飾したい場合は対象の文字をcreateTextRunメソッドで追加します。この時の戻り値に引数に指定した文字列のオブジェクトが変えるので、そこからgetFontメソッドを呼び文字を装飾します。

    ここでは文字の色付けにgetFont()から呼び出したオブジェクトのsetColorメソッドにColorインスタンスを渡しています。この方法は先の文字の装飾でも利用できます。また逆に、先で紹介したgetColor経由のsetARGBメソッドもこのRichTextで使えます。

    ...
use PhpOffice¥PhpSpreadsheet¥RichText¥RichText;
use PhpOffice\PhpSpreadsheet\Style\Color;
...
$rt = new RichText();
$rt->createText('装飾のない文字列');

$part = $rt->createTextRun('太字のイタリックで赤色');
$part->getFont()->setBold(true);
$part->getFont()->setItalic(true);
$part->getFont()->setColor(new Color(Color::COLOR_RED));

$rt->createText('ここから元に戻ります');

    装飾したリッチテキストは、通常の文字列同様にセルにセットできます。

    $sheet->getCell('A1')
  ->setValue($rt);
  • 式

    =から始まる式は文字列としてそのままセットできます。

    $sheet->setCellValue('A1',1)
  ->setCellValue('A2',3)
  ->setCellValue('A3',5)
  ->setCellValue('A4','=SUM(A1:A3)');
  • 配列をセット

    fromArrayメソッドを使うとPHPで作成した2次元配列をそのままシートにセットすることができます。第1引数にセットしたい2次元配列、第2引数にNULL、第3引数に左上となるセルの位置をセットします。

    $sheet->fromArray($array,NULL,'D4')
  • フォーマット

    フォーマットを設定する場合は、エクセルで「表示形式」「ユーザー定義」のフォーマットで指定する際と同じ書式をsetFormatCodeメソッドに渡します。

    また、「vendor¥phpoffice¥phpspreadsheet¥src¥PhpSpreadsheet¥Style¥NumberFormat.php」に定数としても、それらの書式が設定されています。

    例えば、パーセンテージとして表示したい場合は、次のようにします(ここでは、名前空間を定義していないのでフルパスで記述しています)。 

     $sheet->getStyle('A1')->getNumberFormat()->setFormatCode(¥PhpOffice¥PhpSpreadsheet¥Style¥NumberFormat::FORMAT_PERCENTAGE);

    また、3桁区切りで,を入れ、0以上は青、0未満は赤で表示するフォーマットは次のように設定します。

    $sheet->getStyle('A1')->getNumberFormat()->setFormatCode('[Blue][>=0]#,##0;[Red][<0]-#,##0');

セル関連の設定

セル関連の設定は、シートからgetStyle('A1')といった形でスタイル適用範囲を指定して使うものが多いですが、このメソッドはgetStyle('C1:D3')といったように範囲で指定する事もできます。

  • 背景色

    背景色を設定するには塗りつぶし用のFillクラスの設定をします。

    FillTypeを設定しないと塗りつぶされないので注意してください。

    ...
  use PhpOffice¥PhpSpreadsheet¥Style¥Fill;
  ...
  $sheet->getStyle('B1:D3')->getFill()->setFillType(Fill::FILL_SOLID);
  $sheet->getStyle('B1:D3')->getFill()->getStartColor()->setARGB('FFCCCCCC');
  • 罫線

    罫線を利用するにはBorderクラスを設定します。

    ...
  use PhpOffice¥PhpSpreadsheet¥Style¥Border;
  ...
  $sheet->getStyle('B1:D3')->getBorders()->getTop()->setBorderStyle(Border::BORDER_THICK);
  $sheet->getStyle('B1:D3')->getBorders()->getBottom()->setBorderStyle(Border::BORDER_THICK);
  $sheet->getStyle('B1:D3')->getBorders()->getLeft()->setBorderStyle(Border::BORDER_THICK);
  $sheet->getStyle('B1:D3')->getBorders()->getRight()->setBorderStyle(Border::BORDER_THICK);

    Borderクラスで線の種類として指定できる定数は次の通りです。

    BORDER_NONE, BORDER_DASHDOT, BORDER_DASHDOTDOT, BORDER_DASHED, BORDER_DOTTED, BORDER_DOUBLE, BORDER_HAIR, BORDER_MEDIUM, BORDER_MEDIUMDASHDOT, BORDER_MEDIUMDASHDOTDOT, BORDER_MEDIUMDASHED, BORDER_SLANTDASHDOT, BORDER_THICK, BORDER_THIN

    ここまで上下左右それぞれ独立して設定しましたが、エクセルだと「外枠」とか「格子」などを設定できます。それらはそれぞれ「outline」や「allborders」で利用可能です。

    $sheet->getStyle('B1:D3')->getBorders()->getAllborders()->setBorderStyle(Border::BORDER_THICK);
$sheet->getStyle('B1:D3')->getBorders()->getOutline()->setBorderStyle(Border::BORDER_THICK);

    罫線の色は罫線からのgetColorを経由してsetARGBを使います。

    $sheet->getStyle('B1:D3')->getBorders()->getBottom()->getColor()->setARGB('FFFF3333');

    基本的に設定が競合する場合は後から設定したものが優先になりますが、罫線の場合は上下左右のセルで競合が起きるので特に注意が必要です。たとえば、2行目の下枠と3行目の上枠、5列目の右枠と6列目の左枠といった具合です。

  • アライメント

    アライメントを利用するにはAlignmentクラスを設定します。

    ...
  use PhpOffice¥PhpSpreadsheet¥Style¥Alignment;
  ...
  $sheet->getStyle('B2')->getAlignment()->setHorizontal(ALignment::HORIZONTAL_RIGHT);

    アライメントとして指定できる主な定数は次の通りです。VERTICALの接頭がついているものに関しては、setVerticalでセットします。

    HORIZONTAL_GENERAL, HORIZONTAL_LEFT, HORIZONTAL_RIGHT, HORIZONTAL_CENTER, HORIZONTAL_CENTER_CONTINUOUS, HORIZONTAL_JUSTIFY, HORIZONTAL_FILL, VERTICAL_BOTTOM, VERTICAL_TOP, VERTICAL_CENTER, VERTICAL_JUSTIFY
  • スタイル設定の配列化

    さきほど、配列化した値を設定しましたが、スタイル(セルの設定)も配列化させて適用させることができます。

    $styles = [
  'borders' => [
     'outline' => [
        'borderStyle' => Border::BORDER_THIN,
        'color' => ['argb' => 'FFFF3333'],
     ],
  ],
];

$sheet->getStyle('C3:D5')->applyFromArray($styles);
  • 列の幅

    列の幅を指定するには、getColumnDimensionメソッドで選択した列からsetWidthを使います。

    オプションで第2引数に幅の単位を指定できます、それにはpt(ポイント)、px(ピクセル)、mm(ミリ）などがあります。

    また、0で非表示、-1でデフォルト値となります。

    $sheet->getColumnDimension('A')->setWidth(30);
  • 行の高さ

    行の高さを指定するにはgetRowDimensionメソッドで選択した行からsetRowHeightを使います。行番号は数値ですが、文字列として渡します。

    $sheet->getRowDimension('5')->setRowHeight(30,'px');
  • セルの結合

    セルの結合は、シートからmergeCellsメソッドを呼び結合したい範囲を引数に渡します。

    また元に戻す際にはunmergeCellsメソッドを使います。

    $sheet->mergeCells('A1:B2');
$sheet->unmergeCells('A1:B2');

シートの設定

  • シートの取得

    Spreadsheet(ワークブック)からシートを取得したい場合、現在選択されているシートを選択するにはgetActiveSheetが使えます。他のシートを取得する場合は、インデックスを指定したgetSheetを使うか、名前を指定したげgetSheetByNameを使います。

    また、getIndexにシートのインスタンスを渡すとインデックスを取得することができます。

    $book->getActiveSheet();
$book->getSheet(0);
$book->getSheetByName('Sheet1');
  • シートの追加

    Spreadsheet(ワークブック)にシートの追加したい場合はcreateSheetメソッドを呼びます。デフォルトだと「Worksheet 1」から連番で名付けられます。

    このメソッドの戻り値にはシートが返るので、そこから次に紹介するsetTitleを使うことで名前を付けることができます。

    引数に挿入位置を指定できます。

    $book->createSheet();
$addSheet = $book->createSheet();
$book->createSheet(0);//先頭に挿入
  • シートに名前を付ける

    シートに名前を付けるにはシートインスタンスからsetTitleメソッドに名前を渡します。

    もし同名のシートが存在していた場合は設定した名前の後ろにスペースと1から始まるインデックスが付与された形で名づけれられます。

    $sheet->setTitle('サンプル');
// 既存の場合「サンプル 1」といった風にインデックスが付与されます。
  • シートをコピー

    シートをコピーしたい場合は、Spreadsheet(ワークブック)からシートをcloneで生成したのち名前をかえてSpreadsheetに加えます。

    addSheetでシートを加える時は同名のシートがあると「 Rename this worksheet first 」というエラーがスローされコードの実行が止まります。

    $cloneSheet = clone $book->getSheetByName('サンプル');
$cloneSheet->setTitle('サンプル(コピー)');
$book->addSheet($cloneSheet);

出力

シート保存時に、IOFactoryクラスからパラメータを指定してWriterを取得することでxlsxだけでなく、xls、csv、odsといったフォーマットも利用できます。

またsave時にファイルパスの代わりに「php://output」を指定することで作成したファイルを直接ダウンロードさせることもできます。

...
use PhpOffice¥PhpSpreadsheet¥IOFactory;
...
$writer=IOFactory::createWriter($book, 'Xlsx');// Xlsx, Xls, Csv, Ods, Htmlなどが指定可能

$writer->save('file.xlsx');

$writer->save('php://output');//直接ダウンロード

ちなみにvendor¥phpoffice¥phpspreadsheet¥src¥PhpSpreadsheet¥Writer¥Pdfに、DompdfをはじめいくつかのPDFコンバーターの実装が書かれています。

そのためフォーマットに、"Dompdf"という文字列を指定すればHTMLからの変換でPDFファイルが作成できます。

ただしdompdfを利用するには、事前に「composer require dompdf/dompdf」としてdompdfをインストールする必要がある他、dompdfはデフォルトでは日本語フォントがないので、日本語のPDFドキュメントを作成したい場合はその設定も必要です。

筆者紹介

自分の写真
がーふぁ、とか、ふぃんてっく、とか世の中すっかりハイテクになってしまいました。プログラムのコーディングに触れることもある筆者ですが、自分の作業は硯と筆で文字をかいているみたいな古臭いものだと思っています。 今やこんな風にブログを書くことすらAIにとって代わられそうなほど技術は進んでいます。 生活やビジネスでPCを活用しようとするとき、そんな第一線の技術と比べてしまうとやる気が失せてしまいがちですが、おいしいお惣菜をネットで注文できる時代でも、手作りの味はすたれていません。 提示されたもの(アプリ)に自分を合わせるのでなく、自分の活動にあったアプリを作る。それがPC活用の基本なんじゃなかと思います。 そんな意見に同調していただける方向けにLinuxのDebianOSをはじめとした基本無料のアプリの使い方を紹介できたらなと考えています。

広告

プライバシーポリシー