Python -- quickly identify data differences between two workbooks, python workbooks

Python -- quickly identify data differences between two workbooks, python 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

　　

1 #! /Usr/bin/env python 2 #-*-coding: UTF-8-*-3 4 # import module openpyxl 5 import openpyxl 6 from openpyxl. styles import PatternFill 7 from openpyxl. styles import colors 8 from openpyxl. styles import Font, Color 9 10 # Read the excel file 11 # the strings in the brackets are the two excel paths you want to compare. Use "/" 12 wb_a = openpyxl. load_workbook ('d:/BAKFILE/d011232/Desktop/check excel/test.xlsx ') 13 wb_ B = openpyxl. load_workbook ('d:/BAKFILE/d011232/Desktop/check excel/test2.xlsx ') 14 # define a method to get the content of a column in the table and return a list 15 # 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 16 def getIP (wb): 17 sheet = wb. get_active_sheet () 18 ip = [] 19 for cellobj in sheet ['G']: 20 ip. append (cellobj. value) 21 22 return ip23 # obtain the ip list 24 ip_a = getIP (wb_a) 25 ip_ B = getIP (wb_ B) 26 # convert the two lists to a set 27 aa = set (ip_a) 28 bb = set (ip_ B) 29 # Find the different rows of the two lists and convert them to list 30 difference = list (aa ^ bb) 31 # print the element 32 in the list # at this step, different data in the two tables has been found 33 for I in difference: 34 print (I) 35 36 # highlight different rows 37 print ("start the first table" + "----" * 10) 38 a = wb_a.get_active_sheet () ['G'] 39 for cellobj in a: 40 if cellobj. value in difference: 41 print (cellobj. value) 42 cellobj. font = Font (color = colors. BLACK, italic = True, bold = True) 43 cellobj. fill = PatternFill ("solid", fgColor = "dddddddd") 44 print ("start second table" + "----" * 10) 45 B = wb_ B .get_active_sheet () ['G'] 46 for cellobj in B: 47 if cellobj. value in difference: 48 print (cellobj. value) 49 cellobj. font = Font (color = colors. BLACK, italic = True, bold = True) 50 cellobj. fill = PatternFill ("solid", fgColor = "dddddddd") 51 52 wb_a.save ('d:/BAKFILE/d01_32/Desktop/a.xlsx') 53 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