【ぇ、使えるの!?】PHPExcel＋マクロでグラフ付きExcelを出力
PHPExcelでゼロからグラフを書いてみよう
PHPExcelでテンプレートファイルからグラフを作ってみよう
の最終章
Excel_Reviserが終了し、xlsしか出力できないので、
xlsxで出力したい場合、PHPでExcelをいじる場合はPHPExcelを使うしかない。
PHPExcelでグラフを出力するための備忘録。
PHPExcelバージョン：1.8.1
Excel:2013
PHP:5.5.27
考えられるパターン
- テンプレートファイル使わない
- テンプレートファイルを使う -> グラフをあらかじめ埋め込んでいるもの
- テンプレートファイルを使う -> マクロ埋め込み
「テンプレートファイルを使う -> マクロ埋め込み」で行ってみましょう。
（そもそも）ぇ、マクロ使えるの？
「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さえ分かれば...)
流れ
- テンプレートとなるマクロ付きExcelファイルをPHPExcelで読み込む
- グラフのデータとなる表にPHPExcelで値を埋める
- 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でグラフの種類を選択とかしたら綺麗になります。
この辺が限界？（かな