代码收藏家技术教程 2024-09-30

Python对比Excel数据差异详解：内容比对与差异标注指南（收藏必备）

在处理excel文件时，往往需要双比表格哪些被修改过了，今天就给大家分享一段代码，代码中运用到的模块，请自行用pip命令安装

一、准备好两个excel表格（A表与B表）

二、执行代码

from openpyxl.styles import PatternFill
from openpyxl.styles import colors
from openpyxl.styles import Font
import openpyxl as pxl

# 加载data1.xlsx文件
workbook_1 = pxl.load_workbook(r'对比excel文档/excel文档A.xlsx')

# 加载data2.xlsx文件
workbook_2 = pxl.load_workbook(r'对比excel文档/excel文档B.xlsx')

# 选择Sheet1对象
workbook_1_sheet_1 = workbook_1['Sheet1']

# 选择Sheet2对象
workbook_2_sheet_1 = workbook_2['Sheet1']

# 进行行、列运算
max_row = workbook_1_sheet_1.max_row if workbook_1_sheet_1.max_row > workbook_2_sheet_1.max_row else workbook_2_sheet_1.max_row
max_column = workbook_1_sheet_1.max_column if workbook_1_sheet_1.max_column > workbook_2_sheet_1.max_column else workbook_2_sheet_1.max_column

for i in range(1, (max_row + 1)):
    for j in range(1, (max_column + 1)):
        cell_1 = workbook_1_sheet_1.cell(i, j)
        cell_2 = workbook_2_sheet_1.cell(i, j)
        if cell_1.value != cell_2.value:
            cell_1.fill = PatternFill("solid", fgColor='FFFF00')
            cell_1.font = Font(color=colors.BLACK, bold=True)
            cell_2.fill = PatternFill("solid", fgColor='FFFF00')
            cell_2.font = Font(color=colors.BLACK, bold=True)

# 标注完成的文件保存为data3.xlsx文件
workbook_1.save('对比excel文档/excel文档A_1.xlsx')

# 标注完成的文件保存为data4.xlsx文件
workbook_2.save('对比excel文档/excel文档B_1.xlsx')