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----------")