【ぇ、使えるの!?】PHPExcel＋マクロでグラフ付きExcelを出力

PHPExcelでゼロからグラフを書いてみよう
PHPExcelでテンプレートファイルからグラフを作ってみよう
の最終章

Excel_Reviserが終了し、xlsしか出力できないので、
xlsxで出力したい場合、PHPでExcelをいじる場合はPHPExcelを使うしかない。

PHPExcelでグラフを出力するための備忘録。

PHPExcelバージョン：1.8.1
Excel:2013
PHP:5.5.27

考えられるパターン

1. テンプレートファイル使わない
2. テンプレートファイルを使う -> グラフをあらかじめ埋め込んでいるもの
3. テンプレートファイルを使う -> マクロ埋め込み

**「テンプレートファイルを使う -> マクロ埋め込み」**で行ってみましょう。

（そもそも）ぇ、マクロ使えるの？

「PHPExcel マクロ」とかで検索すると、だいたい「マクロが消える」「losing macros」とかに引っかかり最初から断念しがち。

しかし！なんと公式にこんなISSUEが
http://phpexcel.codeplex.com/workitem/19017

どっかの頭いい人がPHPExcelでテンプレート読み込み時にVBAを切り離し、
書き出すときにいい感じにマージする機能をバージョン1.7.8から追加してくれたご様子。

つまり、VBAさえ分かれば、ほぼお望みのExcelをPHPから書き出せる。
VBAさえ分かれば！
(VBAさえ分かれば...)
(VBAさえ分かれば...)(VBAさえ分かれば...)
(VBAさえ分かれば...)(VBAさえ分かれば...)(VBAさえ分かれば...)

流れ

1. テンプレートとなるマクロ付きExcelファイルをPHPExcelで読み込む
2. グラフのデータとなる表にPHPExcelで値を埋める
3. PHPExcelで出力

読み込み、書き出し時ともに「～.xlsm」になるので注意。

テンプレートの作成

今回は、以下の画像のように、右の表から左のグラフを作成するマクロを作って、Excelに載せておく
このグラフ、PHPExcelのchartじゃ書けません（2軸とか
値は消しておいた方が分かりやすいですよ（値の埋め忘れに気づきやすくするため

グラフ描画用マクロ

Sub draw_chart()
    Worksheets("Sheet1").Select
    Set Rng = Range("B6:J26")
    Dim chartObj As ChartObject

    Set chartObj = ActiveSheet.ChartObjects.Add(Rng.Left, Rng.Top, Rng.Width, Rng.Height)
    chartObj.Chart.SetSourceData Sheets("Sheet1").Range("M13:N26")
    chartObj.Chart.FullSeriesCollection(1).XValues = "=Sheet1!$L$14:$L$26"

    chartObj.Chart.Legend.Position = xlLegendPositionBottom
    
    chartObj.Chart.FullSeriesCollection(2).AxisGroup = 2
    chartObj.Chart.FullSeriesCollection(2).ChartType = xlLineMarkersStacked
    
    chartObj.Chart.FullSeriesCollection(1).ApplyDataLabels
    chartObj.Chart.FullSeriesCollection(2).ApplyDataLabels
End Sub

ついでに、ファイル開いたときに自動的にMacroが走るようにしてたら幸せ

Private Sub Workbook_Open()
    Call Module1.draw_chart
End Sub

サンプルコード

こちらはCakePHP2.xで書いているので、Cakeなところが残っていたらごめんなさい。
ダウンロードして開いたらグラフが出てくる（ﾊｽﾞ

完成図

コード

大事なこと：**拡張子「.xlsm」**に気を付けて

    public function getReportFromMacro()
    {
        //PHPExelオブジェクトの作成
        $objPHPExcel = new PHPExcel();
        $filepath = '/var/www/html/aems/app/tmp/excel/templateWithMacro.xlsm';
        $objReader = PHPExcel_IOFactory::createReader('Excel2007');
        $objReader->setIncludeCharts(TRUE);
        $objPHPExcel  = $objReader->load($filepath);

        //一枚目のシートを選択
        $objWorksheet = $objPHPExcel->getActiveSheet(0);

        $values=[
            [372,102],
            [296,82],
            [700,192],
            [1127,474],
            [829,430],
            [600,387],
            [950,342],
            [501,90],
            [203,150],
            [891,364],
            [983,334],
            [1003,158],
            [501,90]
        ];
        //配列の値を「M14」からExcelに埋めていく
        $objWorksheet->fromArray($values, null, 'M14');

        /********Excelファイル書き出し処理********/
        //Excelファイルの名前(xlsm)
        $fname = 'chartFromMacro.xlsm';
        //ファイルのサーバでの保存先
        $fpath = '/var/www/html/aems/app/tmp/'.$fname;

        // Excel書き出し用のオブジェクトを作成_Excelのバージョンを指定
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        //書き出すExcelにグラフが含まれていると宣言
        $objWriter->setIncludeCharts(TRUE);
        //サーバ内に保存
        $objWriter->save($fpath);

        // 該当ファイルをHeaderに書き出し
        header('Content-Type: application/force-download');
        header('Content-Length: '.filesize($fpath));
        header('Content-disposition: attachment; filename="'.$fname.'"');
        readfile($fpath);
        /************************************/
    }

やっぱり少しレイアウトは崩れる

おそらく、PHPExcelから書き出す際に「draw~.xml」とかなんとかが書き出されないから、レイアウトは少し古臭くなってしまう模様。
どうしても気になるなら一回Excelでグラフの種類を選択とかしたら綺麗になります。

この辺が限界？（かな