PHPExcelで.xls形式のファイルを扱う

PHPExcelで.xls形式のファイルを扱う

イメージ

こんにちは、「ザ・ビートルズ・ボックス」を買おうかまだ迷ってるinoueです。
シーブレインのそばにあるディスクユニオンさんが昨日は屋外にビートルズリマスター盤特設売り場を設けていたり、ちょっとしたお祭り騒ぎですね。

さて、今回はExcelファイルとPHPでのデータの受け渡しを行うCakePHP Componentを作ったので、その概略をお話したいと思います。

外部ライブラリとして使うのはPHPExcel。
こちらはExcel2007形式ファイル(拡張子が.xlsx）との入出力をメインに作られたライブラリですが、Excel2003以前の.xls形式のファイルを扱うクラスも用意されています。
そこで、 今回はその機能を使い.xls形式のファイルを読み書きを行います。

手順は下記の通り。

1. ライブラリの配置

  • /home/sample
    • app
      • controllers
        • components
          • excel.php
      • views
        • components
          • index.ctp
    • vendors
      • phpexcel
        • Classes
          • PHPExcel
          • PHPExcel.php
        • phpexcel.php

/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用入出力コンポーネントを用意しておくと、様々なアプリケーションで活用することができるかと思います。

私自身も作成したコンポーネントをもう少し改良して、今後公開しようと思います。

参考情報

  • このエントリーをはてなブックマークに追加

この記事を読んだ人にオススメ

Copyright © C-brains Corporation. all rights reserved.