python3实现excel根据条件找到目标数据所在单元格的行数和列数，并插入批注

根据水果名称和城市，将表格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")