how to delete columns by column name from an excel using python openpyxl

user_8513242 0
Viewed 4770

I have an Excel worksheet from which I want to delete certain columns based on their column names using python openpyxl as the column positions aren't fixed.

Their indices can change every time i get a new report, but the column name to be deleted remains the same each time. In the below example i want to delete columns if their names are equal to = ["To be determined","No Value","Total"] enter image description here

I have tried fetching the column index number so i can delete those using index value, but it isn't working as expected. enter image description here where max_file is the excel file path and sh2 is sheet2 containing the data

columnname openpyxl python-3.x python
3 Answers

Have you tried using

sh2.delete_cols(2,4)

2 is the starting column and 4 is the number of columns.

user_82390470

Found a solution to retrieve the column number for the required column names: enter image description here

user_85132420

Function to delete multiple columns by column name

# Even if your cell names are not like 'B12' instead only 'B'. That also can be done just let me know in the comment
list_of_cell_names_need_to_be_deleted = ['B1', 'C2', 'E7', 'A5']

def delete_columns_by_name(list_of_cell_names_need_to_be_deleted):

    for i in list_of_cell_names_need_to_be_deleted:

        # For the first iteration first it splits 'B1' to ('B',1) and stores 'B' to the assigned variable below
        cell_name_only_alpha = openpyxl.utils.cell.coordinate_from_string(i)[0]

        # After splitting it, we will store the index of the letter exctrated ( here for first iteration it will be 'B' and hence index will be 2 )
        index_value = openpyxl.utils.cell.column_index_from_string(cell_name_only_alpha)
        
        # Now let's delete the colu
        # If you don't know what is ws, here is a brief information
        # wb - Used to mention the name of the file.
        # ws - Used to mention the sheet name of the file. (There may be more than one sheet within an excel file)
        # For more info : https://openpyxl.readthedocs.io/en/stable/tutorial.html
        ws.delete_cols(index_value,1)



delete_columns_by_name(list_of_cell_names_need_to_be_deleted)

# NOTE : At the end don't forget to save the file
# Code to save the file : wb.save(name_of_file_here)

wb.save(name_of_file_here)
user_157042860
Related