PhpSpreadsheetをいろいろ試してみる

PhpSpreadsheetはPHPでExcelファイルの読み書きができるライブラリです。

PHPで業務Webアプリを作ってると、割と分析用Excelを出力してほしいという要望があるので、試してみようかなーって感じです。

参考

セットアップ

環境を用意するのが面倒なので、Dockerを利用します。

code:Dockerfile

FROM php:8.0-apache

# php関連

COPY php.ini /usr/local/etc/php/php.ini

COPY --from=composer:1.10 /usr/bin/composer /usr/bin/composer

RUN apt update -y && apt install -y zip unzip

RUN apt install -y libzip-dev zlib1g-dev libpng-dev \

&& docker-php-ext-install zip gd

# apache関連

COPY 000-default.conf /etc/apache2/sites-available/000-default.conf

RUN a2enmod rewrite

code:000-default.conf

<VirtualHost *:80>

# The ServerName directive sets the request scheme, hostname and port that

# the server uses to identify itself. This is used when creating

# redirection URLs. In the context of virtual hosts, the ServerName

# specifies what hostname must appear in the request's Host: header to

# match this virtual host. For the default virtual host (this file) this

# value is not decisive as it is used as a last resort host regardless.

# However, you must set it for any further virtual host explicitly.

ServerAdmin webmaster@localhost

DocumentRoot ${APACHE_DOCUMENT_ROOT}

# Available loglevels: trace8, ..., trace1, debug, info, notice, warn,

# error, crit, alert, emerg.

# It is also possible to configure the loglevel for particular

# modules, e.g.

ErrorLog ${APACHE_LOG_DIR}/error.log

CustomLog ${APACHE_LOG_DIR}/access.log combined

# For most configuration files from conf-available/, which are

# enabled or disabled at a global level, it is possible to

# include a line for only one particular virtual host. For example the

# following line enables the CGI configuration for this host only

# after it has been globally disabled with "a2disconf".

<Directory ${APACHE_DOCUMENT_ROOT}>

Options Indexes FollowSymLinks MultiViews

AllowOverride All

Require all granted

</Directory>

</VirtualHost>

# vim: syntax=apache ts=4 sw=4 sts=4 sr noet

環境変数を使うので、docker-compose.ymlも用意します

code:docker-compose.yml

version: '3'

services:

php-excel:

build:

context: .

dockerfile: Dockerfile

container_name: "php-excel"

ports:

- "8093:80"

working_dir: "/var/www/php-excel"

volumes:

- ".:/var/www/php-excel"

environment:

- TZ=Asia/Tokyo

- APACHE_DOCUMENT_ROOT=/var/www/php-excel

tty: true

これで環境の準備は完了

PhpSpreadsheetのインストール

これらはDockerfileでセットアップしてるので、気にせず実行して下さい。

Dockerfileでやっときたい方はどうぞ。

Hello, World!

PhpSpreadsheetにHello, Worldがあるのでそれと、それを標準出力に出してダウンロードされます。

code:hello_world.php

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;

use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();

$sheet = $spreadsheet->getActiveSheet();

$sheet->setCellValue('A1', 'Hello World !');

header("Content-Disposition: attachment; filename=\"HelloWorld.xlsx\"");

header('Cache-Control: max-age=0');

$writer = new Xlsx($spreadsheet);

$writer->save('php://output');

素晴らしい。

1セル1文字でHello World !

code:hello_world2.php

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;

use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();

$sheet = $spreadsheet->getActiveSheet();

foreach (str_split("Hello World !") as $i => $s) {

$sheet->getCellByColumnAndRow($i + 1, 1)?->setValue($s);

}

header("Content-Disposition: attachment; filename=\"HelloWorld2.xlsx\"");

header('Cache-Control: max-age=0');

$writer = new Xlsx($spreadsheet);

$writer->save('php://output');

Column, Rowって順で指定し、1, 1がA列1行目を指します。

ってことで、九九を作るならこんな感じ。

code:9x9.php

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;

use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();

$sheet = $spreadsheet->getActiveSheet();

for ($i = 1; $i <= 9; $i++) {

for ($j = 1; $j <= 9; $j++) {

$sheet->getCellByColumnAndRow($i, $j)?->setValue($i * $j);

}

}

header("Content-Disposition: attachment; filename=\"9x9.xlsx\"");

header('Cache-Control: max-age=0');

$writer = new Xlsx($spreadsheet);

$writer->save('php://output');

縦と横の見出し

code:header.php

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;

use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();

$sheet = $spreadsheet->getActiveSheet();

$sheet->fromArray($rowHeader, null, $sheet->getCellByColumnAndRow(2, 1)->getCoordinate());

$sheet->fromArray(array_chunk($colHeader, 1), null, $sheet->getCellByColumnAndRow(1, 2)->getCoordinate());

$i = 0;

$row = 0;

while (true) {

$day = date("Y-m-d", strtotime("+$i day", strtotime("2021-01-01")));

$w = (int)date("w", strtotime($day));

$sheet->getCellByColumnAndRow($w + 2, 2 + $row)?->setValue($day);

$i++;

if ($w === 6) $row++;

if ($row >= 10) break;

}

header("Content-Disposition: attachment; filename=\"header.xlsx\"");

header('Cache-Control: max-age=0');

$writer = new Xlsx($spreadsheet);

$writer->save('php://output');

縦の見出しと横の見出しを追加し、その内側に日付を入れていきました。

10週目までのカレンダーのようなものが出力されるはず。

第一引数に配列、第二引数にnull時の値、第三引数に開始位置、それ以降も引数はあります。

多重配列にすればそのまま二次元の表になります。

開始位置はCellではなく文字列である必要があるため、Cellをとってから文字列を取り直しています。

折れ線グラフ

https://gyazo.com/1cf8016641136df7b56b1e430888677e

今気付いたけど、Stackedじゃないわ。Standardでした。

まずはコードを貼り付けるけど、割と長いから後で説明する

code:graph.php

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Chart\Chart;

use PhpOffice\PhpSpreadsheet\Chart\DataSeries;

use PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues;

use PhpOffice\PhpSpreadsheet\Chart\Legend;

use PhpOffice\PhpSpreadsheet\Chart\PlotArea;

use PhpOffice\PhpSpreadsheet\Chart\Title;

use PhpOffice\PhpSpreadsheet\Spreadsheet;

use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();

$sheet = $spreadsheet->getActiveSheet();

$sheet->fromArray($rowHeader, null, $sheet->getCellByColumnAndRow(2, 1)->getCoordinate());

$sheet->fromArray(array_chunk($colHeader, 1), null, $sheet->getCellByColumnAndRow(1, 2)->getCoordinate());

for ($i = 0; $i < count($rowHeader); $i++) {

for ($j = 0; $j < count($colHeader); $j++) {

$sheet->getCellByColumnAndRow(2 + $i, 2 + $j)?->setValue(rand(0, 100));

}

}

$dataSeriesLabels = [];

foreach ($rowHeader as $i => $head) {

$dataSeriesLabels[] = new DataSeriesValues(

dataType: DataSeriesValues::DATASERIES_TYPE_STRING,

dataSource: getCellCoordinate($sheet, 2 + $i, 1),

pointCount: 1,

);

}

$xAxisTickValues = [

new DataSeriesValues(

dataType: DataSeriesValues::DATASERIES_TYPE_STRING,

dataSource: getRangeCoordinate($sheet, 1, 2, 1, 1 + count($colHeader)),

pointCount: count($colHeader),

),

];

$dataSeriesValues = [];

foreach ($rowHeader as $i => $head) {

$dataSeriesValues[] = new DataSeriesValues(

dataType: DataSeriesValues::DATASERIES_TYPE_NUMBER,

dataSource: getRangeCoordinate($sheet, 2 + $i, 2, 2 + $i, 1 + count($colHeader)),

pointCount: count($colHeader)

);

}

$series = new DataSeries(

plotType: DataSeries::TYPE_LINECHART,

plotGrouping: DataSeries::GROUPING_STANDARD,

plotOrder: range(0, count($dataSeriesValues) - 1),

plotLabel: $dataSeriesLabels,

plotCategory: $xAxisTickValues,

plotValues: $dataSeriesValues,

);

$chart = new Chart(

name: "chart1",

title: new Title(caption: "Test Stacked Line Chart"),

legend: new Legend(position: Legend::POSITION_TOPRIGHT),

displayBlanksAs: DataSeries::EMPTY_AS_GAP,

);

$chart->setTopLeftPosition("A7");

$chart->setBottomRightPosition("H20");

$sheet->addChart($chart);

header("Content-Disposition: attachment; filename=\"graph.xlsx\"");

header('Cache-Control: max-age=0');

$writer = new Xlsx($spreadsheet);

$writer->setIncludeCharts(true);

$writer->save('php://output');

function getCellCoordinate(Worksheet $sheet, int $col, int $row): string

{

return $sheet->getCodeName() . "!" . $sheet->getCellByColumnAndRow($col, $row)->getCoordinate();

}

function getRangeCoordinate(Worksheet $sheet, int $startCol, int $startRow, int $endCol, int $endRow): string

{

return $sheet->getCodeName() . "!" . $sheet->getCellByColumnAndRow($startCol, $startRow)->getCoordinate() . ":" . $sheet->getCellByColumnAndRow($endCol, $endRow)->getCoordinate();

}

まず最初に、表を作る

code:graph.php

$sheet->fromArray($rowHeader, null, $sheet->getCellByColumnAndRow(2, 1)->getCoordinate());

$sheet->fromArray(array_chunk($colHeader, 1), null, $sheet->getCellByColumnAndRow(1, 2)->getCoordinate());

for ($i = 0; $i < count($rowHeader); $i++) {

for ($j = 0; $j < count($colHeader); $j++) {

$sheet->getCellByColumnAndRow(2 + $i, 2 + $j)?->setValue(rand(0, 100));

}

}

ここね。

4人の4か月の何かしらの成績みたいなのが縦長にあるかんじ。

https://gyazo.com/f3b3bfc501fee1f88bfd949d54cb670f

こんなのね。

ここからグラフの元になるデータを指定する

code:graph.php

$dataSeriesLabels = [];

foreach ($rowHeader as $i => $head) {

$dataSeriesLabels[] = new DataSeriesValues(

dataType: DataSeriesValues::DATASERIES_TYPE_STRING,

dataSource: getCellCoordinate($sheet, 2 + $i, 1),

pointCount: 1,

);

}

$xAxisTickValues = [

new DataSeriesValues(

dataType: DataSeriesValues::DATASERIES_TYPE_STRING,

dataSource: getRangeCoordinate($sheet, 1, 2, 1, 1 + count($colHeader)),

pointCount: count($colHeader),

),

];

$dataSeriesValues = [];

foreach ($rowHeader as $i => $head) {

$dataSeriesValues[] = new DataSeriesValues(

dataType: DataSeriesValues::DATASERIES_TYPE_NUMBER,

dataSource: getRangeCoordinate($sheet, 2 + $i, 2, 2 + $i, 1 + count($colHeader)),

pointCount: count($colHeader)

);

}

この3つ。

code:grahp.php

function getCellCoordinate(Worksheet $sheet, int $col, int $row): string

{

return $sheet->getCodeName() . "!" . $sheet->getCellByColumnAndRow($col, $row)->getCoordinate();

}

function getRangeCoordinate(Worksheet $sheet, int $startCol, int $startRow, int $endCol, int $endRow): string

{

return $sheet->getCodeName() . "!" . $sheet->getCellByColumnAndRow($startCol, $startRow)->getCoordinate() . ":" . $sheet->getCellByColumnAndRow($endCol, $endRow)->getCoordinate();

}

これで値の準備はできたから、これをグラフにしてExcelに貼り付ける。

code:graph.php

$series = new DataSeries(

plotType: DataSeries::TYPE_LINECHART,

plotGrouping: DataSeries::GROUPING_STANDARD,

plotOrder: range(0, count($dataSeriesValues) - 1),

plotLabel: $dataSeriesLabels,

plotCategory: $xAxisTickValues,

plotValues: $dataSeriesValues,

);

$chart = new Chart(

name: "chart1",

title: new Title(caption: "Test Stacked Line Chart"),

legend: new Legend(position: Legend::POSITION_TOPRIGHT),

displayBlanksAs: DataSeries::EMPTY_AS_GAP,

);

$chart->setTopLeftPosition("A7");

$chart->setBottomRightPosition("H20");

$sheet->addChart($chart);

たぶん複数series用意して、複雑なグラフを作ることもできるんやと思う。

タイトルとか、見出しとか、今回はしてないけどX軸とかY軸のラベルを付けたりできる。

最後に忘れちゃだめなのが、出力時にChartの出力を有効にすること。

code:gragh.php

$writer = new Xlsx($spreadsheet);

$writer->setIncludeCharts(true);

$writer->save('php://output');

この2行目の。

これをしないとグラフが出力されない。

感想

グラフ以外はめっちゃ簡単。割と思い通りにExcelを作れる。

問題はグラフや。

グラフの生成に失敗したのが分かるのって、Excelファイルを開くときなんすよね。

つまり、プログラム側でエラーが分からない。

んで、Excelさんは頭がよくて、破損してるデータがあったから削っといたよーっていって開いちゃう。破損判定の理由は分からない。

うまくグラフが出せるまでめっちゃ試行錯誤した……。

更新履歴