1 主要目的

在Excel中，經常會遇到需要制作多級聯動下拉菜單的情況，要求單元格內填寫的內容只能從指定的多個選項中進行選擇，并且需要設置多級目錄，其中下級目錄的選項內容要根據上級目錄的填寫內容確定，如下圖所示：

?

上圖中的數據區域均要求通過下拉菜單實現。其中A列要通過下拉菜單選擇省份信息，B列要根據相應的省份信息，通過下拉菜單選擇相應的地市信息。

之前的博客已經講過多級聯動下拉菜單在Excel中的設置步驟。但考慮到數據量較大的情況，手動在Excel文件中進行設置的工作量較大，本文通過Python中的openpyxl模塊實現多級聯動下拉菜單的自動化設置。

2 基本原理

2.1 Excel中多級聯動下拉菜單設置的主要步驟

Excel中的下拉菜單主要通過名稱管理器以及數據驗證界面進行設置。

名稱管理器的界面如下圖所示。其中“名稱”欄記錄各選項集的名稱，“數值”欄記錄各選項集中的所有選項，“引用位置”欄記錄選項集中各選項在Excel文件中所在的單元格。

數據驗證界面如下圖所示。其中“來源”欄可通過indirect函數與名稱管理器中的選項集名稱進行關聯，以此來設置該單元格的選項范圍為該選項集中的選項；也可通過indirect函數關聯上一級目錄對應的單位格位置，以設置本單元格的選項應根據上一級單元格的內容來確定，實現多級聯動下拉菜單設置。

?

?

2.2 使用openpyxl完成自動化設置的主要思路?

由于Excel中的名稱管理器以及數據驗證相關設置均可由openpyxl模塊完成，可以通過該模塊對相關數據進行處理，并完成對Excel文件的相關設置，以實現多級聯動下拉菜單設置的自動化。主要包括三個關鍵步驟：

（1）將各選項集的信息寫入Excel文件。通過openpyxl模塊的相關操作，將各選項集信息寫入Excel文件中指定的頁面。

（2）將各選項集的信息加載至名稱管理器。由于openpyxl.workbook.defined_name模塊中的DefinedName函數可以進行Excel名稱管理器相關設置，可以將各選項集信息加載至名稱管理器。

（3）進行數據驗證相關內容設置。由于openpyxl.worksheet.datavalidation模塊中的DataValidation函數可以對數據驗證相關內容進行設置，可以通過該方法完成下拉菜單的制作。

3 選項數據準備

由于Excel中的多級聯動下拉菜單選項通常需要通過名稱管理器進行關聯，為了便于將數據選項寫入Excel中的名稱管理器，在Python中建立有序字典（OrderedDict），以保存下拉菜單的選項信息。

字典中的每條數據作為一個選項集合，鍵（key）為選項集名稱，值（value）為選項集中的選項。

代碼如下：

# data_options.pyfrom collections import OrderedDictdic = OrderedDict()
dic["省份"] = ["河北省","河南省","山西省"]
dic["河北省"] = ["石家莊市","保定市","張家口市"]
dic["河南省"] = ["鄭州市","開封市","洛陽市"]
dic["山西省"] = ["太原市","運城市","大同市"]if __name__ == "__main__":for key, value in dic.items():print(f"key = {key}, value = {value}")

如果選項集較多、數據量較大，可以使用Pandas模塊進行讀取和處理，并轉換為上述OrderedDict格式。具體過程不再贅述。

4 主要步驟

4.1 將選項數據寫入Excel文件

為了制作Excel中的下拉菜單，需要獲取每個下拉菜單的選項信息，即選項信息需要保存在Excel中。這里使用openpyxl模塊將選項信息寫入Excel文件。代碼如下：

# write_options.pyfrom data_options import dic
from openpyxl import Workbook
from openpyxl.utils import get_column_letterpath = r"D:\temp\table01.xlsx"  # 文件輸出路徑
sheet_name_dic = "dic"  # 保存在Excel中頁面的名稱book = Workbook()  # 新建Workbook
for sheet in book.sheetnames:del book[sheet]  # 刪除其他頁面
sheet_dic = book.create_sheet(sheet_name_dic)  # 新建sheetfor num, (key, value) in enumerate(dic.items(), start=1):sheet_dic[f"A{num}"] = key  # 第1列的值為key值（選項集名稱）for i in range(len(value)):sheet_dic[f"{get_column_letter(i+2)}{num}"] = value[i]# （上一行）依次將該選項集中的選項填充在該行后續列的單元格中book.save(path)  # 保存Excel文件

運行上述代碼后，打開保存的Excel文件（table01.xlsx）中的dic頁面如下：

其中A列為各選項集的名稱，B列及后續列為各選項集中的選項名稱。

4.2 Excel中名稱管理器設置

在將選項信息寫入Excel文件后，需要通過名稱管理器將選項信息與下拉菜單進行關聯，因此需要將各選項集的選項信息寫入Excel的名稱管理器中。

該步驟可以結合上一步（將選項數據寫入Excel文件）進行，在遍歷每個選項集的過程中，將該選項集的信息寫入Excel文件相應位置后，同步保存在Excel的名稱管理器中。

因此可以改寫上一步的代碼（write_options.py文件），加入保存名稱管理器相關步驟。主要添加以下代碼：

attr_text = f"{sheet_name_dic}!$B${num}:${get_column_letter(len(value)+1)}${num}"
# （上一行）設置名稱管理器中每個選項集的取值區域（如：attr_text = "dic!$B$1:$D$1）
value_range = DefinedName(key, attr_text=attr_text)  # 將選項集的名稱和選項映射為Excel名稱管理器中的格式
book.defined_names.append(value_range)  # 加入Excel中的名稱管理器

上述代碼中，第1行設置了每個選項集的選項取值區域，第3行將選項集名稱與選項信息轉換為Excel名稱管理器相關格式，第4行將該選項集信息加入Excel的名稱管理器。

改寫后的代碼如下：

# write_options.pyfrom data_options import dic
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
from openpyxl.workbook.defined_name import DefinedNamepath = r"D:\temp\table01.xlsx"  # 文件輸出路徑
dic_name = "dic"  # 保存在Excel中頁面的名稱book = Workbook()  # 新建Workbook
for sheet in book.sheetnames:del book[sheet]  # 刪除其他頁面
sheet_dic = book.create_sheet(dic_name)  # 新建sheetfor num, (key, value) in enumerate(dic.items(), start=1):sheet_dic[f"A{num}"] = key  # 第1列的值為key值（選項集名稱）for i in range(len(value)):sheet_dic[f"{get_column_letter(i+2)}{num}"] = value[i]# （上一行）依次將該選項集中的選項填充在該行后續列的單元格中attr_text = f"{dic_name}!$B${num}:${get_column_letter(len(value)+1)}${num}"# （上一行）設置名稱管理器中每個選項集的取值區域（如：attr_text = "dic!$B$1:$D$1）value_range = DefinedName(key, attr_text=attr_text)  # 將選項集的名稱和選項映射為Excel名稱管理器中的格式book.defined_names.append(value_range)  # 加入Excel中的名稱管理器sheet_dic.sheet_state = "hidden"  # 隱藏該頁面
book.save(path)  # 保存Excel文件

運行上述代碼后，打開保存的Excel文件（table01.xlsx）中的名稱管理器如下：

可知各選項集的選項信息以保存在Excel文件的名稱管理器中。?

4.3 Excel中數據驗證設置

為了設置Excel中的下拉菜單，通常需要手動在Excel中的數據驗證界面完成相應的設置，如下圖所示：

為了完成數據驗證的自動化設置，需要使用openpyxl模塊，具體代碼如下：

# set_validation.pyfrom openpyxl import load_workbook
from openpyxl.worksheet.datavalidation import DataValidation"""參數設置"""
path = r"D:\temp\table01.xlsx"  # 文件路徑
table_name = "table"  # 數據表頁面名稱
row_num = 3  # 數據區域的行數（計劃在數據表中錄入多少條數據）
row_start = 1  # 數據區域從第幾行開始（如果第1行需要設置為標題行，則數據區域的起始行數應往后放）
name1 = "省份"  # Excel名稱管理器中，一級目錄選項關聯的選項集名稱"""文件讀取"""
book = load_workbook(path)  # 讀取Excel文件
sheet = book.create_sheet(table_name)  # 創建數據表頁面"""一級目錄的數據驗證設置"""
validation1 = DataValidation(type="list", allow_blank=True, showDropDown=False)  # 數據驗證選項
validation1.formula1 = f'=indirect("{name1}")'  # 數據驗證表達式
validation1.sqref = f"$A{row_start}:$A{row_start+row_num-1}"  # 數據驗證規則的應用區域
sheet.add_data_validation(validation1)  # 添加改規則"""二級目錄的數據驗證設置"""
validation2 = DataValidation(type="list", allow_blank=True, showDropDown=False)  # 數據驗證選項
validation2.formula1 = f'=indirect($A{row_start}:$A{row_start+row_num-1})'  # 數據驗證表達式
validation2.sqref = f"$B{row_start}:$B{row_start+row_num-1}"  # 數據驗證規則的應用區域
sheet.add_data_validation(validation2)  # 添加改規則"""保存文件"""
book.save(path)

其中DataValidation函數對應著一個Excel數據驗證界面設置；formula1對應著Excel數據驗證界面的“來源”一欄；sqref對應著該數據驗證規則的應用區域。

運行程序后，重新打開該Excel文件，發現已經成功新建了“table”頁面。打開該頁面，發現指定的區域已經成功設置了下拉菜單，如下圖所示：

分別打開A列（省份）、B列（地市）單元格的數據驗證界面，發現其設置符合預期要求。

?

5 小結?

本文通過Python中的openpyxl模塊分別完成“選項數據寫入”“名稱管理器設置”“數據驗證設置”等步驟，實現了Excel多級聯動下拉菜單的自動化設置，在錄入數據量較大的情況下能夠減少人工錄入的工作。

本文通過data_options.py、write_options.py以及set_validation.py共3個Python腳本文件，分別實現了選項數據準備、選項數據寫入及Excel名稱管理器設置、數據驗證設置功能。在實際操作時，可以將上述步驟合并為一個Python文件運行，以避免不必要的文件讀寫操作。合并后的總代碼如下：

from collections import OrderedDict
from openpyxl import Workbook, load_workbook
from openpyxl.utils import get_column_letter
from openpyxl.workbook.defined_name import DefinedName
from openpyxl.worksheet.datavalidation import DataValidation"""參數設置"""
path = r"D:\temp\table03.xlsx"  # 輸出文件路徑
dic_name = "dic"  # Excel中保存選項的頁面名稱
table_name = "table"  # Excel中數據表頁面名稱
row_num = 3  # 數據表中數據區域的行數（計劃在數據表中錄入多少條數據）
row_start = 1  # 數據表中數據區域從第幾行開始（如果第1行需要設置為標題行，則數據區域的起始行數應往后放）
name1 = "省份"  # Excel名稱管理器中，一級目錄選項關聯的選項集名稱"""選項數據整理"""
dic = OrderedDict()
dic[name1] = ["河北省","河南省","山西省"]
dic["河北省"] = ["石家莊市","保定市","張家口市"]
dic["河南省"] = ["鄭州市","開封市","洛陽市"]
dic["山西省"] = ["太原市","運城市","大同市"]"""選項數據寫入以及Excel名稱管理器設置"""
book = Workbook()  # 新建Workbook
for sheet in book.sheetnames:del book[sheet]  # 刪除其他頁面
sheet_dic = book.create_sheet(dic_name)  # 新建頁面，用于存放選項數據
for num, (key, value) in enumerate(dic.items(), start=1):sheet_dic[f"A{num}"] = key  # 第1列的值為key值（選項集名稱）for i in range(len(value)):sheet_dic[f"{get_column_letter(i+2)}{num}"] = value[i]# （上一行）依次將該選項集中的選項填充在該行后續列的單元格中attr_text = f"{dic_name}!$B${num}:${get_column_letter(len(value)+1)}${num}"# （上一行）設置名稱管理器中每個選項集的取值區域（如：attr_text = "dic!$B$1:$D$1）value_range = DefinedName(key, attr_text=attr_text)  # 將選項集的名稱和選項映射為Excel名稱管理器中的格式book.defined_names.append(value_range)  # 加入Excel中的名稱管理器
sheet_dic.sheet_state = "hidden"  # 隱藏該頁面"""數據驗證設置"""
sheet_table = book.create_sheet(table_name)  # 創建數據表頁面# 一級目錄
validation1 = DataValidation(type="list", allow_blank=True, showDropDown=False)  # 數據驗證選項
validation1.formula1 = f'=indirect("{name1}")'  # 數據驗證表達式
validation1.sqref = f"$A{row_start}:$A{row_start+row_num-1}"  # 數據驗證規則的應用區域
sheet_table.add_data_validation(validation1)  # 添加改規則# 二級目錄
validation2 = DataValidation(type="list", allow_blank=True, showDropDown=False)  # 數據驗證選項
validation2.formula1 = f'=indirect($A{row_start}:$A{row_start+row_num-1})'  # 數據驗證表達式
validation2.sqref = f"$B{row_start}:$B{row_start+row_num-1}"  # 數據驗證規則的應用區域
sheet_table.add_data_validation(validation2)  # 添加改規則"""保存文件"""
book.save(path)