In this tutorial, we will learn how to merge/unmerge cells in an excel file and assign the value to unmerged cells in an excel sheet using the openpyxl module in python. In openpyxl, we will use the merge_cells() built-in function to merge cells and unmerge_cells() to unmerge the cells.Before jumping into the coding section download the sample excel file by clicking the download sample file button or you can use your document as well.
Merge:
Below is the snapshot of the input sheet.We are going to merge red outlined cells.
Source Code:
import openpyxl
from openpyxl.styles import Alignment
wbook=openpyxl.load_workbook("openpyxl_merge_unmerge.xlsx")
sheet=wbook["merge_sample"]
data=sheet['B4'].value
sheet.merge_cells('B4:E4')
sheet['B4']=data
sheet['B4'].alignment = Alignment(horizontal='center')
wbook.save("openpyxl_merge_unmerge.xlsx")
exit()
Now save the above program and give a name merge_openpyxl.py then execute the file as shown below.
Output:
Unmerge:
Below is the snapshot of the excel sheet on which we are going to perform an unmerged operation. Also, we will assign the value to unmerged cells as well.
![](http://thequickblog.com/wp-content/uploads/2020/06/image.png)
Source code:
import openpyxl
from openpyxl.utils import range_boundaries
wbook=openpyxl.load_workbook("openpyxl_merge_unmerge.xlsx")
sheet=wbook["unmerge_sample"]
for cell_group in sheet.merged_cells.ranges:
min_col, min_row, max_col, max_row = range_boundaries(str(cell_group))
top_left_cell_value = sheet.cell(row=min_row, column=min_col).value
sheet.unmerge_cells(str(cell_group))
for row in sheet.iter_rows(min_col=min_col, min_row=min_row, max_col=max_col, max_row=max_row):
for cell in row:
cell.value = top_left_cell_value
wbook.save("openpyxl_merge_unmerge.xlsx")
exit()
Now save the above program and give a name unmerge_openpyxl.py and execute the program as shown below.