How does python quickly identify data differences between two workbooks?

How does python quickly identify data differences between two workbooks?

Recently, I just got started with python and found some small tasks to train my hands. I hope I can continue to improve my problem solving skills in practice.

In the company, there is a scenario where the content of a workbook is used by two or three departments or more departments, these employees will maintain the data in these tables from time to time with new departments. After a long time, everyone's data began to fight, which is not conducive to management. How can we quickly find the data difference between two or more workbooks?

Solution:

1. Excel methods (Baidu if you are interested)

2. Write a small script in python

#! /Usr/bin/env python #-*-coding: UTF-8-*-# import module openpyxl import openpyxlfrom openpyxl. styles import PatternFillfrom openpyxl. styles import colorsfrom openpyxl. styles import Font, Color # Read the excel file # string in the brackets for the two excel files you want to compare, note that use "/" wb_a = openpyxl. load_workbook ('d:/BAKFILE/d011232/Desktop/check excel/test.xlsx ') wb_ B = openpyxl. load_workbook ('d:/BAKFILE/d011232/Desktop/check excel/test2.xlsx ') # define a method to retrieve the content of a column in the table and return a list # here, in my table: IP is unique, so I use it to differentiate data differences, the IP column in my table is the "G" column def getIP (wb): sheet = wb. get_active_sheet () ip = [] for cellobj in sheet ['G']: ip. append (cellobj. value) return ip # obtain the ip list ip_a = getIP (wb_a) ip_ B = getIP (wb_ B) # convert the two lists to a set aa = set (ip_a) bb = set (ip_ B) # identify different rows of the two lists and convert them to list difference = list (aa ^ bb) # print out the elements in the list # To this step, different data in the two tables has been found. for I in difference: print (I) # highlight different rows in print ("start the first table" + "----" * 10) a = wb_a.get_active_sheet () ['G'] for cellobj in a: if cellobj. value in difference: print (cellobj. value) cellobj. font = Font (color = colors. BLACK, italic = True, bold = True) cellobj. fill = PatternFill ("solid", fgColor = "dddddddd") print ("start the second table" + "----" * 10) B = wb_ B .get_active_sheet () ['G'] for cellobj in B: if cellobj. value in difference: print (cellobj. value) cellobj. font = Font (color = colors. BLACK, italic = True, bold = True) cellobj. fill = PatternFill ("solid", fgColor = "dddddddd") wb_a.save ('d:/BAKFILE/d01_32/Desktop/a.xlsx ') wb_ B .save ('d: /BAKFILE/d01_32/Desktop/B .xlsx ')

In this way, two copies of the excel file are saved, and the cell fill color and font color are used to mark different data differences between the two tables.

Unsolved:

1. How to add the different data to a table to form a complete table

2. How to optimize the simplified code

How to quickly find out the data difference between the two workbooks in the above python is all the content shared by the editor. I hope you can give us a reference and support for the customer's house.