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

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

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


相关文章
|
3天前
|
机器学习/深度学习 数据处理 Python
如何利用Python实现高效的数据清理与预处理
数据清理和预处理是数据科学家和分析师工作中不可或缺的一环,而Python作为一门强大的编程语言,可以使这个过程变得更加高效和便捷。本文将介绍一些常见的数据清理和预处理技术,并演示如何使用Python来实现这些技术。
|
7天前
|
Java 关系型数据库 MySQL
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口(下)
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口
45 0
|
7天前
|
Java 关系型数据库 MySQL
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口(上)
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口
48 0
|
2天前
|
前端开发 Java
基于Java爬取微博数据(二) 正文长文本+导出数据Excel
【5月更文挑战第12天】基于Java爬取微博数据,正文长文本+导出数据Excel
|
2天前
|
数据采集 数据可视化 Python
Python分析香港26281套在售二手房数据
Python分析香港26281套在售二手房数据
|
3天前
|
数据采集 存储 数据挖掘
Python DataFrame初学者指南:轻松上手构建数据表格
【5月更文挑战第19天】本文是针对初学者的Pandas DataFrame指南,介绍如何安装Pandas、创建DataFrame(从字典或CSV文件)、查看数据(`head()`, `info()`, `describe()`)、选择与操作数据(列、行、缺失值处理、数据类型转换、排序、分组聚合)以及保存DataFrame到CSV文件。通过学习这些基础,你将能轻松开始数据科学之旅。
|
3天前
|
数据挖掘 数据处理 Python
【Python DataFrame 专栏】Python DataFrame 入门指南:从零开始构建数据表格
【5月更文挑战第19天】本文介绍了Python数据分析中的核心概念——DataFrame,通过导入`pandas`库创建并操作DataFrame。示例展示了如何构建数据字典并转换为DataFrame,以及进行数据选择、添加修改列、计算统计量、筛选和排序等操作。DataFrame适用于处理各种规模的表格数据,是数据分析的得力工具。掌握其基础和应用是数据分析之旅的重要起点。
【Python DataFrame 专栏】Python DataFrame 入门指南:从零开始构建数据表格
|
6天前
|
JSON JavaScript 数据格式
利用 python 分析基金,合理分析数据让赚钱赢在起跑线!(1)
利用 python 分析基金,合理分析数据让赚钱赢在起跑线!(1)
|
6天前
|
Java Apache 索引
POI操作大全(动态合并单元格,为单元格生成一个自定义的数据显示格式,自定义公式计算结果生成,读取excel,word文件在生成图片,word指定位置生成图片)
POI操作大全(动态合并单元格,为单元格生成一个自定义的数据显示格式,自定义公式计算结果生成,读取excel,word文件在生成图片,word指定位置生成图片)
|
7天前
|
存储 JSON 数据格式
Python知识点——高维数据的格式化
Python知识点——高维数据的格式化
8 0