前言背景
年前運(yùn)用Python寫的小應(yīng)用，借此刻寫本文的機(jī)會(huì)吟榴，再次整理總結(jié)。
我們公司電子工程師會(huì)使用Mentor工具導(dǎo)出一張BOM表者吁，然后相關(guān)專員會(huì)將其手動(dòng)輸入到SAP系統(tǒng)里 ( 為何不能一鍵導(dǎo)入囊骤？）摇零。但由于數(shù)據(jù)龐雜虎敦，專員擔(dān)心手動(dòng)輸入可能有誤股淡，于是又將SAP里數(shù)據(jù)導(dǎo)出挺狰，讓工程師去比對(duì)確認(rèn)材料明郭、位號(hào)和數(shù)量有無錯(cuò)誤。
有一次看到某同事一直忙于在整理Excel她渴、比較數(shù)據(jù)达址，于是我就想能否用Pandas和PyQT5去寫個(gè)小工具自動(dòng)比較并導(dǎo)出結(jié)果？
工具界面十分簡(jiǎn)單趁耗，只要選擇SAP數(shù)據(jù)表和Mentor數(shù)據(jù)表后沉唠，點(diǎn)擊保存至Excel即可。有差異的地方按照自己想要的樣式呈現(xiàn)在Excel苛败，如此節(jié)省工程師整理比較數(shù)據(jù)時(shí)間满葛。
SAP表里可能會(huì)有個(gè)某個(gè)大類產(chǎn)品下多個(gè)型號(hào)的設(shè)備BOM數(shù)據(jù)，而Mentor 表是單一型號(hào)設(shè)備數(shù)據(jù)罢屈。SAP 表每一行都是某個(gè)位號(hào)的材料信息嘀韧，而Mentor 表里每行是某個(gè)Part No信息，2張表樣式（部分）如下：
SAP和Mentor數(shù)據(jù)表(部分內(nèi)容)
從上圖中可看出SAP和Mentor數(shù)據(jù)表里都有很多列缠捌，但我們需要比較的分別是SAP 的
Material,Componet(同Mentor里的PART NO) 和Installation point(同Mentor里的REF DES)锄贷，和Mentor里的PART NO,REF DES以及COUNT译蒂。SAP Material列篩選后有4個(gè)不同的數(shù)據(jù)，50149261-003谊却，50149261-013柔昼，50149261-023，50149261-033炎辨，它們是用于區(qū)分同類產(chǎn)品下的4個(gè)不同module的設(shè)備捕透。Mentor表文件名稱包含這些字段，如文件名50149261-033(SCBIP-24V)碴萧。
工具界面
小工具界面
SAP數(shù)據(jù)
我們Upload SAP BOM表時(shí)需要對(duì)Excel進(jìn)行簡(jiǎn)單判斷乙嘀，要求其必須含有Installation point列(Mentor不含此列)。選擇Mentor數(shù)據(jù)表時(shí)破喻，要求Excel文件名稱含關(guān)鍵字段虎谢，否則給予錯(cuò)誤提示。
def loadSAPData(self):
file_Path, _ = QFileDialog.getOpenFileName(self, 'Open file', "C:\\", 'Excel files(*.xlsx , *.xls)')
# 如果用戶不選擇文件低缩，直接取消選擇嘉冒，
if file_Path =='':
return
df = pd.read_excel(file_Path) #根據(jù)路徑讀取excel文件
columns = df.columns.values.tolist() # 獲取表所有的列名
if 'Installation point' in columns: # 判斷關(guān)鍵字在列名稱列表里
self.label_sap_file.setText(file_Path.split('/')[-1]) #文件名顯示在界面上
self.sap_df = df
self.materials = df['Material'].unique() #列表，存放不同型號(hào)
self.label_sap.setStyleSheet("QLabel{border-image: url(:icons/excel.png);}") #label設(shè)置圖片
else:
self.showMessage() #選擇的文件不含有Installation point列咆繁，則提示錯(cuò)誤信息
def showMessage(self):
self.materials = [] #清空列表里的material
messageBox = QMessageBox()
messageBox.setWindowIcon(QIcon(':icons/error.png')) #注意為了打包后APP上能夠顯示圖片讳推，此處使用冒號(hào)，icons 是項(xiàng)目里的一個(gè)自己創(chuàng)建的文件夾
messageBox.setWindowTitle('Error Message')
messageBox.setText(
"This file doesn't include a column named 'Installation point'.\nWould you like reload a file? ")
messageBox.setStandardButtons(QMessageBox.Yes | QMessageBox.No)
buttonY = messageBox.button(QMessageBox.Yes)
buttonY.setText('Select File')
buttonN = messageBox.button(QMessageBox.No)
buttonN.setText('Cancel')
messageBox.exec_()
if messageBox.clickedButton() == buttonY:
self.loadSAPData()
選擇文件錯(cuò)誤提示
根據(jù)material清洗整理SAP數(shù)據(jù)
前文中說到SAP數(shù)據(jù)表里包含多個(gè)module的數(shù)據(jù)玩般，而Mentor數(shù)據(jù)表是單個(gè)型號(hào)的數(shù)據(jù)银觅，其文件名稱里包含material字段。
首先需要根據(jù)material來篩選出不同的數(shù)據(jù)：
def getSperatedDF(self, material):
# 根據(jù)材料名篩選坏为，如'50149261-003'
df = self.sap_df.loc[self.sap_df['Material'] == material]
# 無重復(fù)的location 數(shù)量
count_df = df.groupby('Component', sort=False)[['Installation point']].nunique().reset_index()
# 同一顆的不同位置拼接在一起并空格分隔
gpby_df = df.groupby('Component', sort=False).apply(lambda x: ' '.join(x['Installation point'])).reset_index()
# 取出'Component', 'Installation point' 2 列數(shù)據(jù)
gpby_df.columns = ['Component', 'Installation point']
gpby_df['SAP_COUNT'] = count_df['Installation point']
# 對(duì)位號(hào)字符串排序
sorted_df = self.tool.getSortedLocationsDF(gpby_df,'Installation point')
return sorted_df
接下來需要對(duì)Installation point列各單元格里雜亂的位號(hào)字符串進(jìn)行排序 究驴，如將C1 C20 C4 C3排成C1 C3 C4 C20。
def sortLocationstr(self, installation_point_str):
if installation_point_str !='': #判斷不為空
split_list = re.findall(r'[0-9]+|[a-zA-Z]+',installation_point_str)
first_letters = split_list[0] #獲取第一個(gè)字母匀伏，是C/R/…
numStr_list = list(set(split_list)) # 對(duì)所有去重洒忧，用set集合方法
numStr_list.remove(first_letters)# 去掉字母，只留下數(shù)字
num_list= []
for num_str in numStr_list:
num_list.append(int(num_str))
num_list = sorted(num_list) #數(shù)字進(jìn)行排序
locations = [] #位置
for num in num_list:
location = first_letters + str(num)
locations.append(location)
return ' '.join(locations)
else:
return ''
根據(jù)列名稱和df文件生成排序后的文件：
def getSortedLocationsDF(self, df, column_name):
for i in range(df.shape[0]): # shape[0]獲取行數(shù)
installation_point_str = df.loc[i, column_name]
df.loc[i, column_name] = self.sortLocationstr(installation_point_str) # 對(duì)位置字符串排序
return df
Mentor數(shù)據(jù)
def loadMentorData(self):
file_Path, _ = QFileDialog.getOpenFileName(self, 'Open file', "C:\\", 'Excel files(*.xlsx , *.xls)')
# 如果用戶不選擇文件够颠，直接取消選擇熙侍，
if file_Path == '':
return
df = pd.read_excel(file_Path)
material = file_Path.split('/')[-1].split('.')[0]
if '(' in material:
material = material.split('(')[0]
if material in self.materials:
self.label_mentor_file.setText( file_Path.split('/')[-1])
self.label_mentor.setStyleSheet("QLabel{border-image: url(:icons/excel.png);}")
self.mentor_file_name = material
self.mentor_df = df
self.mentor_df = self.mentor_df[['PART NO','REF DES','COUNT']].fillna('') # 選出 'PART NO','REF DES','COUNT' 3列數(shù)據(jù)
self.tool.getSortedLocationsDF(self.mentor_df,'REF DES') # 對(duì)'REF DES' 列單元格里的位置排序
self.material_df = self.getSperatedDF(material)
else:
#以下代碼主要是彈出錯(cuò)誤提示
self.mentor_file_name = ''
messageBox = QMessageBox()
messageBox.setWindowTitle('Error Message')
messageBox.setWindowIcon(QIcon(':icons/error.png'))
messageBox.setText(
"This file isn't part of the SAP data. \nWould you like reload a file? ")
messageBox.setStandardButtons(QMessageBox.Yes | QMessageBox.No)
buttonY = messageBox.button(QMessageBox.Yes)
buttonY.setText('Select File')
buttonN = messageBox.button(QMessageBox.No)
buttonN.setText('Cancel')
messageBox.exec_()
if messageBox.clickedButton() == buttonY:
self.loadMentorData()
合并數(shù)據(jù)并比較
至此我們需要的material_df 和 mentor_df已經(jīng)處理完畢，接下來需要將2者merge并進(jìn)行比較履磨。
def mergeData(self):
compare_df = pd.merge(self.material_df,self.mentor_df,left_on='Component',right_on='PART NO',how= 'outer')
compare_df = compare_df.fillna('')
compare_df['Component_Bool'] = compare_df.apply(lambda x: self.tool.compare(x['Component'],x['PART NO']),axis = 1)
compare_df['COUNT_Bool'] = compare_df.apply(lambda x: self.tool.compare(x['SAP_COUNT'],x['COUNT']),axis = 1 )
compare_df['Location_Bool'] = compare_df.apply(lambda x: self.tool.compare(x['Installation point'],x['REF DES']),axis = 1 )
compare_df['Location_Change'] = compare_df.apply(lambda x: self.tool.compareLocation(str(x['Installation point']), str(x['REF DES'])), axis = 1)
return compare_df
這里主要比較對(duì)應(yīng)的兩列內(nèi)容是否一致蛉抓，一致就返回True，否則為False剃诅。
def compare(self, a, b):
if a == b:
return 'True'
else:
return 'False'
比較 2個(gè)位號(hào)字符串差異并返回差異結(jié)果巷送，如C2,C4,C7 和C2,C3,C7, 它們的差異就是C3和C4。
def compareLocation(self, sap_loction, mentor_locatoin):
if set(sap_loction.split(' ')).difference(mentor_locatoin.split(' ')) != {''}:
location_list = list(set(sap_loction.split(' ')).difference(set(mentor_locatoin.split(' '))))
else:
location_list = list(set(mentor_locatoin.split(' ')).difference(set(sap_loction.split(' '))))
return ' '.join(location_list)
Excel格式化
創(chuàng)建一個(gè)Excel 類并設(shè)置其相關(guān)格式矛辕，根據(jù)你自己的需求進(jìn)行格式修改笑跛。
import numpy as np
from openpyxl import load_workbook
from openpyxl.styles import Alignment, Side, Border, PatternFill, Font
from openpyxl.styles.colors import BLACK, RED, YELLOW
class Excel(object):
def __init__(self,excel_path):
super().__init__()
self.wb = load_workbook(excel_path)
self.ws = self.wb.active
self.excel_path = excel_path
self.font_name = 'Biome Light'
def formatExcel(self):
font = Font(name = self.font_name , size=10, color = BLACK) #字體
false_font = Font(name = self.font_name, bold=True, size=10, color = BLACK) #false單元格字體
alignment = Alignment(horizontal='center', #水平居中
vertical='center', #垂直居中
wrap_text=True, # 文字換行
shrink_to_fit=False, # 自適應(yīng)寬度付魔，改變文字大小,上一項(xiàng)false
)
thin = Side(border_style="thin", color=BLACK) #邊框線顏色
border = Border(top=thin, left=thin, right=thin, bottom=thin) #邊框線
header_fill = PatternFill(fill_type='solid', fgColor= YELLOW ) #表頭填充顏色
false_fill = PatternFill(fill_type='solid', fgColor= RED) #false單元格填充顏色
header_font = Font(name=self.font_name, bold=True, size=12, color=BLACK) #表頭單元格字體
for row in self.ws.rows:
for cell in row:
cell.font = font
cell.alignment = alignment
cell.border = border
if cell.value == 'False':
cell.fill = false_fill
cell.font = false_font
# 如果是第一行，列名稱那行
for cell in list(self.ws.rows)[0]:
cell.fill = header_fill
cell.font = header_font
self.setColumnsWidth(25)
self.wb.save(self.excel_path)
def setColumnsWidth(self,width):
column = self.ws.max_column # 獲取表格列數(shù)
numbers = np.arange(65, 65 + column) # 大寫字母A 是65
for i in [chr(i) for i in numbers]: #生產(chǎn)字母A,B,C,D……
self.ws.column_dimensions[i].width = width
導(dǎo)出Excel文件
def saveToExcel(self):
compare_df = self.mergeData()
sorted_df = self.tool.getSortedLocationsDF(compare_df,'Location_Change')
sorted_df.to_excel('{}_compare.xlsx'.format(self.mentor_file_name),sheet_name= 'compare', engine = 'openpyxl',index=False)
excel = Excel('{}_compare.xlsx'.format(self.mentor_file_name))
excel.formatExcel() #設(shè)置格式
對(duì)比結(jié)果（部分）
打包調(diào)用圖片
我們需要?jiǎng)?chuàng)建一個(gè)名稱為makeqrc.py的文件堡牡，運(yùn)行此文件會(huì)生成2個(gè)文件抒抬，分別是images.qrc和images.py文件。
十分重要的一點(diǎn)是晤柄，必須在導(dǎo)包的文件里import images,images 是images.py。
import subprocess, os
images = os.listdir('icons') #icons是存放圖片文件的文件夾
f = open('images.qrc', 'w+')
f.write(u'<!DOCTYPE RCC>\n<RCC version="1.0">\n<qresource>\n')
for item in images:
f.write(u'<file alias="icons/'+ item +'">icons/'+ item +'</file>\n')
# for item in qss:
# f.write(u'<file alias="qss/'+ item +'">qss/'+ item +'</file>\n')
f.write(u'</qresource>\n</RCC>')
f.close()
pipe = subprocess.Popen(r'pyrcc5 -o images.py images.qrc', stdout = subprocess.PIPE, stdin = subprocess.PIPE, stderr = subprocess.PIPE, creationflags=0x08)
導(dǎo)包
在Terminal 里輸入下面代碼：
pyinstaller -F -w - i icon.ico **.py
其中icon.ico為exe文件添加圖標(biāo)的文件名妖胀， -w是無黑窗口芥颈，即報(bào)錯(cuò)時(shí)看不到打印信息。
