Python3.7 比较两个Excel文件指定列的值的异同，并将核对结果写入Excel中（含升级版本）
背景：
最近工作中需要核对客户的历史数据，
接近400个产品，需要核对的列有15列，行数有8000+行
肉眼核对简直要吐血
心想着反正在学python呢
人生苦短
何不用python写个脚本
帮助我核对
我省出时间来做些更有意义的事情呢
前提：
我需要核对的两份Excel有以下特点：
- 有共同的主键
- 两份Excel的结构不尽相同，仅需要核对源文件47列中的15列，这些列在目标文件中都能找到
主要思路：
将两个文件的主键作为键，将要核对的列作为值，保存到字典中
然后再进行比较
实现代码：
import xlrd
import xlwt
import time
origin_dict = {} # 初始化，用于保存源文件{key(主键):value(需要核对的列值)}
target_dict = {} # 初始化，用于保存目标文件{key(主键):value(需要核对的列值)}
放在函数外部声明 会报错 还不晓得原因
success = 0
fail = 0
def compare_excel(ori_path,tar_path,col_ori,col_tar):
'''
ori_path:用于存放源文件
tar_path:用于存放目标文件
col_ori:源文件中需要比较的列
col_tar:目标文件中需要比较的列
'''
success = 0 # 核对无差异的个数
fail = 0 # 核对结果有差异的个数
row_res = 0 #要写入的文件的行
# 分别打开源文件与目标文件
wb\_ori = xlrd.open\_workbook(ori\_path)
wb\_tar = xlrd.open\_workbook(tar\_path)
# 新建一个excel，用于存放核对结果
wb\_res = xlwt.Workbook()
# 分别获取源文件与目标文件的sheet
sheet\_ori = wb\_ori.sheet\_by\_index(0)
sheet\_tar = wb\_tar.sheet\_by\_index(0)
# 新建一名称为result的sheet页，用于存放核对具体结果
sheet\_res = wb\_res.add\_sheet('result')
# 获取源文件中由主键、需核对列组成的键值对，放入字典中
for row\_ori in range(1,sheet\_ori.nrows):
cell\_ori\_key = sheet\_ori.cell\_value(row\_ori,0) #因我的源文件的主键位于第0列，故该列未参数化，大家可以视自己实际情况进行优化
cell\_ori\_value = sheet\_ori.cell\_value(row\_ori,col\_ori)
origin\_dict\[cell\_ori\_key\] = cell\_ori\_value
# print('源文件获取成功')
# print('源文件如下：%s' % origin\_dict)
# 获取目标文件中由主键、待核对列组成的键值对，放入字典中
for row\_tar in range(1,sheet\_tar.nrows):
cell\_tar\_key = sheet\_tar.cell\_value(row\_tar,0)
cell\_tar\_value = sheet\_tar.cell\_value(row\_tar,col\_tar)
target\_dict\[cell\_tar\_key\] = cell\_tar\_value
# print('目标文件获取成功')
# print('目标文件如下：%s' % target\_dict)
# 核对逻辑
try:
for i in origin\_dict.keys(): # 获取源文件字典的键
if target\_dict.get(i) == origin\_dict.get(i): # 对比两个字典中相同键的值
success += 1 # 值相等，则无差异数+1
sheet\_res.write(row\_res+1,0,i) # 将键写入结果文件的第0列
sheet\_res.write(row\_res+1,1,'你俩长一样') #将核对无差异结果写入结果文件的第1列
row\_res += 1 # 结果文件行数+1
print('金融产品 %s 核对无差异'% i)
else:
fail +=1 # 值不相等，则有差异数+1
sheet\_res.write(row\_res+1,0,i)
sheet\_res.write(row\_res+1,1,'核对有差异：源文件的值为：%s，目标文件的值为:%s' % (origin\_dict.get(i),target\_dict.get(i))) # #将核对有差异结果写入结果文件的第1列
row\_res += 1 # 结果文件行数+1
print('金融产品 %s 核对有差异：源文件的值为：%s，目标文件的值为:%s' % (i,origin\_dict.get(i),target\_dict.get(i)))
wb\_res.save('result.xlsx') # 保存结果文件
print(time.strftime('%Y-%m-%d %H-%M-%S',time.localtime())+'核对完成，共核对 %d 条，其中无差异 %d 条， 有差异 %d条' % (len(origin\_dict),success,fail))
except Exception as error:
print(str(error))compare_excel('C111111.xlsx','O222222.xlsx',3,2) # 核对源文件第4列，目标文件第3列
后续：
代码还有很多优化的空间：
比如说源文件中有的键目标文件中没有，如何提示？
比如说能否一次比较多个列的值
比如扩展成双主键/多主键，该如何比对？
如果你有方法或思路，可以跟我这个小白一起交流
*************************************************************************************************************************************************************************************************
上面说的优化空间，今天解决了多主键的问题
多主键问题解决的关键
是把多主键拼接成一个字符串，然后再作为字典的键
进而比较其值
优化的点还包括：
将核对结果追加写入源文件中
而非新建一个Excel
具体代码如下：
import xlrd
import xlwt
import xlutils
import time
from datetime import datetime
from xlrd import xldate_as_tuple
from xlutils import copy
dict_ori = {} # 初始化，用于保存源文件{key(主键):value(需要核对的列值)}
dict_tar = {} # 初始化，用于保存目标文件{key(主键):value(需要核对的列值)}
def Excel_Compare(ori_path,tar_path,sheet_index_ori,col_ori,col_tar):
'''
ori_path:源文件地址
tar_path:目标文件地址
sheet_index_ori:源文件中待核对的sheet的索引
col_ori:源文件中待核对的列
col_tar:目标文件中待核对的列
'''
success = 0
fail = 0
space = 0 # 空行个数
row_res = 0
# 分别打开源文件与目标文件
wb_ori = xlrd.open_workbook(ori_path,'w+')
wb_tar = xlrd.open_workbook(tar_path)
# 新建一个excel，用于存放核对结果
# wb_res = xlwt.Workbook()
wb_res = copy.copy(wb_ori) # 在源文件中追加写入
# 分别获取源文件与目标文件的sheet
sheet\_ori = wb\_ori.sheet\_by\_index(sheet\_index\_ori)
sheet\_tar = wb\_tar.sheet\_by\_index(0)
# 新建一名称为result的sheet页，用于存放核对具体结果
# sheet\_res = wb\_res.add\_sheet('result')
sheet\_res = wb\_res.get\_sheet(sheet\_index\_ori) # 在原sheet中追加写入
# 获取源文件中由主键、需核对列组成的键值对，放入字典中
for row\_ori in range(1,sheet\_ori.nrows):
product\_id\_ori = sheet\_ori.cell\_value(row\_ori,1)
# print(product\_id\_ori)
product\_name\_ori = sheet\_ori.cell\_value(row\_ori,4).split('-')\[1\] # 源文件中账套名称为：XX-xx形式，因核对时只需要后面的xx，故需要做一下处理
# product\_name\_ori\_ctype = sheet\_ori.cell(row\_ori,4).ctype
# print(product\_name\_ori)
# print(product\_name\_ori\_ctype)
# 以下3行代码将从Excel读出的日期数据进行转化并格式化
date\_ori\_1 = sheet\_ori.cell\_value(row\_ori,11)
date\_ori\_2 = datetime(\*xldate\_as\_tuple(date\_ori\_1,0))
date\_ori = date\_ori\_2.strftime('%Y-%m-%d')
# print(date\_ori)
key\_ori\_list = \[product\_id\_ori,product\_name\_ori,date\_ori\]
key\_ori = '--'.join(key\_ori\_list) # 关键点，将多主键拼接成字符串
# print(key\_ori)
income\_ori\_1 = sheet\_ori.cell\_value(row\_ori,col\_ori)
income\_ori = round(float(income\_ori\_1),2) # 读出来的数据为str类型，需转化为float类型，方便进行处理，注意源文件中不能有空行
# dict\_ori = {key\_ori:income\_ori} # 该方式最终仅保存一次，不是想要的结果
dict\_ori\[key\_ori\] = income\_ori # 将提取出的关键信息追加保存为字典格式
# print("源文件数据获取成功")
# print(dict\_ori)
# 获取源文件中由主键、需核对列组成的键值对，放入字典中
for row\_tar in range(1,sheet\_tar.nrows):
product\_id\_tar = sheet\_tar.cell\_value(row\_tar,1)
product\_name\_tar = sheet\_tar.cell\_value(row\_tar,5)
date\_tar\_1 = sheet\_tar.cell\_value(row\_tar,15)
key\_tar\_list = \[product\_id\_tar,product\_name\_tar,date\_tar\_1\]
key\_tar = '--'.join(key\_tar\_list)
income\_tar\_1 = sheet\_tar.cell\_value(row\_tar,col\_tar)
income\_tar = round(float(income\_tar\_1),2)
# income\_tar\_ctype = sheet\_tar.cell(row\_tar,19).ctype
# print(income\_tar\_ctype)
# dict\_tar = {key\_tar:income\_tar}
dict\_tar\[key\_tar\] = income\_tar
# print("目标文件数据获取成功")
# print(dict\_tar)
# 核对逻辑
try:
for i in dict\_ori.keys():
# print(type(dict\_tar.get(i)))
# income\_ori\_float = float(dict\_ori.get(i))
# print(i)
if dict\_tar.get(i) == dict\_ori.get(i): # 无差异的情况
success += 1
product\_id\_res = i.split('--')\[0\]
product\_name\_res = i.split('--')\[1\]
date\_res = i.split('--')\[2\]
sheet\_res.write(row\_res+1,20,product\_id\_res)
sheet\_res.write(row\_res+1,21,product\_name\_res)
sheet\_res.write(row\_res+1,22,date\_res)
sheet\_res.write(row\_res+1,23,'核对无误')
row\_res += 1
print('金融产品:%s，账套:%s，日期：%s的收益数据核对无差异' % (product\_id\_res,product\_name\_res,date\_res))
elif dict\_ori.get(i) == 0.00 and dict\_tar.get(i) == None: #有空值的情况
space += 1
product\_id\_res = i.split('--')\[0\]
product\_name\_res = i.split('--')\[1\]
date\_res = i.split('--')\[2\]
sheet\_res.write(row\_res+1,20,product\_id\_res)
sheet\_res.write(row\_res+1,21,product\_name\_res)
sheet\_res.write(row\_res+1,22,date\_res)
sheet\_res.write(row\_res+1,23,'空值')
row\_res += 1
print('金融产品:%s，账套:%s，日期：%s的数据为空' % (product\_id\_res,product\_name\_res,date\_res))
else: # 核对有差异的情况
fail += 1
product\_id\_res = i.split('--')\[0\]
product\_name\_res = i.split('--')\[1\]
date\_res = i.split('--')\[2\]
sheet\_res.write(row\_res+1,20,product\_id\_res)
sheet\_res.write(row\_res+1,21,product\_name\_res)
sheet\_res.write(row\_res+1,22,date\_res)
sheet\_res.write(row\_res+1,23,'数值有差异，源文件收益为：%s,目标文件的收益为:%s'%(dict\_ori.get(i),dict\_tar.get(i)))
row\_res += 1
print('金融产品:%s，账套:%s，日期：%s 的收益数据核对有差异，源文件的收益为%s,目标文件的收益为%s'%(product\_id\_res,product\_name\_res,date\_res,dict\_ori.get(i),dict\_tar.get(i)))
except Exception as error:
print(str(error))
wb\_res.save(ori\_path) # 保存源文件
print(time.strftime('%Y-%m-%d %H:%M:%S',time.localtime())+'核对完成，共核对%d条，其中，无差异%d条，有差异%d条'%(success+fail,success,fail))Excel_Compare('CashSequence111.xlsx','CashSequence222.xlsx',3,16,19)
手机扫一扫
移动阅读更方便
你可能感兴趣的文章