问题

我想在通过Python读取Excel文件时捕获被筛选的字段或列名。我看到我们也可以使用openpyxl仅捕获未隐藏的行，并使用hidden == False（https://stackoverflow.com/questions/46002159/how-to-import-filtered-excel-table-into-python）。在我的项目中，识别Excel文件中被筛选的字段/列非常重要。这是否可能？如何实现？

添加一个示例。

pip install openpyxl
from openpyxl import load_workbook

wb = load_workbook('test_filter_column.xlsx')
ws = wb['data']

这是未隐藏的数据

而如果性别列如下所示被筛选

所以我期望的输出应该是性别，因为它被筛选了。如果有多个字段被筛选，则期望提供所有被筛选的列名。

pip install openpyxl
from openpyxl import load_workbook

 wb = load_workbook('test_filter_column.xlsx')
 ws = wb['data'] 

答案1

得分: 5

受此帖子启发，但适应您的情况，并且考虑到存在多个筛选条件时：

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

# 加载工作簿
wb = load_workbook('/path/to/xlsx')
# 提取工作表
ws = wb['data']

# 创建一个字典以存储相关信息
filters = {}

# 获取被筛选的列的ID
filters['col_id'] = [col.col_id for col in ws.auto_filter.filterColumn]

# 获取被筛选的列的字母
# 这似乎是从1开始索引的，但get_column_letter
# 是从0开始索引的
filters['col_letter'] = [get_column_letter(col + 1) for col in filters['col_id']]

# 提取列名 - 假设它在列的第1行中给出
filters['col_name'] = [ws[f'{col}1'].value for col in filters['col_letter']]

# 获取正在使用的筛选器的值
filters['filter_values'] = [col.filters.filter for col in ws.auto_filter.filterColumn]

print(filters)

输出：

{'col_id': [3], 'col_letter': ['D'], 'col_name': ['gender'], 'filter_values': [['F']]}

我认为这涵盖了您提供的示例，并希望还显示了您可能想要的其他信息。

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

# Load workbook
wb = load_workbook('/path/to/xlsx')
# Extract sheet
ws = wb['data']

# Create a dict to store relevant info
filters = {}

# Get the ID of the columns that are filtered
filters['col_id'] = [col.col_id for col in ws.auto_filter.filterColumn]

# Get the letter of the columns that are filtered
# This appears to be one-indexed, but get_column_letter
# is zero indexed
filters['col_letter'] = [get_column_letter(col + 1) for col in filters['col_id']]

# Extract the column name - assuming that it is
# given in row 1 of the column
filters['col_name'] = [ws[f'{col}1'].value for col in filters['col_letter']]

# Get the values of the filters being used
filters['filter_values'] = [col.filters.filter for col in ws.auto_filter.filterColumn]

print(filters)

{'col_id': [3], 'col_letter': ['D'], 'col_name': ['gender'], 'filter_values': [['F']]}

答案2

得分: 0

from openpyxl import load_workbook
wb = load_workbook('hello.xlsx')
ws = wb['S1']
filter_features = []
for column in ws.iter_cols(min_col=1, max_col=ws.max_column):
    if column[0].hidden == False:
        for col in filter_features:
            print(f"{col}")

filter_features.append(column[0].column_letter)

This will help you. Moreover, if you want to find filter rows then you must see Filtered Rows

<details>
<summary>英文:</summary>



    from openpyxl import load_workbook
    wb = load_workbook(&#39;hello.xlsx&#39;)
    ws = wb[&#39;S1&#39;]
    filter_features = []
    for column in ws.iter_cols(min_col=1, max_col=ws.max_column):
        if column[0].hidden == False:
           for col in filter_features:
              print(f&quot;{col}&quot;)
           
    filter_features.append(column[0].column_letter)





This will help you. Moreover, if you want to find filter rows then you must see 
[Filtered Rows][1]


  [1]: https://stackoverflow.com/questions/63085533/python-openpyxl-how-to-iterate-through-1-rows-columns-to-find-a-value

</details>