求助：批量读取并合并多Excel文件同工作表数据的实现方案

实现思路 #

先初始化四个空DataFrame对应四个工作表，遍历目标文件夹里的所有Excel文件，逐个读取每个文件的四个工作表数据，追加到对应的DataFrame中，最后把合并好的DataFrame保存成独立Excel文件，或者保留为DataFrame做后续处理。

代码示例 #

import pandas as pd
import os

# 替换成你的Excel文件所在文件夹路径
folder_path = "你的Excel文件文件夹路径"

# 初始化四个空DataFrame
df_sheet1 = pd.DataFrame()
df_sheet2 = pd.DataFrame()
df_sheet3 = pd.DataFrame()
df_sheet4 = pd.DataFrame()

# 遍历文件夹里的Excel文件
for filename in os.listdir(folder_path):
    if filename.endswith((".xlsx", ".xls")):
        file_path = os.path.join(folder_path, filename)
        # 读取当前文件的四个工作表
        sheet1 = pd.read_excel(file_path, sheet_name="Sheet1")
        sheet2 = pd.read_excel(file_path, sheet_name="Sheet2")
        sheet3 = pd.read_excel(file_path, sheet_name="Sheet3")
        sheet4 = pd.read_excel(file_path, sheet_name="Sheet4")
        # 追加到对应DataFrame，ignore_index重置索引避免重复
        df_sheet1 = pd.concat([df_sheet1, sheet1], ignore_index=True)
        df_sheet2 = pd.concat([df_sheet2, sheet2], ignore_index=True)
        df_sheet3 = pd.concat([df_sheet3, sheet3], ignore_index=True)
        df_sheet4 = pd.concat([df_sheet4, sheet4], ignore_index=True)

# 保存合并后的文件，index=False不保留行索引
df_sheet1.to_excel("合并后的Sheet1.xlsx", index=False)
df_sheet2.to_excel("合并后的Sheet2.xlsx", index=False)
df_sheet3.to_excel("合并后的Sheet3.xlsx", index=False)
df_sheet4.to_excel("合并后的Sheet4.xlsx", index=False)

实现思路 #

新建一个Excel工作簿作为合并结果容器，确保它有四个对应工作表，然后遍历目标文件夹里的所有Excel文件，逐个复制每个文件四个工作表的数据到新工作簿对应表的末尾，注意跳过重复表头。

代码示例 #

打开Excel按Alt+F11打开VBA编辑器，插入一个模块，粘贴以下代码：

Sub MergeExcelSheets()
    Dim wsDest As Worksheet
    Dim wbSource As Workbook
    Dim wsSource As Worksheet
    Dim folderPath As String
    Dim fileName As String
    Dim lastRowDest As Long
    Dim lastRowSource As Long
    
    ' 替换成你的Excel文件文件夹路径，末尾要加反斜杠
    folderPath = "C:\你的Excel文件文件夹路径\"
    
    ' 创建新工作簿存合并结果
    Set wbDest = Workbooks.Add
    ' 确保新工作簿有4个工作表，不够就添加
    Do While wbDest.Sheets.Count < 4
        wbDest.Sheets.Add
    Loop
    ' 重命名工作表（和源文件对应）
    wbDest.Sheets(1).Name = "Sheet1"
    wbDest.Sheets(2).Name = "Sheet2"
    wbDest.Sheets(3).Name = "Sheet3"
    wbDest.Sheets(4).Name = "Sheet4"
    
    ' 遍历文件夹里的Excel文件
    fileName = Dir(folderPath & "*.xlsx")
    Do While fileName <> ""
        ' 打开源文件
        Set wbSource = Workbooks.Open(folderPath & fileName)
        
        ' 处理Sheet1
        Set wsSource = wbSource.Sheets("Sheet1")
        Set wsDest = wbDest.Sheets("Sheet1")
        lastRowSource = wsSource.Cells(Rows.Count, 1).End(xlUp).Row
        lastRowDest = wsDest.Cells(Rows.Count, 1).End(xlUp).Row
        ' 第一个文件复制表头，后续文件跳过表头
        If lastRowDest = 1 And wsDest.Cells(1, 1).Value = "" Then
            wsSource.Range("A1:" & wsSource.Cells(lastRowSource, Columns.Count).End(xlToLeft).Address).Copy _
                wsDest.Range("A1")
        Else
            wsSource.Range("A2:" & wsSource.Cells(lastRowSource, Columns.Count).End(xlToLeft).Address).Copy _
                wsDest.Cells(lastRowDest + 1, 1)
        End If
        
        ' 处理Sheet2
        Set wsSource = wbSource.Sheets("Sheet2")
        Set wsDest = wbDest.Sheets("Sheet2")
        lastRowSource = wsSource.Cells(Rows.Count, 1).End(xlUp).Row
        lastRowDest = wsDest.Cells(Rows.Count, 1).End(xlUp).Row
        If lastRowDest = 1 And wsDest.Cells(1, 1).Value = "" Then
            wsSource.Range("A1:" & wsSource.Cells(lastRowSource, Columns.Count).End(xlToLeft).Address).Copy _
                wsDest.Range("A1")
        Else
            wsSource.Range("A2:" & wsSource.Cells(lastRowSource, Columns.Count).End(xlToLeft).Address).Copy _
                wsDest.Cells(lastRowDest + 1, 1)
        End If
        
        ' 处理Sheet3
        Set wsSource = wbSource.Sheets("Sheet3")
        Set wsDest = wbDest.Sheets("Sheet3")
        lastRowSource = wsSource.Cells(Rows.Count, 1).End(xlUp).Row
        lastRowDest = wsDest.Cells(Rows.Count, 1).End(xlUp).Row
        If lastRowDest = 1 And wsDest.Cells(1, 1).Value = "" Then
            wsSource.Range("A1:" & wsSource.Cells(lastRowSource, Columns.Count).End(xlToLeft).Address).Copy _
                wsDest.Range("A1")
        Else
            wsSource.Range("A2:" & wsSource.Cells(lastRowSource, Columns.Count).End(xlToLeft).Address).Copy _
                wsDest.Cells(lastRowDest + 1, 1)
        End If
        
        ' 处理Sheet4
        Set wsSource = wbSource.Sheets("Sheet4")
        Set wsDest = wbDest.Sheets("Sheet4")
        lastRowSource = wsSource.Cells(Rows.Count, 1).End(xlUp).Row
        lastRowDest = wsDest.Cells(Rows.Count, 1).End(xlUp).Row
        If lastRowDest = 1 And wsDest.Cells(1, 1).Value = "" Then
            wsSource.Range("A1:" & wsSource.Cells(lastRowSource, Columns.Count).End(xlToLeft).Address).Copy _
                wsDest.Range("A1")
        Else
            wsSource.Range("A2:" & wsSource.Cells(lastRowSource, Columns.Count).End(xlToLeft).Address).Copy _
                wsDest.Cells(lastRowDest + 1, 1)
        End If
        
        ' 关闭源文件，不保存修改
        wbSource.Close SaveChanges:=False
        ' 读取下一个文件
        fileName = Dir
    Loop
    
    MsgBox "合并完成！"
End Sub

内容的提问来源于stack exchange，提问作者Frodo