Help us understand the problem. What is going on with this article?

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

More than 3 years have passed since last update.

【ぇ、使えるの!?】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軸とか
値は消しておいた方が分かりやすいですよ(値の埋め忘れに気づきやすくするため
2015-09-09_14h39_03.png

グラフ描画用マクロ

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なところが残っていたらごめんなさい。
ダウンロードして開いたらグラフが出てくる(ハズ

完成図

2015-09-09_14h55_05.png

コード

大事なこと:拡張子「.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でグラフの種類を選択とかしたら綺麗になります。

この辺が限界?(かな

tutida
fusic
個性をかき集めて、驚きの角度から世の中をアップデートしつづける。
https://fusic.co.jp/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした