import os
import pandas as pd
import numpy as np
import glob
file_list=list(glob.glob(".\\*.xls*"))
file_list.remove(".\\要填充的表格.xlsx")
path_jieguo = ".\\vlookup智能匹配--结果.xlsx"
path_tianchong = ".\\要填充的表格.xlsx"
yaotian = pd.read_excel(path_tianchong,index_col=0)
data_list=[]
data = pd.DataFrame()
for file in file_list:
f = pd.ExcelFile(file)
f.sheet_names
for i in f.sheet_names:
d = pd.read_excel(file, sheet_name=i,index_col=0)
d.dropna(how='all',inplace=True)
d.fillna(0,inplace=True)
d.columns = [str(i)+'表_'+str(j) for j in d.columns[0:]]
data_list = data_list+[d]
data=pd.concat(data_list,join='outer',axis=1)
data.fillna(0,inplace=True)
data.drop_duplicates()
data2=data.sort_values(by="标志", ascending=True,na_position='last')
merge=pd.concat([yaotian,data2],join='inner',axis=1)
merge2=merge.sort_index(inplace=True)
with pd.ExcelWriter(path_jieguo) as writer:
merge.to_excel(writer,sheet_name = "要填充的表格")
data2.to_excel(writer,sheet_name = "碰撞后结果")