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请求，改成浏览器直接访问
（感谢博客共享和码农们的不懈努力）
