Excel多级联动下拉菜单设置

1.问题描述

现有数据表如下图所示：

该表中包括省、市、县三级目录。

现要将其整理成数据表模板，如下图所示：

要求制作成下拉菜单的形式，且每一级目录的下拉菜单列表要根据上一级目录的内容来确定。

如上图所示，只有在“省”级列表中选择了“北京市”，才能在“市”级列表中选择“北京市市辖区”，进而才能在“县区”级列表中选择“海淀区”“朝阳区”等内容。

2.主要思路

2.1第一步：数据字典整理

首先根据数据表中的内容，整理出多级数据字典，存放在Excel文件中不同的页面。如下图所示：

上图中分别整理出了省、市、县区三级字典。

其中一级字典（省）中，将数据项垂直排列即可；

二级字典（市）中，每行的第1个元素为上一级字典中的项，后续元素为该一级字典对应的二级字典中的项。如在二级字典（市）中，第1行中，第1个元素为“河南省”，后续元素分别为“开封市”“郑州市”。

三级字典（县区）以此类推。

2.2第二步：下拉菜单设置

根据前面制作的数据字典，在Excel中进行相关设置，制作出多级联动下拉菜单。

详细过程参见后文。

3.数据整理

（如果能够手动整理出上述的数据字典，则该部分可以略过。）

为了制作出所需的数据字典，使用Python对数据表进行处理，详细代码如下。

其中path为源文件路径，sheet_name为Excel文件内页面的名称，path_out为输出的字典文件路径。

import pandas as pd"""文件路径设置"""
path = r"D:\temp\data.xlsx"  # 源文件路径
sheet_name = "Sheet1"
path_out = r"D:\temp\data2.xlsx"  # 输出文件路径"""转换函数定义"""
"""用于将DataFrame格式转换为list（二维列表）格式"""
def frame2list(data):data_1 = data.iloc[:,0]  # 提取出DataFrame的第1列set_1 = set(data_1)  # 构建集合，用于将DataFrame第1列的数据去重if len(data.columns) == 1:return list(set_1)else:list_all = list()  # 初始化二维列表，用于存放DataFrame第1列每一项的处理结果for item in set_1:  # 依次处理DataFrame第1列每项数据data_temp = data[data.iloc[:,0]==item]  # 获取DataFrame中该项对应的所有行set_temp = set(data_temp.iloc[:,1])  # 将对应到的第2列中所有数据进行去重list_item = [item]  # 初始化一维列表，用于存放对应到的DataFrame第2列中的每项数据for item_temp in set_temp:  # 依次处理对应到的DataFrame第2列每项数据list_item.append(item_temp)  # 将该第2列数据加入一维列表list_all.append(list_item)  # 将一维列表加入二维列表return list_all"""主函数"""
if __name__ == "__main__":data = pd.read_excel(path, sheet_name=sheet_name, header=0)  # 读取Excel文件class_num = len(data.columns)  # 获取列数with pd.ExcelWriter(path_out) as f:  # 打开输出文件并写入for i in range(class_num):  # 逐列计算并写入输出文件dic_i = set()  # 初始化集合，用于消除重复值if i == 0:dic_i = set(data.iloc[:,0])else:dic_i = frame2list(data.iloc[:,i-1:i+1])df_i = pd.DataFrame(dic_i)  # 转换为DataFrame格式，便于输出为Excel文件df_i.to_excel(f, index=False, header=False, sheet_name=data.columns[i])  # 输出文件

该程序最终输出一个Excel文件，其中包含3个页面，分别对应3级数据字典。

如前文2.1节中的图所示。