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.
Below is the snapshot of the input sheet.We are going to merge red outlined cells.
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.
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.
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.