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

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

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

效果如图：

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

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

composer require PHPoffice/phpspreadsheet

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

 '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;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++;                }            }        }    }} 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;}function create_excel_drop_down($obpe, $sub_table_num, $table_name, $data_source){    /    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}"        )    );}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);}    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

 查看即可

就是如此简单

来源地址：https://blog.csdn.net/u013416034/article/details/132039024