如何用Python的openpyxl模块优化Excel模板主机名批量更新逻辑？

需求说明 #

• 基于带表头和2条示例数据的Excel模板，完成以下操作：
  • 根据输入的主机名，更新B列（Hostname字段）和C列（Acct Name字段）：C列保留原有前缀（如UXT_、AST_），替换后缀为新主机名
  • 其余列数据保持不变
  • 每个输入的主机名对应生成2行数据（复用模板中的2条示例行），输入N个主机名则生成2*N行数据

输入模板表格 #

期望输出表格（以输入主机名xyz_1234为例） #

现有代码问题 #

当前代码存在多处硬编码缺陷：

• 固定写入行号（row=2、row=4），无法适配多主机名的批量生成
• Acct Name的前缀硬编码为UXT_，忽略了模板中不同行的前缀差异
• 逻辑混乱，循环嵌套不合理，且workbook.save()缩进错误（未包含在函数内）

现有代码：

import openpyxl
def write_to_excel(file_name, sheet_name, data_list):
    workbook = openpyxl.load_workbook(file_name)
    sheet = workbook[sheet_name]

    for index, data in enumerate(data_list):
        for col_index, value in enumerate(data, start=1):
            sheet.cell(row=2, column=col_index).value = value
            sheet.cell(row=2, column=col_index+1).value = "UXT_" + value
            sheet.cell(row=4, column=col_index).value = value
            sheet.cell(row=4, column=col_index+1).value = "UXT_" + value

workbook.save(file_name)
hostnames = [[ "xyz_1234" ]]
write_to_excel("template.xlsx", "Sheet1", hostnames)

优化后的实现方案 #

核心思路 #

1. 读取模板中的示例行（表头下的所有行）作为模板，保留除Hostname和Acct Name外的所有数据
2. 对每个输入的主机名，复制模板行并更新指定字段：
   • Hostname直接替换为新主机名
   • Acct Name提取原有前缀（下划线_之前的部分），拼接新主机名
3. 清空模板原有数据行（保留表头），将生成的所有新行写入Excel

优化代码 #

import openpyxl

def generate_excel_from_template(template_path, output_path, hostnames):
    # 加载模板工作簿
    wb = openpyxl.load_workbook(template_path)
    sheet = wb.active
    
    # 读取表头和模板行（表头下的所有示例行）
    header_row = [cell.value for cell in sheet[1]]
    template_rows = []
    for row in sheet.iter_rows(min_row=2, max_row=sheet.max_row, values_only=True):
        template_rows.append(list(row))
    
    # 清空原有数据行（仅保留表头）
    sheet.delete_rows(2, sheet.max_row - 1)
    
    # 为每个主机名生成对应数据行
    current_row = 2  # 从表头下一行开始写入
    for hostname in hostnames:
        for template in template_rows:
            new_row = template.copy()
            # 更新Hostname（B列，索引1）
            new_row[1] = hostname
            # 更新Acct Name（C列，索引2）：提取前缀+新主机名
            prefix = new_row[2].split('_')[0]
            new_row[2] = f"{prefix}_{hostname}"
            # 写入新行
            for col_idx, value in enumerate(new_row, start=1):
                sheet.cell(row=current_row, column=col_idx, value=value)
            current_row += 1
    
    # 保存到新文件（避免修改原模板）
    wb.save(output_path)

# 示例调用：输入2个主机名，生成4行数据
if __name__ == "__main__":
    target_hostnames = ["xyz_1234", "abc_5678"]
    generate_excel_from_template(
        template_path="template.xlsx",
        output_path="output.xlsx",
        hostnames=target_hostnames
    )

优化点说明 #

• 无硬编码：模板行动态读取，支持调整模板示例行数量
• 批量兼容：支持任意数量的主机名输入，自动生成对应行数
• 逻辑复用：Acct Name前缀从模板行自动提取，无需手动指定
• 安全操作：结果保存到新文件，不修改原模板
• 结构清晰：函数职责明确，注释易懂，适合初学者理解

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