Edited at

【ぇ、使えるの!?】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でグラフの種類を選択とかしたら綺麗になります。

この辺が限界?(かな