Zenn
🍑

[PHP]PHPExcelとPhpSpreadsheetをサンプルコードで比較

2022/03/03に公開
PHP
リプレイス
PHPExcel
PhpSpreadsheet
tech

コード

Excelのテンプレートファイルをもとに新規ファイルを作成し、グループごとに表示させる内容（覚書きです）

PHPExcel, PhpSpreadsheet各ライブラリの必要条件等については、以下に記載してます
[PHP]PHPExcelとPHPSpreadsheetの比較

PHPExcel

samplePhpExcel.php
<?php

// ライブラリの読み込み
require_once('/usr/lib/PHPExcel/Classes/PHPExcel.php');
require_once('/usr/lib/PHPExcel/Classes/PHPExcel/IOFactory.php');

// タイムゾーン設定
date_default_timezone_set('Asia/Tokyo');

// テンプレートファイルと作成ファイルに関する設定
$template = 'test0202.xlsx';
$newfile = 'test0202_' . date("Ymd") . '_excel.xlsx';
$tempsheet = 'n.xxxのデータ';
$fileType = 'Excel2007';

// 出力データ: 配列のこの形式はPHP5系では使えないので注意
$array = [
    'male' => [
        ['id' => '1', 'name' => 'Jean'],
        ['id' => '2', 'name' => 'Bill'],
        ['id' => '4', 'name' => 'Michael'],
    ],
    'female' => [
        ['id' => '3', 'name' => 'Julia'],
        ['id' => '5', 'name' => 'Anna'],
    ],
];

// テンプレートファイルのロード
$reader = PHPExcel_IOFactory::createReader($fileType);
$excel = $reader->load($template);

// グループごとに出力内容のセッティング
foreach ($array as $key => $values) {
    $sheetCount = $excel->getSheetCount();
    $copiedWorksheet = $excel->getSheetByName($tempsheet)->copy();
    $copiedWorksheet->setTitle($targetSheet = str_replace('n', $sheetCount, str_replace('xxx', $key, $copiedWorksheet->getTitle())));
    $excel->addSheet($copiedWorksheet);
    $sheet = $excel->getSheetByName($targetSheet);

    for ($i = 0; $i < count($values); $i++) {
        echo $i . "\n";
        echo $values[$i]['id'] . "\n";
        echo $values[$i]['name'] . "\n";
        // PHPExcelは、列と行でセル指定する場合、列のみ0始まりのようだ（わかりづらい・・）
        $sheet->setCellValueByColumnAndRow(1, $i + 3, $values[$i]['id']);
        $sheet->setCellValueByColumnAndRow(2, $i + 3, $values[$i]['name']);
    }
    
    // 罫線を引く: PHPExcelだと、うまく罫線が引けなかった
    $max_row = $sheet->getHighestRow();
    $max_col = $sheet->getHighestColumn();
    $maxCellAddress = $max_col . $max_row;
    $sheet->getStyle("B2:{$maxCellAddress}")->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
    // A1セルを選択
    $sheet->setSelectedCell('A1');
    /* A1セルを選択（列・行指定の場合
    $sheet->setSelectedCellByColumnAndRow(0, 1);
    */
}

// テンプレートシートの削除
$sheetIndex = $excel->getIndex(
    $excel->getSheetByName($tempsheet)
);
$excel->removeSheetByIndex($sheetIndex);

// 先頭のシートを選択
$excel->setActiveSheetIndex(0);

// Excelファイルへの書き込み
$writer = PHPExcel_IOFactory::createWriter($excel, $fileType);
$writer->save($newfile);

PhpSpreadsheet

samplePhpSpreadsheet.php
<?php
// ライブラリ群のautoload.phpの読み込み
require_once('/usr/lib/phpspreadsheet/autoload.php');

use PhpOffice\PhpSpreadsheet\Reader\Xlsx as XlReader;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx as XlWriter;
use PhpOffice\PhpSpreadsheet\Style\Border as Border;
use PhpOffice\PhpSpreadsheet\Calculation\Calculation as Calculation;

// タイムゾーン設定
date_default_timezone_set('Asia/Tokyo');

// テンプレートファイルと作成ファイルに関する設定
$template = 'test0202.xlsx';
$newfile = 'test0202_' . date("Ymd") . '_spread.xlsx';
$tempsheet = 'n.xxxのデータ';

// 出力データ
$array = [
    'male' => [
        ['id' => '1', 'name' => 'Jean'],
        ['id' => '2', 'name' => 'Bill'],
        ['id' => '4', 'name' => 'Michael'],
    ],
    'female' => [
        ['id' => '3', 'name' => 'Julia'],
        ['id' => '5', 'name' => 'Anna'],
    ],
];

// テンプレートファイルのロード
$reader = new XlReader();
$spreadsheet = $reader->load($template);
// 計算キャッシュの無効化: テンプレートに埋め込んだ関数が計算されて表示されるようにするため　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　
Calculation::getInstance($spreadsheet)->disableCalculationCache();

// グループごとに出力内容のセッティング
foreach ($array as $key => $values) {
    $sheetCount = $spreadsheet->getSheetCount();
    $clonedWorksheet = clone $spreadsheet->getSheetByName($tempsheet);
    $clonedWorksheet->setTitle($targetSheet = str_replace('n', $sheetCount, str_replace('xxx', $key, $clonedWorksheet->getTitle())));
    $spreadsheet->addSheet($clonedWorksheet);
    $sheet = $spreadsheet->getSheetByName($targetSheet);
    for ($i = 0; $i < count($values); $i++) {
        echo $i . "\n";
        echo $values[$i]['id'] . "\n";
        echo $values[$i]['name'] . "\n";
        $sheet->setCellValueByColumnAndRow(2, $i + 3, $values[$i]['id']);
        $sheet->setCellValueByColumnAndRow(3, $i + 3, $values[$i]['name']);
    }
    $max_row = $sheet->getHighestRow();
    $max_col = $sheet->getHighestColumn();
    $maxCellAddress = $max_col . $max_row;
    $sheet->getStyle("B2:{$maxCellAddress}")->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
    // A1セルを選択
    $sheet->setSelectedCell('A1');
    /* A1セルを選択（列・行指定の場合
    $sheet->setSelectedCellByColumnAndRow(1, 1);
    */
}

// テンプレートシートの削除
$sheetIndex = $spreadsheet->getIndex(
    $spreadsheet->getSheetByName($tempsheet)
);
$spreadsheet->removeSheetByIndex($sheetIndex);

// 先頭のシートを選択
$spreadsheet->setActiveSheetIndex(0);

// Excelファイルへの書き込み
$writer = new XlWriter($spreadsheet);
$writer->save($newfile);

※本コードで使用するテンプレートには、関数の埋め込みはないですが、ある場合を加味しての内容にしています

バージョンによって、上記コードの関数が変更されたり、非推奨になったりする可能性があるので、コーディングの際は、GitHubのTagsと使用しているPhpSpreadsheetのバージョンを照合すること

わかったこと

(1) 関数名はほぼ同じで、そこまで同じ内容の実装に苦労しなかった

(2) PHPExcelは以下を留意する必要あり
① セルを列, 行指定する場合に、列は0始まり、行は1始まりのようだ
② 罫線がうまく引けなかった（PhpSpreadSheetでも同様の事象はあるらしいが）

(3) PhpSpreadsheetは以下を留意する必要あり
① 関数を埋め込んだテンプレートファイルを使う場合は計算キャッシュを無効化にする設定が必要
② Excelテンプレートが背景色付きの場合、うまくコピーされない場合がある（PhpSpreadsheetではExcelの背景色の読み取りまではサポートしていない）

(4) PHPExcel→PhpSpreadsheet以降の際は、Automated toolを使う、でもよさそう

参考

PHPExcel

PhpSpreadsheet

PhpSpreadsheetでテンプレートに埋め込んだ関数が計算されない問題

PhpSpreadsheetでテンプレートの背景色がうまくコピーされない問題

履歴

  • 2022/09/27: PhpSpreadsheetでの問題と解決策を追記
  • 2022/09/29: コードの微修正とPhpSpreadsheetに関する留意点、参考ページの追記

Discussion