PYTHON 两个EXCEL文件数据对比

解决问题：对比文件A和文件B，查找文件A中文件B的数据，如果有，则高亮背景绿色显示。

文件A：source.xlsx

文件B: dest.xlsx

from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.styles import Alignment,Border,Side,PatternFill

def read_workbook(filename):
    wb = load_workbook(filename)
    #print(wb.sheetnames)
    return wb

def find_different(sourfile, sourcesheet,destfile, destsheet):
    #print("find_different \n")
    sourWB = read_workbook(sourfile)
    sourSheet = sourWB[sourcesheet]
    destWB = read_workbook(destfile)
    destSheet = destWB[destsheet]
    initfill = PatternFill('solid')
    flag = False
    fill = PatternFill('solid', fgColor="00FF00")
    border = \
        Border(left=Side(style='thin', color='00000000'),
               bottom=Side(style='thin', color='00000000'),
               right=Side(style='thin', color='00000000'),
               top=Side(style='thin', color='00000000'))
    initborder = \
        Border(left=Side(style='thin', color='00000000'),
               bottom=Side(style='thin', color='00000000'),
               right=Side(style='thin', color='00000000'),
               top=Side(style='thin', color='00000000'))

    differences = []
    #遍历两个工作表的行和单元格
    for row1 in sourSheet.iter_rows(min_row=3,max_row=None,min_col=1,max_col=1,values_only=False):
        for cell1 in row1:
            value1 = cell1.value
            if value1:
                for row2 in destSheet.iter_rows(min_row=11,max_row=None,min_col=4,max_col=4,values_only=False):
                    for cell2 in row2:
                        value2 = cell2.value
                        if value2 == value1:
                            flag = True
                            #print(f"find the same: {value1},{value2}")
                            cell1.border = border
                            cell1.fill = fill
                            break
                        else:
                            flag = False
                            #print(f"Can't find the same: {value1}")
                            #没有找到匹配的单元格，记录下来
                            #cell1.border = initborder
                            #cell1.fill = initfill
                            #differences.append((cell1.row,cell1.column, value1))
            if flag == False:
                #cell1.border = initborder
                #cell1.fill = initfill
                differences.append(cell1)


    print(differences)
    sourWB.save(sourfile)
    destWB.save(destfile)

    return

def read_sheet2():
    myworkbook = load_workbook("test.xlsx")
    sheet = wb["工作表1"]
    #rows = sheet.cell(row=1, column=3)
    for rows in sheet:
        for cell in rows:
            print(cell.value,end=" ")
    return

def create_mysheet(filename):
    myworkbook = load_workbook(filename)
    wb.save(filename)
    sheet = wb.active
    sheet2 = wb.create_sheet("工作表1")
    sheet3 = wb.create_sheet("工作表2")

    return wb

#创建一个工作表
wb = Workbook()
wb.save("test1.xlsx")
wb = create_mysheet("test1.xlsx")
#保存为本地excel文件
sheet = wb.active
sheet2 = wb['工作表1']
sheet3 = wb['工作表2']
sourceFile = f"MCU_CDCU_CommunicationMatrix.xlsx"
destFile = f"F57_CDCU_Testcase_下行测试用例Lev2.xlsx"
sourceSheet = f"IVIToMCU"
destSheet = f"TC"

find_different(sourceFile,sourceSheet, destFile, destSheet)

#在前3行，前6列输入数据
for i in range(1,3):
    for j in range(1,6):
        cell = sheet.cell(row= i, column= j, value= 111)
        cell.alignment = Alignment(horizontal="center", vertical="center")
        cell.border =\
            Border(left=Side(style='thin', color='00000000'),
            bottom=Side(style='thin', color='00000000'),
            right=Side(style='thin', color='00000000'),
            top=Side(style='thin', color='00000000'))
        cell.fill = PatternFill('solid', fgColor="00FF00")
        sheet2.cell(row= i, column= j, value= 222)
        sheet3.cell(row=i, column=j, value=333)

#for s in wb:
    #print(s.title)
current = wb["工作表1"]

#read_sheet2()
wb.save("工作表1")