Python Openpyxl Apply Filter and delete all visible rows

Question

I am trying to achieve the following using OpenpyXL. I need to apply a filter based on a condition to my sheet and then delete all visible rows at once.

Initial State:

After I apply the filter:

I now need to delete these 2 rows which are visible after the filter is applied.

May be something like:

ws.auto_filter.ref = "A1:C6"
ws.auto_filter.add_filter_column(3, ["1"])
ws.visiblecells.delete.entirerow

Any help is appreciated.

Answer 1

You can't do this in openpyxl: the filter is applied by Excel based on the definition of the filter you create in openpyxl. In fact, when it filters, Excel hides the rows and adjusts the formatting.

If you want to filter the data in Python then you will need to write your own code. The following example should help.

rows_to_delete = []
for row in ws.iter_rows(min_col=3, max_col=3, min_row=2):
    cell = row[0]
    if cell.value == 1:
         rows_to_delete.append(cell.row)

for row in reversed(rows_to_delete): # always delete from the bottom of the sheet
    ws.delete_rows(row)

For more complicated filtering, you might want to load the cells into a Pandas dataframe, filter there, and write the results back into the worksheet.