使用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)