Excel合并表格

日常有很多日、周、月报表，或者同类型的不同发送方提供的报表，要怎样将一堆文件的数据整合到一个文件里呢？下面是通过PYTHON和VBA实现这一功能的两个案例：

*小批量的多文件表格合并推荐使用PowerQuery，更适合小白。VBA适合对编程了解一些的朋友，python可能需要学习一下环境搭建先。

Python代码：

import pandas as pd
import os

# 1. Set the root folder path where your Excel files are located
root_path = r'C:\Your\Main\Folder\Path'
output_file = 'combined_data.xlsx'

all_data = []

# 2. Walk through all directories and subdirectories
for root, dirs, files in os.walk(root_path):
    for file in files:
        if file.endswith('.xlsx') or file.endswith('.xls'):
            # Skip the output file if it already exists in the same folder
            if file == output_file:
                continue
                
            file_path = os.path.join(root, file)
            print(f"Processing: {file_path}")
            
            # Read the first sheet (change header/sheet_name if needed)
            df = pd.read_excel(file_path)
            
            # Optional: Add a column to track which file the data came from
            df['Source_File'] = file
            
            all_data.append(df)

# 3. Merge everything and save
if all_data:
    combined_df = pd.concat(all_data, ignore_index=True)
    combined_df.to_excel(output_file, index=False)
    print(f"Successfully combined {len(all_data)} files into {output_file}!")
else:
    print("No Excel files found.")

VBA代码：

Sub CombineExcelFilesFromSubfolders()
    Dim FSO As Object
    Dim rootFolder As String
    Dim targetSheet As Worksheet
    
    ' 1. Set your main folder path here
    rootFolder = "C:\Your\Main\Folder\Path\" 
    
    Set targetSheet = ThisWorkbook.Sheets(1)
    targetSheet.Cells.Clear ' Clear previous data
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    ' 2. Start the recursive search
    Call ProcessFolder(FSO.GetFolder(rootFolder), targetSheet)
    
    MsgBox "Done! All data combined.", vbInformation
End Sub

Sub ProcessFolder(Folder As Object, targetSheet As Worksheet)
    Dim SubFolder As Object
    Dim File As Object
    Dim wbSource As Workbook
    Dim lastRow As Long
    Dim nextRow As Long

    ' Loop through files in the current folder
    For Each File In Folder.Files
        If InStr(File.Name, ".xls") > 0 And File.Name <> ThisWorkbook.Name Then
            Set wbSource = Workbooks.Open(File.Path)
            
            ' Find last row of source and next empty row in target
            lastRow = wbSource.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
            nextRow = targetSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
            If nextRow = 2 And targetSheet.Range("A1") = "" Then nextRow = 1
            
            ' Copy data (assumes headers are in row 1; skips header after first file)
            If nextRow = 1 Then
                wbSource.Sheets(1).Rows("1:" & lastRow).Copy targetSheet.Rows(nextRow)
            Else
                wbSource.Sheets(1).Rows("2:" & lastRow).Copy targetSheet.Rows(nextRow)
            End If
            
            wbSource.Close False
        End If
    Next

    ' Recursive call for each subfolder
    For Each SubFolder In Folder.SubFolders
        ProcessFolder SubFolder, targetSheet
    Next
End Sub