Python读取两个excel文件，比对每个sheet中的值，将不相等的单元格标黄，并显示两边各自的值

最新推荐文章于 2024-10-07 01:36:35 发布

阿天ATean

最新推荐文章于 2024-10-07 01:36:35 发布

阅读量264

点赞数 1

分类专栏：资深工程师python笔记文章标签： python 开发语言

本文链接：https://blog.csdn.net/cnngavij/article/details/141126353

版权

资深工程师python笔记专栏收录该内容

3 篇文章 0 订阅

订阅专栏

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
from openpyxl.comments import Comment

# 读取两个Excel文件
file1 = pd.ExcelFile('./data/1_mid.xlsx')
file2 = pd.ExcelFile('./data/1_di.xlsx')

# 遍历每个sheet
for sheet_name in file1.sheet_names:
    df1 = file1.parse(sheet_name)
    df2 = file2.parse(sheet_name)
    # 获取行数和列数
    num_rows, num_cols = df1.shape

    # 创建一个副本以保存标记后的数据
    marked_df = df1.copy()

    # 遍历每个元素，比较两个DataFrame
    for i in range(num_rows):
        for j in range(num_cols):
            value1 = df1.iat[i, j]
            value2 = df2.iat[i, j]
            if value1 != value2:
                marked_df.iat[i, j] = value1  # 标记原始值为黄色

    # 保存标记后的数据到另一个Excel文件
    with pd.ExcelWriter('marked_file.xlsx') as writer:
        marked_df.to_excel(writer, index=False)

    # 打开Excel文件并将不一致的位置标记为黄色，并添加评论
    wb = load_workbook('marked_file.xlsx')
    ws = wb.active
    yellow_fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')

    for row in ws.iter_rows(min_row=2, max_row=num_rows + 1, min_col=1, max_col=num_cols):
        for cell in row:
            if cell.value != df2.iat[cell.row - 2, cell.column - 1]:
                cell.fill = yellow_fill
                comment = Comment(f'底稿值: {df2.iat[cell.row - 2, cell.column - 1]}', 'AI Assistant')
                cell.comment = comment

    # 保存修改后的Excel文件
    wb.save(f'marked_file{sheet_name}.xlsx')

执行效果：