CODEWITHSUNDEEP
python-3.xopenpyxl
babsdoc
babsdoc

Reputation: 749

Python Openpyxl Apply Filter and delete all visible rows

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: enter image description here

After I apply the filter: enter image description here

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.

Upvotes: 4

Views: 2531

Answers (1)

Charlie Clark
Charlie Clark

Reputation: 19527

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.

Upvotes: 3

Related Questions