How does python quickly identify data differences between two workbooks?

Source: Internet
Author: User

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.