python自动化系列：自动将多个工作簿中指定工作表的列数据拆分为多行

本文链接：https://blog.csdn.net/2401_86033173/article/details/141327969

作品介绍

作品名称：自动将多个工作簿中指定工作表的列数据拆分为多行

开发环境：PyCharm 2023.3.4 + python3.7

用到的库：os、xlwings、pandas

作品简介：该实例的功能是遍历指定目录下的所有Excel工作簿，并对每个工作簿中指定名称的工作表进行列数据拆分为多行的操作。

实现过程

如果你正在学习Python并且找不到方向和学习路线的话可以试试我这一份学习方法＋籽料呀！
点击领取（无偿获得）

一、代码设计

下面是对代码的详细说明：

导入必要的库：

os：用于操作系统功能，如文件和目录操作。
xlwings：用于与Excel交互，操作Excel文件。
pandas：用于数据处理和分析。

定义branch函数：

参数direct：要处理的Excel文件的目录。
参数name：要处理的工作表名。

使用os.listdir(direct)。

创建Excel应用实例：

使用xw.App创建一个不可见的Excel应用实例，参数visible=False表示不显示Excel界面，add_book=False表示不自动添加新工作簿。

遍历文件列表：

对于每个文件，检查是否为.xlsx格式的Excel文件，并跳过以~$开头的临时文件。

打开Excel工作簿和工作表：

使用app.books.open(file_path)打开工作簿，然后使用workbook.sheets[name]获取指定的工作表。

读取数据：

使用worksheet.range('A1').options方法读取工作表的数据，转换成pandas的DataFrame对象。

分列操作：

对DataFrame中的规格列使用str.split('*', expand=True)进行拆分，生成新的列。

删除原列并设置新列名：

删除原规格列，并将拆分后的数据设置为新的列名长、宽、高。

转置DataFrame：

使用.T将DataFrame转置，使列变成行。

设置新列名和索引：

使用转置后的DataFrame的第一行作为列名，第一列作为索引名。

删除包含列名或索引名称的首行：

使用drop方法删除转置后DataFrame的第一行。

清空工作表并写入新数据：

清空工作表的原有内容，然后将转置后的DataFrame写入工作表。

自动调整列宽：

使用worksheet.autofit()自动调整列宽以适应内容。

保存和关闭工作簿：

保存更改后的工作簿，并关闭它。

异常处理：

使用try-except结构捕获并处理在处理文件时可能发生的异常，打印错误信息。

退出Excel应用：

使用app.quit()退出Excel应用实例。

设置direct_path为要处理的目录路径，new_name为要处理的工作表名。

调用branch函数：

调用branch(direct_path, new_name)执行上述操作。


"""
自动将多个工作簿中指定工作表的列数据拆分为多行
"""
import os
import xlwings as xw
import pandas as pd


def branch(direct, name):
    """
    将多个工作簿中指定工作表的数据进行分列
    :param direct: 要处理的Excel文件的目录
    :param name: 要处理的工作表名
    """
    file_list = os.listdir(direct)  # 获取direct目录下的所有文件和文件夹列表
    app = xw.App(visible=False, add_book=False)  # 创建一个不可见的Excel应用实例
    for filename in file_list:
        # 过滤临时文件
        if filename.startswith('~$'):
            continue
        if filename.endswith('.xlsx'):
            file_path = os.path.join(direct, filename)
            try:
                workbook = app.books.open(file_path)
                worksheet = workbook.sheets[name]
                values = worksheet.range('A1').options(pd.DataFrame, header=1, index=False, expand='table').value
                new_values = values['规格'].str.split('*', expand=True)  # 进行分列操作
                values['长'] = new_values[0]
                values['宽'] = new_values[1]
                values['高'] = new_values[2]
                values.drop(columns=['规格'], inplace=True)  # 删除原规格列
                values = values.T  # 将DataFrame转置，使原来的列变成行
                values.columns = values.iloc[0]  # 设置新列名
                values.index.name = values.iloc[0].index.name  # 设置索引名称
                values.drop(values.iloc[0].index.name, inplace=True)  # 删除包含列名或索引名称的首行
                worksheet.clear()
                worksheet['A1'].value = values
                worksheet.autofit()  # 自动调整工作表的列宽以适应内容
                workbook.save()
                workbook.close()
            except Exception as e:
                print(f"处理文件时出错 {filename}: {e}")
    # 退出app
    app.quit()


direct_path = './记录表'
new_name = '规格表'
branch(direct_path, new_name)