'openpyxl, disable autofilter
I have a function that uses openpyxl, which filters on a column and deletes everything after the filter is applied, and creates a new file. However the "filter" in the excel file still exists, which doesnt really effect anything its just annoying to see. Does anyone know how to turn this off?
This question was also asked but looks like was never answered: How to disable autofilter in openpyxl tables?
code:
def prepareSpreadsheetToAttach(thePerson,
filter_on="Name",
path_to_file=atpAttachFile,
sheet_to_process='Tracker'): # @@
# Set the full filepath of the temp file to create
if 'John Smith' in thePerson:
filename='ATP - John Smith.xlsx'
else:
filename = 'ATP - ' + thePerson + '.xlsx'
plOutfile = Temp_Dir / filename
wb = load_workbook(path_to_file)
# Get a reference to the sheet
ws = wb[sheet_to_process]
# Identifying column to filter on. Assume header is on 1st row
for cell in ws[1]:
if cell.value == filter_on:
break
else:
print(f'ERROR: could not find the column header "{filter_on}"')
return None
filter_col = cell.column
# Identify rows to DELETE by testing col {filter_col} against criteria
delete_these = []
for i,r in enumerate(ws.iter_rows()):
if i==0:
continue # Of course, do not delete the header row
rownum = i + 1 # when we use it, must by 1-indexed like Excel
val = r[filter_col - 1].value
if str(val) not in thePerson:
delete_these.append(rownum)
# Deleting the rows
# Must reverse the order, because as you delete the following rows have their
# numbers dropped by 1
delete_these.reverse()
for x in delete_these:
ws.delete_rows(x)
# Save as Excel
wb.save(plOutfile)
# Finally, return the path
return plOutfile
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|

