php excel导出柱状图,YII2框架下使用PHPExcel导出柱状图

导出结果：

首先，到官网下载PHPExcel插件包，下载后文件夹如下：

将Classes文件夹放入到项目公共方法内。

新建控制器(访问导出的方法)：EntryandexitController

namespace appmodulesreportmanagementcontrollers;

use Yii;

use yiiwebController;

use appmodulesreportmanagementcomponentsSummaryexport;

class EntryandexitController extends BaseController

{

public function actionIndex(){

//查询需导出的数据

$result = array();

//调用导出方法

Summaryexport::export($result);

}

}

新建控制器(公共方法)：Summaryexport

namespace appmodulesreportmanagementcomponents;

//引入PHPExcel插件

require dirname(dirname(__FILE__)).'/components/phpexcel/PHPExcel.php';

class Summaryexport

{

public static function export($result){

header("content-type:text/html;charset=utf-8");

error_reporting(E_ALL);

date_default_timezone_set('Europe/London');

//创建sheet工作薄

$objPHPExcel = new PHPExcel();

$objWorksheet = $objPHPExcel->setActiveSheetIndex(0);

//$result封装(方式选其一即可) -> 此数据可用此方式输出表格或使用单元格赋值两种方式(表格数据)

//方式一

$objWorksheet->fromArray(

array(

array('航次',    'E人数','W人数','总人数','增长率','E商人','W商人','总商人数','团体','一般'),

array('10010','10','12','=SUM(B2:B3)','=SUM((B2:B3)/B3)','10','2','12','12','12'),

array('10020','10','12','=SUM(B2:B3)','=SUM((B2:B3)/B3)','10','2','12','12','12'),

...

)

);

//方式二

//标题

$title_name = "月汇总出入境人数报表";

$objPHPExcel->getActiveSheet()->mergeCells('A1:J1');

$objPHPExcel->getActiveSheet()->SetCellValue('A1', $title_name);

$objPHPExcel->getActiveSheet()->SetCellValue('A2', '航次');

$objPHPExcel->getActiveSheet()->SetCellValue('B2', 'E人数');

$objPHPExcel->getActiveSheet()->SetCellValue('C2', 'W人数');

$objPHPExcel->getActiveSheet()->SetCellValue('D2', '小计');

$objPHPExcel->getActiveSheet()->SetCellValue('E2', '增长率');

$objPHPExcel->getActiveSheet()->SetCellValue('F2', 'E商人');

$objPHPExcel->getActiveSheet()->SetCellValue('G2', 'W商人');

$objPHPExcel->getActiveSheet()->SetCellValue('H2', '小计');

$objPHPExcel->getActiveSheet()->SetCellValue('I2', '团体');

$objPHPExcel->getActiveSheet()->SetCellValue('J2', '一般');

for($i=1;$i<=12;$i++){

$index = 'A'.($i+2);

$index_b = 'B'.($i+2);

$index_c = 'C'.($i+2);

$index_d = 'D'.($i+2);

$index_e = 'E'.($i+2);

$index_f = 'F'.($i+2);

$index_g = 'G'.($i+2);

$index_h = 'H'.($i+2);

$index_i = 'I'.($i+2);

$index_j = 'J'.($i+2);

$prev_index_d = 'D'.($i+1);

$growth_rate = $i == 1?'-':'=SUM(('.$index_d.'-'.$prev_index_d.')/'.$prev_index_d.')';

$objPHPExcel->getActiveSheet()->SetCellValue($index,'一月份');

$objPHPExcel->getActiveSheet()->SetCellValue($index_b, 12);

$objPHPExcel->getActiveSheet()->SetCellValue($index_c, 10);

$objPHPExcel->getActiveSheet()->SetCellValue($index_d, '=SUM('.$index_b.':'.$index_c.')');

$objPHPExcel->getActiveSheet()->SetCellValue($index_e, $growth_rate );

$objPHPExcel->getActiveSheet()->SetCellValue($index_f, 10);

$objPHPExcel->getActiveSheet()->SetCellValue($index_g, 20);

$objPHPExcel->getActiveSheet()->SetCellValue($index_h, '=SUM('.$index_f.':'.$index_g.')');

$objPHPExcel->getActiveSheet()->SetCellValue($index_i, 10);

$objPHPExcel->getActiveSheet()->SetCellValue($index_j, 10)

}

//开始图表

//取得绘制图表的标签(图例项)

$dataseriesLabels = array(

new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$D$2', NULL, 1),    //    '总人数'

new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$H$2', NULL, 1),    //    '商人'

new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$I$2', NULL, 1),    //    '团体'

new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$J$2', NULL, 1),    //    '一般旅客'

);

//取得图表X轴的刻度 (第四个参数值:单元格长度A3-A14)

$xAxisTickValues = array(

new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$3:$A$14', NULL, 12),    //    一月份 to 十二月份

);

//取得绘图所需的数据

$dataSeriesValues = array(

new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$D$3:$D$14', NULL, 12),

new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$H$3:$H$14', NULL, 12),

new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$I$3:$I$14', NULL, 12),

new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$J$3:$J$14', NULL, 12),

);

//根据取得的东西做出一个图表框架

$series = new PHPExcel_Chart_DataSeries(

PHPExcel_Chart_DataSeries::TYPE_SURFACECHART,        // plotType

PHPExcel_Chart_DataSeries::GROUPING_CLUSTERED,    // plotGrouping

range(0, count($dataSeriesValues)-1),            // plotOrder

$dataseriesLabels,                                // plotLabel

$xAxisTickValues,                                // plotCategory

$dataSeriesValues                                // plotValues

);

//设置生成图表所需要的参数(坐标是横向或竖向显示：DIRECTION_COL|DIRECTION_BAR)

$series->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_COL);

$layout = new PHPExcel_Chart_Layout();

$layout->setShowPercent(TRUE);

$plotarea = new PHPExcel_Chart_PlotArea($layout, array($series));

$legend = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false);

$title = new PHPExcel_Chart_Title('2018年各渠道购票数对比图');

//         $xAxisLabel = new PHPExcel_Chart_Title('Financial Period');    //X轴标题

$yAxisLabel = new PHPExcel_Chart_Title('人数 ($k)');    //Y轴标题

$chart = new PHPExcel_Chart(

'chart1',        // name

$title,            // title,空为null

$legend,        // legend

$plotarea,        // plotArea

true,            // plotVisibleOnly

0,                // displayBlanksAs

null,    // xAxisLabel

$yAxisLabel        // yAxisLabel

);

//给定图表所在表格中的位置

$chart->setTopLeftPosition('L2');

$chart->setBottomRightPosition('T15');

//将chart添加到sheet中

$objWorksheet->addChart($chart);

//--------------图表end;

$objPHPExcel->getActiveSheet()->setTitle("全年合计");

$filename = '出入境人数报表.xls';

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

$objWriter->setIncludeCharts(TRUE);    //图表必须

header("Pragma: public");

header("Expires: 0");

header("Cache-Control:must-revalidate,post-check=0,pre-check=0");

header("Content-Type:application/force-download");

header("Content-Type:application/vnd.ms-execl");

header("Content-Type:application/octet-stream");

header("Content-Type:application/download");

header("Content-Disposition:attachment;filename=".$filename);

header("Content-Transfer-Encoding:binary");

$objWriter->save("php://output");

}

已完结，暂未解决表格数据与图表数据关联。

内容来源于网络如有侵权请私信删除