Excel数据验证之选择下拉列表小思路

最近遇到了一个商品信息填写问题，需求是对如下图所示从平台上自动导出的数据表格模板进行填写，列表选项未知，想选择第一个数据项要怎么操作呢？这就来唠一唠数据验证那些事儿~

一、数据验证

数据验证是一种Excel功能，可用于确保在工作表中输入的数据符合特定的规则和条件。它可以帮助减少数据输入错误，并提高数据的准确性和一致性。下面是一些常见的Excel数据验证方法：

1. 数据有效性（Data Validation）：数据有效性是Excel中用于限制用户输入的一种工具。通过定义一组规则，例如数字范围、日期格式、文本长度等，并将其应用于单元格或单元格范围。这样，在输入数据时，若不符合规则则显示错误消息。
2. 列表（List）：列表数据验证允许单元格创建一个下拉列表，用户可以从中选择预定义的选项。这对于限制输入值的范围或提供预先定义的选项非常有用。
3. 自定义公式（Custom Formula）：使用自定义公式数据验证，通过编写自己的公式来验证数据。
4. 错误警告（Error Alert）：数据验证允许用户定义错误警告消息。当用户输入不符合验证规则时，Excel可以显示一条警告消息。

二、设置数据下拉列表

1.指令版

2.编码版

DataValidation对象，可以对单个单元格或单元格范围应用数据验证，并定义验证规则、输入消息和错误消息。

def set_data_validation():
    # 加载文件
    workbook = load_workbook(r"C:\Users\moli\Desktop\工作日记\测试包\测试测试再测试.xlsx")  

    # 选择操作工作表
    worksheet = workbook['Sheet1']  

    # 设置数据验证区域
    cell_column = 'A'  
    for cell_row in range(2,100):
        # 设置下拉数据源的数据验证规则
        data_validation = f'"男,女"' 
        # 将数据验证规则应用于指定的空单元格
        data_val = DataValidation(type="list", formula1=data_validation)
        worksheet.add_data_validation(data_val)
        data_val.add(worksheet[cell_column + str(cell_row)])

    # 保存文件
    workbook.save(r"C:\Users\moli\Desktop\工作日记\测试包\测试测试再测试.xlsx")

三、获取下拉列表数据项

1.快捷键选择法

缺点：效率低，需提前知道每个选项的位置

选中区域->键盘操作ctrl+alt+↓+↓+enter选择第一个下拉框中的选项

2.DataValidation对象获取验证规则

validation.sqref 属性是一个字符串，表示适用于数据验证规则的单元格范围。

Formula1和 Formula2 属性：用于设置或返回验证规则的条件。这些属性根据验证类型的不同而有所不同。包括字符串、公式、引用单元格等。

def read_with_dropdown(book_name, sheet_name):
    # 读取excel
    wb = openpyxl.load_workbook(book_name)
    # 读取sheet表
    ws = wb[sheet_name]
    # 获取内容存在下拉选的框数据
    validations = ws.data_validations.dataValidation
    list = []
    # 遍历存在下拉选的单元格
    for validation in validations:
        # 获取下拉框中的所有选择值
        cell = validation.sqref
        result = validation.formula1
        dict = {"单元格位置":str(cell),"下拉内容":result.replace("\"", "")}
        print(dict)
        list.append(dict)
    return list

输出结果如下：

拿到返回的数据稍微处理一下，就可以获取每列的验证规则以及数据项啦~