如何使用 Python 将 Excel 文件中的单元格合并

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 *  # Import entire module from ironxl for Excel operations

# Set license key for IronXL (Replace the license key with an actual valid key)
License.LicenseKey = "IRONSUITE.XYZ.ABC.COM.15796-DEPLOYMENT.TRIAL-5X73V4.TRIAL.EXPIRES.27.MAY.2024"

# Load existing workbook
workbook = WorkBook.Load("test_excel.xlsx")

from ironxl import *  # Import entire module from ironxl for Excel operations

# Set license key for IronXL (Replace the license key with an actual valid key)
License.LicenseKey = "IRONSUITE.XYZ.ABC.COM.15796-DEPLOYMENT.TRIAL-5X73V4.TRIAL.EXPIRES.27.MAY.2024"

# Load existing workbook
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 trial license. 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.

# Merge cells within specified ranges
worksheet.Merge("E5:E7")
worksheet.Merge("E9:E10")

# Save workbook to retain changes
workbook.Save()

# Merge cells within specified ranges
worksheet.Merge("E5:E7")
worksheet.Merge("E9:E10")

# Save workbook to retain changes
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.

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:

# Unmerge cells within specified ranges
worksheet.Unmerge("E5:E7")
worksheet.Unmerge("E9:E10")

# Save workbook to retain changes
workbook.Save()

# Unmerge cells within specified ranges
worksheet.Unmerge("E5:E7")
worksheet.Unmerge("E9:E10")

# Save workbook to retain changes
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.