Python：对Excel数据进行对比处理

 使用Python工具Pandas对Excel数据进行对比处理。

import openpyxl
import pandas as pd

dev_add_file = '.xls'
dev_add_sheet = ''
dev_add_new_file = 'xls'
dev_add_cmp_sheet = ''

dev_rm_file = '.xls'
dev_rm_sheet = ''
dev_rm_new_file = '.xls'

excel_add = pd.read_excel(dev_add_file, sheet_name=dev_add_sheet, engine="xlrd")
# ascending 默认等于True，按从小到大排列，改为False 按从大到小排。
excel_add.sort_values(by=['部品號碼','SIDE'], inplace=True, ascending=True)
#excel_add.to_excel(dev_add_new_file, dev_add_sheet, index=False)

excel_rm = pd.read_excel(dev_rm_file, sheet_name=dev_rm_sheet, engine="xlrd")
# ascending 默认等于True，按从小到大排列，改为False 按从大到小排。
excel_rm.sort_values(by=['部品號碼','SIDE'], inplace=True, ascending=True)
#excel_rm.to_excel(dev_rm_new_file, dev_rm_sheet, index=False)

#excel_add_cmp = pd.read_excel(dev_add_file, sheet_name=dev_add_cmp_sheet, engine="xlrd")

countor = 2

for i in range(2, excel_add.iloc[:,0].size) :
    number_bigger = 0 # 增加表 部门号码 大于减少表
    number_equal = 0 # 增加表 部门号码 等于减少表
    side_bigger = 0 # 增加表 SIDE 大于减少表
    side_equal = 0  # 增加表 SIDE 等于减少表

    for j in range(countor, excel_rm.iloc[:,0].size) :
        if (excel_add['部品號碼'][i] > excel_rm['部品號碼'][j]) :
            number_bigger = 1
            number_equal = 0
            continue
        elif (excel_add['部品號碼'][i] < excel_rm['部品號碼'][j]) :
            number_bigger = 0
            number_equal = 0
            countor = j + 1
            break
        else :
            number_equal = 1
            if (excel_add['SIDE'][i] > excel_rm['SIDE'][j]) :
                side_bigger = 1
                side_equal = 0
                continue
            elif (excel_add['SIDE'][i] < excel_rm['SIDE'][j]) :
                side_bigger = 0
                side_equal = 0
                countor = j + 1
                break
            else :
                side_equal = 1
                data['部品號碼'].append(excel_rm['部品號碼'][j])
                data['SIDE'].append(excel_rm['SIDE'][j])
                countor = j + 1
                break

df_dict = pd.DataFrame.from_dict(data)
df_dict.to_excel(dev_add_new_file, dev_add_cmp_sheet, index=False)

import openpyxl
import pandas as pd

dev_add_file = '设变增加1.xls'
dev_add_sheet = '设变增加'
dev_add_new_file = '设变增加_New.xls'

dev_cmp_file = '设变对比表.xls'
dev_cmp_sheet = '设变对比'

dev_rm_file = '设变减少1.xls'
dev_rm_sheet = '设变减少'
dev_rm_new_file = '设变减少表_New.xls'

excel_add = pd.read_excel(dev_add_file, sheet_name=dev_add_sheet, engine="xlrd")
# ascending 默认等于True，按从小到大排列，改为False 按从大到小排。
excel_add.sort_values(by=['部品號碼','SIDE'], inplace=True, ascending=True)
excel_add.to_excel(dev_add_new_file, dev_add_sheet, index=False)
excel_add = pd.read_excel(dev_add_new_file, sheet_name=dev_add_sheet, engine="xlrd")

excel_rm = pd.read_excel(dev_rm_file, sheet_name=dev_rm_sheet, engine="xlrd")
# ascending 默认等于True，按从小到大排列，改为False 按从大到小排。
excel_rm.sort_values(by=['部品號碼','SIDE'], inplace=True, ascending=True)
excel_rm.to_excel(dev_rm_new_file, dev_rm_sheet, index=False)
excel_rm = pd.read_excel(dev_rm_new_file, sheet_name=dev_rm_sheet, engine="xlrd")

data = {'部品號碼': [], 'SIDE': []}
countor = 0

for i in range(0, excel_add.iloc[:,0].size - 1) :
    for j in range(countor, excel_rm.iloc[:,0].size - 1) :
        if (excel_add['部品號碼'][i] > excel_rm['部品號碼'][j]) :
            continue
        elif (excel_add['部品號碼'][i] < excel_rm['部品號碼'][j]) :
            break
        else :
            if (excel_add['SIDE'][i] > excel_rm['SIDE'][j]) :
                continue
            elif (excel_add['SIDE'][i] < excel_rm['SIDE'][j]) :
                break
            else :
                data['部品號碼'].append(excel_rm['部品號碼'][j])
                data['SIDE'].append(excel_rm['SIDE'][j])
                break

df_dict = pd.DataFrame.from_dict(data)
df_dict.to_excel(dev_cmp_file, dev_cmp_sheet, index=False)