Excelファイルを出力する「PHPExcel」をCakePHP2.3.xで使う方法

2013/09/03 2019/05/30

既存のExcelフォーマットへ、CakePHPで作成（蓄積）したデータを流しこむという処理があったので、PHPExcelを使ってみました。
今回もまた、忘備録を兼ねたポストです。

PHPExcelを使う

PHPExcelとは

PHPExcelとは、PHPでExcelファイルの加工・出力を行うためのフリーコンポーネントです。
Excel97-2003にも対応はしていますが、Excel2007に力を入れたコンポーネントのようです。

とのことで、PHPでExcelファイルを扱いたい、てな場面で活躍するコンポーネントと考えて下さい。

ダウンロード・設置

ダウンロード

PHPExcel - Home

上記サイト、右の方の｢DOWNLOAD｣ボタンよりダウンロードします。

この記事を書いた時点でのバージョンは、1.7.9でした。

解凍・設置

書庫ファイルを解凍します。

上記は解凍したイメージ。
Classes 以下のフォルダ及びファイルを使用します。

今回は、CakePHP2.3.6で動作させるので、Classes 以下のフォルダ及びファイルを CakePHP の Vendorディレクトリへコピーします。

コピー後のイメージは上記の感じで。

コントローラー

宣言

使用するコントローラーで以下の宣言を行います。
古いバージョンでも動いてほしい、要するに拡張子が「xls」のファイルが良い！との要請があったので、Excel95用を読み込ませて使用します。

ちなみに、拡張子と対応するReadrを表にすると、以下となります。

拡張子	対応するReadr
xls	PHPExcel_Reader_Excel5
xlsx	PHPExcel_Reader_Excel2007
ods	PHPExcel_Reader_OOCalc
slk	PHPExcel_Reader_SYLK
xml	PHPExcel_Reader_Excel2003XML
gnumeric	PHPExcel_Reader_Gnumeric
csv	-

※PHPExcel-1.7 テンプレートファイルの読み込み PHPExcel_IOFactory::createReader PHPExcel_IOFactory::load
あたもこ様より転載。

今回は UsersController.php で使う事とします。

app/Controller/UsersController.php

<?php
// Excel出力用ライブラリ
App::import( 'Vendor', 'PHPExcel', array('file'=>'phpexcel' . DS . 'PHPExcel.php') );
App::import( 'Vendor', 'PHPExcel_IOFactory', array('file'=>'phpexcel' . DS . 'PHPExcel' . DS . 'IOFactory.php') );
App::import( 'Vendor', 'PHPExcel_Cell_AdvancedValueBinder', array('file'=>'phpexcel' . DS . 'PHPExcel' . DS . 'Cell' . DS . 'AdvancedValueBinder.php') );
// Excel95用ライブラリ
App::import( 'Vendor', 'PHPExcel_Writer_Excel5', array('file'=>'phpexcel' . DS . 'PHPExcel' . DS . 'Writer' . DS . 'Excel5.php') );
App::import( 'Vendor', 'PHPExcel_Reader_Excel5', array('file'=>'phpexcel' . DS . 'PHPExcel' . DS . 'Reader' . DS . 'Excel5.php') );

class UsersController extends AppController
{
    // モデル
    var $uses = array('User');

	以下略
}

<?php

// Excel出力用ライブラリ

App::import( 'Vendor', 'PHPExcel', array('file'=>'phpexcel' . DS . 'PHPExcel.php') );

App::import( 'Vendor', 'PHPExcel_IOFactory', array('file'=>'phpexcel' . DS . 'PHPExcel' . DS . 'IOFactory.php') );

App::import( 'Vendor', 'PHPExcel_Cell_AdvancedValueBinder', array('file'=>'phpexcel' . DS . 'PHPExcel' . DS . 'Cell' . DS . 'AdvancedValueBinder.php') );

// Excel95用ライブラリ

App::import( 'Vendor', 'PHPExcel_Writer_Excel5', array('file'=>'phpexcel' . DS . 'PHPExcel' . DS . 'Writer' . DS . 'Excel5.php') );

App::import( 'Vendor', 'PHPExcel_Reader_Excel5', array('file'=>'phpexcel' . DS . 'PHPExcel' . DS . 'Reader' . DS . 'Excel5.php') );

class UsersController extends AppController

{

// モデル

var $uses = array('User');

以下略

}

読めばわかると思いますが、app/Vendor/phpecel/PHPExcel/ 以下の各ファイルへのパスを設定しているだけです。

データ取得

使用したいアクション内で、以下のように処理していきます。

まずは出力データの取得から。

// データを取得
$users = $this->User->find( 'all',
 略
);

// データを取得

$users = $this->User->find( 'all',

略

);

テンプレ読み込み

bootstrap.php にてパスなどを define で定義しています。
define 内容は以下のようにしてみました。

// テンプレ置き場
define('EXCEL_SHEET_PATH', WWW_ROOT . 'files/excel_sheet/');

// 出力パス
define('OUTPUT_EXCEL_SHEET_PATH', WWW_ROOT . 'users/output_excel_sheet/');

// テンプレ置き場

define('EXCEL_SHEET_PATH', WWW_ROOT . 'files/excel_sheet/');

// 出力パス

define('OUTPUT_EXCEL_SHEET_PATH', WWW_ROOT . 'users/output_excel_sheet/');

本処理に戻り、使用するテンプレ、出力するファイル名や場所などを指定。

// EXCELシート（テンプレ）読み込み
$_read_sheet = EXCEL_SHEET_PATH . "template_users.xls";

// 年_月ディレクトリがあるか調査、無ければ作成
$ym_dirname = date("Y") . '_' . date("m");
if ( !file_exists(OUTPUT_EXCEL_SHEET_PATH . 'user/' . $ym_dirname) ) {
    mkdir(OUTPUT_EXCEL_SHEET_PATH . 'users/' . $ym_dirname, 0707);
}

// ファイル名「年月」
$output_file_name = "USER_REPORT_" . date("YmdHis", time());

// 出力ファイル名設定
$output_file = $output_file_name . ".xls";

// 出力パスを含む、ファイル名
$output_file_path = 'user/' . $ym_dirname . '/' . $output_file;

// 文字コード変換
$output_file = mb_convert_encoding($output_file, "SJIS", "UTF-8");

// テンプレを読み込んで、PHPExcelオブジェクトを生成する。
$reader = PHPExcel_IOFactory::createReader('Excel5');
$xl = $reader->load("$_read_sheet");

// EXCELシート（テンプレ）読み込み

$_read_sheet = EXCEL_SHEET_PATH . "template_users.xls";

// 年_月ディレクトリがあるか調査、無ければ作成

$ym_dirname = date("Y") . '_' . date("m");

if ( !file_exists(OUTPUT_EXCEL_SHEET_PATH . 'user/' . $ym_dirname) ) {

mkdir(OUTPUT_EXCEL_SHEET_PATH . 'users/' . $ym_dirname, 0707);

}

// ファイル名「年月」

$output_file_name = "USER_REPORT_" . date("YmdHis", time());

// 出力ファイル名設定

$output_file = $output_file_name . ".xls";

// 出力パスを含む、ファイル名

$output_file_path = 'user/' . $ym_dirname . '/' . $output_file;

// 文字コード変換

$output_file = mb_convert_encoding($output_file, "SJIS", "UTF-8");

// テンプレを読み込んで、PHPExcelオブジェクトを生成する。

$reader = PHPExcel_IOFactory::createReader('Excel5');

$xl = $reader->load("$_read_sheet");

シートの選択

$xl->setActiveSheetIndex(0);		// 0番目のシートを選択
$sheet = $xl->getActiveSheet();		// 選択シートの情報取得

1 2	$xl->setActiveSheetIndex(0); // 0番目のシートを選択 $sheet = $xl->getActiveSheet(); // 選択シートの情報取得

セルへ書込み

// セル「A1」に「エクセルファイル」という文字列を書き込み
$objSheet->setCellValue("A1", "エクセルファイル");

// セル「B2」に今日の日付を挿入
$objSheet->setCellValue("B2", date("Y/m/d")); 

$line = 3;
foreach ( $users AS $key => $val ) {

	// セル「A3」に ID を挿入
	$sheet->setCellValue("A".$line, Sanitize::clean($val['id']));

	// セル「B3」に UserName を挿入
	$sheet->setCellValue("B".$line, Sanitize::clean($val['username']));

	$line++;
}

// セル「A1」に「エクセルファイル」という文字列を書き込み

$objSheet->setCellValue("A1", "エクセルファイル");

// セル「B2」に今日の日付を挿入

$objSheet->setCellValue("B2", date("Y/m/d"));

$line = 3;

foreach ( $users AS $key => $val ) {

// セル「A3」に ID を挿入

$sheet->setCellValue("A".$line, Sanitize::clean($val['id']));

// セル「B3」に UserName を挿入

$sheet->setCellValue("B".$line, Sanitize::clean($val['username']));

$line++;

}

ファイル保存

// xls形式で保存
$writer = PHPExcel_IOFactory::createWriter($xl, 'Excel5');

// 指定パスに出力
$writer->save(OUTPUT_EXCEL_SHEET_PATH . $output_file_path);

// xls形式で保存

$writer = PHPExcel_IOFactory::createWriter($xl, 'Excel5');

// 指定パスに出力

$writer->save(OUTPUT_EXCEL_SHEET_PATH . $output_file_path);

これで指定したパス、今回だと

public_html/users/output_excel_sheet/user/2013_09/USER_REPORT_20130901130545.xls

に保存されます。

参考にしたサイト様

◆PHPExcelで、PHPからExcelを作成・操作する | Styler

◆PHPExcel-1.7 テンプレートファイルの読み込み PHPExcel_IOFactory::createReader PHPExcel_IOFactory::load

-CakePHP, Excel
-cakephp2, excel, phpexcel, エクセル

: CakePHP2.4以降（2.6.x）でファイルをダウンロードする方法

CakePHP2.6でPHPExcelを使い、データベースのレコードをエクセルファイルとしてダウンロードしようとすると、CakePHP2.3まで使えていた方法（メディアビュー）が使えなくなっていました。

ちょっと戸惑ったので、備忘録としてポストします。

: 【CakePHP2】RFC準拠違反のメールアドレス宛にメール送信する方法

CakePHP2（Ver2.3.10）のCakeEmailを利用してメールフォームを作成しているのですが、ある日、携帯アドレス宛にメール送信する際に500エラーが出るとの申告を受けました。
よくよく聞いてみると、送信先のメールアドレスというのが、RFC準拠違反の、ezweb.ne.jp宛。

他方、CakePHPの方を調査すると、どうやらCakeEmailではメールアドレスをセットした際にバリデーションチェック（Validation::email()）を行い、RFC非準拠のアドレス宛へは送信せず「SocketException」が返る仕様の様子。エラーログにも確かに「SocketException」が記録されていました。

: CakePHP2.xのFormヘルパーを使ってのラジオボタンの作り方。

超基本的な事ですが、CakePHP2.x のFormヘルパーでラジオボタンを作る方法。
アンチョコ化してコピペで作れるよにしておいてやる。

: CakePHP2.xでログローテーションする方法。

CakePHP2.xで、エラーログやデバッグログを日付ごとに出したいと思ったので挑戦したところ、思いの外簡単にできてしまったので、忘備録としてポストです。

: CakePHP2.xで取得したXMLを配列化する方法。

サブジェクト通り、取得したXMLを配列化する方法のメモ。
CakePHP 2.3.10で試しました。

PREV: Nexus7(2013)用多機能ケースが届いたので装着してみた。
NEXT: CakePHP2.3.Xでエクセルファイルなどをダウンロードさせる方法。

Excelファイルを出力する「PHPExcel」をCakePHP2.3.xで使う方法

PHPExcelを使う

PHPExcelとは

ダウンロード・設置

ダウンロード

解凍・設置

コントローラー

宣言

データ取得

テンプレ読み込み

シートの選択

セルへ書込み

ファイル保存

参考にしたサイト様

【DIY】2x4や1x4など、SPF材サイズ早見表 (564,958)

【知識】電話番号の+81の意味と固定電話・携帯電話番号の書き方 (279,676)

TPPやLCCなど、時事や普段使っている「アルファベット略語」を集めてみた (257,916)

【歴史】江戸幕府歴代将軍一覧 (143,588)

【知識】牛肉の部位「おっぱい」は本当に乳房なのかが気になったので調べてみた (133,242)

【Excel】ダブルクォーテーションでデータを囲んだCSVファイルの作成方法 (131,306)