利用phpspreadsheet导出Excel图表（折线图、饼状图、柱状图）

利用phpspreadsheet导出Excel图表

安装 phpoffice/phpspreadsheet

安装 phpoffice/phpspreadsheet

composer require phpoffice/phpspreadsheet
1

折线图

需要使用的包

use PhpOffice\PhpSpreadsheet\Chart\Chart;
use PhpOffice\PhpSpreadsheet\Chart\DataSeries;
use PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues;
use PhpOffice\PhpSpreadsheet\Chart\Layout;
use PhpOffice\PhpSpreadsheet\Chart\Legend as ChartLegend;
use PhpOffice\PhpSpreadsheet\Chart\PlotArea;
use PhpOffice\PhpSpreadsheet\Chart\Properties;
use PhpOffice\PhpSpreadsheet\Chart\Title;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
1
2
3
4
5
6
7
8
9
10

实例代码

$spreadsheet = new Spreadsheet();
  $worksheet = $spreadsheet->getActiveSheet();
  $worksheet->fromArray(
      [
          ['', 2010, 2011, 2012],
          ['Q1', 12, 15, 21],
          ['Q2', 56, 73, 86],
          ['Q3', 52, 61, 69],
          ['Q4', 30, 32, 0],
      ]
  );

  $dataSeriesLabels = [
      new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$B$1', null, 1), // 2010
      new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$C$1', null, 1), // 2011
      new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$D$1', null, 1), // 2012
  ];
  $dataSeriesLabels[0]->setFillColor('FF0000');
  $xAxisTickValues = [
      new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$A$2:$A$5', null, 4), // Q1 to Q4
  ];

  $dataSeriesValues = [
      new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$B$2:$B$5', null, 4),
      new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$C$2:$C$5', null, 4),
      new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$D$2:$D$5', null, 4),
  ];
  $dataSeriesValues[2]->setLineWidth(60000 / Properties::POINTS_WIDTH_MULTIPLIER);
  // Build the dataseries
  $series = new DataSeries(
      DataSeries::TYPE_LINECHART, // plotType
      DataSeries::GROUPING_STANDARD, // plotGrouping
      range(0, count($dataSeriesValues) - 1), // plotOrder
      $dataSeriesLabels, // plotLabel
      $xAxisTickValues, // plotCategory
      $dataSeriesValues, // plotValues
      null,
      true
  );

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

  // 把值标注在上面
//            $layout1 = new Layout();
//            $layout1->setShowVal(true);
//            $layout1->setShowPercent(true);
//            $plotArea = new PlotArea($layout1, [$series]);
  // Set the series in the plot area
  $plotArea = new PlotArea(null, [$series]);
  // Set the chart legend
  $legend = new ChartLegend(ChartLegend::POSITION_TOPRIGHT, null, false);

  $title = new Title('Test Stacked Line Chart');
  $yAxisLabel = new Title('Value ($k)');

  // Create the chart
  $chart = new Chart(
      'chart1', // name
      $title, // title
      $legend, // legend
      $plotArea, // plotArea
      true, // plotVisibleOnly
      DataSeries::EMPTY_AS_GAP, // displayBlanksAs
      null, // xAxisLabel
      $yAxisLabel  // yAxisLabel
  );

  // Set the position where the chart should appear in the worksheet
  $chart->setTopLeftPosition('A7');
  $chart->setBottomRightPosition('o30');

  // Add the chart to the worksheet
  $worksheet->addChart($chart);

  // Save Excel 2007 file
  $filename = "test";
  header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8');
  header('Content-Disposition: attachment;filename="' . $filename . '.xlsx"');
  header('Cache-Control: max-age=0');
  ob_end_clean();
  $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
  $writer->setIncludeCharts(true);
  $writer->save("php://output");
  $spreadsheet->disconnectWorksheets();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84

效果图

饼状图

需要使用的包

use PhpOffice\PhpSpreadsheet\Chart\Chart;
use PhpOffice\PhpSpreadsheet\Chart\DataSeries;
use PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues;
use PhpOffice\PhpSpreadsheet\Chart\Layout;
use PhpOffice\PhpSpreadsheet\Chart\PlotArea;
use PhpOffice\PhpSpreadsheet\Chart\Title;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Chart\Legend as ChartLegend;
1
2
3
4
5
6
7
8
9

实例代码

$spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
        $worksheet->fromArray(
            [
                ['', 2010, 2011, 2012],
                ['Q1', 12, 15, 21],
                ['Q2', 56, 73, 86],
                ['Q3', 52, 61, 69],
                ['Q4', 30, 32, 0],
            ]
        );
        $dataSeriesLabels1 = [
            new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$C$1', null, 1), // 2011
        ];
        $xAxisTickValues1 = [
            new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$A$2:$A$5', null, 4), // Q1 to Q4
        ];
        $dataSeriesValues1 = [
            new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$C$2:$C$5', null, 4),
        ];
        
        $series1 = new DataSeries(
            DataSeries::TYPE_PIECHART, // plotType
            null, // plotGrouping (Pie charts don't have any grouping)
            range(0, count($dataSeriesValues1) - 1), // plotOrder
            $dataSeriesLabels1, // plotLabel
            $xAxisTickValues1, // plotCategory
            $dataSeriesValues1          // plotValues
        );
        
        $layout1 = new Layout();
        $layout1->setShowVal(true);
        $layout1->setShowPercent(true);
        
        $plotArea1 = new PlotArea($layout1, [$series1]);
        $legend1 = new ChartLegend(ChartLegend::POSITION_RIGHT, null, false);

        $title1 = new Title('Test Pie Chart');
        
        $chart1 = new Chart(
            'chart1', // name
            $title1, // title
            $legend1, // legend
            $plotArea1, // plotArea
            true, // plotVisibleOnly
            DataSeries::EMPTY_AS_GAP, // displayBlanksAs
            null, // xAxisLabel
            null   // yAxisLabel - Pie charts don't have a Y-Axis
        );/**/
        
        $chart1->setTopLeftPosition('A7');
        $chart1->setBottomRightPosition('H20');
        
        $worksheet->addChart($chart1);
        $dataSeriesLabels2 = [
            new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$C$1', null, 1), // 2011
        ];

        $xAxisTickValues2 = [
            new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$A$2:$A$5', null, 4), // Q1 to Q4
        ];
        $dataSeriesValues2 = [
            new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$C$2:$C$5', null, 4),
        ];
        
        $series2 = new DataSeries(
            DataSeries::TYPE_DONUTCHART, // plotType
            null, // plotGrouping (Donut charts don't have any grouping)
            range(0, count($dataSeriesValues2) - 1), // plotOrder
            $dataSeriesLabels2, // plotLabel
            $xAxisTickValues2, // plotCategory
            $dataSeriesValues2        // plotValues
        );
        
        $layout2 = new Layout();
        $layout2->setShowVal(true);
        $layout2->setShowCatName(true);
        
        $plotArea2 = new PlotArea($layout2, [$series2]);

        $title2 = new Title('Test Donut Chart');
        
        $chart2 = new Chart(
            'chart2', // name
            $title2, // title
            null, // legend
            $plotArea2, // plotArea
            true, // plotVisibleOnly
            DataSeries::EMPTY_AS_GAP, // displayBlanksAs
            null, // xAxisLabel
            null   // yAxisLabel - Like Pie charts, Donut charts don't have a Y-Axis
        );
        
        $chart2->setTopLeftPosition('I7');
        $chart2->setBottomRightPosition('P20');
        
        $worksheet->addChart($chart2);
        
        $filename = "test";
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8');
        header('Content-Disposition: attachment;filename="' . $filename . '.xlsx"');
        header('Cache-Control: max-age=0');
        ob_end_clean();
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->setIncludeCharts(true);
        $writer->save("php://output");
        $spreadsheet->disconnectWorksheets();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107

效果图

在这里插入图片描述

柱状图

需要使用的包

use PhpOffice\PhpSpreadsheet\Chart\Chart;
use PhpOffice\PhpSpreadsheet\Chart\ChartColor;
use PhpOffice\PhpSpreadsheet\Chart\DataSeries;
use PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues;
use PhpOffice\PhpSpreadsheet\Chart\GridLines;
use PhpOffice\PhpSpreadsheet\Chart\Layout;
use PhpOffice\PhpSpreadsheet\Chart\Legend as ChartLegend;
use PhpOffice\PhpSpreadsheet\Chart\PlotArea;
use PhpOffice\PhpSpreadsheet\Chart\Properties;
use PhpOffice\PhpSpreadsheet\Chart\Title;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
1
2
3
4
5
6
7
8
9
10
11
12

实例代码

$spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
        $worksheet->fromArray(
            [
                ['', 2010, 2011, 2012],
                ['Q1', 12, 15, 21],
                ['Q2', 56, 73, 86],
                ['Q3', 52, 61, 69],
                ['Q4', 30, 32, 0],
            ]
        );

        $colors = [
            'cccccc', '00abb8', 'b8292f', 'eb8500',
        ];

        $dataSeriesLabels1 = [
            new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$C$1', null, 1), // 2011
        ];
        $xAxisTickValues1 = [
            new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$A$2:$A$5', null, 4), // Q1 to Q4
        ];
        $dataSeriesValues1 = [
            new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$C$2:$C$5', null, 4, [], null, $colors),
        ];
        $labelLayout = new Layout();
        $labelLayout
            ->setShowVal(true)
            ->setLabelFontColor(new ChartColor('FFFF00'))
            ->setLabelFillColor(new ChartColor('accent2', null, 'schemeClr'));
        $dataSeriesValues1[0]->setLabelLayout($labelLayout);

        $series1 = new DataSeries(
            DataSeries::TYPE_BARCHART, // plotType
            null, // plotGrouping (Pie charts don't have any grouping)
            range(0, count($dataSeriesValues1) - 1), // plotOrder
            $dataSeriesLabels1, // plotLabel
            $xAxisTickValues1, // plotCategory
            $dataSeriesValues1          // plotValues
        );

        $layout1 = new Layout();
        $layout1->setShowVal(true);
        $layout1->setShowPercent(true);

        $plotArea1 = new PlotArea($layout1, [$series1]);
        $legend1 = new ChartLegend(ChartLegend::POSITION_RIGHT, null, false);
        $title1 = new Title('Test Bar Chart');
        $chart1 = new Chart(
            'chart1', // name
            $title1, // title
            $legend1, // legend
            $plotArea1, // plotArea
            true, // plotVisibleOnly
            DataSeries::EMPTY_AS_GAP, // displayBlanksAs
            null, // xAxisLabel
            null   // yAxisLabel - Pie charts don't have a Y-Axis
        );
        $majorGridlinesY = new GridLines();
        $majorGridlinesY->setLineColorProperties('FF0000');
        $minorGridlinesY = new GridLines();
        $minorGridlinesY->setLineStyleProperty('dash', Properties::LINE_STYLE_DASH_ROUND_DOT);
        $chart1
            ->getChartAxisY()
            ->setMajorGridlines($majorGridlinesY)
            ->setMinorGridlines($minorGridlinesY);
        $majorGridlinesX = new GridLines();
        $majorGridlinesX->setLineColorProperties('FF00FF');
        $minorGridlinesX = new GridLines();
        $minorGridlinesX->activateObject();
        $chart1
            ->getChartAxisX()
            ->setMajorGridlines($majorGridlinesX)
            ->setMinorGridlines($minorGridlinesX);

        $chart1->setTopLeftPosition('A7');
        $chart1->setBottomRightPosition('H20');

        $worksheet->addChart($chart1);

        $dataSeriesLabels2 = [
            new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$C$1', null, 1), // 2011
        ];
        $xAxisTickValues2 = [
            new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$A$2:$A$5', null, 4), // Q1 to Q4
        ];
        $dataSeriesValues2 = [
            $dataSeriesValues2Element = new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$C$2:$C$5', null, 4),
        ];
        $dataSeriesValues2Element->setFillColor($colors);

        $series2 = new DataSeries(
            DataSeries::TYPE_DONUTCHART, // plotType
            null, // plotGrouping (Donut charts don't have any grouping)
            range(0, count($dataSeriesValues2) - 1), // plotOrder
            $dataSeriesLabels2, // plotLabel
            $xAxisTickValues2, // plotCategory
            $dataSeriesValues2        // plotValues
        );

        $layout2 = new Layout();
        $layout2->setShowVal(true);
        $layout2->setShowCatName(true);
        $layout2->setLabelFillColor(new ChartColor('FFFF00'));

        $plotArea2 = new PlotArea($layout2, [$series2]);

        $title2 = new Title('Test Donut Chart');

        $chart2 = new Chart(
            'chart2', // name
            $title2, // title
            null, // legend
            $plotArea2, // plotArea
            true, // plotVisibleOnly
            DataSeries::EMPTY_AS_GAP, // displayBlanksAs
            null, // xAxisLabel
            null   // yAxisLabel - Like Pie charts, Donut charts don't have a Y-Axis
        );

        $chart2->setTopLeftPosition('I7');
        $chart2->setBottomRightPosition('P20');

        $worksheet->addChart($chart2);

        $filename = "test";
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8');
        header('Content-Disposition: attachment;filename="' . $filename . '.xlsx"');
        header('Cache-Control: max-age=0');
        ob_end_clean();
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->setIncludeCharts(true);
        $writer->save("php://output");
        $spreadsheet->disconnectWorksheets();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134

效果图

在这里插入图片描述

相关阅读:
花椰菜的 8 大健康益处，为你一一盘点
 STC15单片机-按键检测单击或长按(外部中断)
拉格朗日粒子扩散FLEXPART模式
 Kruise Rollout：灵活可插拔的渐进式发布框架
 【C#语音文字互转】.NET的TTS文本转语音合成
 数电再回顾
 3Dslicer医学图像三维坐标系(xyz,RAS,IJK)差异及处理
 智能合约漏洞，Dyna 事件分析
 vue实现登录功能
 Apache的运用与实战
原文地址：https://blog.csdn.net/maxiaojingabc/article/details/128135024