Merge or Unmerge Cells in Excel with Python (Simple Example)

Merging and unmerging cells in Excel can be a powerful tool for formatting your data. By merging cells together, you can create headings, titles or display information in a much clearer way. Conversely, unmerging cells allows you to split previously merged cells back into their original form. In this article, we will explore how to merge or unmerge cells in an Excel file in Python.

We will discuss the following topics:

• Merge Specific Cells in an Excel File in Python
• Unmerge Specific Cells in an Excel File in Python
• Unmerge All Merged Cells in an Excel File in Python

Python Library to Merge Excel Files

To merge or unmerge cells in Excel using Python, we will use the Spire.XLS for Python library.

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.

Once the library is installed, we can start coding.

Merge Specific Cells in an Excel File in Python

The following code snippet demonstrates how to merge specific cells within an Excel worksheet in Python:

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

#Create a workbook
workbook = Workbook()
#Load an Excel file
workbook.LoadFromFile("Input.xlsx")

#Get the first worksheet
sheet = workbook.Worksheets[0]

#Merge the particular range of cells in the worksheet
sheet.Range["A2:A6"].Merge()
sheet.Range["A7:A11"].Merge()
sheet.Range["A12:A16"].Merge()

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

The above code first created a new object of the Workbook class and loaded an existing Excel file using the Workbook.LoadFromFile() method. It then got the first worksheet in the workbook and merged specific cells in the worksheet using the Worksheet.Range[].Merge() method. Finally, it saved the result file as a new Excel file using the Workbook.SaveToFile() method.

The following picture shows the result Excel file after merging cells A2:A6, A7:A11 and A12:A16:

Merge Specific Cells in Excel in Python

Unmerge Specific Cells in an Excel File in Python

The following code snippet demonstrates how to unmerge a specific cell within an Excel worksheet in Python:

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

#Create a workbook
workbook = Workbook()
#Load an Excel file
workbook.LoadFromFile("MergeCells.xlsx")

#Get the first worksheet
sheet = workbook.Worksheets[0]

#Unmerge the particular merged cell in the worksheet
sheet.Range["A2"].UnMerge()

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

The above code first created a new object of the Workbook class and loaded an existing Excel file using the Workbook.LoadFromFile() method. It then got the first worksheet in the workbook and unmerged specific cells in the worksheet using the Worksheet.Range[].UnMerge() method. Finally, it saved the result file as a new Excel file using the Workbook.SaveToFile() method.

The following picture shows the result Excel worksheet after unmerging the merged cell A2:

Unmerge Specific Cells in Excel in Python

Unmerge All Merged Cells in an Excel File in Python

The following code snippet demonstrates how to unmerge all merged cells within an Excel worksheet in Python:

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

#Create a workbook
workbook = Workbook()
#Load an Excel file
workbook.LoadFromFile("MergeCells.xlsx")

#Get the first worksheet
sheet = workbook.Worksheets[0]

#Get the merged cell ranges in the first worksheet and put them into a CellRange array
ranges = sheet.MergedCells

#Iterate through each merged cell in the array and unmerge it
for cell in ranges:
    cell.UnMerge()

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

The above code first created a new object of the Workbook class and loaded an existing Excel file using the Workbook.LoadFromFile() method. It then got the first worksheet in the workbook and retrieved all merged cell ranges within that worksheet using the Worksheet.MergedCells property which returns an array of CellRange objects. After that, it used a for loop to iterate through each merged cell in the array and invoked the CellRange.UnMerge() method to unmerge it. Finally, it saved the result file as a new Excel file using the Workbook.SaveToFile() method.

The following picture shows the result Excel worksheet after unmerging all merged cells:

Unmerge All Merged Cells in Excel in Python

Conclusion

In this article, we have discussed how to merge or unmerge cells in an Excel file with Python. We hope it’s helpful to you.

See More

Python: Convert Excel to PDF