用pandas合并同一文件夹下的xlsx文件

import os
import pandas as pd
import openpyxl
def get_files_path(rootdir):
“”"
获取文件路径
:param rootdir: 目录地址
:return:
“”"
name_list=os.listdir(rootdir)
path = [os.path.join(rootdir, item) for item in name_list]
return path
rootdir = r"D:\BS\test2"
path_list =get_files_path(rootdir)
print(get_files_path(rootdir))#测试数据
df=[]
cnt=0
for path in path_list:
write=pd.ExcelFile(path)
for i in range(0,len(write.sheet_names)):
cnt +=1
data=pd.read_excel(path,sheet_name=i)
df.append(data)
total_df=pd.concat(df)
total_df.to_excel(r"D:\BS\test2\all_EXCEL.xlsx",index=False)
print(“共合并”,cnt)