如何用Python(openpyxl)给多工作簿指定单元格区域应用Excel公式及排错

错误原因拆解 #

1. 字符串格式化错误：'BK{0}:BK{*1200*}' 写法完全不符合Python字符串格式化规则，解析时找不到对应参数，直接触发IndexError。
2. 公式语法错误：VLOOKUP公式的引号嵌套混乱，缺少闭合引号，且外部文件的引用格式不符合Excel要求。
3. 循环逻辑颠倒：先加载单个文件再遍历文件列表，后续逻辑完全错位；iter_rows调用末尾缺冒号，语法不合法。
4. 行范围硬编码：没有动态获取BK列最后非空行，硬写1200不符合需求。

关键修正步骤 #

• 正确获取目标文件：用glob.glob批量读取文件夹中的Excel文件（注意：openpyxl仅支持.xlsx格式，若为.xls需改用xlrd库）。
• 调整循环顺序：先遍历所有文件，再逐个加载工作簿、处理工作表。
• 动态获取有效行范围：通过sheet.max_row获取工作表最后一行，结合BK列（列号63）构建遍历范围。
• 修正公式格式：Excel引用外部文件需用'[文件名]工作表名'格式，同时让VLOOKUP的查找行号与当前行对应。

完整修正代码 #

import openpyxl as op
import os
import glob
from openpyxl import load_workbook

# 替换为你的Excel文件夹路径，支持.xlsx文件
folder_path = "你的文件夹路径"
files = glob.glob(os.path.join(folder_path, "*.xlsx"))

for file_path in files:
    # 加载当前工作簿
    wb = load_workbook(filename=file_path)
    sheet = wb.worksheets[0]
    
    # 在第61列后插入新列（对应列号62）
    sheet.insert_cols(62)
    
    # 获取BK列（列号63）的最后非空行
    max_row = sheet.max_row
    # 遍历BK列第2行到最后一行（假设表头在第1行）
    for row in range(2, max_row + 1):
        # 获取当前行的BK列单元格
        cell = sheet.cell(row=row, column=63)
        # 构建正确的VLOOKUP公式：引用当前文件的Sheet1，查找A列对应行的值
        # 若需引用其他外部文件，替换为目标文件路径，格式为'[外部文件路径]Sheet1'!A:C
        cell.value = f"=VLOOKUP(A{row},'[{os.path.basename(file_path)}]Sheet1'!A:C,3,0)"
    
    # 保存修改后的文件，可改为另存为避免覆盖原文件
    wb.save(file_path)
    print(f"已处理文件：{file_path}")

额外说明 #

• 如果需要引用其他外部文件而非当前文件，只需把公式中的os.path.basename(file_path)替换为目标文件的完整路径（路径含空格时需用单引号包裹）。
• 若处理的是.xls格式文件，需替换openpyxl为xlrd和xlwt库（xlrd仅支持读取旧版xls，写入需用xlwt）。

内容的提问来源于stack exchange，提问作者Θοδωρής Πάλλης