1.下载phpoffice/phpexcel扩展（PHP软件包搜索安装）

composer require phpoffice/phpexcel

项目需求苦逼的码农啊，网上参考摘抄代码后亲测

（代码参考自 树下水月 - PHPOffice/PHPExcel生成省市区三级联动的excel表格 和 1O(∩_∩)O1 - PHPEXCEL 下拉框功能  和 关于PHPExcel 导出下载表格，调试器响应乱码）

2.直接上代码

public function demo()
{
    $subject = 'demo';
    $title = ['工种','考试科目','申报条件','姓名','性别','手机号','证件号码','出生年月','考生类别','学历','专业','现工作地址','职业资格/技能等级证书(如：无，则后面5项无需填写)','职称证书名称','职业资格等级','证书编号','发证日期','发证单位','有无专业技术职称证书(如：无，则后面5项无需填写)','职称证书名称','专业技术等级','证书编号','发证日期','发证单位','领取方式','邮寄地址','备注'];

    /***** 下拉列表联动格式 *****/
   
        $data = [
            [
                'name' => '湖北省', // 省
                'children' => [
                    [
                        'name' => '武汉市', // 市
                        'children' => ['江夏区','洪山区','青山区','武昌区','汉口'] // 区，县
                    ],
                    [
                        'name' => '宜昌市',
                        'children' => ['当阳市','夷陵区','庙前']
                    ],
                    [
                        'name' => '荆州市',
                        'children' => ['荆州区','荆州城区']
                    ]
                ]
            ],
        
        ];

    $high = 0;
    $objPHPExcel = new \PHPExcel();
    $titleRow = array('A1','B1','C1','D1','E1','F1','G1','H1','I1','J1','K1','L1','M1','N1','O1','P1','Q1','R1','S1','T1','U1','V1','W1','X1','Y1','Z1','AA1','AB1','AC1','AD1');
    for($a = 0; $a < count($title); $a++){
             // 设置单元格的值
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($titleRow[$a], $title[$a]);
    }
    $supportSheet = new \PHPExcel_Worksheet($objPHPExcel, 'support'); //创建一个工作表
    $objPHPExcel->addSheet($supportSheet); //插入工作表
    $col = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA'];

    /*********** 下拉列表联动 ***********/
    foreach ($data as $key=>$first){
        $objPHPExcel->getSheetByName('support')->setCellValue($col[0].($key+1+$high),$first['name']);
        $max = 0; //重置max
        $secondNum = count($first['children']);
        foreach ($first['children'] as $index=>$second){
            $objPHPExcel->getSheetByName('support')->setCellValue($col[$index+1].($key+1+$high),$second['name']);
            $thirdNum = count($second['children']);
            if ($thirdNum > $max){
                $max = $thirdNum;
            }
            foreach ($second['children'] as $id=>$third){
                $objPHPExcel->getSheetByName('support')->setCellValue($col[$index+1].($key+1+$high+$id+1),$third);
            }
            //定义三级名称
            $objPHPExcel->addNamedRange(
                new \PHPExcel_NamedRange(
                    $second['name'],
                    $objPHPExcel->getSheetByName('support'),
                    $col[$index+1].($key+1+$high+1).':'.$col[$index+1].($key+1+$high+1+$thirdNum-1)
                )
            );
        }
        //定义二级名称
        $objPHPExcel->addNamedRange(
            new \PHPExcel_NamedRange(
                $first['name'],
                $objPHPExcel->getSheetByName('support'),
                $col[1].($key+1+$high).':'.$col[1+$secondNum-1].($key+1+$high)
            )
        );
        $high += $max;
    }
    //移花接木
    foreach ($data as $var=>$content){
        $objPHPExcel->getSheetByName('support')->setCellValue('UI'.($var+1),$content['name']);
    }


    //定义顶级名称
    /*$total = count($data);
    $str = '';
    $count = 0;
    $max = 0;
    for ($i = 0;$i < $total;$i++){
        $str .= $col[0].(1+$count+$i).',';
        $secondCount = count($data[$i]['children']);
        for ($j = 0;$j < $secondCount;$j++){
            if (count($data[$i]['children'][$j]['children']) > $max){
                $max = count($data[$i]['children'][$j]['children']);
            }
        }
        $count += $max;
    }
    $str = rtrim($str,',');
    $objPHPExcel->addNamedRange(
        new \PHPExcel_NamedRange(
            'region',
            $objPHPExcel->getSheetByName('support'),
            $str
        )
    );*/
    $total = count($data);
    $objPHPExcel->addNamedRange(
        new \PHPExcel_NamedRange(
            'region',
            $objPHPExcel->getSheetByName('support'),
            'UI1'.':'.'UI'.$total
        )
    );

    //数据验证
    for ($i = 2;$i <= 500;$i++){
        $objValidation = $objPHPExcel->getActiveSheet()->getCell('A'.$i)->getDataValidation(); // 设置在A列
        $objValidation->setType(\PHPExcel_Cell_DataValidation::TYPE_LIST );
        $objValidation->setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
        $objValidation->setAllowBlank(false);
        $objValidation->setShowInputMessage(true);
        $objValidation->setShowErrorMessage(true);
        $objValidation->setShowDropDown(true);
        $objValidation->setErrorTitle('输入错误');
        $objValidation->setError('不在列表中的值');
        $objValidation->setPromptTitle('请选择');
        $objValidation->setPrompt('请从列表中选择一个值.');
        $objValidation->setFormula1("=region"); //设置公式1

        $objValidation = $objPHPExcel->getActiveSheet()->getCell('B'.$i)->getDataValidation(); // 设置在B列
        $objValidation->setType(\PHPExcel_Cell_DataValidation::TYPE_LIST );
        $objValidation->setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
        $objValidation->setAllowBlank(false);
        $objValidation->setShowInputMessage(true);
        $objValidation->setShowErrorMessage(true);
        $objValidation->setShowDropDown(true);
        $objValidation->setErrorTitle('输入错误');
        $objValidation->setError('不在列表中的值');
        $objValidation->setPromptTitle('请选择');
        $objValidation->setPrompt('请从列表中选择一个值.');
        $objValidation->setFormula1('=INDIRECT($'.'A'.'$'.$i.')'); //设置公式1，绑定A列

        $objValidation = $objPHPExcel->getActiveSheet()->getCell('C'.$i)->getDataValidation(); // 设置在C列
        $objValidation->setType(\PHPExcel_Cell_DataValidation::TYPE_LIST );
        $objValidation->setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
        $objValidation->setAllowBlank(false);
        $objValidation->setShowInputMessage(true);
        $objValidation->setShowErrorMessage(true);
        $objValidation->setShowDropDown(true);
        $objValidation->setErrorTitle('输入错误');
        $objValidation->setError('不在列表中的值');
        $objValidation->setPromptTitle('请选择');
        $objValidation->setPrompt('请从列表中选择一个值.');
        $objValidation->setFormula1('=INDIRECT($'.'B'.'$'.$i.')'); //设置公式1，绑定B列
    }
    /*********** 下拉列表联动结束 ***********/

    /************ 下拉列表 ************/
            
        // 级别
        // $enroll_level = Dictionary::query()
        //     ->where('code_type','enroll_level')
        //     ->orderBy('sort','DESC')
        //     ->pluck('name')->toArray();
    $spectials = [
        ['column'=>'E','select_options'=>['男','女']],// 性别
        // ['column'=>'F','select_options'=>$enroll_level],// 级别
    ];
    $objActSheet = $objPHPExcel->getActiveSheet();
    foreach($spectials as $spectial)
    {
        $optionsString = null;
        $optionsString = implode(',', $spectial['select_options']);

        $n = 2;
        $num = 500;
        // 我这里设置500行，可自行设置
        while($n <= $num) {
            $objValidation = $objActSheet->getCell($spectial['column'].(string)$n)->getDataValidation(); //这一句为要设置数据有效性的单元格
            // $objValidation的各项设置参数可详见phpexcel文件，
            // 目录大概为/.../phpoffice/phpexcel/Classes/PHPExcel/Writer/Excel5/Worksheet.php
            // 2767行 方法名：writeDataValidity
            $objValidation -> setType(\PHPExcel_Cell_DataValidation::TYPE_LIST)
                -> setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_STOP)
                -> setAllowBlank(true)
                -> setShowInputMessage(true)
                -> setShowErrorMessage(true)
                -> setShowDropDown(true)
                -> setErrorTitle('输入的值有误')
                -> setError('您输入的值不在下拉框列表内.')
                -> setPromptTitle('')
                -> setPrompt('')
                -> setOperator(\PHPExcel_Cell_DataValidation::OPERATOR_BETWEEN)
                -> setFormula1('"'.$optionsString.'"');
            $n++;
        }
    }
    /*********** 下拉列表结束 ************/
    /************* 时间格式限制 **************/
    for ($ii=2; $ii<=500; $ii++)
    {
        $objPHPExcel->getActiveSheet()->getStyle("H".(string)$ii)->getNumberFormat()->setFormatCode("YYYY-m-d");
        $objPHPExcel->getActiveSheet()->getStyle("Q".(string)$ii)->getNumberFormat()->setFormatCode("YYYY/m/d");
    }

    $objPHPExcel->setActiveSheetIndex(0);
    //输出表格
    $filename = $subject.date('Ymd').time().'.xlsx'; // 表格名称
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename='.$filename);
    header('Cache-Control: max-age=0');

    $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save($filename); // 保存
    
    // 直接下载
    // ob_end_clean();//清除缓冲区,避免乱码
    // header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=utf-8'); // 
    // header("Pragma: public");
    // header("Expires: 0");
    // header("Access-Control-Allow-Origin:*");
    // header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
    // header("Content-Type:application/force-download");
    // header("Content-Type:application/octet-stream");
    // header("Content-Type:application/download");
    // header('Content-Disposition: attachment;filename='.$filename);
    // header('Cache-Control: max-age=0');
    // $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    // $objWriter->save('php://output');
    // exit();

}

直接下载浏览器调试器响应是乱码，不能AJAX请求，改成浏览器直接访问

（感谢博客共享和码农们的不懈努力）