import openpyxl
from openpyxl.styles import PatternFill
#对比两个sheet,数据一致性校验
# 获取sheet对象的某一行
def getRow(sheet, rowNo):
try:
rows = []
for row in sheet.iter_rows():
rows.append(row)
return rows[rowNo - 1]
except Exception as e:
raise e
def getCol(sheet, rowNo):
try:
rows = []
for row in sheet.iter_rows():
rows.append(row)
return rows[rowNo - 1]
except Exception as e:
raise e
# 获取指定坐标单元格的值
def getCellOfValue(sheet, coordinate=None, rowNo=None, colsNo=None):
if coordinate != None:
try:
return sheet.cell(coordinate=coordinate).value
except Exception as e:
raise e
elif coordinate is None and rowNo is not None and \
colsNo is not None:
try:
return sheet.cell(row=rowNo, column=colsNo).value
except Exception as e:
raise e
else:
raise Exception("insufficient Coordinates of cell!")
print("----------对比程序运行START------------------")
origin_file = r"D:\Desktop\归档表.xlsx"
target_file = r"D:\Desktop\39归档表.xlsx"
# 把源文件和目标文件加载到内存对象
wb_origin = openpyxl.load_workbook(origin_file)
wb_target = openpyxl.load_workbook(target_file)
# 通过sheet名拿到sheet对象
target_sheet = wb_origin["ora"]
origin_sheet = wb_origin["rds"]
# 获得最大行号
origin_sheet_max_row = origin_sheet.max_row
target_sheet_max_row = target_sheet.max_row
##获得最大列号
origin_sheet_max_column = origin_sheet.max_column
target_sheet_max_column = target_sheet.max_column
if origin_sheet_max_column != target_sheet_max_column:
print("2个文件列数不一致,请检查")
if origin_sheet_max_row != target_sheet_max_row:
print("2个文件行数不一致,请检查")
print("—————————对比程序进行中,开始循环遍历——————————————————————")
print("origin_sheet_max_row",origin_sheet_max_row)
for row_no in range(1, origin_sheet_max_row + 1):
row = getRow(origin_sheet, row_no) #获取一行。
#col = getCol(origin_sheet, col_no)
row_length = len(row)
#print("row_length:",row_length)
#col_length = len(col)
col_num = 1 # 列号,从第一列开始比较,逐个单元格做对比。
red_fill = PatternFill("solid", fgColor="FF0000")
while col_num < row_length +1:
#print(getCellOfValue(origin_sheet, rowNo=row_no, colsNo=col_num))
print(getCellOfValue(target_sheet, rowNo=row_no, colsNo=col_num))
if getCellOfValue(origin_sheet, rowNo=row_no, colsNo=col_num) != getCellOfValue(target_sheet, rowNo=row_no,
colsNo=col_num):
print(row_no,col_num,"asdfsdfsadf")
print("第%s行%s列单元格的数据比对结果不一致,源:%s" %(
row_no, col_num, getCellOfValue(origin_sheet, rowNo=row_no, colsNo=col_num)))
row[col_num - 1].fill = red_fill
print("给源文件单元格%s打标" % row[col_num - 1])
else:
pass
col_num += 1
wb_origin.save(origin_file)
print("---------对比程序运行结束END------------")
#** ** ** ** ** ** ** **
#使用前请把主键放在A1列并排序,保持两份文件顺序完全一致。
#该程序的问题在于:两边主键必须完全顺序一致,
#如A文件为
#1,2,3,4,5
#B文件为
#2,3,4,5,6
#那么对比就会出现问题
#如果两边不一致,则在源文件把不一致的单元格填充背景色为红色
#** **/
比较不同的excel,或者同一个excel的两个不同sheet页,并将不同之处标红,python代码实现
最新推荐文章于 2024-09-03 17:37:40 发布