DEV Community

Jeremy K.
Jeremy K.

Posted on

Merge and Unmerge Excel Cells in Python

#python #programming

Automate Excel cell merging and splitting using Python—without installing Microsoft Office or WPS. This guide covers every common scenario (entire rows, entire columns, custom rectangular ranges, and batch unmerging) using the Free Spire.XLS library. Ideal for CI/CD pipelines, data preprocessing, and automated report generation, this solution reads and writes .xls/.xlsx files natively, focusing purely on document structure and data manipulation.

1. Setup and Installation

Install the required package via pip:

pip install Spire.XLS.Free
Enter fullscreen mode Exit fullscreen mode

Then, import the core modules at the top of your Python script:

from spire.xls import *
from spire.xls.common import *
Enter fullscreen mode Exit fullscreen mode

2. Merging an Entire Row

To collapse all cells in a specific row into a single cell (e.g., for a wide column header), use the Rows[index].Merge() method. Row indices are zero‑based.

# Load the workbook and access the first worksheet
workbook = Workbook()
workbook.LoadFromFile("SalesData.xlsx")
sheet = workbook.Worksheets[0]

# Merge the entire first row
sheet.Rows[0].Merge()
# Only the top‑left cell (A1) retains its value
sheet.Range["A1"].Value = "2026 Annual Sales Summary"

workbook.SaveToFile("MergedRowResult.xlsx", ExcelVersion.Version2013)
workbook.Dispose()
Enter fullscreen mode Exit fullscreen mode

3. Merging an Entire Column

Similarly, to merge all cells in a column (e.g., for a vertical category label), call Columns[index].Merge():

# Merge the second column (index 1 = column B)
sheet.Columns[1].Merge()
# Content is preserved only in cell B1
sheet.Range["B1"].Value = "Region Classification"
Enter fullscreen mode Exit fullscreen mode

4. Merging a Custom Cell Range

For maximum flexibility—such as spanning titles across multiple columns or grouping summary rows—use Range["StartCell:EndCell"].Merge() to target any rectangular block:

# Merge horizontally from A1 to E1 (5 columns)
sheet.Range["A1:E1"].Merge()

# Merge vertically from C3 to C8 (6 rows)
sheet.Range["C3:C8"].Merge()

# Merge a 2×3 block from D4 to F6
sheet.Range["D4:F6"].Merge()
Enter fullscreen mode Exit fullscreen mode

Critical Rule: After a merge, only the value in the upper‑left cell of the range is preserved. All other values within the range are automatically discarded. Always populate data or back up the original file before merging.

5. Unmerging (Splitting Cells)

To reverse a merge, use the UnMerge() method. You can target an individual merged region, an entire row, an entire column, or all merged areas in the sheet.

Unmerge a specific merged region 

# Pass any cell that belongs to the merged region
sheet.Range["A1"].UnMerge()
Enter fullscreen mode Exit fullscreen mode

Unmerge an entire row 

sheet.Rows[0].UnMerge()
Enter fullscreen mode Exit fullscreen mode

Unmerge an entire column 

sheet.Columns[1].UnMerge()
Enter fullscreen mode Exit fullscreen mode

Unmerge all merged regions in the worksheet at once 

merged_regions = sheet.MergedCells   # Returns a list of all merged ranges
for cell_range in merged_regions:
    cell_range.UnMerge()
Enter fullscreen mode Exit fullscreen mode

6. End‑to‑End Example Script

The following script demonstrates a complete workflow: loading a file, applying three different types of merges, saving the result, and then reloading to perform targeted unmerges.

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

def merge_demo():
    # ---------- Phase 1: Load and merge ----------
    workbook = Workbook()
    workbook.LoadFromFile("OriginalReport.xlsx")
    sheet = workbook.Worksheets[0]

    # 1. Merge row 1 as the master title
    sheet.Rows[0].Merge()
    sheet.Range["A1"].Value = "National Store Sales Overview"

    # 2. Merge column B as the region header
    sheet.Columns[1].Merge()
    sheet.Range["B1"].Value = "Region"

    # 3. Merge D2:E4 as a footnote block
    sheet.Range["D2:E4"].Merge()
    sheet.Range["D2"].Value = "Note: Data excludes Hong Kong, Macao, and Taiwan"

    # Save the merged workbook
    workbook.SaveToFile("MergedResult.xlsx", ExcelVersion.Version2013)
    workbook.Dispose()

    # ---------- Phase 2: Reload and unmerge ----------
    workbook2 = Workbook()
    workbook2.LoadFromFile("MergedResult.xlsx")
    sheet2 = workbook2.Worksheets[0]

    # Unmerge the D2:E4 region
    sheet2.Range["D2"].UnMerge()

    # Unmerge the first row
    sheet2.Rows[0].UnMerge()

    # Save the final output
    workbook2.SaveToFile("UnmergedResult.xlsx", ExcelVersion.Version2013)
    workbook2.Dispose()

if __name__ == "__main__":
    merge_demo()
Enter fullscreen mode Exit fullscreen mode

After execution, MergedResult.xlsx will contain all three merged structures, while UnmergedResult.xlsx will restore the split state for the selected areas.

7. Best Practices and Important Tips

  • Data safety: Since merging deletes all values except the top‑left one, always perform merge operations before writing detailed data, or keep a backup copy of the original file.

  • Writing to merged cells: After a merge, you can only assign new values using the address of the upper‑left cell (e.g., sheet.Range["A1"].Value = ...). Writing to any other cell within the merged area will have no effect.

  • Performance: If your worksheet contains hundreds of merged regions, repeated Merge() or UnMerge() calls may slow down execution. For better performance, collect all target ranges first and apply changes in batch.

  • Conflict handling: Calling Merge() on an already‑merged range does not raise an exception, but it can lead to nested or overlapping merged structures. To avoid unintended behavior, inspect the sheet.MergedCells property beforehand and explicitly unmerge existing regions if necessary.

  • Resource cleanup: Always call Dispose() on the Workbook object after saving to release underlying system resources—especially important in long‑running automation scripts.

Top comments (0)