delete all rows from first 3 columns of a worksheet (keeping the row with column names) using openpyxl

Question

I am relatively new to Python. I am unable to figure out how to delete all rows (without deleting the row with column names) of specific columns with openpyxl.

I have tried the below code but it is deleting all rows of all columns in the sheet.

wb = xl.load_workbook('test.xlsx')
ws = wb['Sheet1']

for row in ws.iter_rows(min_row=1, max_row=sheet.max_row, min_col=1, max_col=3):
       ws.delete_rows(row[1].row, ws.max_row - 1)

Answer 1

From what I have understand, you want to delete the first 3 cells in each row, you have to delete the column names. Or you could set them to none. I will show you both ways. Let's say the dataframe is df:

df.iloc[:,[1,2,3]]=None #you can use np.nan or 0 too

Now if you want to drop all the 3 columns, with the names:

df = df.iloc[: , 3:]

I hope this solves your problem!

Answer 2

Possibly easiest to just delete the columns and re-insert then re-add the Column Headers. Maybe an issue is the Headers formatting as this would also need to be re-applied. The example below will do as mentioned and includes some standard font formatting so font name, size, colour etc can be applied to the header names. Obviously change to suit or remove if no format is needed.

import openpyxl
from openpyxl.styles import Font


del_start_col = 1
del_num_cols = 3

wb = openpyxl.load_workbook('Book1.xlsx')
ws = wb['Sheet1']

col_headings = [c.value for c in next(ws.iter_rows(min_row=1, max_row=1))]

ws.delete_cols(del_start_col, del_num_cols)
ws.insert_cols(del_start_col, amount = del_num_cols)

for i in range(del_start_col, del_start_col + del_num_cols):
    ws.cell(row=1, column=i).value = col_headings[i - 1]
    ws.cell(row=1, column=i).font = Font(name='Calibri',
                                         size=12,
                                         bold=True,
                                         italic=False,
                                         vertAlign=None,
                                         underline='none',
                                         strike=False,
                                         color='000000')
wb.save(path)