Effortlessly Merge Multiple Excel Files and Sheets with Python

1. Introduction

Hello, I am Cui Yanfei. Previously I shared a Python automation article about splitting Excel into CSV files; now a reader asked how to merge many Excel files.

2. Project Goal

Use Python to merge multiple Excel files and their sheets.

3. Preparation

Software: PyCharm. Required libraries: pandas, xlrd, os.

4. Analysis

How to select Excel files?

Use the os module to list all target Excel files.

How to select sheets?

Use the xlrd library to read Excel files and obtain sheet names.

How to merge?

Use pandas.concat to append data from each sheet sequentially.

How to save?

Use DataFrame.to_excel to write the merged result.

5. Implementation

Step 1: Import libraries

import pandas as pd
import xlrd
import os

Step 2: Choose Excel files

# Path to files
path = "D:/b/"
# Get all .xlsx filenames in the folder
xlsx_names = [x for x in os.listdir(path) if x.endswith(".xlsx")]

Step 3: Choose sheets

# Get the first Excel file name
xlsx_names1 = xlsx_names[0]
aa = path + xlsx_names1
first_file_fh = xlrd.open_workbook(aa)
first_file_sheet = first_file_fh.sheets()

Step 4: Loop and merge sheets

# Loop over sheet names
for sheet_name in sheet_names:
    df = None
    # Loop over Excel files
    for xlsx_name in xlsx_names:
        sheet_na = pd.ExcelFile(path + xlsx_name).sheet_names
        if sheet_name in sheet_na:
            _df = pd.read_excel(path + xlsx_name, sheet_name=sheet_name, header=None)
            if df is None:
                df = _df
            else:
                df = pd.concat([df, _df], ignore_index=True)
        else:
            continue

Step 5: Save merged file

df.to_excel(excel_writer=writer, sheet_name=sheet_name, encoding="utf-8", index=False)
print(sheet_name + "  保存成功！共%d个，第%d个。" % (len(sheet_names), num))
num += 1
writer.save()
writer.close()

6. Result Demonstration

Before merging:

Progress screenshot:

After merging:

7. Conclusion

The article shows how to use Python to merge multiple Excel files and sheets, greatly reducing manual copy‑paste, saving time, and minimizing errors. The code is concise, and readers can adapt it, package it as an executable, or extend it with other methods.