<?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);
    }
}
调用方法:

$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