Thinkphp5中使用PhpSpreadsheet实现excel特定下拉框联动模板的生成

前言：

最近有遇到过这样的项目需求，需要导出 excel 模板的时候在模板中内置好需要联动的下拉框选择功能，这样在进行excel填写的时候可以进行选择内置的内容。

这里就是进行联动选择的，省、市、区三级进行联动，根据前边选择框中选择的条件联动后续的选择框。

功能实现

具体的实现功能如下：

<?php
namespace 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');
        //如果浏览器为IE9
        header('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.1
        header('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
**