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

因项目需要导出Excel表   需要支持下拉   且 还需要支持三级联动功能 

目前应为PHPExcel 不在维护，固采用 PhpSpreadsheet

效果如图：

 第一步：首先 使用composer 获取PhpSpreadsheet

我这里PHP 版本 7.4  命令如下：

composer require phpoffice/phpspreadsheet

 第二步：新建php 文件 代码如下   可根据实际情况 自己改动：

<?php

require './vendor/autoload.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;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;


 // 定义表头
$header = [
    ['col' => 'A', 'title' => '姓名', 'field' => 'name', 'width' => 20, 'id_down_type' => 0, 'is_text' => 1],
    ['col' => 'B', 'title' => '证件号', 'field' => 'id_card', 'width' => 30, 'id_down_type' => 0, 'is_text' => 1],
    ['col' => 'C', 'title' => '性别', 'field' => 'gender', 'width' => 20, 'id_down_type' => 1, 'is_text' => 1, 'count' => 2, 'sub_table_name' => 'sex'],
    ['col' => 'D', 'title' => '联系方式', 'field' => 'phone', 'width' => 20, 'id_down_type' => 0, 'is_text' => 1],
    ['col' => 'E', 'title' => '门店', 'field' => 'mendiao', 'width' => 30, 'id_down_type' => 2, 'is_text' => 0],
    ['col' => 'F', 'title' => '公寓', 'field' => 'gongyu', 'width' => 30, 'id_down_type' => 2, 'is_text' => 0],
    ['col' => 'G', 'title' => '门牌号', 'field' => 'menpai', 'width' => 30, 'id_down_type' => 2, 'is_text' => 0],
];

// 下拉数据[这里模拟出来数据格式，实际情况从数据库获取数据并整理成下列数据格式]
$oneData = [
    [
        'id' => 1,
        'title' => '我是A',
        'text'=>'我是A_1',
        'children' =>
            [
                [
                    'id' => 2,
                    'title' => '我是A的下级A1',
                    'text'=>'我是A的下级A1_2',
                    'children' =>
                        [
                            [
                                'id' => 3,
                                'title' => '我是A1的下级A11',
                                'text'=>'我是A1的下级A11_3',
                            ],
                            [
                                'id' => 4,
                                'title' => '我是A1的下级A12',
                                'text'=>'我是A1的下级A12_4',
                            ]
                        ]
                ],
                [
                    'id' => 5,
                    'title' => '我是A的下级A2',
                    'text'=>'我是A的下级A2_5',
                    'children' =>
                        [
                            [
                                'id' => 6,
                                'title' => '我是A2的下级A21',
                                'text'=>'我是A2的下级A21_6',
                            ],
                            [
                                'id' => 7,
                                'title' => '我是A2的下级A22',
                                'text'=>'我是A2的下级A22_7',
                            ]
                        ]
                ]
            ]
    ],
    [
        'id' => 8,
        'title' => '我是B',
        'text'=>'我是B_8',
        'children' =>
            [
                [
                    'id' => 9,
                    'title' => '我是B的下级B1',
                    'text'=>'我是B的下级B1_9',
                    'children' =>
                        [
                            [
                                'id' => 10,
                                'title' => '我是B1的下级B11',
                                'text'=>'我是B1的下级B11_10',
                            ],
                            [
                                'id' => 11,
                                'title' => '我是B1的下级B12',
                                'text'=>'我是B1的下级B12_11',
                            ]
                        ]
                ],
                [
                    'id' => 12,
                    'title' => '我是B的下级B2',
                    'text' => '我是B的下级B2_12',
                    'children' =>
                        [
                            [
                                'id' => 13,
                                'title' => '我是B2的下级B21',
                                'text' => '我是B2的下级B21_13',
                            ],
                            [
                                'id' => 14,
                                'title' => '我是B2的下级B22',
                                'text' => '我是B2的下级B22_14'
                            ]
                        ]
                ]
            ]
    ],
];

 //实例化Spreadsheet对象
$spreadsheet = new Spreadsheet();

$sex_list = [
        ['id' => 1, 'text' => '男'],
        ['id' => 2, 'text' => '女'],
 ];
$sub_table_num = 1;  //子表顺序  默认1 
//创建子表下拉定义  用于下拉
$sub_table_info_arr = [
    ['title' => 'sex', 'index' => $sub_table_num, 'data' => $sex_list],
];
$sub_table_num++;

//创建下拉分表数据
create_excel_sub_table($spreadsheet, $sub_table_info_arr);


//创建下拉联动分表数据 
$liandong_name = 'region';  // 这里的region为分表名称
create_excel_drop_down($spreadsheet, $sub_table_num, $liandong_name, $oneData);  //无限极oneData   里面结构为[['text'=>'部门1_00001','children'=>[['text'=>'部门1子集1_00001']]]


// 初始化表头
$spreadsheet->setActiveSheetIndex(0);
$spreadsheet->getActiveSheet()->setTitle('导入模板');

foreach ($header as $key => $value) {

    //$spreadsheet->getActiveSheet()->setCellValueByColumnAndRow($key, 1, $value['title']);
    $spreadsheet->setActiveSheetIndex(0)->setCellValue($value['col'] . "1", $value['title']);
    $spreadsheet->getActiveSheet()->getStyle($value['col'] . "1", $value['title'])->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED); //设置红色文字

    // 设置每列宽度
    $spreadsheet->getActiveSheet()->getColumnDimension($value['col'])->setWidth($value['width']);
    //设置单元格格式为文本
    if ($value['is_text'] == 1) {
        $spreadsheet->getActiveSheet()->getStyle($value['col'])->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_TEXT);
    }
}
$objActSheet = $spreadsheet->getActiveSheet();

// 输出下拉菜单
for ($i = 2; $i < 2000; $i++) {

    foreach ($header as $key1 => $value1) {
        //$this->set_select_cell($objActSheet,'A','id_type',$key,$i);
        if ($value1['id_down_type'] == 1) {
            //单纯下拉
            set_select_cell($objActSheet, $value1['col'], $value1['sub_table_name'], $value1['count'], $value1['title'], $i);
        } elseif ($value1['id_down_type'] == 2) {
            //联动下拉
            if ($value1['col'] == 'A' || $value1['col'] == 'E' || $value1['col'] == 'AC') {
                switch ($value1['col']) {
                    case 'E':
                        $fit_col = ['F','G'];
                        break;
                    case 'AC':
                        //这里为关联 如AC 列  关联AD AE 这个
                        $fit_col = ['AD', 'AE'];
                        break;
                }
                $mian_col = $value1['col'];
                //处理联动下拉
                set_drop_down_select_cell($objActSheet, $mian_col, $fit_col, $value1['title'], $i,$liandong_name);
            }
        }
    }
}
$path = "./";
$subject = "导出模板".date('YmdHis');
$ext = ".xlsx";  //后缀
$export = $path . $subject . $ext;
$writer = new Xlsx($spreadsheet);
//保存文件
$writer->save($export);
//关闭连接，销毁变量
$spreadsheet->disconnectWorksheets();

unset($spreadsheet);
var_dump($export);
die;


/**
 * 创建Excel 分表
 * @param obpe Excel 对象
 * @param sub_table_info_arr  需要需要创建的分表二维数组   key title_name 分表表名标题    key index 分表顺序  key data 数据源  且数据源必须为 id text 键值对
 * @param write_data 写入的数据    二维  包含 id text 两个键值
 */
function create_excel_sub_table($obpe, $sub_table_info_arr)
{
    if (!empty($sub_table_info_arr)) {
        foreach ($sub_table_info_arr as $key => $value) {
            $obpe->createSheet();

            $obpe->setActiveSheetIndex($value['index']);  //设置序号
            $obpe->getActiveSheet()->setTitle($value['title']); //设置表名
            $obpe->getActiveSheet()->setSheetState(Worksheet::SHEETSTATE_HIDDEN); //隐藏


            //密码
            $obpe->getActiveSheet()->getProtection()->setPassword('PHPExcel');
            $obpe->getActiveSheet()->getProtection()->setSheet(true);// This should be enabled inorder to enable any of thefollowing!
            $obpe->getActiveSheet()->getProtection()->setSort(true);
            $obpe->getActiveSheet()->getProtection()->setInsertRows(true);
            $obpe->getActiveSheet()->getProtection()->setFormatCells(true);


            $key = 1;
            if (!empty($value['data'])) {
                foreach ($value['data'] as $k => $v) {
                    $str = $v['text'] . '_' . $v['id'];
                    $obpe->getActiveSheet()->setCellValue('A' . $key, $str);
                    $key++;
                }
            }
        }
    }
}

/**
 * 生成Excel 列
 * @param int $num 需要的列数
 * @return array
 */
 function  getExcelIndexRow($num=1){
    $r = [];
    $idx = ['idx' => 0];
    for ($i = 0; $i < $num; $i++) {
        $r[] =  getNextIdx($idx);
    }
    unset($idx);
    return $r;
}


function getNextIdx(&$idx) {
     // 先转换26进制
    $num26 = base_convert($idx['idx'], 10, 26);

    $result = '';
    // 对每一位进行字符转换
    for ($i = 0; $i < strlen(strval($num26)); $i++) {
        if ($i == 0 && strlen(strval($num26)) != 1) {
            if (is_numeric($num26[$i])) {
                $result.= chr(ord($num26[$i]) + 16);
            } else {
                $result.= chr(ord($num26[$i]) - 23);
            }
        } else {
            if (is_numeric($num26[$i])) {
                $result.= chr(ord($num26[$i]) + 17);
            } else {
                $result.= chr(ord($num26[$i]) - 22);
            }
        }
    }
    $idx['idx'] = $idx['idx'] + 1;
    return $result;
}

/**
 *生成Excel 联动下拉
 * @param obpe Excel对象句柄
 * @param sub_table_num 分表序号
 * @param table_name 分表名称
 * @param data_source 需要处理的数据源
 */
function create_excel_drop_down($obpe, $sub_table_num, $table_name, $data_source)
{
    //***********************
    $obpe->createSheet();
    $obpe->setActiveSheetIndex($sub_table_num);
    $obpe->getActiveSheet()->setTitle($table_name);
    $obpe->getActiveSheet()->setSheetState(\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::SHEETSTATE_HIDDEN); //隐藏

    //密码
    $obpe->getActiveSheet()->getProtection()->setPassword('PHPExcel');
    $obpe->getActiveSheet()->getProtection()->setSheet(true);// This should be enabled inorder to enable any of thefollowing!
    $obpe->getActiveSheet()->getProtection()->setSort(true);
    $obpe->getActiveSheet()->getProtection()->setInsertRows(true);
    $obpe->getActiveSheet()->getProtection()->setFormatCells(true);

    $key = 1;
    //$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', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ', 'BA'];
    //这里定义1000个下拉  若超过 1000个下拉 异常输出
    $col = getExcelIndexRow(1000);
    $high = 0;

    //数据源格式必须按照以下格式规整 要不然数据处理出错
    /*$re = [['name'=>'湖北省','children'=>[['name'=>"武汉市",'children' =>['江夏区','洪山区','青山区','武昌区']],['name'=>"宜昌市",'children' =>['江夏区1','洪山区1','青山区1','武昌区1']],['name'=>"荆州市",'children' =>['江夏区2','洪山区2','青山区2','武昌区2']]]],
    ['name'=>'湖南省','children'=>[['name'=>"长沙市",'children' =>['长沙市','长沙市1','长沙市3','长沙市6']],['name'=>"岳阳市",'children' =>['岳阳市4','岳阳市3','岳阳市2','岳阳市1']],[ 'name'=>"常德市",'children' =>['常德市1','常德市2','常德市3','常德市4']]]],];*/
    foreach ($data_source as $k => $v) {
        $str = $v['text'];
        $obpe->getActiveSheet()->setCellValue($col[0] . ($key + $high), $str);
        $max = 0; // 重置max
        $secondNum = count($v['children']);
        if (!empty($v['children'])) {
            foreach ($v['children'] as $index => $sendcond) {
                $obpe->getActiveSheet()->setCellValue($col[$index + 1] . ($key + $high), $sendcond['text']);
                //这里需要处理  若没有
                if (isset($sendcond['children']) && $sendcond['children']) {
                    $thirdNum = count($sendcond['children']);
                } else {
                    $thirdNum = 0;
                }

                if ($thirdNum > $max) {
                    $max = $thirdNum;
                }
                if (!empty($sendcond['children'])) {
                    foreach ($sendcond['children'] as $id => $third) {
                        //$obpe->getActiveSheet()->setCellValue($col[$index+1].($key+$high+$id+1),$third);
                        $obpe->getActiveSheet()->setCellValue($col[$index + 1] . ($key + $high + $id + 1), $third['text']);
                    }
                    //定义三级名称
                    $obpe->addNamedRange(
                        new NamedRange(
                            $sendcond['text'],
                            $obpe->getSheetByName($table_name),
                            $col[$index + 1] . ($key + $high + 1) . ':' . $col[$index + 1] . ($key + $high + 1 + $thirdNum - 1)
                        )
                    );
                }
            }
            //定义二级
            $obpe->addNamedRange(
                new NamedRange(
                    $v['text'],
                    $obpe->getSheetByName($table_name),
                    $col[1] . ($key + $high) . ':' . $col[$secondNum] . ($key + $high)
                )
            );
        }

        $high += $max;
        $key++;

    }

    //移花
    foreach ($data_source as $var => $cont) {
        $obpe->getSheetByName($table_name)->setCellValue('UI' . ($var + 1), $cont['text']);
    }
    $total = count($data_source);
    $n = 1;
    $obpe->addNamedRange(
        new NamedRange(
            $table_name,
            $obpe->getSheetByName($table_name),
            "=\$UI\${$n}:\$UI\${$total}"
        )
    );
}


/**
 *设置Excel  下拉 2020年8月4日16:02:09
 * @param objActSheet Excel对象句柄
 * @param col         操作的列
 * @param table_name  Excel电子薄分表下拉名称
 */
function set_select_cell($objActSheet, $col = '', $table_name, $key = '', $setPromptTitle, $i = '', $setErrorTitle = '输入的值有误', $setError = '您输入的值不在下拉列表内')
{
    $objValidation = $objActSheet->getCell("{$col}" . $i)->getDataValidation();
     $objValidation->setType(DataValidation::TYPE_LIST)
          // ->setErrorStyle(DataValidation::STYLE_INFORMATION)
          ->setErrorStyle(DataValidation::STYLE_STOP)
          ->setAllowBlank(true)
          ->setShowInputMessage(true)
          ->setShowErrorMessage(true)
          ->setShowDropDown(true)
          ->setErrorTitle('输入的值有误')
          ->setError('您输入的值不在下拉列表内')
          ->setPromptTitle("{$setPromptTitle}（在列表内选择）")
          ->setPrompt('请从列表中选择一个值')
          ->setFormula1($table_name . '!$A$1:$A$' . $key);
}

    /**
     * 设置联动下拉的Excel 渲染 2020年8月8日20:23:39
     * @param objActSheet Excel对象句柄
     * @param mian_col    联动下拉第一列
     * @param fit_col     数组   子下拉节点  setFormula1 依次为下拉第一列名   后续依次关联
     * @param setPromptTitle       中文字段映射名称
     */
function set_drop_down_select_cell($objActSheet, $main_col, $fit_col, $setPromptTitle, $i,$main_table_name='region')
{
    $indirect_list_arr = $fit_col;  //从主下拉节点开始计算
    array_unshift($indirect_list_arr, $main_col);
    //定义主表名
    
    //处理主下拉
    $objValidation = $objActSheet->getCell($main_col . $i)->getDataValidation();
    $objValidation->setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST)
        ->setErrorStyle(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_STOP)
        ->setAllowBlank(false)
        ->setShowInputMessage(true)
        ->setShowErrorMessage(true)
        ->setShowDropDown(true)
        ->setErrorTitle('输入的值有误')
        ->setError('您输入的值不在下拉列表内')
        ->setPromptTitle("{$setPromptTitle}（在列表内选择）")
        //->setPromptTitle("type_list（在列表内选择）")
        ->setPrompt('请从列表中选择一个值')
        ->setFormula1("={$main_table_name}");
    //处理分节点下拉
    if (!empty($fit_col)) {
        foreach ($fit_col as $key => $value) {
            $objValidation = $objActSheet->getCell($value . $i)->getDataValidation();
            $objValidation->setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST)
                ->setErrorStyle(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_STOP)
                ->setAllowBlank(false)
                ->setShowInputMessage(true)
                ->setShowErrorMessage(true)
                ->setShowDropDown(true)
                ->setErrorTitle('输入的值有误')
                ->setError('您输入的值不在下拉列表内')
                ->setPromptTitle('请选择')
                ->setPrompt('请从列表中选择一个值')
                ->setFormula1('=INDIRECT($' . $indirect_list_arr[$key] . $i . ')');
        }
    }
    return true;
}

步骤三： 运行php   我这里使用命令行 

php index.php

 查看即可

就是如此简单