import pandas as pd from openpyxl import load_workbook import change_font_ok import pyautogui import os
def get_target_value(arr1,arr2):#比较两列的值,相同的值的行号和内容组成字典放入equal,不相同的值的行号和内容组成字典放入diff, equal= {} diff={} for i in range(len(arr1)): if arr1[i][0]!=arr2[i][0]: arr2[i][0]=arr1[i][0] diff[i]=arr1[i][0] else: equal[i]=arr1[i][0]#不同的值 return equal,diff
def main(col1,col2,filename,result_filename):#col1,col2为比较两列的列号 # filename = r'D:\dwp_backup\python study\PDF\python tecnology competetion\pdf\Corresponding name-0810.xlsx' df2 = pd.read_excel(filename, sheet_name='Sheet1',header=None) #这里可以用try except if df2.shape[1]<=col1+1 or df2.shape[1]<=col2+1: pyautogui.alert(text='列号超出范围',title='',button='OK') print('输入的列号超出范围!') else: print('输入的列号在范围内!') #退出程序1 # 取出dataframe中的第一列和第二列组成数组array try: df_refer=df2.iloc[1:, [1,col1,col2]]#Corresponding name-0810原始数据第0行为索引 except IndexError: pyautogui.alert(text='IndexError', title='', button='OK') os._exit(0) src_arr = df2.iloc[:, col1:col1+1].values#查询速度快, tar_arr = df2.iloc[:, col2:col2+1].values d = get_target_value(src_arr, tar_arr)
def wrtoex(filepath,dict,col):#filepath为文件名的绝对路径,dict为要写入的内容,key为要写入的行号,value为要写入的内容。clo为列号 book = load_workbook(filepath) with pd.ExcelWriter(filepath) as writer: writer.book = book sheet = book['Sheet1'] # 通过sheet名称 获取 sheet for key,value in dict.items(): sheet.cell(key+1, col, value) def exl_wr(df3,sheetname,result_filename): # with pd.ExcelWriter(path='./csv_files/demo.xlsx', mode='a', engine='openpyxl', if_sheet_exists='replace') as writer: with pd.ExcelWriter(path=result_filename, mode='a', engine='openpyxl', if_sheet_exists='replace') as writer: # print(writer.sheets) # 返回元祖,key为sheet名字,value为sheet对象 # df1.to_excel(excel_writer=writer, sheet_name='Sheet1') # 写入Sheet1方式为覆盖 # # writer.if_sheet_exists = 'overlay' # 写入Sheet2方式改为追加 # df2.to_excel(excel_writer=writer, sheet_name='Sheet2', startrow=10) # writer.if_sheet_exists = 'new' # 写入Sheet3方式改为新建Sheet,如果表已经存在则会根据'openpyxl'的命名方式创建sheet # df3.to_excel(excel_writer=writer, sheet_name=sheetname, startrow=0)#不能新建,如有相同的sheetname新建之后的sheetname发生改变,更改字体时无法读取sheetname writer.if_sheet_exists = 'replace' # 写入Sheet3方式改为新建Sheet,如果表已经存在则会根据'openpyxl'的命名方式创建sheet df3.to_excel(excel_writer=writer, sheet_name=sheetname, startrow=0) # 不能新建,如有相同的sheetname新建之后的sheetname发生改变,更改字体时无法读
def main(col1,col2,filename,result_filename):#col1,col2为比较两列的列号 # filename = r'D:\dwp_backup\python study\PDF\python tecnology competetion\pdf\Corresponding name-0810.xlsx' df2 = pd.read_excel(filename, sheet_name='Sheet1',header=None) #这里可以用try except if df2.shape[1]<=col1+1 or df2.shape[1]<=col2+1: pyautogui.alert(text='列号超出范围',title='',button='OK') print('输入的列号超出范围!') else: print('输入的列号在范围内!') #退出程序1 # 取出dataframe中的第一列和第二列组成数组array try: df_refer=df2.iloc[1:, [1,col1,col2]]#Corresponding name-0810原始数据第0行为索引 except IndexError: pyautogui.alert(text='IndexError', title='', button='OK') os._exit(0) src_arr = df2.iloc[:, col1:col1+1].values#查询速度快, tar_arr = df2.iloc[:, col2:col2+1].values d = get_target_value(src_arr, tar_arr) d[0]#两列相等的字典key为行号,value为内容 d[1]#两列不相等的字典,将不相等的行号所对应的单元格字体更换。 sheetname=df2.iloc[1][col1]+'_'+df2.iloc[1][col2] exl_wr(df_refer, sheetname[0:29],result_filename)#报告sheetname不可以超过32个字符,将810文件的1 7 8列放入compare_result内 change_font_ok.change_font(d[1],sheetname[0:29],result_filename,filename)#调用更改字体.py文件。
if __name__=='__main__': result_filename = 'compare.xlsx' filename = 'Corresponding.xlsx' col=pyautogui.prompt(text='输入需要比较的列号,用英文输入的逗号隔开',title='',default='输入需要比较的列号')#输入需要比较的列号,从0开始,中间用空格隔开 try: col = col.split(',') col1=int(col[0]) col2 =int(col[1]) except ValueError: pyautogui.alert(text='ValueError', title='', button='OK') os._exit(0) except AttributeError: os._exit(0) # col1=7 # col2=21 main(col1,col2,filename,result_filename)