python实现比较2个excel

# @Time    :  '2021-1-11 20:35'
# @Author  :  'pc.kang'

"""
使用前请把主键放在A1列并排序，保持两份文件顺序完全一致
代码遍历源文件的每一行，然后遍历每行的所有单元格去和目标文件相同位置的值作比较，
如果两边不一致，则在源文件把不一致的单元格填充背景色为红色
"""
import openpyxl
from openpyxl.styles import PatternFill


# 获取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 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"C:\Users\54718\Desktop\origin.xlsx"
target_file = r"C:\Users\54718\Desktop\target.xlsx"
# 把源文件和目标文件加载到内存对象
wb_origin = openpyxl.load_workbook(origin_file)
wb_target = openpyxl.load_workbook(target_file)

# 通过sheet名拿到sheet对象
origin_sheet = wb_origin.get_sheet_by_name("Sheet1")
target_sheet = wb_target.get_sheet_by_name("Sheet1")
# 获取最大行号
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 = origin_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("----------比对程序运行中，开始循环遍历----------")
for row_no in range(2, origin_sheet_max_row + 1):
	# row = wb_origin.getRow(origin_sheet, row_no) # row_no行号
	row = getRow(origin_sheet, row_no)
	row_length = len(row)
	num = 1 # 列号，从第二列开始比对
	red_fill = PatternFill("solid", fgColor="FF0000")
	while num < row_length:
		if getCellOfValue(origin_sheet, rowNo=row_no, colsNo=num) != getCellOfValue(target_sheet, rowNo=row_no, colsNo=num):
			print("第%s行%s列单元格的数据比对结果不一致，源文件中单元格的值是：%s"%(row_no, num, getCellOfValue(origin_sheet, rowNo=row_no, colsNo=num)))
			row[num-1].fill = red_fill
			print("给源文件单元格%s打标"%row[num-1])
		else:
			pass
		num += 1

wb_origin.save(origin_file)
print("----------比对程序运行 END----------")