Help us understand the problem. What is going on with this article?

PHPExcelでエクセル出力入門

More than 1 year has passed since last update.

:warning: PHPExcelの後継PhpSpreadsheetを使おう :warning:

PHPExcelは非推奨となったため、これからExcel出力を始める場合はPHPExcelの後継であるPHPOffice/PhpSpreadsheetを導入することを推奨する。


PHPExcelでエクセルを出力する方法を紹介していきます。なお、ここで紹介するPHPコードはGitHubのsuin/phpexcel-playground: PHPExcelの遊び場で公開しています。

PHPExcelとは?

PHPExcelとは、Excel95、Excel2007等のエクセルファイルを読み書きすることができるピュアPHPのライブラリです。WindowsやExcelが必要ないため、Linux環境だけで完結するのが特徴です。

PHPExcelのインストール

Composerでインストールする

composer require phpoffice/phpexcel

空っぽのExcelを出力する

プロパティだけを持った空っぽのエクセルを出力してみます。

01-output-empty-excel.php
date_default_timezone_set('Asia/Tokyo');
require __DIR__ . '/vendor/autoload.php';

$book = new PHPExcel();
$book->getProperties()
    ->setCreator("田中 太郎")
    ->setLastModifiedBy("山田 花子")
    ->setCompany('株式会社○○')
    ->setCreated(strtotime('2016-01-02 03:04:05'))
    ->setModified(strtotime('2016-02-03 04:05:06'))
    ->setManager('佐藤 次郎')
    ->setTitle("タイトル")
    ->setSubject("サブジェクト")
    ->setDescription("説明文")
    ->setKeywords("エクセル PHP 出力")
    ->setCategory("PHPすごい");

$writer = PHPExcel_IOFactory::createWriter($book, 'Excel2007');
$writer->save('output/01-からっぽのエクセル.xlsx');

出力結果

01-からっぽのエクセル_xlsx_のプロパティ_and_01-からっぽのエクセル_xlsx.png

セルに値を入れる

05-output-write-cells.php
$book = new PHPExcel();
$sheet = $book->getActiveSheet();

// セル番地で書いてみる
$sheet->setCellValue('A1', 10000);
$sheet->setCellValue('A2', true);
$sheet->setCellValue('A3', 'テスト');

// 行列番号で書いてみる
$column = 1;
$sheet->setCellValueByColumnAndRow($column, 1, 'B1');
$sheet->setCellValueByColumnAndRow($column, 2, 'B2');
$sheet->setCellValueByColumnAndRow($column, 3, 'B3');

$writer = PHPExcel_IOFactory::createWriter($book, 'Excel2007');
$writer->save('output/05-セルに書いてみる.xlsx');

出力結果

05-セルに書いてみる_xlsx.png

数式をセルに書く

数式も値と同様に、setCellValueに文字列として渡すことで書き込むことができます。

12-output-formula.php
$book = new PHPExcel();
$sheet = $book->getActiveSheet();
$sheet->setCellValue('A1', 64);
$sheet->setCellValue('B1', 4);
$sheet->setCellValue('C1', '=A1 * B1');

$writer = PHPExcel_IOFactory::createWriter($book, 'Excel2007');
$writer->save('output/12-計算式を書き込む.xlsx');

出力結果

12-計算式を書き込む_xlsx.png

シートに名前をつける

02-output-named-sheet.php
$book = new PHPExcel();
$book->getActiveSheet()->setTitle("シート1です");
$book->createSheet()->setTitle("二枚目!");
$book->createSheet()->setTitle("さんまいめだよ");

$writer = PHPExcel_IOFactory::createWriter($book, 'Excel2007');
$writer->save('output/02-シートに名前をつける.xlsx');

出力結果

02-シートに名前をつける_xlsx.png

xlsとxlsxを出力する

Excel 97〜Excel 2003のxls形式と、Excel 2007以降のxlsx形式を出力する方法です。

08-output-excel2003-and-excel2007.php
$book = new PHPExcel();
$sheet = $book->getActiveSheet();
$sheet->setCellValue('A1', 'テスト');

// xls: Excel97~2003
$writer2003 = PHPExcel_IOFactory::createWriter($book, 'Excel5');
$writer2003->save('output/08-excel2003.xls');

// xlsx: Excel2007~
$writer2007 = PHPExcel_IOFactory::createWriter($book, 'Excel2007');
$writer2007->save('output/08-excel2007.xlsx');

Excelをダウンロードできるようにする

出力したExcelをダウンロードできようにするには、HTTPヘッダにContent-Type: application/vnd.ms-excelContent-Dispositionを設定して、出力先をphp://outputにします。

09-output-and-download.php
$book = new PHPExcel();
$book->getActiveSheet()->setTitle('テスト');

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="output.xlsx"');
header('Cache-Control: max-age=0');

$writer = PHPExcel_IOFactory::createWriter($book, 'Excel2007');
$writer->save('php://output');

なお、日本語ファイル名でダウンロードさせる際は、User-Agent分岐無しに日本語ファイル名でファイルをダウンロードさせる (理論編)をご参考に。

範囲を指定して罫線を引く

10-output-bordered-table.php
$book = new PHPExcel();
$sheet = $book->getActiveSheet();
$sheet
    ->getStyle('A1:C3')
    ->getBorders()
    ->getAllBorders()
    ->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

// 色を指定する場合
$sheet
    ->getStyle('A5:C8')
    ->applyFromArray([
        'borders' => [
            'allborders' => [
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => ['rgb' => 'FF0000'],
            ],
        ],
    ]);

$writer = PHPExcel_IOFactory::createWriter($book, 'Excel2007');
$writer->save('output/10-罫線で表を描く.xlsx');

出力結果

10-罫線で表を描く_xlsx.png

テンプレートから出力する

SUMなどの数式や罫線などをあらかじめ仕込んだ「03-見積書テンプレート.xltx」というテンプレートを作って、テンプレートのセルに値を入れていく例です。

03-見積書テンプレート5_xltx.png

03-見積書テンプレート.xltxをダウンロード

03-output-using-template.php
$products = [
    ['Microsoft Excel 2016',      14000, 2],
    ['Microsoft Word 2016',       14800, 1],
    ['Microsoft PowerPoint 2016', 15000, 1],
];

$book = PHPExcel_IOFactory::load('templates/03-見積書テンプレート.xltx');
$sheet = $book->getActiveSheet();

$rowOffset = 3;
foreach ($products as $row => $product) {
    foreach ($product as $col => $value) {
        $sheet->setCellValueByColumnAndRow($col, $row + $rowOffset, $value);
    }
}

$writer = PHPExcel_IOFactory::createWriter($book, 'Excel2007');
$writer->save('output/03-テンプレートを使って出力.xlsx');

出力結果

03-テンプレートを使って出力_xlsx.png

大量の行を書いてみる

PHPExcelがどの程度メモリを使うのか調べてみます。エクセルは最大1,048,576行までも持てますが、今回は100,000行の範囲で、1行に「あ」を32文字ずつ書いてメモリ使用量を調査してみます。

04-output-large-excel.php
ini_set('memory_limit', '1024M');

$book = new PHPExcel();
$sheet = $book->getActiveSheet();
$startedOn = time();

for ($row = 1; $row <= 100000; $row++) {
    $sheet->setCellValueByColumnAndRow(0, $row, str_repeat('あ', 32));
    if ($row % 10000 === 0) {
        $writer2007 = PHPExcel_IOFactory::createWriter($book, 'Excel2007');
        $writer2007->save('output/04-large-excel.xlsx');
        $writer5 = PHPExcel_IOFactory::createWriter($book, 'Excel5');
        $writer5->save('output/04-large-excel.xls');
        rap($row, $startedOn);
    }
}

function rap($row, $startedOn)
{
    printf(
        "% 4d sec| rows: % 6d xlsx: % 4uKB xls: % 4uKB mem: %.02fMB\n",
        time() - $startedOn,
        $row,
        filesize('output/04-large-excel.xlsx') / 1024,
        filesize('output/04-large-excel.xls') / 1024,
        memory_get_usage(true) / 1024 / 1024
    );
}

結果

   0 sec| rows:  10000 xlsx:   57KB xls:  338KB mem: 19.00MB
   2 sec| rows:  20000 xlsx:  107KB xls:  673KB mem: 33.00MB
   4 sec| rows:  30000 xlsx:  158KB xls: 1007KB mem: 42.25MB
   7 sec| rows:  40000 xlsx:  208KB xls: 1342KB mem: 57.00MB
  11 sec| rows:  50000 xlsx:  258KB xls: 1677KB mem: 66.75MB
  16 sec| rows:  60000 xlsx:  309KB xls: 2011KB mem: 76.25MB
  23 sec| rows:  70000 xlsx:  359KB xls: 2284KB mem: 95.00MB
  31 sec| rows:  80000 xlsx:  409KB xls: 2481KB mem: 104.25MB
  42 sec| rows:  90000 xlsx:  460KB xls: 2678KB mem: 114.50MB
  56 sec| rows: 100000 xlsx:  510KB xls: 2875KB mem: 122.25MB

1万行あたり、10~20メガバイトほど消費するようです。

範囲を選択してコピー&ペースト

罫線・セル結合・値などが入ったテンプレート「07-交通費精算書テンプレート.xlsx」の明細を増やしていって、複数の交通費精算が記載されたエクセルを出力するサンプルです。下図がそのテンプレートです。

07-交通費精算書テンプレート_xlsx.png

07-交通費精算書テンプレート.xlsxをダウンロード

PHPExcelには範囲を選択してコピペするAPIがないため、PHPExcelで行のコピー : 時々、失業SEの開発日誌で紹介されているコードを活用します。

07-output-copy-paste-range.php
<?php
/**
 * 行を完全コピーする
 *
 * http://blog.kotemaru.org/old/2012/04/06.html より
 * @param PHPExcel_Worksheet $sheet
 * @param int $srcRow
 * @param int $dstRow
 * @param int $height
 * @param int $width
 * @throws PHPExcel_Exception
 */
function copyRows(
    PHPExcel_Worksheet $sheet,
    $srcRow,
    $dstRow,
    $height,
    $width
) {
    for ($row = 0; $row < $height; $row++) {
        // セルの書式と値の複製
        for ($col = 0; $col < $width; $col++) {
            $cell = $sheet->getCellByColumnAndRow($col, $srcRow + $row);
            $style = $sheet->getStyleByColumnAndRow($col, $srcRow + $row);

            $dstCell = PHPExcel_Cell::stringFromColumnIndex($col) . (string)($dstRow + $row);
            $sheet->setCellValue($dstCell, $cell->getValue());
            $sheet->duplicateStyle($style, $dstCell);
        }

        // 行の高さ複製。
        $h = $sheet->getRowDimension($srcRow + $row)->getRowHeight();
        $sheet->getRowDimension($dstRow + $row)->setRowHeight($h);
    }

    // セル結合の複製
    // - $mergeCell="AB12:AC15" 複製範囲の物だけ行を加算して復元。
    // - $merge="AB16:AC19"
    foreach ($sheet->getMergeCells() as $mergeCell) {
        $mc = explode(":", $mergeCell);
        $col_s = preg_replace("/[0-9]*/", "", $mc[0]);
        $col_e = preg_replace("/[0-9]*/", "", $mc[1]);
        $row_s = ((int)preg_replace("/[A-Z]*/", "", $mc[0])) - $srcRow;
        $row_e = ((int)preg_replace("/[A-Z]*/", "", $mc[1])) - $srcRow;

        // 複製先の行範囲なら。
        if (0 <= $row_s && $row_s < $height) {
            $merge = $col_s . (string)($dstRow + $row_s) . ":" . $col_e . (string)($dstRow + $row_e);
            $sheet->mergeCells($merge);
        }
    }
}

$book = PHPExcel_IOFactory::load('templates/07-交通費精算書テンプレート.xlsx');
$sheet = $book->getActiveSheet();

copyRows($sheet, 2, 7, 5, 5);
copyRows($sheet, 2, 7 + 5, 5, 5);
copyRows($sheet, 2, 7 + 10, 5, 5);
copyRows($sheet, 2, 7 + 15, 5, 5);

$writer = PHPExcel_IOFactory::createWriter($book, 'Excel2007');
$writer->save('output/07-交通費精算書.xlsx');

出力結果

07-交通費精算書_xlsx.png

ブック全体のデフォルトフォントスタイルを設定する

11-output-default-font-style.php
$book = new PHPExcel();
$book
    ->getDefaultStyle()
    ->getFont()
    ->setName('メイリオ')
    ->setSize(16)
    ->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_GREEN));

$book->getActiveSheet()->setCellValue('A1', 'シート1');
$book->createSheet()->setCellValue('A1', 'シート2');

$writer = PHPExcel_IOFactory::createWriter($book, 'Excel2007');
$writer->save('output/11-デフォルトのスタイル.xlsx');

出力結果

11-デフォルトのスタイル_xlsx.png

セル背景色を塗る

06-output-fill-cell-color.php
$image = imagecreatefrompng('images/mc.png');
$width = imagesx($image);
$height = imagesy($image);

$book = new PHPExcel();
$sheet = $book->getActiveSheet();

for ($x = 0; $x < $width; $x ++) {
    $sheet->getColumnDimensionByColumn($x)->setWidth(1);
    for ($y = 0; $y < $height; $y ++) {
        $color = vsprintf('%02x%02x%02x', imagecolorsforindex($image, imagecolorat($image, $x, $y)));
        $sheet->getRowDimension($y + 1)->setRowHeight(6);
        $sheet
            ->getStyleByColumnAndRow($x, $y + 1)
            ->getFill()
            ->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
            ->getStartColor()
            ->setRGB($color); // FFFFFFの16進数で色を指定する
    }
}

$writer = PHPExcel_IOFactory::createWriter($book, 'Excel2007');
$writer->save('output/06-セルの背景色.xlsx');

出力結果: ちゃんと塗れます。

06-セルの背景色_xlsx.png

生成したExcelをPDFに出力する

PHPExcel自体にはPDF生成ライブラリがないので、composerで次の3つのライブラリのうち、どれかをインストールしておく。

composer require tecnickcom/tcpdf
composer require dompdf/dompdf
composer require mpdf/mpdf

次のサンプルは、これら3種類のPDF出力ライブラリと、LibreOfficeのCUIを使ってPDFを生成するものです。

13-output-pdf.php
$products = [
    ['Microsoft Excel 2016',      14000, 2],
    ['Microsoft Word 2016',       14800, 1],
    ['Microsoft PowerPoint 2016', 15000, 1],
];

$book = PHPExcel_IOFactory::load('templates/13-見積書テンプレート.xlsx');
$sheet = $book->getActiveSheet();

$rowOffset = 3;
foreach ($products as $row => $product) {
    foreach ($product as $col => $value) {
        $sheet->setCellValueByColumnAndRow($col, $row + $rowOffset, $value);
    }
}

$excelWriter = PHPExcel_IOFactory::createWriter($book, 'Excel2007');
$excelWriter->save('output/13-excel.xlsx');

// tcPDF
PHPExcel_Settings::setPdfRenderer(
    PHPExcel_Settings::PDF_RENDERER_TCPDF,
    __DIR__ .'/vendor/tecnickcom/tcpdf'
);
$pdfWriter = PHPExcel_IOFactory::createWriter($book, 'PDF');
$pdfWriter->save('output/13-tcPDF.pdf');

// DomPDF
PHPExcel_Settings::setPdfRenderer(
    PHPExcel_Settings::PDF_RENDERER_DOMPDF,
    __DIR__ .'/vendor/dompdf/dompdf'
);
$pdfWriter = PHPExcel_IOFactory::createWriter($book, 'PDF');
$pdfWriter->save('output/13-Dompdf.pdf');

// mPDF
PHPExcel_Settings::setPdfRenderer(
    PHPExcel_Settings::PDF_RENDERER_MPDF,
    __DIR__ .'/vendor/mpdf/mpdf'
);
$pdfWriter = PHPExcel_IOFactory::createWriter($book, 'PDF');
$pdfWriter->save('output/13-mPDF.pdf');

// LibreOfficeでPDF化する
$soffice = '/Applications/LibreOffice.app/Contents/MacOS/soffice';
$outdir = __DIR__ . '/output';
$command = "$soffice --headless --convert-to pdf --outdir $outdir $outdir/13-excel.xlsx";
echo $command, PHP_EOL;
passthru($command);

出力結果

PDF変換前のExcelです:

13-excel.png

tcPDF:

13-tcPDF_pdf__1_page_.png

Dompdf:

13-Dompdf_pdf__1_page_.png

mPDF:

13-mPDF_pdf__1_page_.png

LibreOfficeのCUI:

13-excel_pdf__1_page_.png

tcPDF, Dompdf, mPDFについては日本語フォントをちゃんと指定すれば、文字化けせずに出力出来そうですが、やり方がよくわかりませんでした。Excelのシートに限りなく見た目を同じにしたい場合は、LibreOfficeのCUIを使うのが良さそうですが、式の計算結果が印字されないバグ?があるようです。

おわり

他にも追記していこうと思います。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away