使用pandas从Excel读取下拉框的值

Question

我有一个带下拉框的Excel表格。我一直在尝试读取Excel下拉列表，但它只能读取所选选项。

请查看以下图片进行更好理解：

import pandas

df = pandas.read_excel("BQA.xlsx", header=0)
df.columns = df.columns.str.strip()

print(df)

输出：

 Empty DataFrame
 Columns: [Column 1, Column 2, Column 3, Column 4, yes]
 Index: []

预期输出:

Empty DataFrame
Columns: [Column 1, Column 2, Column 3, Column 4, [yes, no, yes1, no1]]
Index: []

- ReKx

---

这是一个有趣的问题，希望有人能回答。我的直觉告诉我，在 Python 中可能没有 xlrd 等库中提供该功能。在 .NET 中，使用 excel-interop 可以实现单元格验证属性。参见：https://dev59.com/PoPba4cB1Zd3GeqPnhYA 。还有一种可能无果的建议是看看 xlwings。 - Dickster

将列表存储在单元格中通常是一个不好的想法，但在列中使用它真的很难处理。你会接受更合理的存储方法吗？ - DSM

好的，请给我一个方法，让我能够读取该单元格的所有选项。 - ReKx

@ReKx 不确定您是否仍然关心这个问题，但我的做法是将选项列表存储在字典中，然后将选项的键存储在数据框中。 - Nathan Tew

Answer 1

你可以使用 openpyxl 来提取下拉列表的信息：它存储在给定工作表的 data_validations 中。例如（为了易于阅读，插入了换行符）：

>>> wb = openpyxl.load_workbook("dropdown.xlsx")
>>> ws = wb["Sheet1"]
>>> ws.data_validations
<openpyxl.worksheet.datavalidation.DataValidationList object>
Parameters:
disablePrompts=None, xWindow=None, yWindow=None, count=1, 
dataValidation=[<openpyxl.worksheet.datavalidation.DataValidation object>
Parameters:
sqref=<MultiCellRange [E1]>, showErrorMessage=True, showDropDown=None, showInputMessage=True, 
allowBlank=False, errorTitle=None, error=None, promptTitle=None, prompt=None,
type='list', errorStyle=None, imeMode=None, operator=None, formula1='$L$4:$L$7', formula2=None]

我不会处理所有可能的情况，所以这只是一些你可能要做的事情的示例，但是类似于这样的内容：

def read_with_dropdown(book_name, sheet_name, range_str):
    wb = openpyxl.load_workbook(book_name)
    ws = wb[sheet_name]
    data = [[cell.value for cell in row] for row in ws[range_str]]

    validations = ws.data_validations.dataValidation
    for validation in validations:
        ranges = validation.sqref.ranges
        if len(ranges) != 1:
            raise NotImplementedError
        if validation.type == 'list':
            list_cells = ws[validation.formula1]
            values = [cell.value for cell_row in list_cells for cell in cell_row]
        else:
            raise NotImplementedError
        bounds = ranges[0].bounds
        try:
            data[bounds[1]-1][bounds[0]-1] = values
        except IndexError:
            pass
    return data

给我（再次插入换行符）：

>>> data = read_with_dropdown("dropdown.xlsx", "Sheet1", "A1:E5")
>>> data
[['Column 1', 'Column 2', 'Column 3', 'Column 4', ['yes', 'no', 'yes1', 'no1']],
 [None, None, None, None, None],
 [None, None, None, None, None],
 [None, None, None, None, None],
 [None, None, None, None, None]]

- DSM

---

我遇到了一个错误： ----> ranges = validation.sqref.ranges AttributeError: 'str'对象没有属性'ranges' - ReKx

非常好的答案，对我有用。只是不得不删除你的第一个 if, else 语句，因为我的表格中有多个范围，其中有下拉菜单。 - Nathan Tew

在我的情况下，所有“列表”类型的验证都有像“name6”，“name8”，“name19”等这样的“formula1”。 - m_ocean

没关系！我已经弄清楚了，它们是定义名称。在这种情况下，它们定义了范围。 - m_ocean