Python+Pandas批量合并大量excel文件

requirments.txt

et-xmlfile==1.1.0
numpy==1.24.3
openpyxl==3.1.2
pandas==2.0.1
python-dateutil==2.8.2
pytz==2023.3
six==1.16.0
tzdata==2023.3

main.py

import os
import pandas as pd

dir_path = os.path.dirname(os.path.abspath(__file__))
source_location = os.path.join(dir_path,"source")

df = []
target = []
i = 0 
for filename in os.listdir(source_location): 
    if filename.endswith('.xlsx'):
        df.append(filename)
    

for i in range (0,len(df)):
# for i in range (0,2):
    excel_file_location = os.path.join(source_location,df[i])
    print(excel_file_location)
    dfs = pd.read_excel(excel_file_location)
    dfs.insert(loc=18,column='file_name',value=df[i])
    target.append(dfs)
    i = i +1
df_concat = pd.concat(target)
df_concat.to_excel(dir_path+'/{}.xlsx'.format('合并结果'),index=None)


df_concat.to_csv('df_concat.csv',sep=',',header=True, encoding='utf_8_sig', index=False)
print(i)

excel文件放在source目录下