Merge/Unmerge Cells
In this example I am going to show you how to merge and unmerge cells in excel sheet using Python script. You can merge two or more columns in a single row or multiple rows in excel file.
For merging cells you need to specify the range of rows and columns in the excel sheet. So, you need to specify from which row to which row and from which column to which column you want to merge. Rows and columns are 1 based index.
Remember when you merge multiple cells, the contents of only one cell (the upper-left cell for left-to-right languages, or the upper-right cell for right-to-left languages) appear in the merged cell. The contents of the other cells that you merge are deleted.
You can also unmerge cells which were previously merged in an excel sheet (file). For unmerging cells you also need to specify a range of rows and columns.
Prerequisites
Python 3.9.1, openpyxl 3.0.10 (pip install openpyxl)
Merge Cells
Here I am going to discuss about merging cells in excel sheet. Let’s say I have the following data in the excel file:
I am going to merge cells or columns B and C to distribute the text “This is a merge test” over two columns.
The following code snippet will do the merging job:
ws.merge_cells('B2:C2')Or even you can specify row and column indices to merge your column data:
ws.merge_cells(start_row=2, start_column=2, end_row=2, end_column=3)The both lines of above code will produce the same output as shown in the below image:
You can also merge multiple rows and columns of excel sheet. For example, the following line of code will merge second and third rows/columns:
ws.merge_cells('B2:C3')Or the following line will produce the same output:
ws.merge_cells(start_row=2, start_column=2, end_row=3, end_column=3)The output will be as shown in the following image:
Unmerge Cells
Now I am going to show you how to unmerge cells in excel sheet. Now I will unmerge the merged cells from the above example.
To unmerge columns or cells, you can use the following line code:
ws.unmerge_cells('B2:C3')Or
ws.unmerge_cells(start_row=2, start_column=2, end_row=3, end_column=3)provided that you have merged second and third rows/columns or change accordingly. The unmerge functionality will produce the text in the second row and third column.