excel两列值进行比较，相同的放在字典内

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)