如何使用Python检查Excel工作表中的筛选并清除它们？

Question

我想知道怎么用Python检查Excel表格中的某一列是否有过滤器，然后再清除这个过滤器。

Answer 1

下面的代码可以正常工作：

import xlwings as xw

wb = xw.Book('foo.xlsx')
ws = wb.sheets('Sheet1')    

def remove_filter():
  if ws.api.AutoFilter:
       print("yes")
       ws.api.AutoFilter.ShowAllData()
  return None

Answer 2

假设你说的是自动筛选功能，那么在一个工作表上只能有一个自动筛选...

Openpyxl 的用法大概如下就可以了;

import openpyxl


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

if ws.auto_filter:
    print(f'Sheet has autofilter in range {ws.auto_filter.ref}')
    ws.auto_filter.ref = None

wb.save('foo.xlsx')

Xlwings 你可以使用以下代码;

import xlwings as xw

with xw.App(visible=False) as app:
    wb = xw.Book('foo.xlsx')
    ws = wb.sheets('Sheet1')

    if ws.api.AutoFilter:
        print(f'Sheet has autofilter in range {ws.api.AutoFilter.Range.Address}')
        ws.api.AutoFilterMode = False

    wb.save('foo.xlsx')

Win32com 的用法和 Xlwings 类似;

import win32com.client as win32

xl = win32.gencache.EnsureDispatch("Excel.Application")
xl.Visible = False

wb = xl.Workbooks.Open('<path>/foo.xlsx')

ws = wb.Sheets('Sheet1')
print()
if ws.AutoFilterMode:
    print(f'Sheet has autofilter in range {ws.AutoFilter.Range.Address}')
    ws.AutoFilterMode = False

wb.Save()
# wb.SaveAs('<path>/foo2.xlsx')  # Or save as another workbook