使用openpyxl实现Excel条件匹配填充遇列类型错误求解决方案

本人刚接触openpyxl，需要实现以下功能：Excel文件A列存储街道名称列表，用户输入要搜索的街道名、待填充字符串（day）和目标列序号，当A列匹配到指定街道名时，在对应行的目标列插入该字符串。

原代码执行时触发类型错误，误以为openpyxl仅识别字符形式列名，以下是两种解决方案：

原代码 #

#Necessary imports for xlsx files
import openpyxl as xl

#Import file
wb = xl.load_workbook('test.xlsx')
ws = wb.active

#Initialize values for openpyxl use
sheet = wb['Sheet1']

#Accept user input
street = input("Street name: ")
day = input("Day to append: ")
add_to_col = int(input("Column to add value to: "))

#Initialize values for loop use
row = 2
column = add_to_col
mycell = sheet['A2']

#Iterate through column and return corresponding day in adjacent row
for i in range(row,6300):
    
    if mycell.value == street:
        cellref = sheet.cell(row=i, column=column)
        mycell.value == day

#Save workbook
wb.save("test.xlsx")

方案A：使用字符形式列名匹配 #

openpyxl的sheet.cell()本就支持数字列参数，原代码的错误是循环逻辑问题——mycell始终指向A2未更新，且用了==比较运算符而非=赋值运算符。如果偏好字符形式列名，可以先把用户输入的列序号转成Excel列名，再操作：

修正后代码 #

import openpyxl as xl

def col_num_to_name(col_num):
    # 把列序号转成Excel列名（如1→A，26→Z，27→AA）
    name = ''
    while col_num > 0:
        col_num, remainder = divmod(col_num - 1, 26)
        name = chr(ord('A') + remainder) + name
    return name

wb = xl.load_workbook('test.xlsx')
sheet = wb['Sheet1']

street = input("Street name: ")
day = input("Day to append: ")
add_to_col = int(input("Column to add value to: "))

# 转换列序号为字符形式
col_name = col_num_to_name(add_to_col)

# 遍历A列第2行到6299行
for row in range(2, 6300):
    a_cell = sheet[f'A{row}']
    if a_cell.value == street:
        target_cell = sheet[f'{col_name}{row}']
        target_cell.value = day

wb.save("test.xlsx")

方案B：优化循环逻辑直接用数字列参数 #

无需转换列名，直接修正原代码的循环逻辑即可：

修正后代码 #

import openpyxl as xl

wb = xl.load_workbook('test.xlsx')
sheet = wb['Sheet1']

street = input("Street name: ")
day = input("Day to append: ")
add_to_col = int(input("Column to add value to: "))

# 遍历A列第2行到6299行
for row in range(2, 6300):
    # 获取当前行A列单元格
    a_cell = sheet.cell(row=row, column=1)
    if a_cell.value == street:
        # 获取目标列单元格并赋值
        target_cell = sheet.cell(row=row, column=add_to_col)
        target_cell.value = day

wb.save("test.xlsx")

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