用到的库:xlrd
python版本:3.10
需求:
1文件为目标文件,2文件为处理文件。2文件中有多个sheet,1文件中只有1个sheet。
2文件中每个sheet有数据,需要和1文件中数据做对比,并记录是否相同。
文件内容图片:
import os
import xlrd as xl
from xlutils.copy import copy
def contrastFun(target_path,file_path):
#读取原文件,也就是整理好的文件
file = xl.open_workbook(file_path)
#整理好的编码,全部存储入file_content_list
file_content_list = []
file_content_list_name = []
file_sheet_names = file.sheet_names()
#遍历源文件,获取全部编码
all_row_num = 0
for i in range(0,file.nsheets):
sheet = file.sheet_by_index(i)
all_row_num = all_row_num + sheet.nrows
for j in range(sheet.nrows):
rowValue = sheet.row_values(j,0,1)
file_content_list.append(rowValue[0])
file_content_list_name.append(file_sheet_names[i])
#读取目标文件,也就是合同信息
target = xl.open_workbook(target_path)
saveFile = copy(target)
sh=saveFile.get_sheet(0)
#获取全部合同编码
sheet = target.sheet_by_index(0)
targetID = sheet.col_values(0)
yes = 0
nullRow = 0
for i in range(0,len(targetID)):
if sheet.cell_type(i,0) == 0:
nullRow = nullRow+1
continue
for j in range(0,len(file_content_list)):
if file_content_list[j] == targetID[i]:
yes = yes +1
sh.write(i,3,"*****")
sh.write(i,4,file_content_list_name[j])
sh.write(len(targetID),3, str(yes))
sh.write(len(targetID),4, str(len(targetID) - yes))
sh.write(len(targetID),5, str(nullRow))
saveFile.save("处理结果.xls")
if __name__ == '__main__':
#图片路径
target_path = r'1.xls'
#图片保存路径
file_path = r'2.xls'
contrastFun(target_path,file_path)