Python: Merge Excel Files into One

When dealing with multiple Excel files that contain similar data structures, merging them enables comprehensive data analysis and reporting. By combining data from various sources, you can gain a holistic view and uncover insights that may not be apparent when examining each file individually. In this article, you will learn how to merge Excel files into one in Python using Spire.XLS for Python library.

• Merge Multiple Excel Workbooks into One in Python
• Merge Multiple Excel Worksheets into One in Python

Install Spire.XLS for Python

This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.

pip install Spire.XLS

If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows

Merge Multiple Excel Workbooks into One in Python

Spire.XLS for Python enables you to merge multiple workbooks into one by copying the worksheets from the workbooks to be merged into a new workbook using Workbook.Worksheets.AddCopy() method. The detailed steps are as follows.

• Put the file paths of the workbooks to be merged into a list.
• Initialize a Workbook object to create a new workbook and clear its default worksheets.
• Initialize a temporary Workbook object.
• Loop through the file paths in the list.
• Load the workbook specified by the file path into the temporary Workbook object using Workbook.LoadFromFile() method.
• Loop through the worksheets in the temporary workbook, then copy each worksheet from the temporary workbook to the newly created workbook using Workbook.Worksheets.AddCopy() method.
• Save the resulting workbook using Workbook.SaveToFile() method.

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("Input1.xlsx" )
files.append("Input2.xlsx")
files.append("Input3.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()

Merge Multiple Excel Worksheets into One in Python

To merge multiple Excel worksheets, you can copy the data ranges used in these worksheets to a single worksheet using CellRange.Copy(destRange) method. The detailed steps are as follows.

• Initialize a Workbook object and load an Excel workbook using Workbook.LoadFromFile() method.
• Get the two worksheets that need to be merged using Workbook.Worksheets[sheetIndex] property. Note the sheet index is zero-based.
• Get the used range of the second worksheet using Worksheet.AllocatedRange property.
• Specify the destination range in the first worksheet using Worksheet.Range[rowIndex, columnIndex] property. Note the row and column indexes are 1-based.
• Copy the used range of the second worksheet to the destination range in the first worksheet using CellRange.Copy(destRange) method.
• Remove the second worksheet from the workbook.
• Save the resulting workbook using Workbook.SaveToFile() method.

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

# Create a Workbook object
workbook = Workbook()
# Load an Excel workbook
workbook.LoadFromFile("File2.xlsx")

# Get the first worksheet
sheet1 = workbook.Worksheets[0]
# Get the second worksheet
sheet2 = workbook.Worksheets[1]

# Get the used range in the second worksheet
sourceRange = sheet2.AllocatedRange
# Specify the destination range in the first worksheet
destRange = sheet1.Range[sheet1.LastRow + 1, 1]

# Copy the used range from the second worksheet to the destination range in the first worksheet
sourceRange.Copy(destRange)

# Remove the second worksheet
sheet2.Remove()

# Save the resulting workbook to a .xlsx file
workbook.SaveToFile("MergeWorksheets.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.