PhpSpreadsheet导出图表折线图

  • 233
  • 阅读模式
<?php
/**
 * 版权所有 2017-2027 潍坊筋斗云网络科技有限公司,并保留所有权利。
 * 网站地址: http://www.jindouyuns.com
 * ----------------------------------------------------------------------------
 * 这不是一个自由软件!您只能在不用于商业目的的前提下对程序代码进行修改和使用 .
 * 不允许对程序代码以任何形式任何目的的再发布。
 * 采用最新Thinkphp6.0保持出色的性能和至简代码的同时,更注重易用性。
 * Author: peter
 * Date: 2022-01-25 下午9:01
 */

namespace app\viptool\common;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
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;
use PhpOffice\PhpSpreadsheet\Chart\PlotArea;
use PhpOffice\PhpSpreadsheet\Chart\Title;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Chart\Axis;
use PhpOffice\PhpSpreadsheet\Chart\GridLines;
use PhpOffice\PhpSpreadsheet\Cell\Cell;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use think\facade\Db;
class CreateExcelChart
{
  /**
   * 创建折线图表
   * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
   * @author peter
   * @date 2022-01-26 上午11:01
   */
  public static function createLine($data){

    $spreadsheet = new Spreadsheet();
    $worksheet = $spreadsheet->getActiveSheet();
//    $data=[
//        array("","一班","二班","三班"),
//        array("不及格",1,2,3),
//        array("良好",3,2,1),
//        array("优秀",2,1,3),
//        array("3",3 ,2,3),
//        array("4",4,2,1),
//        array("5",5,1,3)
//    ];

    $X = config('behaviorJson.excel')[count($data[0])];
    $worksheet->fromArray($data, null, "A1", true);
    $worksheet->getStyle("A1:D".sizeof(array_keys($data)))->getNumberFormat()->setFormatCode('0');
    $dataSeriesLabels = [
//        new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$A$2', null, 1),
//        new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$A$3', null, 1),
//        new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$A$4', null, 1),
//        new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$A$5', null, 1),
//        new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$A$6', null, 1),
//        new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$A$7', null, 1),
//        new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$A$8', null, 1),
//        new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$A$9', null, 1),
//        new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$A$10', null, 1),
//        new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$A$11', null, 1),
//        new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$A$12', null, 1),
//        new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$A$13', null, 1),
    ];

    $xAxisTickValues = [
        new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$B$1:

#039;.$X.'$1', null, 3), // Q1 to Q4
];
$dataSeriesValues = [
// new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$B$2:


#039;.$X.'$2', null, 3),
// new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$B$3:


#039;.$X.'$3', null, 3),
// new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$B$4:


#039;.$X.'$4', null, 3),
// new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$B$5:


#039;.$X.'$5', null, 3),
// new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$B$6:


#039;.$X.'$6', null, 3),
// new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$B$7:


#039;.$X.'$7', null, 3),
// new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$B$8:


#039;.$X.'$8', null, 3),
// new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$B$9:


#039;.$X.'$9', null, 3),
// new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$B$10:


#039;.$X.'$10', null, 3),
// new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$B$11:


#039;.$X.'$11', null, 3),
// new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$B$12:


#039;.$X.'$12', null, 3),
// new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$B$13:


#039;.$X.'$13', null, 3),
];
for ($i=2; $i<=count($data)+1; $i++) {
array_push($dataSeriesLabels,new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$A


#039;.$i, null, 1));
array_push($dataSeriesValues,new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$B


#039;.$i.':


#039;.$X.'


#039;.$i, null, 3));
}
$series = new DataSeries(
DataSeries::TYPE_LINECHART, // plotType line
// DataSeries::TYPE_PIECHART, // plotType
// null, //pie is null
DataSeries::GROUPING_STANDARD, // plotGrouping
range(0, count($dataSeriesValues) - 1), // plotOrder
$dataSeriesLabels, // plotLabel
$xAxisTickValues, // plotCategory
$dataSeriesValues // plotValues
);
$plotArea = new PlotArea(null, [$series]);
$legend = new Legend(Legend::POSITION_TOPRIGHT, null, false);
$title = new Title('title');
$yAxisLabel = new Title('统计');
// 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
);
$chart->setTopLeftPosition('A13');
$chart->setBottomRightPosition('k31');
$worksheet->setCellValue( 'A32', '统计表');
$worksheet->setCellValue( 'A33', '时间范围:'.'2021~2022');
$worksheet->setCellValue( 'A34', '用户名称:'.'$nick_name' );
$worksheet->setCellValue( 'A35', '名称:'.'$name' );
$worksheet->addChart($chart);
$filename = 'test.xls';
//ob_end_clean();
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: max-age=0');
//清空缓存区
ob_end_clean();
$objWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
$objWriter->setIncludeCharts(true);
$name="name";
$uid = 1;
//下载
$objWriter->save('php://output');
//保存本地
$objWriter->save('本地路径.xls');

}

}

weinxin
我的微信
这是我的微信扫一扫
开拓者博主
  • 本文由 发表于 2022年1月28日20:37:08
  • 转载请务必保留本文链接:https://www.150643.com/562.html
匿名

发表评论

匿名网友 填写信息