How to organize excel file by python?

I wrote a program to open an excel file and try to get 3 column in it. after running the program unfortunately It is not exactly what should it be. If I show you the pictures you will understand what I am talking about.

This is the original excel file:

enter image description here

After running the program the excel file is something like this:

enter image description here

in the above photo we have some lines which does not have any column. It is stupid. because if I click merging the data will come back! I don’t want to be like about. my purpose is something like below:

enter image description here

This is my code:

from tkinter import * 
from tkinter.ttk import *
from tkinter.filedialog import askopenfile
import openpyxl

excel_file = openpyxl.load_workbook('EX (5).xlsx')

excel_sheet = excel_file['Recovered_Sheet1']
# delete row
excel_sheet.delete_rows(idx=1, amount=12)
# delete column 
excel_sheet.delete_cols(idx=1, amount=2)
excel_sheet.delete_cols(idx=2, amount=2)
excel_sheet.delete_cols(idx=3, amount=1)
excel_sheet.delete_cols(idx=4, amount=4)

sh = excel_file.active
for row in range(1,300):
    if (sh.cell(row=row,column=2).value) == None:
        sh.delete_rows(idx=row)
    if (sh.cell(row=row,column=1).value) == "Total:":
        sh.delete_rows(idx=row)

# to save the file
excel_file.save('Edited.xlsx')

Answer

Interesting. This is arguably a bug in openpyxl. Because you deleted rows 1 and 3, each of which have their first 4 columns merged, you would expect them to become unmerged, but they don’t.

You can either explicitly delete the 2 groups of merged cells (before deleting the rows), or simply unmerge all merged cells.

# Unmerge all merged cells in the sheet
sh.merged_cells = openpyxl.worksheet.cell_range.MultiCellRange()

# Or unmerged those 2 merged cells individually (before deleting the rows)
sh.unmerge_cells(start_row=1, start_column=1, end_row=1, end_column=4)
sh.unmerge_cells(start_row=3, start_column=1, end_row=3, end_column=4)