Python办公自动化-EXCEL月报处理整合
将每个派发处理人填写的对接老师的工作任务明细汇总到不同对接老师的EXCEL里。
import pandas as pd
import datatime as dt
path1 = r'D:\jz'
path2 = r'D:\jz\汇总'
os.chdir(path1) #将当前目录切换到path1
if os.path.exists('汇总'):
pass
else:
os.mkdir('汇总')
new_df=pd.DataFrame()
file_lists=[file for file in os.listdir(path1] if file.rfind('.xlsx') != -1]
for file in file_lists:
wb1 = pd.read_excel(os.path.join(path1,file),header=0,encoding='UTF-8',dtype=["工作内容":str,"对接老师":str,"工作量":str],parse_dates=['开始时间','结束时间'])
wb1['派发处理人'] = file.replace('.xlsx','')
wb1['开始时间'] = wb1['开始时间'].dt.date
wb1['结束时间'] = wb1['结束时间'].dt.date
new_df = pd.concat([new_df,wb1],sort=True,axis=0)
customers = new_Df['对接老师'].unique()
for customer in customers:
new_df2 = new_df[new_df['对接老师']==customer]
new_df2 = new_df2[['对接老师','派发处理人','工作内容','开始时间','结束时间','工作量']]
new_df2.to_excel(os.path.join(path2,customer+'.xlsx'),index=False)