debug的时候,path正确,Sheet名正确,还是覆盖,一直成功不了
已解决
原因是ExcelWriter的位置问题,ExcelWriter要放在循环外面,
之前是:读一次,打开,写一次,保存;
修改后:打开,读一次写一次,读一次写一次,保存;
这样就可以正常写入了
import pandas as pd
import numpy as np
import re
import os
import warnings
import time
warnings.filterwarnings('ignore')
path=r'C:\Users\feng\Desktop\分组数据\F'
today_date = time.strftime("%Y%m%d", time.localtime(time.time()))
L={}
for root, dirs, files in os.walk(path):
for file in files:
if os.path.splitext(file)[1] == '.xlsx':
if file.split("_")[0] in L:
A=L[file.split("_")[0]]
A.append(os.path.join(root, file))
L[file.split("_")[0]]=A
else:
L[file.split("_")[0]]=[os.path.join(root, file)]
# print(L)
前面这段是字典嵌套列表,用字典的键对应人名,值对应文件名,把值用列表表示
后面循环列表写进同一个Excel表里
修改之后成功:
for key, value in L.items():
i=L[key][0]
print(key)
depart=i.split("\\")[-2]
path1=r'C:\Users\feng\Desktop\分组数据\F'+'\\'+'Sheet汇总'+'\\'
if not os.path.exists(path1):
os.mkdir(path1)
path2=path1+depart+'-广告'+today_date+"\\"
if not os.path.exists(path2):
os.mkdir(path2)
path3=path2+key+'-广告'+today_date+'.xlsx'
with pd.ExcelWriter(path3) as writer:
for a in L[key]:
name=a.split("\\")[-1].split('_')[1]
print(name)
df=pd.read_excel(a)
df.to_excel(excel_writer=writer,sheet_name=name,index=None)
writer.save()
原先的错误代码:
for key, value in L.items():
for i in list(value.split(',')):
print(i)
# print(df)
depart=i.split("\\")[-2]
path1=os.path.dirname(i)+"\\"+depart+"\\"
path2=os.path.dirname(i)+"\\"+depart+"\\"+key+'.xlsx'
if not os.path.exists(path1):
os.mkdir(path1)
# print(path2)
name=i.split("\\")[-1].split('_')[1]
# print(name)
df=pd.read_excel(i)
# writer=pd.ExcelWriter(path2)
with pd.ExcelWriter(path2) as writer:
df.to_excel(excel_writer=writer,sheet_name=name,index=None)
# df.to_excel(writer,name,index=False)
writer.save()