Excel obtains the difference data of two columns of data

background

       In daily work, we will inevitably encounter problems to deal with excel Data problems , such as find excel In two columns of data A Column exists B Data that does not exist in the column ,B Column exists A Data that does not exist in the column . Use when the amount of data is small excel It's convenient to bring your own tools , But when the amount of data is large , It's not that easy to deal with , You can use python operation , There is no need to build a complex environment , Just import python operation excel Just two libraries .

pip install xlrd --user
pip install xlwt --user

import xlrd
import xlwt


def handle(list1, list2):
    a = list(set(list1).difference(list2))
    return a


class ExcelHandle:
    def __init__(self, file_path, sheet_index):
        #  Get excel file 
        self.rd_book = xlrd.open_workbook(file_path)
        #  Get excel One of the sheet
        self.sheet = self.rd_book.sheet_by_index(sheet_index)
        #  Read line header 
        self.rowHead = self.sheet.row_values(0)
        #  Get all the lines 
        self.totalRows = self.sheet.nrows

    def get_cols_list(self):
        #  Define an array , Store the column data to be processed 
        col_data = []
        for j in range(self.totalRows - 1):
            #  Traverse to get the data of each row , What we get is an array 
            col = self.sheet.row_values(j + 1)
            col_data.append(col[0])
        return col_data


if __name__ == '__main__':
    filePath = r'C:Users\fusheng-fate\Desktop\read_excel.xls'
    self1 = ExcelHandle(filePath, 0)
    sheet1_list = ExcelHandle.get_cols_list(self1)
    self2 = ExcelHandle(filePath, 1)
    sheet2_list = ExcelHandle.get_cols_list(self2)
    a_b = handle(sheet1_list, sheet2_list)
    b_a = handle(sheet2_list, sheet1_list)
    #  Write what you get into the file 
    work_book = xlwt.Workbook()
    #  newly added sheet
    worksheet = work_book.add_sheet(" differences ")
    #  Header 
    worksheet.write(0, 0, "1 There is 2 non-existent ")
    worksheet.write(0, 1, "2 There is 1 non-existent ")
    for i in range(len(a_b)):
        worksheet.write(i+1, 0, a_b[i])
    for i in range(len(b_a)):
        worksheet.write(i+1, 1, b_a[i])
    work_book.save(r'C:Users\fusheng-fate\Desktop\handle.xls')

Conclusion

What's not perfect is , Use xlwt This library , Add a new sheet, To this sheet After inserting data, the original... Will be overwritten sheet, All the processed data is written into a new file , The original intention is to put the processed data into the original excel Medium .