首页 > > 网络编程 > Php编程 >

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

2018-06-22 05:36:57来源：未知阅读 ()

导出结果：

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

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

新建控制器（访问导出的方法）：EntryandexitController

<?php
namespace app\modules\reportmanagement\controllers;

use Yii;
use yii\web\Controller;
use app\modules\reportmanagement\components\Summaryexport;

class EntryandexitController extends BaseController
{
   public function actionIndex(){

       //查询需导出的数据
       $result = array();

       //调用导出方法
       Summaryexport::export($result);
   }
}

新建控制器（公共方法）：Summaryexport

<?php
namespace app\modules\reportmanagement\components;
//引入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");

}