1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 | <?php namespace app\api\controller; use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Writer\Xlsx; class ExportController extends CommonController { public $file_name; public $spreadsheet; public $data; public $user_name; public $importance; public function __construct($data = [], $user_name = '', $importance = '') { self::loadExportClass(); $this->data = $data; $this->user_name = $user_name; $this->importance = $importance; } public function loadExportClass(): object { $this->spreadsheet = new Spreadsheet(); return $this->spreadsheet; } //保存自定义样式excel到固定目录 public function exportDate() { $sheet = $this->spreadsheet->getActiveSheet(); $sheet->getStyle('A1')->getFont()->setBold(true)->setSize(18); $sheet->setCellValue('B2', '时间')->getStyle('B2')->getFont()->setBold(true); $sheet->setCellValue('C2', '工作内容')->getStyle('C2')->getFont()->setBold(true); $sheet->setCellValue('D2', '状态')->getStyle('D2')->getFont()->setBold(true); $styleArray = [ 'alignment' => [ 'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER, 'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER, ], ]; //垂直居中 $sheet->getColumnDimension('A')->setWidth(15); $sheet->getColumnDimension('B')->setWidth(15); $sheet->getColumnDimension('C')->setWidth(80); $sheet->getColumnDimension('D')->setWidth(15); $sheet->getStyle('A')->applyFromArray($styleArray); $sheet->getStyle('B')->applyFromArray($styleArray); $sheet->getStyle('C')->applyFromArray($styleArray); $sheet->getStyle('D')->applyFromArray($styleArray); $sheet->getDefaultRowDimension()->setRowHeight(40); //默认行高 $sheet->mergecells('A1:D1')->setCellValue('A1', date('Y-m-d').'文件名称')->getStyle('A1')->applyFromArray($styleArray); $data = $this->data; $count = count($data); //计算有多少条数据 for ($i = 3; $i <= $count+2; $i++) { $sheet->setCellValue('B' . $i, $data[$i-3]['name'])->getStyle('B' . $i)->getAlignment()->setWrapText(true); $sheet->setCellValue('C' . $i, $data[$i-3]['content'])->getStyle('C' . $i)->getAlignment()->setWrapText(true); $sheet->setCellValue('D' . $i, $data[$i-3]['project_progress'])->getStyle('D' . $i)->getAlignment()->setWrapText(true); } $cell = $count+3; $sheet->setCellValue('B'.$cell, '事项')->getStyle('B'.$cell)->applyFromArray($styleArray)->getFont()->setBold(true); $sheet->mergecells('C'.$cell.':'.'D'.$cell) ->setCellValue('C'.$cell, $this->importance != ''? $this->importance: '暂无') ->getStyle('C'.$cell)->applyFromArray($styleArray)->getAlignment()->setWrapText(true); header('Content-Type: application/vnd.ms-excel'); $this->file_name = $this->user_name.date('Ymd').'_'.date('H_i_s').'.xlsx'; header('Content-Disposition: attachment;filename="'.$this->file_name); header('Cache-Control: max-age=0'); $writer = new Xlsx($this->spreadsheet); //$writer->save('php://output'); if(!is_dir(public_path('static').date('Ymd'))){ mkdir(public_path('static').date('Ymd'), 0777); } $writer->save(public_path('static').date('Ymd').'/'.$this->file_name); //删除清空 //$this->spreadsheet->disconnectWorksheets(); //unset($this->spreadsheet); return date('Ymd').'/'.$this->file_name; } //搜索内容，直接下载搜索到的内容至自定义样式excel public function exportSearchData() { $sheet = $this->spreadsheet->getActiveSheet(); $sheet->getStyle('A1')->getFont()->setBold(true)->setSize(18); $sheet->setCellValue('A2', '用户名')->getStyle('A2')->getFont()->setBold(true); $sheet->setCellValue('B2', '日期')->getStyle('B2')->getFont()->setBold(true); $sheet->setCellValue('C2', '时间段')->getStyle('C2')->getFont()->setBold(true); $sheet->setCellValue('D2', '内容')->getStyle('D2')->getFont()->setBold(true); $sheet->setCellValue('E2', '状态')->getStyle('E2')->getFont()->setBold(true); $sheet->setCellValue('F2', '事项')->getStyle('F2')->getFont()->setBold(true); $styleArray = [ 'alignment' => [ 'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER, 'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER, ], ]; //垂直居中 $sheet->getColumnDimension('A')->setWidth(15); $sheet->getColumnDimension('B')->setWidth(20); $sheet->getColumnDimension('C')->setWidth(15); $sheet->getColumnDimension('D')->setWidth(80); $sheet->getColumnDimension('E')->setWidth(15); $sheet->getColumnDimension('F')->setWidth(50); $sheet->getStyle('A')->applyFromArray($styleArray); $sheet->getStyle('B')->applyFromArray($styleArray); $sheet->getStyle('C')->applyFromArray($styleArray); $sheet->getStyle('D')->applyFromArray($styleArray); $sheet->getStyle('E')->applyFromArray($styleArray); $sheet->getStyle('F')->applyFromArray($styleArray); //$sheet->mergecells('C2:E2')->setCellValue('C2', '工作内容')->getStyle('C2')->applyFromArray($styleArray)->getAlignment()->setWrapText(true); $sheet->getDefaultRowDimension()->setRowHeight(30); //默认行高 $sheet->mergecells('A1:F1')->setCellValue('A1', '文件(导出日期：'.date('Y-m-d').')')->getStyle('A1')->applyFromArray($styleArray); $data = $this->data; $count = count($data); //计算有多少条数据 $start = 3; foreach ($data as $k=>$v) { $level = 0; $detailCount = count($v['detailCount']); if ($detailCount > 0) { for ($j = 0; $j < $detailCount; $j++) { $level = ($start + $j); $sheet->setCellValue('C' . $level, $v['detail'][$j]['name'])->getStyle('C' . $level)->getAlignment()->setWrapText(true); $sheet->setCellValue('D' . $level, $v['detail'][$j]['content'])->getStyle('D' . $level)->getAlignment()->setWrapText(true); $sheet->setCellValue('E' . $level, $v['detail'][$j]['project_progress'])->getStyle('E' . $level)->getAlignment()->setWrapText(true); } $sheet->mergecells('A' . $start . ':' . 'A' . $level)->setCellValue('A' . $start, $v['name']); $sheet->mergecells('B' . $start . ':' . 'B' . $level)->setCellValue('B' . $start, date('Y-m-d', strtotime($v['date']))); $sheet->mergecells('F' . $start . ':' . 'F' . $level)->setCellValue('F' . $start, $v['importance']); } $start += $detailCount; } header('Content-Type: application/vnd.ms-excel'); $this->file_name = $this->user_name.'导出日期'.date('Ymd').'_'.date('H_i_s').'.xlsx'; header('Content-Disposition: attachment;filename="'.$this->file_name); header('Cache-Control: max-age=0'); $writer = new Xlsx($this->spreadsheet); $writer->save('php://output'); // if(!is_dir(public_path('static').date('Ymd'))){ // mkdir(public_path('static').date('Ymd'), 0777); // } // $writer->save(public_path('static').date('Ymd').'/'.$this->file_name); //删除清空 $this->spreadsheet->disconnectWorksheets(); unset($this->spreadsheet); } } |
调用方式如下
1 2 3 4 | $export = new ExportController($data['content'], $data['user_name'], $data['importance']); $file_path = $export->exportDate(); //返回路径 $export = new ExportController($data, !empty($param['user_name'])? $param['user_name']: '全部'); $export->exportSearchData(); //直接下载搜索数据导出的excel |
