phpspreadsheet生成Excel表格

参看文档
https://www.cnblogs.com/zx-admin/p/11653863.html
https://blog.csdn.net/baidu_31950961/article/details/90692098
https://www.cnblogs.com/doseoer/p/11041856.html

//使用Spreadsheet类
use PhpOffice\PhpSpreadsheet\Spreadsheet;
//xlsx格式类
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
//可以生成多种格式类
use PhpOffice\PhpSpreadsheet\IOFactory;

public function test()
    {
        $goods = Db::table('rht_product_launch_goods')->field('brand,supplier_name,pro_name,pro_spec,supplier_cost,approval_sales_price,retail_price')->where('pid',102)->select();
        if(empty($goods))
        {
            return ['code'=>0,'info'=>'指令单中商品为空'];
        }

        ini_set('max_execution_time', '0');
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
//设置sheet的名字  两种方法
//        $sheet->setTitle('phpspreadsheet——demo');
        $spreadsheet->getActiveSheet()->setTitle('上市指令模板');
//设置第一行小标题
        //合并单元格  并初始化模板数据
        $spreadsheet->getActiveSheet()->mergeCells('A1:G1');
        $spreadsheet->getActiveSheet()->mergeCells('A2:G2');
        $spreadsheet->getActiveSheet()->mergeCells('B5:G5');
        $spreadsheet->getActiveSheet()->mergeCells('A6:G6');
        $sheet->setCellValue('A1', '测试连锁有限公司 ');
        $sheet->setCellValue('A2', '基本信息区');
        $sheet->setCellValue('A3', '申请人');
        $sheet->setCellValue('C3', '部门');
        $sheet->setCellValue('E3', '申请日期');
        $sheet->setCellValue('A4', '标题');
        $sheet->setCellValue('A5', '申请内容');
        $sheet->setCellValue('A6', '包装设计信息区');
        $sheet->setCellValue('A7', '使用商标');
        $sheet->setCellValue('B7', '生产单位');
        $sheet->setCellValue('C7', '品名');
        $sheet->setCellValue('D7', '规格');
        $sheet->setCellValue('E7', '采购价（元）');
        $sheet->setCellValue('F7', '销售结算价（元）');
        $sheet->setCellValue('G7', '年销售预测（万元）');
        //循环输出产品信息

        foreach($goods as $g_key=>$g_value){
            $i = 8+$g_key;
            $sheet->setCellValue('A'.$i, $g_value['brand']);
            $sheet->setCellValue('B'.$i, $g_value['supplier_name']);
            $sheet->setCellValue('C'.$i, $g_value['pro_name']);
            $sheet->setCellValue('D'.$i, $g_value['pro_spec']);
            $sheet->setCellValue('E'.$i, $g_value['supplier_cost']);
            $sheet->setCellValue('F'.$i, $g_value['approval_sales_price']);
            $sheet->setCellValue('G'.$i, $g_value['retail_price']);
        }
        //统计商品往后的数据排版
       /* $good_num = count($goods)+7;
        $sheet->setCellValue('A'.($good_num+1), '最低零售价');
        $sheet->setCellValue('A'.($good_num+2), '销售公司');
        $sheet->setCellValue('A'.($good_num+3), '购进单位');*/
        //合并单元格  并初始化模板数据
        /*$spreadsheet->getActiveSheet()->mergeCells("A1".($good_num+4).":"."H1".($good_num+4));
        $sheet->setCellValue('A'.($good_num+4), '签字意见区');
        $sheet->setCellValue('A'.($good_num+5), '子公司质量负责人');
        $sheet->setCellValue('A'.($good_num+6), '子公司总经理');
        $sheet->setCellValue('A'.($good_num+7), '品牌品质管理中心');
        $sheet->setCellValue('A'.($good_num+8), '商业运营部');
        $sheet->setCellValue('A'.($good_num+9), '商委会主任');*/

//设置文字水平居中
        $styleArray = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];
        $sheet->getStyle('A1')->applyFromArray($styleArray);
        $sheet->getStyle('A2')->applyFromArray($styleArray);
        $sheet->getStyle('A6')->applyFromArray($styleArray);


//将A3到D4合并成一个单元格
//        $spreadsheet->getActiveSheet()->mergeCells('A3:D4');
//拆分合并单元格
//        $spreadsheet->getActiveSheet()->unmergeCells('A3:D4');

        //给数据画上边框
        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN //细边框
                ]
            ]
        ];
        for ($i = 1;$i<=count($goods)+7;$i++){
            $sheet->getStyle('A'.$i.':'.'G'.$i)->applyFromArray($styleArray);
            //设置文字垂直居中
            $sheet->getStyle('A'.$i.':'.'G'.$i)->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        }
        //设置行高
        $spreadsheet->getActiveSheet()->getRowDimension('1')->setRowHeight(50);
        $spreadsheet->getActiveSheet()->getRowDimension('5')->setRowHeight(50);
        //设置列的宽度
        $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(20);
        //设置第一行字体
        $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setBold(true)->setName('Arial')
            ->setSize(20);
//        $spreadsheet->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
        $file_name = '上市指令模板'.date('Y-m-d', time()).rand(1000, 9999);
//第一种保存方式
        $writer = new Xlsx($spreadsheet);
        //保存的路径可自行设置
        $file_name =env('ROOT_PATH').'public/upload/temp/'.$file_name.".Xlsx";
        $writer->save($file_name);
//第二种直接页面上显示下载
       /* $file_name = $file_name . ".xlsx";
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$file_name.'"');
        header('Cache-Control: max-age=0');
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
//注意createWriter($spreadsheet, 'Xls') 第二个参数首字母必须大写
        $writer->save('php://output');*/
        //事件后销毁资源
        unset($spreadsheet);

    }

显示结果页面