用Python向Excel写入可计算公式并批量迭代及代码无效排查

需求说明 #

需要在Excel中批量写入公式，逻辑如下：

L6 = K6 - L2 + L5
M6 = L6 - M2 + M5
N6 = M6 - N2 + N5
O6 = N6 - O2 + O5

且公式仅需应用在所有Type为E的行。尝试用openpyxl编写代码，但输出文件无变化，原代码如下：

import openpyxl
wb = openpyxl.load_workbook(filename='newfile.xlsx')
ws = wb.worksheets[0]
sb = [i for i in range(6,len(out),5)] #E cells
total = [i for i in range(2,len(out),5)]#A cells
dvl = [i for i in range(5,len(out),5)]#D cells

columns_names = ['K','L','M','N','O','P','R','S','T','U','V','W','X','Y','Z','AA','AB']
colNr = len(columns_names)

for i in sb:
    ws[f'K{i}'] = f'=G{i}'

for i in range(0,len(sb)):
    for cellnr in range(0,colNr):
        try:
            ws[f'{columns_names[cellnr+1]}{sb[i]}'] = f'={columns_names[cellnr]}{sb[i]} - {columns_names[cellnr+1]}{total[i]} + {columns_names[cellnr+1]}{dvl[i]}'
        except:
            pass
wb.save(filename='test.xlsx')

附相关数据：

Type        2023/07 2023/08 2023/09 2023/10 2023/11 2023/12 2024/01 2024/02 2024/03 2024/04 2024/05 2024/06 2024/07 2024/08 2024/09 2024/10 2024/11 2024/12
A       3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3
B       0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
C       3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3
D       0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
E       127 124 121 118 115 112 109 106 103 100 97  94  91  88  85  82  79  76
A       0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
B       0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
C       0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
D       0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
E       3633    3633    3633    3633    3633    3633    3633    3633    3633    3633    3633    3633    3633    3633    3633    3633    3633    3633
A       5   5   5   5   5   5   5   5   5   5   5   5   5   5   5   5   5   5
B       0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
C       5   5   5   5   5   5   5   5   5   5   5   5   5   5   5   5   5   5
D       0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
E       1637    1632    1627    1622    1617    1612    1607    1602    1597    1592    1587    1582    1577    1572    1567    1562    1557    1552
A       1   49  49  61  37  37  37  25  37  25  25  25  49  49  37  49  37  13
B       0   48  48  60  36  36  36  24  36  24  24  24  48  48  36  48  36  12
C       1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1
D       10000   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
E       1023    974 925 864 827 790 753 728 691 666 641 616 567 518 481 432 395 382

问题排查与修正代码 #

原代码无效的核心原因：

1. 变量out未定义，导致sb、total、dvl三个列表为空，循环根本没执行
2. 列名列表中缺少Q列，可能导致公式跳列错误
3. 异常捕获过于宽泛，掩盖了执行中的错误

以下是修正后的代码：

import openpyxl

# 加载工作簿，data_only=False确保保留公式而非计算值
wb = openpyxl.load_workbook(filename='newfile.xlsx', data_only=False)
ws = wb.worksheets[0]

# 找出所有Type为E的行号（Excel行号从1开始）
e_rows = []
for row_idx, row in enumerate(ws.iter_rows(min_row=2, values_only=True), start=2):
    if row[0] == 'E':
        e_rows.append(row_idx)

# 定义完整的列名序列（补充缺失的Q列）
columns_names = ['K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB']
col_count = len(columns_names)

# 处理每个E行
for e_row in e_rows:
    # 给K列赋值公式=G{e_row}
    ws[f'K{e_row}'] = f'=G{e_row}'
    
    # 从K列之后的列开始批量写入公式
    for col_idx in range(col_count - 1):
        current_col = columns_names[col_idx]
        next_col = columns_names[col_idx + 1]
        # 公式逻辑：下一列当前行 = 当前列当前行 - 下一列第2行 + 下一列第5行
        formula = f'={current_col}{e_row} - {next_col}2 + {next_col}5'
        ws[f'{next_col}{e_row}'] = formula

# 保存文件
wb.save(filename='test.xlsx')

关键说明 #

• data_only=False：openpyxl默认如果设为True，会读取单元格的计算结果而非公式，必须设为False才能写入并保留可计算的公式
• 精准筛选E行：通过遍历第一列直接判断Type值，避免手动计算行号出错
• 补全列名：原列表缺少Q列会导致公式跳列，修正后确保列序列连续
• 移除宽泛异常捕获：原代码的try...pass会隐藏所有错误，修正后可直接看到执行问题，便于调试

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