本脚本适用于将多个Excel文件的多个sheet中的数据合并到一个sheet中。
一、读取指定目录下的所有文件
import pandas as pd
import os
target_path = r"D:\工作文档\8月"
file_list = os.listdir(target_path)
resultExcel = []
df = pd.DataFrame()
二、读取指定文件，输出文件内容
for file_name in file_list:
file_path = os.path.join('%s%s%s', (target_path, "\\", file_name))
print(file_path)
excelNames = pd.ExcelFile(file_path)
sheetNames = excelNames.sheet_names
print(sheet_names)
print("开始循环处理表格")
for sheetname in sheetNames:
print('开始处理的表格 ' + sheetname)
df = excelNames.parse(sheetname)
resultExcel.append(df)
三、将读取的所有内容写入到一个Excel文件
df = pd.concat(resultExcel)
excelName = r"D:\工作文档\合并文件.xlsx"
writer = ExcelWriter(excelName)
df.to_excel(excel_writer=writer, sheet_name="sheet_combine", encoding="utf-8", index=False)
writer.save()
writer.close()
四、完整脚本
import pandas as pd
import os
target_path = r"D:\工作文档\8月"
file_list = os.listdir(target_path)
resultExcel = []
df = pd.DataFrame()
for file_name in file_list:
file_path = os.path.join('%s%s%s', (target_path, "\\", file_name))
print(file_path)
excelNames = pd.ExcelFile(file_path)
sheetNames = excelNames.sheet_names # 查看所有sheet页面
print(sheet_names)
print("开始循环处理表格")
for sheetname in sheetNames:
print('开始处理的表格 ' + sheetname)
df = excelNames.parse(sheetname)
resultExcel.append(df) # 将读取的所有内容写入dataframe
df = pd.concat(resultExcel)
excelName = r"D:\工作文档\合并文件.xlsx"
writer = ExcelWriter(excelName)
df.to_excel(excel_writer=writer, sheet_name="sheet_combine", encoding="utf-8", index=False)
writer.save()
writer.close()
参考文章
Pandas DataFrame.to_excel()用法详解
pandas中的ExcelWriter和ExcelFile的实现方法
