1. # -*- coding: utf-8 -*-
    2. # powers 20210512 GTK
    3. import pandas as pd
    4. import os
    5. #os.environ['MATPLOTLIBDATA'] = 'D:/Program Files/Python36/Lib/site-packages/matplotlib/mpl-data'
    6. #import numpy as np
    8. #坐标文件
    9. road_ax = input('please input positon_info xlsx:')
    10. layer_data = pd.DataFrame(pd.read_excel(format(road_ax), sheet_name=0))
    11. #原始bom
    12. road_bom = input('please input bom_data xlsx:')
    13. bom_data = pd.DataFrame(pd.read_excel(format(road_bom), sheet_name=0))
    16. # 根据坐标文件获取top and bot信息
    17. layer_dict = {}
    18. i = 0
    19. while i < layer_data.shape[0]:
    20.     layer_dict[layer_data.iloc[i,0]] = layer_data.iloc[i,1]
    21.     i = i + 1
    24. #新建 bot and top 格式BOM
    25. data_bom_goertek_bot = pd.DataFrame(columns=(
    26.                 'Item', 'GTK_PART_NUMBER', 'Name', 'Describe',
    27.                 'MF_PART_NUMBER', 'MANUFACTURER', 'Quantity', 'Reference', 'Layer'))
    28. data_bom_goertek_top = pd.DataFrame(columns=(
    29.                 'Item', 'GTK_PART_NUMBER', 'Name', 'Describe',
    30.                 'MF_PART_NUMBER', 'MANUFACTURER', 'Quantity', 'Reference', 'Layer'))
    33. #print(bom_data.shape[0])
    35. j = 0
    36. bot_reference = []
    37. top_reference = []
    38. while j < bom_data.shape[0]:
    39.     # 获取原始bom 数据
    40.     reference = bom_data.iloc[j,7]
    41.     reference = reference.split(',')
    42.     Item = bom_data.iloc[j,0]
    43.     GTK_PART_NUMBER = bom_data.iloc[j,1]
    44.     Name = bom_data.iloc[j,2]
    45.     Describe = bom_data.iloc[j,3]
    46.     MF_PART_NUMBER = bom_data.iloc[j,4]
    47.     MANUFACTURER = bom_data.iloc[j,5]
    49.     try:
    50.         # 区分 bot and  top 
    51.         for id in reference:
    52.             if layer_dict[id] == 'YES':
    53.                 bot_reference.append(id)
    54.             elif layer_dict[id] == 'NO':
    55.                 top_reference.append(id)   
    56.             else:
    57.                 pass
    59.         # 根据 bot and top 制作BOM_data   
    60.         if len(bot_reference) > 0:
    61.             Quantity = len(bot_reference)
    62.             bot = ','.join(bot_reference)
    63.             data_bom_goertek_bot.loc[data_bom_goertek_bot.shape[0]] = (Item,GTK_PART_NUMBER,Name,Describe,MF_PART_NUMBER,MANUFACTURER,str(Quantity),bot,'BOT')
    65.         if len(top_reference) > 0:
    66.             Quantity = len(top_reference)
    67.             top = ','.join(top_reference)
    68.             data_bom_goertek_top.loc[data_bom_goertek_top.shape[0]] = (Item,GTK_PART_NUMBER,Name,Describe,MF_PART_NUMBER,MANUFACTURER,str(Quantity),top,'TOP')
    70.         j = j + 1
    71.         bot_reference = []
    72.         top_reference = []
    73.     except Exception as e:
    74.         print(e)
    75.         print('error,please check the positon_info and BOM_DATA')
    76.         break
    78. # 合并bot and top bom_data  
    79. data_bom_goertek = pd.concat([data_bom_goertek_top,data_bom_goertek_bot])
    80. # 保存bom
    81. try:
    82.     with pd.ExcelWriter('bom_top_bot.xlsx') as writer:
    83.         data_bom_goertek.to_excel(writer)
    84.     writer.save()
    85.     print('BOM done,please check the bom,if the script report some error,maybe the bom is not right,thanks')
    86. except Exception as e:
    87.     print(e)