根据水果名称和城市，将表格2中的订单号插入到表格1中的批注中去。
表格2如下
表格1如下
解决方法
from openpyxl import Workbook
from openpyxl.comments import Comment
import openpyxl
from openpyxl.utils import get_column_letter
# 导入excel数据
wb = openpyxl.load_workbook("commit.xlsx")
sheet1 = wb['sheet1']
sheet2 = wb['sheet2']
# 找到水果类型所在的列数
def problems(problem_cell):
global order_column
problem=sheet2[problem_cell].value
row1 = sheet1[1]
for cell in row1:
if problem == cell.value:
order_column = get_column_letter(cell.column)
return order_column
# 找到城市所在的行数
def cities(city_cell):
city = sheet2[city_cell].value
column1 = sheet1['A']
for cell in column1:
if city == cell.value:
order_row = cell.row
return order_row
# 找到批注位置,将列数和行数合在一起，成为key。# 订单号为value, 和key形成字典。
# 找到相同位置的批注（如果位置相同key相同，则valve放在一起）。
def order_dict():
order_dict = dict()
for i in range(2,42):
problem = "A" + str(i)
city = "B" + str(i)
order = "C" + str(i)
order_key = problems(problem)+ str(cities(city))
order_value=sheet2[order].value
get_value = order_dict.get(order_key,False)
if get_value == False:
order_dict[order_key] = order_value
else:
get_value = str(get_value)+ '\n' + str(order_value)
order_dict[order_key] = get_value
# 插入批注
for i in order_dict:
order_key, order_value = i, order_dict[i]
comment = Comment(order_value, "green")
sheet1[order_key].comment = comment
order_dict()
wb.save("test2021.xlsx")