使用Python批量合并Excel文件的所有Sheet数据

目录

一、使用pandas和os库进行合并

二、使用glob和pandas库进行合并

三、使用openpyxl库进行合并

四、使用`xlrd`和`xlwt`库进行合并

总结：

---

在数据处理和分析的过程中，经常需要将多个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 = ()  



  



# 遍历主文件夹下的所有子文件夹和文件  




for root, dirs, files in (main_folder):  



    for file in files:  



        if ('.xlsx') or ('.xls'):  # 确保是Excel文件  




            file_path = (root, file)  # 构建文件的完整路径  




            # 读取Excel文件中的所有Sheet  




            xls = (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。然后，使用()函数遍历主文件夹及其所有子文件夹中的文件。对于每个Excel文件，使用()读取文件，并通过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 = (main_folder + '/' + pattern, recursive=True)  



  



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



all_data = ()  



  



# 遍历文件路径，读取数据并合并  




for file in all_files:  



    xls = (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)

这段代码使用()函数查找主文件夹及其所有子文件夹中的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 (main_folder):




for file in files:




if ('.xlsx'): # 确保是Excel文件




file_path = (root, file)



# 加载Excel文件



wb = load_workbook(filename=file_path, read_only=True)



# 遍历工作簿中的每个工作表




for sheet in :




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 = ()  



merged_ws = merged_wb.add_sheet('MergedData')  



row_num = 0  



  



# 定义主文件夹路径  



main_folder = 'excel_xls_files'  



  



# 遍历主文件夹下的所有子文件夹和文件  




for root, dirs, files in (main_folder):  



    for file in files:  



        if ('.xls'):  # 确保是.xls文件  




            file_path = (root, file)  



            # 加载Excel文件  




            wb = xlrd.open_workbook(file_path)  



            # 遍历工作簿中的每个工作表  




            for sheet in ():  



                for row_idx in range():  



                    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数据的方法。这些方法分别使用了不同的库和工具，各有其优缺点。在实际应用中，可以根据具体需求和文件格式选择合适的方法。通过合理使用这些工具，可以大大提高数据处理和分析的效率。对于新手朋友来说，建议从简单的方法开始尝试，逐步掌握更高级的技巧和工具。