【数据处理】pandas DataFrame 对多个sheet表格的excel 读取、处理、保存

今天一个不太懂编程的朋友让我帮他处理一些excel的表格,要求大概是:有20多个excel表,每个表里有42个sheet表格(sheet数量和名字相同)。最终需要根据对应的sheet名称将所有表格拼接成一个。

为了方式朋友数据泄露,我模拟了一下文件内容,大概如下。不同的excel文件,每个文件中相同的sheet名称。
【数据处理】pandas DataFrame 对多个sheet表格的excel 读取、处理、保存
【数据处理】pandas DataFrame 对多个sheet表格的excel 读取、处理、保存
他用VBA一时间没有写出来,请我看看能不能用python实现。

我大概搜索了一些博客,大多数写的非常复杂,但其实使用pandas.DataFrame配合上openpyxl框架很容易实现。
感觉还挺实用的,就顺手写下来,分享一下实现过程希望能帮助有需要的朋友。

生成文件列表和sheet名称列表

import pandas as pd
import os

# 将excel中的sheet名称放入列表
sheet_names = ["name1","name2","name3","name4", ... ]
# 将excel文件名称放入列表
xlsx_names = [x for x in os.listdir() if x.endswith(".xlsx")]

循环读取并保存

此处的包括两层循环,外层是sheet循环,内层是每个表格循环。外层每次循环完成后,其实就把一个sheet合并完成了。
每次完成一个sheet合并后就进行保存,全部保存进同一个excel中,但是使用不同的sheet名称保存。
但是发现,DataFrame进行保存时为了避免被不断地覆盖,这里使用了openpyxl。参考博客:将多个dataframe 以多个sheet的形式保存到 一个excel文件中

首先是进行设置excel框架。

writer = pd.ExcelWriter('result.xlsx',engine='openpyxl')

然后循环合并并且保存。

num = 1
for sheet_name in sheet_names:
    df = None
    for xlsx_name in xlsx_names:
        _df = pd.read_excel(xlsx_name,sheet_name=sheet_name)
        if df is None:
            df = _df
        else:
            df = pd.concat([df, _df], ignore_index=True)
    # 下面的保存文件处填写writer,结果会不断地新增sheet,避免循环时被覆盖
    df.to_excel(excel_writer=writer, sheet_name=sheet_name, encoding="utf-8", index=False)
    print(sheet_name + "  保存成功!共%d个,第%d个。" % (len(sheet_names),num))
    num += 1
writer.save()
writer.close()

以上就是全部合并的脚本,使用python很容易实现。