今回はPHPでExcelファイルの取り扱う方法を紹介します。

今回はPHPでExcelファイルの取り扱う方法を紹介します。
PHPでにおいてExcelファイルの読み込み、書き込みは従来、PHPExcelがありましたが、これは非推奨になり、現在は、phpspreadsheeが後継です。

Contents

1 利便性
2 利用方法
3 まとめ

利便性

・Excelファイルの出力及び入力がかのうなので、画面入力ではできない情報を取り扱えます。
(Webシステム構築するしExcelファイルを取り扱う場合、画面入力を省くという方法をとる場合にメリットあります。入力が50項目以上など多数ある場合など)

・PDFの出力が難しい場合、Excel出力似て対応する。

・出力項目が多い場合、CSVより、Excel出力をすることによって加工が容易です。

・phpspreadsheetを利用すると、文字コードに左右されないです。

利用方法

インストール

今回はCakePHP3で利用して、composerでインストールをします

$ cd /topass/プロジェクトフォルダ
$ composer require phpoffice/phpspreadsheet

PHP拡張のチェック

以下のPHPを実行すると動作に必要なPHP拡張があるかチェックすることができます。
不足する場合はPHPの拡張をインストールします。

$ cd /topass/プロジェクトフォルダ
$ php vendor/phpoffice/phpspreadsheet/samples/index.php
Requirement check:
PHP 5.6.0 ... passed
PHP extension XML ... passed
PHP extension xmlwriter ... passed
PHP extension mbstring ... passed
PHP extension ZipArchive ... passed
PHP extension GD (optional) ... passed
PHP extension dom (optional) ... passed

インポート文

・・・
use Cake\Core\Configure;
use Cake\Http\Exception\ForbiddenException;
use Cake\Http\Exception\NotFoundException;
use Cake\View\Exception\MissingTemplateException;
・・・
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx as Writer;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx as Reader;
use PhpOffice\PhpSpreadsheet\Style;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
・・・

7行目：必須
8行目：出力用
9行目：読み込み用
10~12行目：スタイル用

Excelファイルのプロパティ制御


class ExcelController extends AppController {
・・・
public function excel01() {
  $spreadsheet = new Spreadsheet();
  //Set document properties
  $spreadsheet->getProperties()
    ->setTitle('タイトル')
    ->setSubject('サブタイトル')
    ->setCreator('作成者')
    ->setCompany('会社名')
    ->setManager('管理者')
    ->setCategory('分類')
    ->setDescription('コメント')
    ->setKeywords('キーワード');

  // Add some data

  $spreadsheet->setActiveSheetIndex(0)
    ->setCellValue('A1', 'Hello')
    ->setCellValue('B2', 'world!')
    ->setCellValue('C1', 'Hello')
    ->setCellValue('D2', 'world!');
  $sheet = $spreadsheet->getActiveSheet();
  $sheet
   ->setCellValue('A4', 10)
   ->setCellValue('A5', 5)
   ->setCellValue('A6', '=A4 + A5');

  $arrayData = [
    [NULL, 2016, 2017, 2018],
    ['Q1', 12, 15, 21],
    ['Q2', 56, 73, 86],
    ['Q3', 52, 61, 69],
    ['Q4', 30, 32, 0],
  ];
  $sheet->fromArray($arrayData, NULL, 'C3');
  // 第四引数をtrueにしないと緩い比較が行われて0がnullとして扱われるため空欄になるので注意
  $sheet->fromArray($arrayData, NULL, 'C9', true);

  $spreadsheet->getActiveSheet()
    ->setTitle('Simple');
  $writer = new Writer($spreadsheet);
  $writer->save(ROOT . '/file/excel01.xlsx');
}
・・・

ソースの説明

4行目：Spreadsheetオブジェクトを定義します。
6行目：Spreadsheetのプロパティを取得します。
7～14行目：取得したプロパティに値を設定します。
18~22行目：セルに値を入力しています。
23～27行目：セルに値を入れ、計算式を入力します。
29～38行目：配列で値を入力します。
40、41行目：シートに名前を付けます。
42行目：xlsxファイルを出力すためのwriteを取得します。
43行目：xlsxファイルを「プロジェクトディレクトリ/file」に出力します。

出力結果

読み込みと出力

    public function excel02() {

        $reader = new Reader();
        $spreadsheet = $reader->load(ROOT . '/file/excel01.xlsx');

        $sheet = $spreadsheet->getActiveSheet();

        $sheet->setCellValue('A6', '=A4 * A5');

        $writer = new Writer($spreadsheet);
        $writer->save(ROOT . '/file/excel02.xlsx');
    }

ソースの説明

3行目：リーダの定義
4行目：xlsxファイルの読み込み
6～8行目：シートを呼び出して計算式を変更
10，11行目：ファイルの書き込み

出力結果

A６の計算式が変わり値が変更される。

スタイルの変更

    public function excel03() {

        $reader = new Reader();
        $spreadsheet = $reader->load(ROOT . '/file/excel02.xlsx');

        $sheet = $spreadsheet->getActiveSheet();

        $sharedStyle1 = new Style\Style();
        $sharedStyle2 = new Style\Style();
        $sharedStyle1->applyFromArray(
                ['fill' => [
                        'fillType' => Fill::FILL_SOLID,
                        'color' => ['argb' => 'FFCCFFCC'],
                    ],
                    'borders' => [
                        'bottom' => ['borderStyle' => Border::BORDER_THIN],
                        'top' => ['borderStyle' => Border::BORDER_THIN],
                        'right' => ['borderStyle' => Border::BORDER_MEDIUM],
                        'left' => ['borderStyle' => Border::BORDER_MEDIUM],
                    ],
                    'font' => [
                        // フォント
                        'name' => 'ヒラギノ丸ゴ Pro',
                        // フォントサイズ
                        'size' => '12',
                    ],
                ]
        );

        $spreadsheet->getActiveSheet()->duplicateStyle($sharedStyle1, 'C3:F3');
        $spreadsheet->getActiveSheet()->duplicateStyle($sharedStyle1, 'C9:F9');

        $sharedStyle = new Style\Style();
        $sharedStyle->applyFromArray([
            'borders' => [
                'bottom' => ['borderStyle' => Border::BORDER_THIN],
                'top' => ['borderStyle' => Border::BORDER_THIN],
                'right' => ['borderStyle' => Border::BORDER_MEDIUM],
                'left' => ['borderStyle' => Border::BORDER_MEDIUM],
            ],
            'font' => [
                // フォント
                'name' => 'ヒラギノ丸ゴ Pro',
                // フォントサイズ
                'size' => '10',
            ],
        ]);

        $spreadsheet->getActiveSheet()->duplicateStyle($sharedStyle, 'C4:F7');
        $spreadsheet->getActiveSheet()->duplicateStyle($sharedStyle, 'C10:F13');

        $writer = new Writer($spreadsheet);
        $writer->save(ROOT . '/file/excel03.xlsx');
    }

}