如何修改Python代码避免Excel打开时提示内容错误并保留VLOOKUP公式

问题根源 #

你的代码中公式模板里的{0}没有被实际替换为单元格的行号，生成了无效的公式（例如=VLOOKUP(I{0},'123'!I:I,1,FALSE)），Excel无法识别这种格式的公式，因此会将其标记为错误并删除。

修改后的代码 #

from openpyxl import load_workbook

wb = load_workbook(f'{working_folder}\\{final_excel_file_name}')
# 直接获取工作表的标题，替代不可靠的正则提取
sheet1_name = wb.worksheets[0].title
sheet2_name = wb.worksheets[1].title

sheet1 = wb[sheet1_name]
sheet2 = wb[sheet2_name]

# 遍历Q列单元格，处理表头和数据行
for cell in sheet1['Q']:
    if cell.row == 1:
        cell.value = ''
        continue
    # 使用f-string动态插入行号和工作表名称，生成合法公式
    cell.value = f"=VLOOKUP(I{cell.row},'{sheet2_name}'!I:I,1,FALSE)"

wb.save(f'{working_folder}\\test.xlsx')

代码优化要点 #

• 可靠获取工作表名称：用worksheet.title直接获取工作表的官方名称，替代通过正则解析str(worksheet)的不稳定方式，避免因工作表名称格式变化导致的错误。
• 动态生成合法公式：通过cell.row获取当前单元格行号，用f-string插入模板，生成符合Excel规范的动态公式（如=VLOOKUP(I2,'Sheet2'!I:I,1,FALSE)）。
• 表头逻辑处理：增加行号判断，跳过表头行（Q1），避免表头被公式覆盖，适配常规表格结构。
• 代码可读性提升：用f-string替代字符串拼接，让公式模板更直观，减少语法错误概率。

额外注意事项 #

• 公式中给工作表名称加单引号是通用写法，能兼容包含空格、特殊字符或数字开头的工作表名称，建议保留。
• 确保load_workbook未设置data_only=True（默认值为False），否则会读取单元格值而非公式，导致无法写入新公式。

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