基于Python处理带依赖下拉列表的Excel自动化问题

需求背景 #

现有XLSB格式工作簿master excel，包含master sheet、sheet1等工作表：

• master sheet的A2单元格下拉列表数据源为sheet1的P列（P2-P96）的96个名称
• 每个名称对应6个月度更新的Excel公式计算参数
• 需要生成含参数名称、域名、数据截至月份、参数值四列的新表，遍历所有名称提取参数

当前遇到三个问题：无法遍历下拉列表、数值导出为浮点数而非原整数、日期显示为Unix epoch格式（如44136）

问题1：遍历下拉列表 #

无需解析Excel下拉列表控件，直接读取sheet1的P列数据源即可（下拉列表选项就是该列内容）：

import pandas as pd

# 读取XLSB文件，依赖pyxlsb引擎（需先安装：pip install pyxlsb）
xlsb_file = pd.ExcelFile("master excel.xlsb", engine="pyxlsb")

# 读取sheet1的P列，跳过第1行表头，提取有效名称
domain_names = pd.read_excel(xlsb_file, sheet_name="sheet1", usecols="P", skiprows=1, header=None).squeeze()
domain_names = domain_names.dropna().tolist()

问题2：数值转整数 #

读取参数值后，将浮点型转换为整数，兼容空值场景：

# 示例：针对参数列批量转换类型
params_df = pd.read_excel(xlsb_file, sheet_name="master sheet")

# 替换为实际参数列名，比如C到H列
param_cols = ["参数列1", "参数列2", "参数列3", "参数列4", "参数列5", "参数列6"]
for col in param_cols:
    params_df[col] = params_df[col].apply(lambda x: int(x) if pd.notna(x) and isinstance(x, float) else x)

问题3：日期格式转换 #

将Excel日期序列号转换为标准YYYY-MM格式：

# 假设"数据截至月份"对应J列，转换为可读日期
params_df["数据截至月份"] = pd.to_datetime(params_df["数据截至月份"], unit="D", origin="1899-12-30").dt.strftime("%Y-%m")

完整整合代码 #

import pandas as pd

# 1. 读取XLSB文件
xlsb_file = pd.ExcelFile("master excel.xlsb", engine="pyxlsb")

# 2. 获取所有域名（下拉列表数据源）
domain_names = pd.read_excel(xlsb_file, sheet_name="sheet1", usecols="P", skiprows=1, header=None).squeeze()
domain_names = domain_names.dropna().tolist()

# 3. 初始化结果容器
result_list = []

# 4. 遍历每个域名提取参数
for domain in domain_names:
    # 读取master sheet，匹配当前域名对应的行（替换"域名列"为实际列名，比如A列）
    master_df = pd.read_excel(xlsb_file, sheet_name="master sheet")
    target_row = master_df[master_df["域名列"] == domain].iloc[0]
    
    # 定义6个参数的映射关系（替换为实际列名）
    param_mapping = [
        ("参数1", target_row["参数1值"]),
        ("参数2", target_row["参数2值"]),
        ("参数3", target_row["参数3值"]),
        ("参数4", target_row["参数4值"]),
        ("参数5", target_row["参数5值"]),
        ("参数6", target_row["参数6值"])
    ]
    
    # 处理每个参数的格式
    raw_date = target_row["数据截至月份"]
    formatted_date = pd.to_datetime(raw_date, unit="D", origin="1899-12-30").dt.strftime("%Y-%m")
    
    for param_name, param_val in param_mapping:
        formatted_param = int(param_val) if pd.notna(param_val) and isinstance(param_val, float) else param_val
        result_list.append([param_name, domain, formatted_date, formatted_param])

# 5. 生成结果表并导出
result_df = pd.DataFrame(result_list, columns=["参数名称", "域名", "数据截至月份", "参数值"])
result_df.to_excel("提取结果.xlsx", index=False)

内容的提问来源于stack exchange，提问作者sumit raj