openpyxl遍历Excel未找到指定字段时补0/NA的代码修改问询

解决方案 #

修改思路 #

每个文件开始处理时先给g设置默认占位值，遍历完整个文件的所有单元格后统一追加到g1，保证每个文件对应7个列表各新增1个元素，长度完全一致。

具体修改点 #

1. 每个文件遍历前初始化g的默认值（可按需替换为0或其他占位值），同时建议给a~f也设置默认值，避免部分文件缺失对应字段时报变量未定义错误。
2. 删除原代码中匹配到"NON Existent"字段时的g1.append(g)语句，仅保留对g的赋值逻辑。
3. 在其余列表统一追加值的代码块中，新增g1.append(g)语句。

完整修改后代码 #

import glob
import openpyxl

# 提前初始化所有列表
a1 = []
b1 = []
c1 = []
d1 = []
e1 = []
f1 = []
g1 = []

files = glob.glob(r'C:\test\Phase 1A\*.xlsm')
for file in files:
    print(file)
    # 每个文件处理前先初始化变量默认值，避免缺失字段报错
    a = b = c = d = e = f = 'N/A'
    g = 'N/A' # 要默认填0就改成 g = 0

    wb = openpyxl.load_workbook(filename=file, data_only=True)
    ws = wb.active
    for row in ws.iter_rows():
        for cell in row:
            if cell.value == "Vessel Volume": #search for Volume
                idxr = cell.row
                idxc = cell.column
                a = ws.cell(idxr, idxc+1).value
            elif cell.value == "Phase": #search for Phase
                idxr = cell.row
                idxc = cell.column
                b = ws.cell(idxr, idxc+1).value    
            elif cell.value == "Test#": #search for Test#
                idxr = cell.row
                idxc = cell.column
                c = ws.cell(idxr, idxc+1).value    
            elif cell.value == "Concentration": #search for Concentration
                idxr = cell.row
                idxc = cell.column
                d = ws.cell(idxr, idxc+1).value
            elif cell.value == "Time Delay": #search for Time Delay
                idxr = cell.row
                idxc = cell.column
                e = ws.cell(idxr, idxc+1).value
            elif cell.value == "Pstat\nEffective": #search for Pred by locating Pstat Effective
                idxr = cell.row
                idxc = cell.column
                f = ws.cell(idxr+1, idxc+4).value
            elif cell.value == "NON Existent":
                idxr = cell.row
                idxc = cell.column
                g = ws.cell(idxr, idxc).value
    # 统一追加，每个文件各列表只加1次
    a1.append(a)
    b1.append(b)
    c1.append(c)
    d1.append(d)
    e1.append(e)
    f1.append(f)
    g1.append(g)

如果需要调整占位值，直接修改初始化g的赋值语句即可。

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