利用phpspreadsheet导出Excel图表

• 安装 phpoffice/phpspreadsheet
• • 折线图
  • • 需要使用的包
    • 实例代码
    • 效果图![实例图]()
  • 饼状图
  • • 需要使用的包
    • 实例代码
    • 效果图
  • 柱状图
  • • 需要使用的包
    • 实例代码
    • 效果图

安装 phpoffice/phpspreadsheet

composer require phpoffice/phpspreadsheet

折线图

需要使用的包

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;

实例代码

$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), // 2010new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$C$1', null, 1), // 2011new 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, // plotTypeDataSeries::GROUPING_STANDARD, // plotGroupingrange(0, count($dataSeriesValues) - 1), // plotOrder$dataSeriesLabels, // plotLabel$xAxisTickValues, // plotCategory$dataSeriesValues, // plotValuesnull,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, // plotAreatrue, // plotVisibleOnlyDataSeries::EMPTY_AS_GAP, // displayBlanksAsnull, // 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();

效果图

饼状图

需要使用的包

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;

实例代码

$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, // plotTypenull, // 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, // plotAreatrue, // plotVisibleOnlyDataSeries::EMPTY_AS_GAP, // displayBlanksAsnull, // xAxisLabelnull   // 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, // plotTypenull, // 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, // titlenull, // legend$plotArea2, // plotAreatrue, // plotVisibleOnlyDataSeries::EMPTY_AS_GAP, // displayBlanksAsnull, // xAxisLabelnull   // 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();

效果图

柱状图

需要使用的包

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;

实例代码

$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, // plotTypenull, // 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, // plotAreatrue, // plotVisibleOnlyDataSeries::EMPTY_AS_GAP, // displayBlanksAsnull, // xAxisLabelnull   // 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, // plotTypenull, // 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, // titlenull, // legend$plotArea2, // plotAreatrue, // plotVisibleOnlyDataSeries::EMPTY_AS_GAP, // displayBlanksAsnull, // xAxisLabelnull   // 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();

效果图