前言：
最近有遇到过这样的项目需求，需要导出 excel 模板的时候在模板中内置好需要联动的下拉框选择功能，这样在进行excel填写的时候可以进行选择内置的内容。
这里就是进行联动选择的，省、市、区三级进行联动，根据前边选择框中选择的条件联动后续的选择框。
功能实现
具体的实现功能如下：
<?phpnamespace app\design\controller;use PhpOffice\PhpSpreadsheet\Cell\DataValidation;use PhpOffice\PhpSpreadsheet\IOFactory;use PhpOffice\PhpSpreadsheet\NamedRange;use PhpOffice\PhpSpreadsheet\Spreadsheet;use PhpOffice\PhpSpreadsheet\Style\Alignment;use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;use PhpOffice\PhpSpreadsheet\Writer\Xlsx;use think\Controller;class Testexcel extends Controller{/*** 下载excel模板* @throws \PhpOffice\PhpSpreadsheet\Exception* @throws \PhpOffice\PhpSpreadsheet\Writer\Exception*/public function exportExcel(){//定义一个excel的header表头$header = ['A1' => '序号', 'B1' => '姓名', 'C1' => '证件号', 'D1' => '张数', 'E1' => '证件类型', 'F1' => '省', 'G1' => '市', 'H1' => '区/镇', 'I1' => '类别', 'J1' => '设计人', 'K1' => '完成时间', 'L1' => '备注'];//需要填充的联动数据$major = [['id' => 1,'title' => '河南省','children' =>[['id' => 2,'title' => '郑州市','children' =>[['id' => 3,'title' => '郑东新区'],['id' => 4,'title' => '金水区']]],['id' => 5,'title' => '信阳市','children' =>[['id' => 6,'title' => '浉河区'],['id' => 7,'title' => '平桥区']]]]],['id' => 8,'title' => '江苏省','children' =>[['id' => 9,'title' => '南京市','children' =>[['id' => 10,'title' => '秦淮区'],['id' => 11,'title' => '玄武区']]],['id' => 12,'title' => '苏州市','children' =>[['id' => 13,'title' => '姑苏区'],['id' => 14,'title' => '吴中区']]]]]];//类别$typeStr = '是,否';//证件类型$biaoduanStr = '身份证,驾照,护照,学生证';//excel中A-Z单元格$letters = range('A', 'Z');//实例化Spreadsheet对象$spreadsheet = new Spreadsheet();$sheetMain = $spreadsheet -> getsheet(0);//主sheet$sheetMain -> setTitle('人员信息');//设置sheet的名称$sheetMain -> getPageSetup() -> setHorizontalCentered(true);$sheetMain -> getPageSetup() -> setVerticalCentered(false);foreach ($header as $key => $value) {$sheetMain -> setCellValue($key, $value);}$sheetMajor = $spreadsheet -> createSheet(1); //省市sheet$sheetProp = $spreadsheet -> createSheet(2); //市区sheet$sheetPropCol = 0;//属性sheet的列foreach ($major as $key => $value) {//省,一个省一列，第一行为市，第二行为区/镇$row = 1; // 专业和子专业sheet的第n行$sheetMajor -> setCellValue($letters[$key] . $row, $value['title']);//所有if (!empty($value['children'])) {foreach ($value['children'] as $value2) {//市$row2 = 1;//属性sheet的第n行$sheetMajor -> setCellValue($letters[$key] . ++$row, $value2['title']);$sheetProp -> setCellValue($letters[$sheetPropCol] . $row2, $value2['title']);if (!empty($value2['children'])) {foreach ($value2['children'] as $value3) {//区/镇$sheetProp -> setCellValue($letters[$sheetPropCol] . ++$row2, $value3['title']);}$spreadsheet -> addNamedRange(new NamedRange($value2['title'], $sheetProp, $letters[$sheetPropCol] . '2:' . $letters[$sheetPropCol] . $row2));//设置市=>区/镇联动}$sheetPropCol++;//属性sheet列+1}$spreadsheet -> addNamedRange(new NamedRange($value['title'], $sheetMajor, $letters[$key] . '2:' . $letters[$key] . $row));}}$spreadsheet -> addNamedRange(new NamedRange('major', $sheetMajor, 'A1:' . $letters[count($major)] . '1'));$sheetMajor -> setSheetState(Worksheet::SHEETSTATE_HIDDEN);//隐藏省市sheet$sheetProp -> setSheetState(Worksheet::SHEETSTATE_HIDDEN);//隐藏市区sheet$maxRows = 200;//模板填充行数foreach (range(2, $maxRows) as $row) {//200行内列数E、F、G、H、I设置成可以下拉选择$this -> setValidation($sheetMain, "E${row}", '"' . $biaoduanStr . '"');$this -> setValidation($sheetMain, "F${row}", '=major');$this -> setValidation($sheetMain, "G${row}", "=INDIRECT(F${row})");//indirect间接的，以某个坐标为依托，联级$this -> setValidation($sheetMain, "H${row}", "=INDIRECT(G${row})");//indirect间接的，以某个坐标为依托，联级$this -> setValidation($sheetMain, "I${row}", '"' . $typeStr . '"');$this -> setInputRule($sheetMain, "A${row}", DataValidation::TYPE_DECIMAL);//设置输入数字类型$this -> setInputRule($sheetMain, "D${row}", DataValidation::TYPE_DECIMAL);//设置输入数字类型$this -> setInputRule($sheetMain, "K${row}", DataValidation::TYPE_TIME);//设置输入时间类型}//调整sheet样式$styleArray = ['font' => ['bold' => true], 'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER]];$sheetMain -> getStyle('A1:L1') -> applyFromArray($styleArray);$sheetMain -> getColumnDimension('B') -> setWidth(25);$sheetMain -> getColumnDimension('C') -> setWidth(25);$sheetMain -> getColumnDimension('E') -> setWidth(20);$sheetMain -> getColumnDimension('F') -> setWidth(20);$sheetMain -> getColumnDimension('G') -> setWidth(20);$sheetMain -> getColumnDimension('H') -> setWidth(20);$sheetMain -> getColumnDimension('K') -> setWidth(20);//$sheetMain->getColumnDimension('H')->setAutoSize(true); // 自动宽度，列表无用//定义文件名称，需要带有定义的后缀名$filename = 'test.xlsx';ob_end_clean(); //清除缓冲区,避免乱码//将输出重定向到客户端的web浏览器header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');header('Content-Disposition: attachment;filename="' . $filename . '"');header('Cache-Control: max-age=0');//如果浏览器为IE9header('Cache-Control: max-age=1');//如果通过SSL向IE提供服务header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');header('Cache-Control: cache, must-revalidate');//HTTP/1.1header('Pragma: public');//HTTP/1.0$writer = IOFactory ::createWriter($spreadsheet, 'Xlsx');$writer -> save('php://output');exit;}/*** 设置某个单元格的下拉列表规则* @param Worksheet $sheet* @param [string] $cellPoint 单元格坐标. A1* @param [sting] $format 公式* @return void*/protected function setValidation(Worksheet $sheet, $cellPoint, $format){$validation = $sheet -> getCell($cellPoint) -> getDataValidation();$validation -> setType(DataValidation::TYPE_LIST);$validation -> setErrorStyle(DataValidation::STYLE_INFORMATION);$validation -> setAllowBlank(false);$validation -> setShowInputMessage(true);$validation -> setShowErrorMessage(true);$validation -> setShowDropDown(true);$validation -> setErrorTitle('输出错误');$validation -> setError('值不在列表中');$validation -> setPromptTitle('请选择');$validation -> setPrompt('请从列表中选择一个值');$validation -> setFormula1($format);// $validation->setFormula1('=major');//使用某个公式// $validation->setFormula1('"A1,A2,A3"');//可以直接写列表，用逗号分隔,最多255字符// $objValidation->setFormula1('=INDIRECT(F5)');//indirect间接的，以某个坐标为依托，联级}/*** 设置某个单元格的输入规则* @param Worksheet $sheet* @param [string] $cellPoint 单元格坐标. A1* @param [sting] type 输入类型 DataValidation::TYPE_LIST* @return void*/protected function setInputRule(Worksheet $sheet, $cellPoint, $type){$validation = $sheet -> getCell($cellPoint) -> getDataValidation();$validation -> setType($type);$validation -> setErrorStyle(DataValidation::STYLE_INFORMATION); //输错了的话显示的提示属于哪个级别$validation -> setAllowBlank(false);$validation -> setShowInputMessage(true);$validation -> setShowErrorMessage(true);$validation -> setErrorTitle('输入格式有误');}}
导出的 excel 模板如下：
**
注意：
目前测试的phpspreadsheet版本为1.11，后续在实际项目中经过测试，1.2版本以上的此方法不生效，联动下拉不起作用，为了方便起见，默认安装1.11版本的。 composer require phpoffice/phpspreadsheet ^1.11
**
