python教程分享基于Python实现对比Exce的工具

import pandas as pd  from openpyxl import load_workbook  #选择文件路径  path=r"c:users小管同学desktopmigration_data_compari对比文件.xls" #input("选择文件路径:")  targetpath=r"c:users小管同学desktopmigration_data_comparison_tool目标文件对比结果.xlsx"  data_ods=pd.read_excel(r"c:users小管同学desktopmigration_data_comparison_tool对比文件.xls",sheet_name="ods")  data_dwh=pd.read_excel(r"c:users小管同学desktopmigration_data_comparison_tool对比文件.xls",sheet_name="dwh")  #选择主键  primarykey="员工编号"#input("选择主键1:")  primarykey  # 员工编号  

def write_to_excel_datavolume(data,targetpath): # cor_df 为要保存的 dataframe       writer = pd.excelwriter(targetpath, engine='xlsxwriter') # 这里用      data.to_excel(writer,sheet_name='sheet1', encoding='utf8', header=false, startcol=0, startrow=2) # 把dataframe的数据从第2行开始      workbook  = writer.book            format1 = workbook.add_format({ # 先把样式打包，然后之后赋值即可          'bold': true, # 字体加粗          'text_wrap': true, # 是否自动换行          'valign': 'bottom',  #垂直对齐方式          'align': 'center', # 水平对齐方式          'fg_color': '#c5d9f1', # 单元格背景颜色          'border': 1,# 边框      })          writer_sheet = writer.sheets['sheet1']      # 设置宽度      writer_sheet.set_column("a:i", 16)      writer_sheet.set_column('c:c',30)      writer_sheet.merge_range(0,0,0,2,'对比结果',format1)      writer_sheet.merge_range(4,2,4,0,'数据量差异',format1)      writer_sheet.write(1,0,'',format1)      writer_sheet.write(1,1,'ods',format1)      writer_sheet.write(1,2,'dwh',format1)      writer.save()      writer.close()  dataframe_datavolume=pd.dataframe([[data_ods.shape[0]],[data_dwh.shape[0]]]).t  dataframe_datavolume.columns =["ods","dwh"]  dataframe_datavolume.index=["数据量"]  dataframe_datavolume  #writefiledatavolume(dataframe_datavolume,targetpath)  write_to_excel_datavolume(dataframe_datavolume,targetpath)  

if data_ods.shape[0]==data_dwh.shape[0]:      pass  else:            data_ods_primarykey=pd.dataframe(data_ods[primarykey])      data_dwh_primarykey=pd.dataframe(data_dwh[primarykey])      df_union = pd.concat([data_ods_primarykey,data_dwh_primarykey])      # 实现1      df_diff_ods = df_union.append(data_ods_primarykey).drop_duplicates(subset=df_union.columns.to_list(), keep=false)      df_diff_dwh = df_union.append(data_dwh_primarykey).drop_duplicates(subset=df_union.columns.to_list(), keep=false)      #dwh多的合同      df_diff_ods      #dwh少的合同      df_diff_dwh      df_diff_dwh_data=[]      df_diff_ods_data=[]      for i in df_diff_ods.head(10).values.tolist():          for n in i:              df_diff_ods_data.append(n)                    for i in df_diff_dwh.head(10).values.tolist():              df_diff_dwh_data.append(n)      while true:          if len(df_diff_dwh_data)>len(df_diff_ods_data):              df_diff_ods_data.append("-")          elif len(df_diff_dwh_data)