在数据处理和分析的过程中，经常需要将多个Excel文件中的数据进行合并。特别是当这些文件分散在多个子文件夹内，且每个文件又包含多个Sheet时，手动合并这些数据会是一项繁琐且易出错的任务。Python作为一种强大的编程语言，提供了多种库和工具，可以方便地实现这一需求。本文将介绍四种使用Python批量合并同一文件夹内所有子文件夹下的Excel文件内所有Sheet数据的方法，并提供详细的代码和案例。
一、使用pandas和os库进行合并
pandas是一个强大的数据处理库，而os库则提供了与操作系统交互的功能。通过这两个库，我们可以遍历文件夹和子文件夹，读取Excel文件，并将数据合并到一个DataFrame中。
案例与代码实现
假设我们有一个名为excel_files的文件夹，其中包含多个子文件夹，每个子文件夹内都有若干个Excel文件。
import os import pandas as pd # 定义主文件夹路径 main_folder = 'excel_files' # 初始化一个空的DataFrame用于存储所有数据 all_data = pd.DataFrame() # 遍历主文件夹下的所有子文件夹和文件 for root, dirs, files in os.walk(main_folder): for file in files: if file.endswith('.xlsx') or file.endswith('.xls'): # 确保是Excel文件 file_path = os.path.join(root, file) # 构建文件的完整路径 # 读取Excel文件中的所有Sheet xls = pd.ExcelFile(file_path) for sheet_name in xls.sheet_names: df = pd.read_excel(xls, sheet_name=sheet_name) # 将每个Sheet的数据追加到all_data中 all_data = all_data.append(df, ignore_index=True) # 将合并后的数据保存到新的Excel文件中 all_data.to_excel('merged_data.xlsx', index=False)
这段代码首先定义了主文件夹路径，并初始化一个空的DataFrame。然后，使用os.walk()函数遍历主文件夹及其所有子文件夹中的文件。对于每个Excel文件，使用pd.ExcelFile()读取文件，并通过sheet_names属性获取所有Sheet的名称。接着，使用pd.read_excel()函数逐个读取Sheet中的数据，并将其追加到all_data DataFrame中。最后，将合并后的数据保存到新的Excel文件中。
二、使用glob和pandas库进行合并
glob库提供了在文件夹中查找文件路径的功能，结合pandas可以更方便地处理Excel文件。
案例与代码实现
import glob import pandas as pd # 定义主文件夹路径和搜索模式 main_folder = 'excel_files' pattern = '**/*.xlsx' # 搜索所有子文件夹中的.xlsx文件 # 使用glob查找所有Excel文件路径 all_files = glob.glob(main_folder + '/' + pattern, recursive=True) # 初始化一个空的DataFrame用于存储所有数据 all_data = pd.DataFrame() # 遍历文件路径，读取数据并合并 for file in all_files: xls = pd.ExcelFile(file) for sheet_name in xls.sheet_names: df = pd.read_excel(xls, sheet_name=sheet_name) all_data = all_data.append(df, ignore_index=True) # 保存合并后的数据 all_data.to_excel('merged_data_glob.xlsx', index=False)
这段代码使用glob.glob()函数查找主文件夹及其所有子文件夹中的Excel文件路径。然后，与第一种方法类似，逐个读取每个文件中的所有Sheet数据，并将其合并到一个DataFrame中。最后，将合并后的数据保存到新的Excel文件中。
三、使用openpyxl库进行合并
openpyxl是一个用于读写Excel 2010 xlsx/xlsm/xltx/xltm文件的Python库。它可以更底层地操作Excel文件，提供更大的灵活性。
案例与代码实现
使用openpyxl进行合并通常涉及更复杂的操作，因为需要手动处理工作簿和工作表。这里仅提供一个大致的框架，具体实现会根据具体需求有所不同。
from openpyxl import load_workbook import os # 初始化一个新的工作簿用于存储合并后的数据 merged_wb = load_workbook(write_only=True) merged_ws = merged_wb.active 定义主文件夹路径 main_folder = 'excel_files' 遍历主文件夹下的所有子文件夹和文件 for root, dirs, files in os.walk(main_folder): for file in files: if file.endswith('.xlsx'): # 确保是Excel文件 file_path = os.path.join(root, file) # 加载Excel文件 wb = load_workbook(filename=file_path, read_only=True) # 遍历工作簿中的每个工作表 for sheet in wb.worksheets: for row in sheet.iter_rows(values_only=True): # 将行数据追加到合并后的工作表中 merged_ws.append(row) 保存合并后的工作簿 merged_wb.save('merged_data_openpyxl.xlsx')
这段代码创建了一个新的工作簿，并遍历主文件夹及其子文件夹中的所有Excel文件。对于每个文件，它加载工作簿并遍历其中的每个工作表。然后，使用`iter_rows()`方法逐行读取数据，并将其追加到合并后的工作表中。最后，保存合并后的工作簿。
需要注意的是，`openpyxl`的`write_only`模式可以提高写入性能，但某些功能可能会受到限制。此外，这种方法假设所有工作表的结构都是相同的，如果结构不同，可能需要进行额外的处理。
四、使用`xlrd`和`xlwt`库进行合并
（注意：`xlrd`库自2.0.0版本后不再支持.xlsx格式，仅支持.xls格式）
`xlrd`和`xlwt`是两个用于读写老版Excel文件（.xls格式）的Python库。虽然它们对于处理新版的.xlsx文件有所限制，但对于.xls文件仍然有效。
**案例与代码实现**
由于`xlrd`不再支持.xlsx格式，这里仅提供一个处理.xls文件的示例。对于.xlsx文件，建议使用`pandas`或`openpyxl`。
import os import xlrd import xlwt # 初始化一个新的工作簿用于存储合并后的数据 merged_wb = xlwt.Workbook() merged_ws = merged_wb.add_sheet('MergedData') row_num = 0 # 定义主文件夹路径 main_folder = 'excel_xls_files' # 遍历主文件夹下的所有子文件夹和文件 for root, dirs, files in os.walk(main_folder): for file in files: if file.endswith('.xls'): # 确保是.xls文件 file_path = os.path.join(root, file) # 加载Excel文件 wb = xlrd.open_workbook(file_path) # 遍历工作簿中的每个工作表 for sheet in wb.sheets(): for row_idx in range(sheet.nrows): row_data = sheet.row_values(row_idx) # 将行数据写入合并后的工作表中 for col_idx, value in enumerate(row_data): merged_ws.write(row_num, col_idx, value) row_num += 1 # 保存合并后的工作簿 merged_wb.save('merged_data_xlrd_xlwt.xls')
这段代码创建了一个新的工作簿和一个工作表，用于存储合并后的数据。然后，它遍历主文件夹及其子文件夹中的所有.xls文件，加载每个文件的工作簿，并遍历其中的每个工作表。对于每个工作表中的行，它读取行数据并将其写入合并后的工作表中。最后，保存合并后的工作簿。
总结：
本文介绍了四种使用Python批量合并同一文件夹内所有子文件夹下的Excel文件内所有Sheet数据的方法。这些方法分别使用了不同的库和工具，各有其优缺点。在实际应用中，可以根据具体需求和文件格式选择合适的方法。通过合理使用这些工具，可以大大提高数据处理和分析的效率。对于新手朋友来说，建议从简单的方法开始尝试，逐步掌握更高级的技巧和工具。
