How to Merge Cells in an Excel File Using Python

In the world of spreadsheet manipulation, Excel stands as a foundational tool, offering a multitude of functionalities catering to various needs in finance, engineering, statistics, and beyond. Among its many features, the ability to merge specific cells in Excel is one often employed to enhance data presentation and visualization. In the dynamic landscape of data management, organizing and consolidating data efficiently is paramount. Excel, with its versatile features, serves as a go-to tool for many professionals. However, when it comes to merging or unmerging cells in Excel programmatically, Python emerges as a powerful ally. In this article, we'll explore how Python, coupled with a library like IronXL, simplifies the process of merging cells in a new Excel file, enabling users to streamline their data organization workflows.

How to Merge Cells in an Excel File Using Python

1. Install IronXL.
2. Load existing Workbook.
3. Select Excel Worksheet.
4. Use the Merge function to merge specific cells.
5. Use the Unmerge function to unmerge cells in Excel.

Why Use IronXL?

IronXL is a powerful Python library developed and maintained by Iron Software. Its primary purpose is to provide an intuitive and efficient way to work with Excel files (and other spreadsheet formats) in Python applications and websites. Here are some key features of IronXL:

1. IronXL allows you to read data from existing Excel files (XLSX, XLS, XLSM, XLTX, CSV, and TSV). You can also generate new Excel files and edit existing ones programmatically.
2. IronXL works seamlessly across different platforms, including Windows, macOS, Linux, Docker, Azure, and AWS. Whether you’re developing on your local machine or deploying to the cloud, IronXL has you covered.
3. The library provides an easy-to-use API for working with Excel spreadsheets. You can load workbooks, select worksheets, read cell values, apply styles, unmerge cells, and perform various operations without a complex setup.
4. IronXL allows you to style cells by setting font properties, background patterns, borders, and alignment. You can format cell values as text, numbers, dates, currency, percentages, and more.
5. You can work with Excel formulas, and they are recalculated every time a sheet is edited. Perform calculations on ranges of cells, such as finding the sum, minimum, or maximum value.

Using IronXL to Merge Cells in Excel File

Before we begin, make sure you have IronXL installed. You can install it using pip:

pip install ironXl

pip install ironXl

This will install IronXL in our Project.

Load Excel Workbook

The very first step is to load the Excel workbook into memory. We will load the following Excel file and utilize it throughout this tutorial.

The following code will load the existing workbook in our project.

from ironxl import *     # Supported for XLSX, XLS, XLSM, XLTX, CSV, and TSV
License.LicenseKey = "IRONSUITE.XYZ.ABC.COM.15796-DEPLOYMENT.TRIAL-5X73V4.TRIAL.EXPIRES.27.MAY.2024";
// WorkBook class
workbook = WorkBook.Load("test_excel.xlsx")

from ironxl import *     # Supported for XLSX, XLS, XLSM, XLTX, CSV, and TSV
License.LicenseKey = "IRONSUITE.XYZ.ABC.COM.15796-DEPLOYMENT.TRIAL-5X73V4.TRIAL.EXPIRES.27.MAY.2024";
// WorkBook class
workbook = WorkBook.Load("test_excel.xlsx")

The above Python code demonstrates the usage of IronXL, a library for working with Excel files. After importing the necessary module from IronXL, a license key is provided to authenticate the usage of the library. You can purchase your free license key from the IronXL website. The Load method is then employed to open an Excel workbook named "test_excel.xlsx". This workbook could contain various sheets with data or formatting. By loading it into the script, you gain the ability to manipulate its contents programmatically. This includes tasks like reading data, modifying cell values, or creating entirely new sheets. The library supports various file formats including XLSX, XLS, XLSM, XLTX, CSV, and TSV, making it versatile for handling different types of Excel files seamlessly.

Select Excel Worksheet

The next step is to select the worksheet, we want to work on. The following code will specify the Excel spreadsheet.

# Select worksheet at index 0
worksheet = workbook.WorkSheets[0]

# Select worksheet at index 0
worksheet = workbook.WorkSheets[0]

The above line selects the worksheet at index 0 from the loaded Excel workbook, assigning it to the variable worksheet. In Excel, worksheets are individual tabs within a workbook where you can organize and manipulate data. By referencing the worksheet at index 0, you're accessing the first worksheet in the workbook, as indexing in Python typically starts from 0. This allows you to perform operations specific to that particular sheet, such as reading data, modifying cell values, or applying formatting.

Merge specific cells in an Excel file

Now that we have loaded the workbook and selected the worksheet, let's define the specific range of cells to merge. Within our selected worksheet, there's a column containing country names. For certain customers, the country value is the same, such as 'United States'. We'll merge cells in Excel for these specific instances. The following code will perform merge operations for a particular range.

worksheet.Merge("E5:E7");
worksheet.Merge("E9:E10");
workbook.Save()

worksheet.Merge("E5:E7");
worksheet.Merge("E9:E10");
workbook.Save()

These lines of code utilize the Merge method to combine cells within the selected worksheet. The first line merges cells in the range from E5 to E7, likely for consolidating data related to the "United States" in the country column. Similarly, the second line merges cells from E9 to E10, for another set of data entries with the same country value. After merging, the Save method is called to save the changes made to the workbook. This action ensures that the merged cells are retained in the Excel file.

The Merge method is used to merge a range of cells. This action combines the cells without erasing any existing values or data. However, only the value of the first cell in the merged cell region will be displayed. Nonetheless, the values of the merged cells remain accessible in IronXL.

Merged cells can be seen in the given output.

Retrieve Merged Regions

Merged regions in Excel refer to contiguous blocks of merged cells that have been combined into a single cell. Retrieving merged regions programmatically allows for the identification and handling of these areas within a worksheet. This information is useful for various tasks such as data analysis, formatting adjustments, or ensuring accurate data manipulation across merged cells. The following code will retrieve merged regions from an Excel worksheet.

print("Merged Regions are:")
retrieveMergedRegions = worksheet.GetMergedRegions();
for mergedRegion in retrieveMergedRegions:
    print(mergedRegion.RangeAddressAsString);

print("Merged Regions are:")
retrieveMergedRegions = worksheet.GetMergedRegions();
for mergedRegion in retrieveMergedRegions:
    print(mergedRegion.RangeAddressAsString);

The above code first prints a header indicating the start of the output related to merged regions. It then retrieves all the merged regions from the specified worksheet using the GetMergedRegions() method and iterates over each merged region. Within the loop, it prints the address of each merged region as a string using RangeAddressAsString. This provides a clear list of all merged regions in the worksheet, aiding in tasks such as identifying merged areas for data analysis, formatting adjustments, or targeted operations within the Excel file.

The previously merged cells are:

Unmerge cells in Excel file

Unmerging cells in an Excel file involves reversing the process of merging and separating previously combined cells into individual cells. To unmerge cells in an Excel file using IronXL, you can use the Unmerge method. Here's how you can do it:

worksheet.Unmerge("E5:E7")
worksheet.Unmerge("E9:E10")
workbook.Save()

worksheet.Unmerge("E5:E7")
worksheet.Unmerge("E9:E10")
workbook.Save()

In the above code snippet, the Unmerge method is used to unmerge the cells within the specified ranges, specifically targeting the ranges E5:E7 and E9:E10. This action restores the individuality of cells within these ranges, allowing for independent manipulation of their contents. Finally, the Save method is invoked to ensure that the changes are reflected and persisted in the Excel workbook.

Conclusion

In conclusion, Python, in combination with IronXL, simplifies the task of merging cells in Excel, enhancing data organization and visualization efforts. IronXL's extensive capabilities, rich features, and user-friendly interface empower users to seamlessly manipulate Excel files, from loading workbooks to merging specific cell ranges programmatically. With a free trial available for exploration and a comprehensive licensing option, IronXL offers a compelling solution for professionals seeking efficient data management in Python applications.