问题
根据水果名称和城市，将表格2中的订单号插入到表格1中的批注中去。
表格2如下
表格1如下
解决方法
from openpyxl import Workbookfrom ments import Commentimport openpyxlfrom 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_columnproblem=sheet2[problem_cell].valuerow1 = 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].valuecolumn1 = sheet1['A']for cell in column1:if city == cell.value:order_row = cell.rowreturn 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].valueget_value = order_dict.get(order_key,False)if get_value == False:order_dict[order_key] = order_valueelse: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 = commentorder_dict()wb.save("test.xlsx")
