php Yii2 execl表格导出样式定义

框架Yii2

扩展：PhpSpreadsheet

看一下模板

public static function dataToExport($headers, $data, $fileName = 'excel', $writeType = 'Xls')
    {
        $excel = new Spreadsheet();
        $ordA = ord('A'); //65
        $key2 = ord("@"); //64
        $sheet = $excel->setActiveSheetIndex(0);
        // 设置标题
        foreach ($headers as $index => $title) {
            if ($ordA > ord("Z")) {
                $colum = chr(ord("A")) . chr(++$key2); //超过26个字母 AA1,AB1,AC1,AD1...BA1,BB1...
            } else {
                $colum = chr($ordA++);
            }

            $sheet->setCellValue($colum . '1', $title);
        }

        $r = 2;
        foreach ($data as $dKey => $log) {
            $column = 1;
            foreach ($headers as $kk => $vv) {
                $sheet->setCellValueExplicitByColumnAndRow($column, $r, $log[$kk], DataType::TYPE_STRING);
                $column++;
            }
            $r++;
        }
        $sheet->freezePane('A2');
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename=' . $fileName . date('Y-m-d') . '.xls');
        header('Cache-Control: max-age=0');
        $excelWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($excel, $writeType);
        $excelWriter->save('php://output');
        die();
    }

导出效果：

很明显差的有点远，下面的来升级一下导出标题的代码：

public static function exportSample($headers, $data, $fileName = 'excel', $writeType = 'Xls')
    {
        $excel = new Spreadsheet();
        $sheet = $excel->setActiveSheetIndex(0);

        // 设置标题单元格的样式：字体加粗，字体大小12，字体颜色红色，水平居中，垂直居中
        $styleArray = [
            'font' => [ //设置字体
                'bold' => true,
                'size' => 9,
//                'color' => array('rgb' => 'FF0000'),
            ],
            'alignment' => [ //对其方式
                'horizontal' => Excel::HORIZONTAL_CENTER, //居中
                'vertical' => Excel::VERTICAL_CENTER,
                'wrapText' => true, // 设置自动换行
            ],
            'fill'=>[ //设置表格的背景色
                'fillType' => Excel::FILL_SOLID,
                'startColor'=>['rgb' => 'B5C6EA'], //设备背景色
            ]
        ];

        $sheet->getStyle('A1:AC2')->applyFromArray($styleArray);

        $styleArray = [
            'borders' => [ //设备边框
                'outline' => [
                    'borderStyle' => Excel::BORDER_THIN, // 边框样式
                    'color' => ['rgb' => '000000'], // 边框颜色为黑色
                ],
            ],
        ];

        //循环两次-后面合并需要
        for ($i=1; $i<=2;$i++){
            // 设置标题
            $ordA = ord('A'); //65
            $key2 = ord("@"); //64
            foreach ($headers as $index => $title) {
                if ($ordA > ord("Z")) {
                    $colum = chr(ord("A")) . chr(++$key2); //超过26个字母 AA1,AB1,AC1,AD1...BA1,BB1...
                } else {
                    $colum = chr($ordA++);
                }
                $_p = $colum . $i;

                //写入标题并且设置样式
                $sheet->setCellValue($_p, $title)->getStyle($_p)->applyFromArray($styleArray);
            }
        }

        //合并单元格-列-切重写
        $sheet->mergeCells('D1:M1')->setCellValue('D1', '计划产品性能参数');
        $sheet->mergeCells('N1:S1')->setCellValue('N1', '对标品牌产品性能参数');

        //合并单元格-行
        $h = ['A','B','C','T','U','V','W','X','Y','Z','AA','AB','AC'];
        foreach ($h as $k=>$y){
            $sheet->mergeCells($y.'1:'.$y.'2');
        }

        $r = 3; //数据写入航标
        foreach ($data as $dKey => $log) {
            $column = 1;
            foreach ($headers as $kk => $vv) {

//                if(in_array($kk,['sam_img','ref_img'])){
//                    // 创建一个Drawing对象
//                    $drawing = new Drawing();
                    $drawing->setPath($log[$kk]);
//
//                    // 设置图片的尺寸大小
//                    $drawing->setCoordinates('B7'); // 图片将被放置在B2单元格
//                    $drawing->setWorksheet($sheet); // 将Drawing对象添加到工作表
//                }else{
                $sheet->setCellValueExplicitByColumnAndRow($column, $r, $log[$kk], DataType::TYPE_STRING);

//                }
                $column++;
            }
            $r++;
        }
        $sheet->freezePane('A3'); //锁定行
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename=' . $fileName . date('Y-m-d') . '.xls');
        header('Cache-Control: max-age=0');
        $excelWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($excel, $writeType);
        $excelWriter->save('php://output');
        die();
    }

 是不是跟模板相似度非常高了，图片部分如果导出的话需要先下载图片资源，考虑到性能问题，直接写入url连接到表格内展示。