Openpyxl || Merge/Unmerge excel cells and assign value to unmerged cells.

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.

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.

Output: