CODEWITHSUNDEEP
pythonindexingconditional-statementsrowsopenpyxl
propod
propod

Reputation: 23

How to delete specific rows in excel with openpyxl python if condition is met

Using openpyxl I am creating python script that will loop through the rows of data and find rows in which some of the column are empty - these will be deleted. The range of rows is 3 to 1800.

I am not excatly sure how to delete these row - please see code I have come up with so far. What I was trying to achieve is to iterate through the rows and check if columns 4, 7 values are set to None. If True I wanted to return row number into suitable collection (need advise which one would be best for this) and then create another loop that would delete specific row number reversed as I don't want change the structure of the file by deleting rows with data.

I believe there may be easier function to do this but could not find this particular answer.

for i in worksheet.iter_rows(min_row=3, max_row=1800):
emp_id = i[4]
full_name = i[7]
if emp_id.value == None and full_name.value == None:
print(worksheet[i].rows)
rows_to_delete.append(i)

Upvotes: 2

Views: 8815

Answers (1)

Eric Duanmu
Eric Duanmu

Reputation: 91

Your iteration looks good.

OpenPyXl offers worksheet.delete_rows(idx, amt=1) where idx is the index of the first row to delete (1-based) and amt is the amount of rows to delete beyond that index.

So worksheet.delete_rows(3, 4) will delete rows 3, 4, 5, and 6 and worksheet.delete_rows(2, 1) will just delete row 2.

In your case, you'd probably want to do something like worksheet.delete_rows(i, 1).

Upvotes: 3

Related Questions