Python字典KeyError排查：Excel处理代码报错'UCR***1'

问题描述 #

我有一个包含多个工作表的Excel文件，需要完成以下操作：

• 在索引为6的工作表中查找UC Care、PP Visit等特定字符串，获取对应行号和列值并计算结果
• 在索引为0的工作表中查找UCR***1、POV***1等特定行，将之前计算的结果写入这些行的第5列

运行代码时出现错误：

if coinds_dict[test_name] is not none: KeyError: UCR***1

原代码 #

import numpy as np
import openpyxl as xl

book = xl.load_workbook('Mapping.xlsx')
# Get the values of the first column in the  worksheet[6]
cgs_values = np.array([[cell.value for cell in row] for row in
                   book.worksheets[6].iter_rows(min_row=2, max_row=300, min_col=3, 
max_col=3)])

# Create a dictionary to store the row numbers for each test in the workbook[0]
test_rows = {'UCR***1': None, 'POV***1': None, 'SOV***1': None}


# Create a dictionary to store the percentage for each test
coins_dict = {'UC Care': None, 'PP Visit': None, 'SP Visit': None}

# Iterate through the test names
 for test_name in ['UC Care', 'PP Visit', 'SP Visit']:

 # Find the row in the worksheet[6] that contains the text
    row_number = np.where(cgs_values == test_name)[0][0] + 2

# Get the value of the cell in the sixth column of that row
coins = book.worksheets[6].cell(row=row_number, column=6).value

# If the value of the cell is not empty, then calculate the  percentage
if coins is not None:
    # If the value of the cell in the seventh column is "Coins", then calculate the coins percentage and store it in the dictionary
    if book.worksheets[6].cell(row=row_number, column=7).value == "Coins":
        coins = book.worksheets[6].cell(row=row_number, column=6).value
        m1 = 100 - int(coins)
        coins_dict[test_name] = str(m1) + "%" + " " + "Coinsurance"
    # If the value of the cell in the seventh column is "Cody", then calculate the  percentage and store it in the dictionary
    elif book.worksheets[6].cell(row=row_number, column=7).value == "Cody":
        coins = book.worksheets[6].cell(row=row_number, column=6).value
        m1 = 100 - int(coins)
        coins_dict[test_name] = str(m1) + "%" + " " + "after deductible"
   

# Find the row numbers for each test in the  workbook[0]
 for test_name, row_name in test_rows.items():
   for row in book.worksheets[0].iter_rows(min_row=1, max_row=book.worksheets[0].max_row, 
   min_col=1, max_col=1):
   if row[0].value == test_name:
        test_rows[test_name] = row[0].row
        break
   if coins_dict[test_name] is not None:
    book.worksheets[0].cell(row=test_rows[test_name], column=5, value=coins_dict[test_name])



  book.save('Mapping.xlsx')
  book.close()

错误原因 #

1. 字典键不匹配：test_rows的键是UCR***1这类标识，而coins_dict的键是UC Care这类名称，遍历test_rows时用test_name（即UCR***1）去coins_dict中查找，自然找不到对应键，触发KeyError。
2. 缩进错误：代码中多个循环和条件语句的缩进不符合Python规范，导致逻辑混乱（比如循环内的代码未正确缩进，会脱离循环执行）。
3. np.where的风险：如果cgs_values中不存在目标字符串，np.where(cgs_values == test_name)[0]会返回空数组，取[0]会触发索引错误。
4. 拼写错误：判断条件中的"Cody"应为"Copay"（免赔额的正确拼写），否则该分支永远不会触发。

修复后的代码 #

import numpy as np
import openpyxl as xl

# 建立测试标识与对应名称的映射，解决键不匹配问题
test_mapping = {
    'UCR***1': 'UC Care',
    'POV***1': 'PP Visit',
    'SOV***1': 'SP Visit'
}

book = xl.load_workbook('Mapping.xlsx')
ws6 = book.worksheets[6]
ws0 = book.worksheets[0]

# 获取工作表6第3列的值（行2到300），简化数组结构
cgs_values = np.array([cell.value for row in ws6.iter_rows(min_row=2, max_row=300, min_col=3, max_col=3) for cell in row])

# 存储计算后的结果
coins_dict = {'UC Care': None, 'PP Visit': None, 'SP Visit': None}

for test_name in coins_dict.keys():
    # 查找目标字符串的位置，处理找不到的情况
    matches = np.where(cgs_values == test_name)[0]
    if len(matches) == 0:
        print(f"未找到{test_name}")
        continue
    row_number = matches[0] + 2  # 转换为Excel行号（从2开始）
    
    coins = ws6.cell(row=row_number, column=6).value
    if coins is not None:
        type_col = ws6.cell(row=row_number, column=7).value
        # 修正拼写错误：Cody -> Copay
        if type_col == "Coins":
            m1 = 100 - int(coins)
            coins_dict[test_name] = f"{m1}% Coinsurance"
        elif type_col == "Copay":
            m1 = 100 - int(coins)
            coins_dict[test_name] = f"{m1}% after deductible"

# 查找工作表0中各测试标识的行号
test_rows = {key: None for key in test_mapping.keys()}
for row in ws0.iter_rows(min_row=1, max_row=ws0.max_row, min_col=1, max_col=1):
    cell_value = row[0].value
    if cell_value in test_rows:
        test_rows[cell_value] = row[0].row

# 写入结果到第5列
for test_id, test_name in test_mapping.items():
    row_num = test_rows[test_id]
    if row_num is not None and coins_dict[test_name] is not None:
        ws0.cell(row=row_num, column=5, value=coins_dict[test_name])

book.save('Mapping.xlsx')
book.close()

修复说明 #

• 添加test_mapping字典，建立标识与名称的对应关系，彻底解决键不匹配问题。
• 修复所有缩进错误，确保代码逻辑正确执行。
• 增加np.where结果判断，避免找不到目标字符串时的索引错误。
• 修正Cody为Copay的拼写错误，保证分支逻辑生效。
• 优化代码结构，用变量存储工作表对象，提升可读性。
• 使用f-string简化字符串拼接，代码更简洁。

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