PHP使用PhpSpreadsheet实现导出Excel时带下拉框列表(可支持联动)

最终效果

核心代码

<?php
// 需要的扩展
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\Cell\Coordinate;
?>

class Excel extends Controller
{
   

	/**
     * @name: 导出下拉列表Excel
     * @author: Turbo
     * @Date: 2022-03-30 02:05:24
     */    
    public function exportselectexcel()
    {
   
        // 定义表头
        $header = [
            'A1' => '门店',
            'B1' => '公寓', 
            'C1' => '门牌号', 
            'D1' => '记账类型', 
            'E1' => '记账项目', 
            'F1' => '支出类型', 
            'G1' => '费用分类', 
            'H1' => '金额', 
            'I1' => '记账日期', 
            'J1' => '收款人', 
            'K1' => '收款人帐号', 
            'L1' => '开户支行',
            'M1' => '备注'
        ];

        // 下拉数据[这里模拟出来数据格式，实际情况从数据库获取数据并整理成下列数据格式]
        $oneData = [
            [
                'id' => 1,
                'title' => '我是A',
                'children' =>
                    [
                        [
                            'id' => 2,
                            'title' => '我是A的下级A1',
                            'children' =>
                                [
                                    [
                                        'id' => 3,
                                        'title' => '我是A1的下级A11'
                                    ],
                                    [
                                        'id' => 4,
                                        'title' => '我是A1的下级A12'
                                    ]
                                ]
                        ],
                        [
                            'id' => 5,
                            'title' => '我是A的下级A2',
                            'children' =>
                                [
                                    [
                                        'id' => 6,
                                        'title' => '我是A2的下级A21'
                                    ],
                                    [
                                        'id' => 7,
                                        'title' => '我是A2的下级A22'
                                    ]
                                ]
                        ]
                    ]
            ],
            [
                'id' => 1,
                'title' => '我是B',
                'children' =>
                    [
                        [
                            'id' => 2,
                            'title' => '我是B的下级B1'<