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("@"); //64foreach ($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连接到表格内展示。