Merge Excel Files in Python (Sheets & Workbooks) Like a Pro

In work and study, merging Excel files is a common need—whether it's compiling monthly reports from different departments, comparing project data, or consolidating exports from sub-systems. Instead of manually copying and pasting, using Python to merge Excel files automatically is a better solution. It not only saves time and effort, but also reduces the risk of errors, helping you efficiently combine large volumes of scattered data into one organized Excel file.

Python Libraries to Merge Excel Easily

There are several Python libraries available for working with Excel, each with different strengths.

Pandas, a widely-used open-source library, supports reading and writing Excel files and is ideal for data manipulation. However, when it comes to merging multiple Excel files into one workbook while keeping each sheet separate, Pandas has limitations—it can only consolidate data into a single worksheet.

OpenPyXL offers more control over Excel structure. It allows you to create and manage multiple worksheets within a single file, making it suitable for merging Excel sheets while preserving them as individual tabs.

For more advanced scenarios—such as generating pivot tables, creating charts, or exporting Excel to PDF—commercial-grade libraries like Spire.XLS and Aspose.Cells are more powerful. They offer full support for workbook-level operations, making them ideal for professional use cases.

In this guide, I’ll use Spire.XLS to demonstrate how to merge Excel files in Python while keeping sheets separate. It can be easily installed via the pip command: pip install Spire.XLS.

Merge One Excel Sheet into Another Workbook Using Python

Sometimes you only need to copy a single worksheet into an existing Excel file—for example, adding the latest month’s data to an annual sales summary. In Spire.XLS, this can be done by copying the worksheet directly into the current workbook. You can use the Worksheet.CopyFrom() method to duplicate the sheet’s data, and then apply the Workbook.CopyTheme() method to retain the original formatting. This ensures the worksheet is seamlessly integrated without data loss or style inconsistencies.

Steps to merge an Excel sheet into another Workbook:

• Create an object of the Workbook class, and load an Excel workbook as a source file using the Workbook.LoadFromFile() method.

• Get a worksheet through the Workbook.Worksheets[] property.

• Create another Workbook instance, and read an Excel workbook as the destination file.

• Add a new worksheet to the destination file using the Workbook.Worksheets.Add() method.

• Copy the worksheet from the source Excel file to the destination with the Worksheet.CopyFrom() method.

• Copy its original format through the Workbook.CopyTheme () method.

• Save the resulting Excel spreadsheet as a new file using the Workbook.SaveToFile() method.

The code example here shows how to merge the first worksheet of an Excel workbook to another one:

from spire.xls import *
from spire.xls.common import *

# Initialize an instance of the Workbook class
sourceWorkbook = Workbook()
# Load the source Excel workbook
sourceWorkbook.LoadFromFile("/sample1.xlsx")

# Get the first worksheet of the source workbook
sourceSheet = sourceWorkbook.Worksheets[0]
# Get the name of the first worksheet
sheetName = sourceSheet.Name + "_Copy"

# Initialize an instance of the Workbook class
destWorkbook = Workbook()
# Load the destination Excel workbook
destWorkbook.LoadFromFile("/sample2.xlsx")

# Add a new worksheet with a specific name to the destination workbook
destSheet = destWorkbook.Worksheets.Add(sheetName)

# Copy the first worksheet of the source workbook to the new worksheet of the destination workbook
destSheet.CopyFrom(sourceSheet)

# Copy the theme from the source workbook to the destination workbook
destWorkbook.CopyTheme(sourceWorkbook)


# Save the destination workbook to another file
destWorkbook.SaveToFile("/CopyToAnotherWorkbook.xlsx", ExcelVersion.Version2013)

sourceWorkbook.Dispose()
destWorkbook.Dispose()

Merge Multiple Excel Files into One Workbook with Python

Imagine you have data from various departments that need to be consolidated into one comprehensive report, or perhaps you have several questionnaires that need to be combined and analyzed together. Manually opening, copying, and pasting data can be time-consuming and tedious. With Python, you can streamline this process by using the Workbook.Worksheets.AddCopy() method to merge multiple Excel files into one workbook without ever needing to open each individual file. Let’s dive into the steps to make this task quicker and easier.

Steps to combine multiple Excel files into one workbook:

• Create a list and append Excel files to be merged into the list.

• Create a Workbook instance and clear the default worksheets.

• Initialize a temporary Workbook object.

• Loop through file paths in the list.

  • Load the current Excel file using the Workbook.LoadFromFile() method.

  • Iterate through all worksheets of the Excel file and then copy them from the temporary workbook to the newly created workbook with the Workbook.Worksheets.AddCopy() method.

• Save the resulting Excel file.

from spire.xls import *
from spire.xls.common import *

# Put the file paths of the workbooks to be merged into a list
files = []
files.append("/template1.xlsx" )
files.append("/template2.xlsx")
files.append("/template3.xlsx")

# Create a new workbook
newbook = Workbook()
newbook.Version = ExcelVersion.Version2013
# Clear the default worksheets
newbook.Worksheets.Clear()

# Create a temporary workbook
tempbook = Workbook()
# Loop through the file paths in the list
for file in files:
    # Load the workbook specified by the file path into the temporary workbook object
    tempbook.LoadFromFile(file)
    # Loop through the worksheets in the temporary workbook
    for sheet in tempbook.Worksheets:
        # Copy the worksheet from the temporary workbook to the new workbook
        newbook.Worksheets.AddCopy(sheet, WorksheetCopyType.CopyAll)

# Save the new workbook to a .xlsx file
newbook.SaveToFile("/MergeWorkbooks.xlsx", ExcelVersion.Version2016)

newbook.Dispose()
tempbook.Dispose()

To Wrap Up

This article shows how to use Python to automate Excel file merge—whether you're merging a worksheet into another Excel file or combining multiple Excel workbooks into one. With detailed steps and code examples, you can quickly master how to merge Excel files in Python and streamline your workflow. Hope you find it helpful!