python学习—合并多个Excel工作簿表格文件

系列文章目录

python学习—合并TXT文本文件
python学习—统计嵌套文件夹内的文件数量并建立索引表格
python学习—查找指定目录下的指定类型文件
python学习—年会不能停，游戏抽签抽奖
python学习—循环语句-控制流

---

---

功能说明

同事有个需求：手里有很多人编辑的Excel工作簿，差不多有20多个，现在想把这些分开的工作簿合并为一整个工作簿，方便数据查询和使用。

我看了一下，这边表格的字段都是相同的，不同的是每行的数据有多有少，笨方法当然可以新建一个空表格，然后再依次打开每个工作簿，将内容复制粘贴到新建表格中，实现合并的效果。

既然是重复性工作，当然是使用python编程了。

本代码目标为：

• 1 将文件夹内的所有表格（包括.xls格式 和 .xlsx格式）合并为一个表格；
• 2 每个工作簿中可能有多个分sheet表；
• 3 合并表格的第一列填写每个工作簿文件名称，第二列填写工作簿内的分表sheet名称。

1 准备工作

首先在D盘根目录下建立文件夹“测试”，在该文件夹内存放多个Excel表格文件，内容随意。
我新建了2个工作簿，”测试表1.xlsx“ 和 “样例表2.xlsx”，工作簿中都有分表，内容如下图：

数据分析需要用到强大的 Pandas 库，创建Excel表格需要用到 openpyxl库，查看自己的电脑是否安装了这2个库，可以在python终端中输入：

pip list

运行后会列出你的python环境中安装的所有库文件。
如果没有安装上述两个库，可以在python终端中使用如下代码进行安装：

pip install pandas openpyxl

我使用的python版本为3.9.0，Pandas版本 1.5.2 ，openpyxl版本 3.1.2.

（知识点） 关于 Pandas 库

——Pandas： Pandas 是一个开源的 Python 数据分析和处理库，提供了大量功能使数据分析工作更加高效便捷。以下是对 Pandas 主要特点和功能的概述：

1. 数据结构：Pandas 两大核心数据结构是 Series（一维数组，类似于带标签的数组）和 DataFrame（二维表格型数据结构，每列可以是不同类型的值）。这两种数据结构非常适合于处理和分析表格化的数据。
2. 数据读写：Pandas 支持多种文件格式的数据读写操作，如 CSV、Excel、SQL 数据库、JSON、HDF5 等，使得数据导入导出变得简单快捷。
3. 数据清洗：提供强大功能用于数据清洗，包括缺失值处理、数据类型转换、数据重塑、行列选择、过滤、排序等，有助于准备数据进行进一步分析。
4. 数据操作：支持类似 SQL 的数据操作方法，如合并（merge）、连接（join）、分组（groupby）、聚合（aggregate）、透视表（pivot table）等，便于对数据进行复杂操作。
5. 时间序列分析：Pandas 对时间序列数据有很好的支持，可以方便地进行重采样、移位、日期时间格式转换等操作，是金融、经济等领域数据分析的理想工具。
6. 统计分析：内置了丰富的统计功能，如计算描述性统计量（均值、中位数、标准差等）、相关性分析、协方差、线性回归等，帮助用户快速理解数据。
7. 可视化：虽然 Pandas 本身不直接提供复杂的可视化功能，但它与 Matplotlib、Seaborn 等图形库集成紧密，可以轻松地对数据进行可视化展示。

总的来说，Pandas 是进行数据预处理、数据分析和探索性数据分析的 强大工具，广泛应用于数据科学、金融、统计学、社会科学等多个领域。

2 第一版代码

第一版本的代码，主要实现一个合并的操作，最简单的情况：工作簿中只有1个表格。

(1) 指定目录

directory = 'D:/测试'

(2) 获取目录下所有.xlsx文件的列表

Excel表格有两种后缀名， .xls 和 .xlsx，使用后缀名判定。

excel_files = [file for file in os.listdir(directory) if file.endswith('.xlsx') or file.endswith('.xls')]

(3) 初始化一个空的DataFrame用于存储所有数据

all_data = pd.DataFrame()

(4) 遍历所有Excel文件并逐个读取，然后追加到all_data中

使用pandas读取Excel文件，假设每份Excel只包含一个工作表，并且想要合并所有工作表的数据。

for file in excel_files:
    file_path = os.path.join(directory, file)   
    data = pd.read_excel(file_path)
    all_data = pd.concat([all_data, data], ignore_index=True)

知识点： pandas.concat() 方法
该函数使用pd.concat()方法将file_data数据框合并到merged_data数据框中。
ignore_index=True 参数表示合并后重新索引，保持索引的连续性。
ignore_index=False 表示保留原始的索引。

(5) 将合并后的数据写入指定文件中

//指定合并后的工作簿保存路径和名称
output_file = 'D:/测试/all_hebing.xlsx'

//将合并后的数据写入新的Excel工作簿
all_data.to_excel(output_file, index=False)

(6) 第一版完整代码

import os
import pandas as pd


directory = 'D:/测试'
excel_files = [file for file in os.listdir(directory) if file.endswith('.xlsx') or file.endswith('.xls')]
all_data = pd.DataFrame()

# 遍历所有Excel文件并逐个读取，然后追加到all_data中
for file in excel_files:
    file_path = os.path.join(directory, file)
    data = pd.read_excel(file_path)
    all_data = pd.concat([all_data, data], ignore_index=True)

output_file = 'D:/测试/all_hebing.xlsx'
all_data.to_excel(output_file, index=False)

print(f'合并完成，结果已保存至：{output_file}')

结果如下图：

可以看到，代码实现了基本的Excel工作表合并功能，sheet4是 "样例表2.xlsx"中的第一个表格，sheet1是”测试表1.xlsx“ 中的第一个表格。

3 第二版代码

在第一版代码的基础上，实现含有多个表格的工作簿合并，合并表格的第一列填写工作簿名称，第二列填写内部sheet名称。

在代码中添加一些数据读写的判定功能，将步骤拆分包装为函数提高运行效率。重点环节代码如下：

(1) 遍历工作簿中的sheet表

遍历工作簿，首先需要获取这个工作簿的名称，然后获取内部的sheet表名称，最后按照两级名称读取表格内容。

// 存储表格内容的空列表
sheets_data = []

// 获取工作簿的名称
xls = pd.ExcelFile(file_path)

// 获取 工作簿 内的 sheet表的名称，并读取表格内容。
for sheet_name in xls.sheet_names:
	sheet_data = xls.parse(sheet_name)

这段代码的作用是遍历 Excel文件中的所有工作表，并解析每个工作表的数据。
xls.sheet_names 返回一个包含所有工作表名称的列表。
xls.parse(sheet_name) 根据给定的工作表名称，解析该工作表的数据并返回。

(2) 填写工作簿 和 内部 sheet表名称

excel表格属于二维表格型数据结构，定义列的位置、名称、内容。

	sheet_data.insert(0, '文件名称', file_path.name)
	sheet_data.insert(1, '内部表名称', sheet_name)
	
	// 将 insert 的内容，append 添加入 保存表格内容的 列表中。
	sheets_data.append(sheet_data)

该函数用于在名为sheet_data的表格的第0列，列名为’文件名称’，并将其赋值为file_path.name，即文件路径中的工作簿文件名部分；
表格的第1列，列名为’内部表名称’，并将其赋值为sheet_name，即工作簿文件名部分；

(3) 使用绝对路径，增强代码兼容性

在代码的路径设置中，使用 .resolve() 方法将该路径解析为一个绝对路径.

file_path = Path(file_path).resolve()

具体来说，它首先使用Path(file_path)创建一个Path对象，然后使用 .resolve() 方法将该路径解析为一个绝对路径。如果该路径是一个符号链接，则会解析为符号链接所指向的目标路径。如果路径不存在，则会抛出 FileNotFoundError 异常。

该函数的作用是确保后续操作使用的路径是绝对路径，避免了相对路径带来的问题，如路径解析错误、文件访问错误等。

(4) 完整代码

完整代码如下：

import os
import pandas as pd
from pathlib import Path


# 读取Excel文件的所有工作表，并为每个工作表的数据添加文件名及工作表名称作为前两列
def read_excel_sheets(file_path):
    try:
        # 使用pathlib的绝对路径确保兼容性
        file_path = Path(file_path).resolve()
        xls = pd.ExcelFile(file_path)
        sheets_data = []
        for sheet_name in xls.sheet_names:
            sheet_data = xls.parse(sheet_name)
            # 添加文件名和工作表名
            sheet_data.insert(0, '文件名称', file_path.name)
            sheet_data.insert(1, '内部表名称', sheet_name)
            sheets_data.append(sheet_data)
        return pd.concat(sheets_data, ignore_index=True)
    except FileNotFoundError:
        print(f"文件 {file_path} 不存在。")
        return pd.DataFrame()
    except PermissionError:
        print(f"没有权限读取文件 {file_path}。")
        return pd.DataFrame()
    except Exception as e:
        print(f"读取文件 {file_path} 时发生未知错误: {e}")
        return pd.DataFrame()


# 合并Excel文件，将每个文件的所有工作表合并为一个DataFrame，并保存到输出文件中。
def merge_excel_files_with_filenames(directory, output_file):
    directory_path = Path(directory).resolve()

    if not directory_path.exists() or not os.access(directory_path, os.R_OK):
        print(f"无法访问目录 {directory_path}，请检查权限和路径。")
        return

    excel_files = list(directory_path.glob('*.xls*'))

    if not excel_files:
        print(f"在目录 {directory_path} 中未找到任何 .xls 或 .xlsx 文件。")
        return

    merged_data = pd.DataFrame()

    for file_path in excel_files:
        file_data = read_excel_sheets(file_path)
        merged_data = pd.concat([merged_data, file_data], ignore_index=True)

    output_dir = Path(output_file).parent.resolve()
    if not output_dir.exists() or not os.access(output_dir, os.W_OK):
        print(f"无法写入输出文件 {output_file} 的目录，请检查权限和路径。")
        return

    merged_data.to_excel(output_file, index=False)
    print(f"合并完成，结果已保存至：{output_file}。")


# 调用合并函数,使用pathlib.path对象以增强代码的清晰度和跨平台能力
if __name__ == '__main__':
    path = Path(r'D:\测试').resolve()
    over_name = 'all_合并总表.xlsx'

    over_path = Path(os.path.join(path, over_name)).resolve()
    merge_excel_files_with_filenames(path, over_path)

再次看一下合并后的Excel工作簿，如下图：

4 后记

通过以上代码，可以实现多个Excel工作簿的合并工作，支持中文 。

案例中每个工作簿的结构和字段名称都是一致的，当遇到字段结构不一致的时刻，代码同样可以实现合并功能。
区别在于：
相同字段的内容会存储在相同字段这一列下，而不同的字段名在合并时会增加列数，用于存放不同字段下面的内容，即合并的结果是保留了每个工作簿中的字段名称，保证所有数据的完整性。

合并后的工作表，不存在 “合并单元格”，即源工作簿中的 那些 合并单元格，将被拆分。