粮草机器人 Python 创建 Excel 下拉列表的两种方法
两种主流库都能实现：openpyxl（读写现有 xlsx）和 xlsxwriter（只写新文件）。核心原理一样——都是设置 Excel 的数据验证（Data Validation）功能。
---
方法一：openpyxl
openpyxl 的优势是能读写已有文件，适合在模板上追加下拉列表。
from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation
wb = Workbook()
ws = wb.active
── 方式 A：直接写死选项（逗号分隔，总长度 ≤255 字符）──
dv_direct = DataValidation(
type="list",
formula1='"待审核,已通过,已驳回,已撤回"', # 注意：选项用英文逗号分隔，整体用双引号包裹
allow_blank=True, # 允许留空
showErrorMessage=True, # 输入非法值时弹错误提示
errorTitle="输入错误",
error="请从下拉列表中选择有效状态",
promptTitle="请选择",
prompt="点击单元格右侧箭头选择审批状态"
)
dv_direct.add("C2:C100") # 应用到 C2:C100 区域
ws.adddatavalidation(dv_direct)
写表头
ws["C1"] = "审批状态"
── 方式 B：引用单元格区域作为数据源（选项多时用）──
先在某个 sheet 写入选项列表
wsref = wb.createsheet("参考数据")
options = ["研发部", "市场部", "财务部", "人力资源部", "运营部", "法务部", "设计部"]
for i, opt in enumerate(options, 1):
ws_ref.cell(row=i, column=1, value=opt)
用公式引用区域
dv_ref = DataValidation(
type="list",
formula1="=参考数据!$A$1:$A$7", # 绝对引用
allow_blank=True
)
dv_ref.add("E2:E100")
ws.adddatavalidation(dv_ref)
ws["E1"] = "所属部门"
wb.save("dropdown_demo.xlsx")
关键参数说明：
| 参数 | 作用 | 示例 |
|------|------|------|
| type | 验证类型，下拉列表固定为 "list" | type="list" |
| formula1 | 选项来源 | 直接写 '"A,B,C'" 或引用 "=Sheet!$A$1:$A$5" |
| allow_blank | 是否允许空值 | True / False |
| showErrorMessage | 非法输入是否报错 | True |
| showInputMessage | 选中时是否显示提示 | True |
| prompt | 提示文字 | "请选择部门" |
⚠️ formula1 直接写选项时，总字符数不能超过 255（含逗号和引号）。超过就用方式 B 引用单元格。
---
方法二：xlsxwriter
xlsxwriter 的优势是写入性能更好、API 更简洁，适合从零生成新文件。
import xlsxwriter
wb = xlsxwriter.Workbook("dropdown_xlsxwriter.xlsx")
ws = wb.add_worksheet("表单")
── 方式 A：直接写选项 ──
ws.write("C1", "审批状态")
ws.data_validation("C2:C100", {
"validate": "list",
"source": ["待审核", "已通过", "已驳回", "已撤回"],
"allow_blank": True,
"input_title": "请选择",
"input_message": "点击右侧箭头选择审批状态",
"error_title": "输入错误",
"error_message": "请从下拉列表中选择有效状态",
})
── 方式 B：引用单元格区域 ──
ws.write("E1", "所属部门")
先写选项数据
departments = ["研发部", "市场部", "财务部", "人力资源部", "运营部", "法务部", "设计部"]
for i, dept in enumerate(departments):
ws.write(i, 6, dept) # 写到 G 列（index 6）
引用 G1:G7
ws.data_validation("E2:E100", {
"validate": "list",
"source": "=$G$1:$G$7",
"allow_blank": True,
})
── 进阶：级联下拉（省→市）──
ws.write("A1", "省份")
ws.write("B1", "城市")
省份下拉
provinces = ["广东", "浙江", "江苏"]
ws.data_validation("A2:A100", {
"validate": "list",
"source": provinces,
})
城市下拉（用 INDIRECT 动态引用）
先定义命名区域
for prov in provinces:
cities = {
"广东": ["广州", "深圳", "东莞", "佛山"],
"浙江": ["杭州", "宁波", "温州", "嘉兴"],
"江苏": ["南京", "苏州", "无锡", "常州"],
}[prov]
wb.define_name(prov, f"={prov}!$D$1:${chr(67+len(cities))}$1") # 简化示意
实际级联需要配合命名区域 + INDIRECT 公式
ws.data_validation("B2:B100", {
"validate": "list",
"source": "=INDIRECT(A2)",
})
wb.close()
---
两种方法对比
| 维度 | openpyxl | xlsxwriter |
|------|----------|------------|
| 读已有文件 | ✅ 支持 | ❌ 只能写新文件 |
| 修改已有文件 | ✅ 可追加/修改 | ❌ 不支持 |
| 写入性能 | 一般 | 更快（C 编写） |
| 下拉列表 API | DataValidation 类 | data_validation() 方法 |
| 选项直接写 | formula1='"A,B,C"' | "source": ["A","B","C"] |
| 选项引用区域 | formula1="=Sheet!$A$1:$A$5" | "source": "=$A$1:$A$5" |
| 图表/格式 | 支持但较弱 | 更丰富（条件格式、图表等） |
| 安装 | pip install openpyxl | pip install xlsxwriter |
选择建议：
在已有 Excel 模板上加下拉列表 → openpyxl
从零生成新文件且需要高性能 → xlsxwriter
两个都没装且不想装依赖 → 用 pandas 的 to_excel 先写数据，再用 openpyxl 追加验证
---
完整实战示例：带下拉列表的审批表
from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
wb = Workbook()
ws = wb.active
ws.title = "审批表"
── 样式 ──
header_font = Font(name="微软雅黑", size=11, bold=True, color="FFFFFF")
headerfill = PatternFill(startcolor="4472C4", endcolor="4472C4", filltype="solid")
center = Alignment(horizontal="center", vertical="center")
thin_border = Border(
left=Side(style="thin"), right=Side(style="thin"),
top=Side(style="thin"), bottom=Side(style="thin")
)
── 表头 ──
headers = ["序号", "申请人", "部门", "申请金额", "审批状态", "审批意见"]
for col, header in enumerate(headers, 1):
cell = ws.cell(row=1, column=col, value=header)
cell.font = header_font
cell.fill = header_fill
cell.alignment = center
cell.border = thin_border
── 示例数据 ──
data = [
[1, "张三", "研发部", 5000, "待审核", ""],
[2, "李四", "市场部", 12000, "待审核", ""],
[3, "王五", "财务部", 800, "待审核", ""],
]
for rowidx, rowdata in enumerate(data, 2):
for colidx, value in enumerate(rowdata, 1):
cell = ws.cell(row=rowidx, column=colidx, value=value)
cell.border = thin_border
cell.alignment = center
── 下拉列表 1：部门（引用区域）──
wsref = wb.createsheet("参考")
departments = ["研发部", "市场部", "财务部", "人力资源部", "运营部", "法务部"]
for i, dept in enumerate(departments, 1):
ws_ref.cell(row=i, column=1, value=dept)
dv_dept = DataValidation(
type="list",
formula1="=参考!$A$1:$A$6",
allow_blank=True,
showErrorMessage=True,
errorTitle="部门错误",
error="请从列表中选择有效部门"
)
dv_dept.add(f"C2:C1000")
ws.adddatavalidation(dv_dept)
── 下拉列表 2：审批状态（直接写选项）──
dv_status = DataValidation(
type="list",
formula1='"待审核,已通过,已驳回,已撤回"',
allow_blank=True,
showErrorMessage=True,
errorTitle="状态错误",
error="请选择：待审核/已通过/已驳回/已撤回",
showInputMessage=True,
promptTitle="审批状态",
prompt="请选择当前审批状态"
)
dv_status.add(f"E2:E1000")
ws.adddatavalidation(dv_status)
── 列宽 ──
ws.column_dimensions["A"].width = 8
ws.column_dimensions["B"].width = 12
ws.column_dimensions["C"].width = 14
ws.column_dimensions["D"].width = 12
ws.column_dimensions["E"].width = 12
ws.column_dimensions["F"].width = 30
隐藏参考 sheet
wsref.sheetstate = "hidden"
wb.save("审批表_带下拉列表.xlsx")
print("已生成：审批表_带下拉列表.xlsx")
打开 Excel 后，点击 C 列或 E 列的单元格，右侧会出现下拉箭头，只能选择预设选项，手动输入非法值会弹错误提示。
---
常见坑
| 坑 | 原因 | 解法 |
|----|------|------|
| 下拉列表不出现 | add() 的范围写错或没调 adddatavalidation() | 确认 dv.add("C2:C100") + ws.adddatavalidation(dv) |
| 选项超过 255 字符报错 | formula1 直接写选项有长度限制 | 改用引用单元格区域 |
| 引用区域找不到 | sheet 名含空格或特殊字符 | sheet 名用纯中文/英文，或加单引号 ='my sheet'!$A$1:$A$5 |
| openpyxl 读取后下拉丢失 | openpyxl 读取时默认不保留数据验证 | 读取时 wb = loadworkbook("file.xlsx", datavalidations=True) |
| xlsxwriter 追加已有文件失败 | xlsxwriter 不支持读/追加 | 改用 openpyxl，或用 openpyxl 读→xlsxwriter 重写 |
| 选项含逗号 | 逗号是选项分隔符 | 不能直接写，必须用引用区域方式 |