Merge Excel Workbooks or Worksheets in Python (Simple Example)

3 min readJun 20, 2023

Merging Excel files is a crucial step in data management. It allows you to consolidate information from various sources to create a more comprehensive data set for better analysis and decision-making. In this article, we will demonstrate how to merge Excel files in Python.

We will discuss the following topics:

Merge Multiple Excel Workbooks into One Workbook in Python
Merge Multiple Excel Worksheets into One Worksheet in Python

Python Library to Merge Excel Files

In order to merge Excel files, this article uses a Python Excel library: Spire.XLS for Python.

Spire.XLS for Python is a multifunctional and easy-to-use library for creating, reading, editing, and converting Excel files within Python applications. With this library, you can easily work with a lot of spreadsheet formats, such as XLS, XLSX, XLSB, XLSM, and ODS. Moreover, you are also able to render Excel files to other types of file formats, such as PDF, HTML, CSV, Text, Image, XML, SVG, ODS, PostScript, and XPS.

You can easily install Spire.XLS for Python from pypi by running the following commands in your terminal:

pip install Spire.Xls

For more detailed information about the installation, you can check this official documentation: How to Install Spire.XLS for Python in VS Code.

Merge Multiple Excel Workbooks into One Workbook in Python

To merge multiple Excel workbooks into a single workbook, you can create a new workbook and then copy worksheets from the workbooks that need to be merged to the new workbook.

The following code snippet shows how to merge multiple Excel workbooks into a single workbook in Python with Spire.XLS for Python:

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

#Create a list of file paths for the Excel files to be merged
files = []
files.append("File-1.xlsx" )
files.append("File-2.xlsx")
files.append("File-3.xlsx")

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

#Create a temporary workbook
tempbook = Workbook()

#Iterate through each file in the list of file paths
for file in files:
    #Load the file into the temporary workbook
    tempbook.LoadFromFile(file)
    #Iterate through each worksheet 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)

outputFile = "MergeExcelFiles.xlsx"        
#Save the merged file to a specific location
newbook.SaveToFile(outputFile, ExcelVersion.Version2013)
newbook.Dispose()
tempbook.Dispose()

Merge Multiple Excel Worksheets into One Worksheet in Python

To merge multiple worksheets into a single worksheet, you can copy the data range from each source worksheet and add it to the destination worksheet.

The following code snippet shows how to merge multiple worksheets in a workbook into a single worksheet in Python with Spire.XLS for Python:

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

#Create a workbook
workbook = Workbook()
#Load an Excel File
workbook.LoadFromFile("Sales.xlsx")

#Get the 1st, 2nd and 3rd worksheets in the file
sheet1 = workbook.Worksheets[0]
sheet2 = workbook.Worksheets[1]
sheet3 = workbook.Worksheets[2]

#Get the used cell range of the 2nd worksheet
sourceRange1 = sheet2.AllocatedRange
#Get the used cell range of the 3rd worksheet
sourceRange2 = sheet3.AllocatedRange

#Copy the used cell range of the 2nd worksheet to the end of the 1st worksheet
sourceRange1.Copy(sheet1.Range[sheet1.LastRow + 1, 1])
#Copy the used cell range of the 3rd worksheet to the end of the 1st worksheet
sourceRange2.Copy(sheet1.Range[sheet1.LastRow + 1, 1])

#Remove the 2nd worksheet
sheet2.Remove()
#Remove the 3rd worksheet
sheet3.Remove()

outputFile = "MergeExcelWorksheets.xlsx"
#Save the result file to a specific location
workbook.SaveToFile(outputFile, ExcelVersion.Version2013)
workbook.Dispose()

Conclusion

In this article, we have explored how to merge multiple Excel workbooks into one workbook along with how to merge multiple Excel worksheets into one worksheet. We hope it’s helpful to you.

Merge Excel Workbooks or Worksheets in Python (Simple Example)

Python Library to Merge Excel Files

Merge Multiple Excel Workbooks into One Workbook in Python

Merge Multiple Excel Worksheets into One Worksheet in Python

Conclusion

Related Topics

Written by Alice Yang