PHPExcelで.xls形式のファイルを扱う
こんにちは、「ザ・ビートルズ・ボックス」を買おうかまだ迷ってるinoueです。
シーブレインのそばにあるディスクユニオンさんが昨日は屋外にビートルズリマスター盤特設売り場を設けていたり、ちょっとしたお祭り騒ぎですね。
さて、今回はExcelファイルとPHPでのデータの受け渡しを行うCakePHP Componentを作ったので、その概略をお話したいと思います。
外部ライブラリとして使うのはPHPExcel。
こちらはExcel2007形式ファイル(拡張子が.xlsx)との入出力をメインに作られたライブラリですが、Excel2003以前の.xls形式のファイルを扱うクラスも用意されています。
そこで、 今回はその機能を使い.xls形式のファイルを読み書きを行います。
手順は下記の通り。
1. ライブラリの配置
- /home/sample
- app
- controllers
- components
- excel.php
- components
- views
- components
- index.ctp
- components
- controllers
- app
- vendors
- phpexcel
- Classes
- PHPExcel
- PHPExcel.php
- phpexcel.php
- Classes
- phpexcel
/home/sample内にCakePHPアプリケーションファイル一式を配置したとして話を進めます。
- vendors/phpexcelにPHPExcel本体を設置
- vendors/phpexcel/phpexcel.phpの作成
- ExcelComponentの作成
- 呼び出し元コントローラの作成
の順に解説していきます。
2. vendors/phpexcelにPHPExcel本体を設置
PHPExcelを本家サイトのDownloadsより入手します。
「1.7.0.zip」のように、バージョン番号がファイル名になっています。
解凍するとフォルダ名もやっぱりバージョン番号なので、トップフォルダを「phpexcel」にリネームして、vendors/下に配置します。
3. vendors/phpexcel/phpexcel.phpの作成
次に、PHPExcelにパスを通し、コンポーネント等から呼び出すための設定をphpexcel.phpを新規作成してまとめます。
<?php
/*
* PHPExcel
*/
ini_set('include_path', get_include_path() . PATH_SEPARATOR . VENDORS. "phpexcel" . DS . "Classes");
/** Main Class */
require_once 'Classes/PHPExcel.php';
/** IO Class */
require_once 'Classes/PHPExcel/IOFactory.php';
?>
4. ExcelComponentの作成
ExcelComponentの全体構成はこんな感じ。
<?php
class ExcelComponent extends Object {
var $errors = array();
/**
* readXls - Excelファイルを読み込む
*/
function readXls($filepath, $colCount = null, $rowCount = null, $sheetIndex = null)
{
}
/**
* write - Excelファイルに書き込む
*
* @param string $filepath テンプレートファイルのパス
* @param array $data
* @param boolean $is_copy $filepathを テンプレートにしてコピーファイルを作るか
*/
function writeXls($filepath, $data = array(), $is_copy = true)
{
}
/**
* import - Excelファイルを読み込む
*/
function import($file, $is_move_file = false, $destination = '', $type = "xls")
{
}
/**
* 指定したセルの文字列を取得する
*/
function _getText($objCell = null)
{
}
}
?>
Excelの読み書きメソッドと、xlsファイルに特化したファイルインポートメソッド(今回は省略)などで構成するシンプルなものです。
各メソッドの処理について、ここからは説明します。
まずは読み込みメソッド。
引数で渡されたファイルの内容を読み取り、配列にして返します。
引数で指定しない限り、全シートの全データ範囲を読み込み対象として処理する仕様となっています。
/**
* readXls - Excelファイルを読み込む
*
*
* @param string $filepath
* @param int $colCount (Optional)
* @param int $rowCount (Optional)
* @param mix $sheetIndex (Optional) 読み込み対象
* @return array 読み込んだデータ
*/
function readXls($filepath, $colCount = null, $rowCount = null, $sheetIndex = null)
{
//include the vendor class
App::import('vendor','phpexcel/phpexcel');
//ファイルを読み込む
$objReader = PHPExcel_IOFactory::createReader('Excel5');
$objPHPExcel = $objReader->load($filepath);
//シートオブジェクトの取得
$sheets = array();
if (is_null($sheetIndex)) {
//すべて
$sheets = $objPHPExcel->getAllSheets();
} elseif (is_array($sheetIndex)) {
foreach($sheetIndex as $idx) {
$sheets[$idx] = $objPHPExcel->getSheet($idx);
}
} elseif (is_int($sheetIndex)) {
$sheets[$sheetIndex] = $objPHPExcel->getSheet($sheetIndex);
}
$data = array();
if (empty($sheets)) {
return $data;
}
//1シートごと処理
foreach ($sheets as $s => $objSheet) {
//シート名の取得
$sheetTitle = $objSheet->getTitle();
$data[$s]['title'] = $sheetTitle;
//データ領域を確認
$rowMax = $rowCount;
if (is_null($rowCount)) {
$rowMax = $objSheet->getHighestRow();
}
$colMax = $colCount;
if (is_null($colCount)) {
$colMax = $objSheet->getHighestColumn();
}
//1セルごとにテキストデータを取得
$sheetData = array();
for($r=1; $r<=$rowMax; $r++) { //rowは1はじまり
for($c=0; $c<=$colMax; $c++) { //colは0はじまり 0 = Aとなる
$objCell = $objSheet->getCellByColumnAndRow($c, $r);
$sheetData[$r][$c]= $this->_getText($objCell);
}
}
$data[$s]['data'] = $sheetData;
}
return $data;
}
セルのテキストデータを取得するところがちょっと複雑ですので要注意。
書式設定がなされたセルの文字列は、getValue()メソッドでは文字列のみ取得できません。
その部分への対応を追加した _getText()メソッドを作成し、テキストデータのみを取得しています。
/**
* 指定したセルの文字列を取得する
*
* 色づけされたセルなどは cell->getValue()で文字列のみが取得できない
* また、複数の配列に文字列データが分割されてしまうので、その部分も連結して返す
*
*
* @param $objCell Cellオブジェクト
*/
function _getText($objCell = null)
{
if (is_null($objCell)) {
return false;
}
$txtCell = "";
//まずはgetValue()を実行
$valueCell = $objCell->getValue();
if (is_object($valueCell)) {
//オブジェクトが返ってきたら、リッチテキスト要素を取得
$rtfCell = $valueCell->getRichTextElements();
//配列で返ってくるので、そこからさらに文字列を抽出
$txtParts = array();
foreach ($rtfCell as $v) {
$txtParts[] = $v->getText();
}
//連結する
$txtCell = implode("", $txtParts);
} else {
if (!empty($valueCell)) {
$txtCell = $valueCell;
}
}
return $txtCell;
}
そして出力メソッド。
引数で指定したxlsファイルをテンプレートとして使用し、そこにデータを書き込んで出力ファイルを作成します。
データ配列の形式はReadXls()の返り値に対応しています。
/**
* write - Excelファイルに書き込む
*
* @param string $filepath テンプレートファイルのパス
* @param array $data
* @param boolean $is_copy $filepathを テンプレートにしてコピーファイルを作るか
*/
function writeXls($filepath, $data = array(), $is_copy = true)
{
//include the vendor class
App::import('vendor','phpexcel/phpexcel');
//ファイルを読み込む
$objReader = PHPExcel_IOFactory::createReader('Excel5');
$objPHPExcel = $objReader->load($filepath);
// set active sheet
foreach ($data as $s => $rows) {
$objPHPExcel->setActiveSheetIndex($s);
$sheet = $objPHPExcel->getActiveSheet();
foreach ($rows['data'] as $r => $cols) {
foreach ($cols as $c => $v) {
// update cell
$sheet->setCellValueByColumnAndRow($c, $r+1, $v);
}
}
}
if ($is_copy) {
$target_filepath = TMP . "output_". date("YmdHis") .".xls"; //ファイル名生成
} else {
$target_filepath = $filepath;
}
//保存先のデータはパスとファイル名に分離が必要
$target_basename = basename($target_filepath);
$target_dir = dirname($target_filepath);
// output excel file
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->setTempDir($target_dir);
$objWriter->save($target_filepath);
return $target_filepath;
}
5. 呼び出し元コントローラの作成
コンポーネントが完成したら、さっそくコントローラから呼び出して使ってみます。
class SampleController extends AppController
{
var $components = array('Excel');
/**
* ダウンロード
*/
function download()
{
//出力データ配列の生成
$writeData = array(...);
//Excelに出力
$return = $this->Excel->writeXls(WWW_ROOT . 'files/templates.xls', $writeData);
//エラーチェック (省略)
//ファイルができた
//ダウンロード開始
$media_id = basename($return);
$media_name = substr($media_id, 0, strlen($media_id) - 4);
$this->view ='media';
$params = array(
'id' => $media_id,
'name' => $media_name,
'download' => true,
'extension' => 'xls',
'path' => dirname($return) . DS,
'mime' => "application/vnd.ms-excel"
);
$this->set($params);
}
/**
* アップロード
*/
function upload()
{
// 一括アップロードファイル確認
$tmp_path = TMP . 'upload_file.xls';
$filename = $this->Excel->import($this->data['upload_file'], true, $tmp_path);
if (!empty($this->Excel->errors)) {
$this->Session->write('error_message', $this->Excel->errors);
$this->redirect("/sample/index");
}
//データの読み込み
$readData = $this->Excel->readXls($filename,11);
//読み込み後の処理 (省略)
}
まとめ
以上のような流れで、汎用的なExcel用入出力コンポーネントを用意しておくと、様々なアプリケーションで活用することができるかと思います。
私自身も作成したコンポーネントをもう少し改良して、今後公開しようと思います。