How Python quickly finds out how the differences in data in two spreadsheets are presented

Here's a small piece to bring you a python how to quickly find out the differences in the data of two electronic tables. Small series feel very good, now share to everyone, also for everyone to make a reference. Let's take a look at it with a little knitting.

Recently, just contact Python, find a little task to practice practiced hand, I hope that in practice, constantly exercise their ability to solve problems.

The company will have such a scenario: there is a spreadsheet content by two or three departments or more departments to use, these employees will maintain these forms of irregular with the new Department of their own data, long time, everyone's data began to fight, very detrimental to management. How can you quickly find the difference between data in two or more spreadsheets?

Workaround:

1. Excel comes with the method (interested in self-Baidu)

2. Python writes a small script

#!/usr/bin/env python#-*-coding:utf-8-*-#导入模块 OPENPYXL import openpyxlfrom openpyxl.styles import Patternfillfrom Open Pyxl.styles Import colorsfrom openpyxl.styles import Font, color# read the Excel file # parentheses in the string for the two Excel path you want to compare, note with "/" wb_a = Openpyxl.load_workbook (' D:/bakfile/d046532/desktop/check excel/test.xlsx ') Wb_b = Openpyxl.load_workbook (' d:/ Bakfile/d046532/desktop/check excel/test2.xlsx ') #定义一个方法来获取表格中某一列的内容, returns a list # here, in my table: IP is unique, so I use it to differentiate between data, and IP this column in my table is the first "G" Column def getip (WB): sheet = wb.get_active_sheet () IP = [] for cellobj in sheet[' G ']: Ip.append (cellobj. Value) return ip# get IP list ip_a = GetIP (wb_a) ip_b = GetIP (wb_b) #将两个列表转换成集合aa = set (ip_a) BB = set (Ip_b) #找出两个列表的不同行 and convert to List diff erence = list (aa ^ bb) #打印出列表中的元素 # In this step, different data from two tables has been found for the for I in Difference:print (i) #将不同行高亮显示print ("Start first table" + "----" * 0) A = Wb_a.get_active_sheet () [' G ']for cellobj in A:If cellobj.value in Difference:print (cellobj.value) cellobj.f ont = Font (color=colors. BLACK, italic=true, bold = True) CELlobj.fill = Patternfill ("Solid", fgcolor= "DDDDDD") print ("Start 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= "dddddd") wb_a.save (' D:/bakfile/d046532/de Sktop/a.xlsx ') wb_b.save (' d:/bakfile/d046532/desktop/b.xlsx ')

This will save two copies of Excel and label the different data differences in the two tables with the cell fill color and font color in the copy.

Not resolved:

1. How to add these different data to a table to form a complete list

2. How to optimize the Lite code