python自动化系列：自动将工作簿下的所有工作表合并到新工作表

作品名称：自动将多个工作簿中的同名工作表合并到新工作表

开发环境：PyCharm 2023.3.4 + python3.7

用到的库：os、xlwings

作品简介：用于合并一个目录下所有Excel文件中指定工作表的数据到一个新的Excel文件中。

注意：运行之前，最好先备份文件，避免偶发性的异常导致文件损坏。

 

实现过程

一、代码设计

以下是代码的详细说明：

1. 导入模块：

   • os：用于操作文件和目录。

   • xlwings：用于与Excel交互的库。

2. 定义summary函数：

   • 参数direct：包含要处理Excel文件的目录路径。

   • 参数name：要合并的工作表名称。

   • 参数dst：保存合并后数据的新Excel文件的完整路径。

3. 获取目录中的文件列表：

   • 使用os.listdir(direct)获取direct目录下的所有文件和文件夹列表。

4. 创建Excel应用实例：

   • 使用xw.App(visible=False, add_book=False)创建一个不可见的Excel应用实例，且不自动添加工作簿。

5. 初始化变量：

   • header：用于存储标题行数据。

   • all_data：用于存储所有工作表的数据。

6. 遍历目录中的每个文件：

   • 对于目录中的每个文件，检查是否为Excel临时文件（以~$开头），若是则跳过。

   • 拼接文件的完整路径，并打开工作簿。

7. 合并工作表数据：

   • 遍历工作簿中的每个工作表。

   • 如果工作表名称与name参数匹配，则读取数据。

   • 首先读取标题行（如果header为空），然后读取从第二行开始的数据。

   • 将读取的数据追加到all_data列表中。

8. 创建新的工作簿和工作表：

   • 使用xw.Book()创建一个新的工作簿。

   • 添加一个新的工作表，并命名为new_worksheet。

9. 写入数据：

   • 将标题行数据写入新工作表的A1单元格。

   • 将所有数据写入从A2开始的单元格。

10. 自动调整列宽：

    • 调用new_worksheet.autofit()自动调整列宽。

11. 保存新工作簿：

    • 使用new_workbook.save(dst)将新工作簿保存到指定路径。

12. 关闭工作簿和退出Excel应用：

    • 关闭新工作簿。

    • 退出Excel应用实例。

13. 设置变量：

    • direct_path：设置为'./销售统计'，这是包含要处理的Excel文件的目录路径。

    • new_name：设置为'产品销售统计'，这是要合并的工作表的名称。

    • dst_path：设置为'./销售统计/上半年产品销售统计表.xlsx'，这是保存合并后数据的新Excel文件的路径。

14. 调用函数：

    • 调用summary(direct_path, new_name, dst_path)执行合并操作。

"""
自动将多个工作簿中的同名工作表合并到新工作表
"""
import os
import xlwings as xw


def summary(direct, name, dst):
    """
    将工作簿的所有工作表合并到一个工作表
    :param direct: 要处理的Excel文件的目录
    :param name: 要处理的工作表名
    :param dst: 保存的文件名
    """
    file_list = os.listdir(direct)
    app = xw.App(visible=False, add_book=False)
    header = ''
    all_data = []
    for i in file_list:
        # 过滤临时文件
        if i.startswith('~$'):
            continue
        file_path = os.path.join(direct, i)
        workbook = app.books.open(file_path)
        for j in workbook.sheets:
            if j.name == name:
                if not header:
                    header = j['A1:I1'].value
                values = j['A2'].expand('table').value
                all_data = all_data + values
    # 把数据保存到新文件下
    new_workbook = xw.Book()
    new_worksheet = new_workbook.sheets.add(name)
    new_worksheet['A1'].value = header
    new_worksheet['A2'].value = all_data
    new_worksheet.autofit()
    new_workbook.save(dst)
    new_workbook.close()
    # 退出app
    app.quit()


direct_path = './销售统计'
new_name = '产品销售统计'
dst_path = './销售统计/上半年产品销售统计表.xlsx'
summary(direct_path, new_name, dst_path)