Python学习笔记——openpyxl读取工作表的数据有效性设置信息

今天在用openpyxl读取表格信息时，需要根据表格中单元格的数据有效性设置中的序列内容来进行下一步操作判断。但是对于如何获取该序列的字符串值一点也不了解，网上查找资料只找到用DataValidation模块可以给单元格设置数据有效性验证，但对于表格中原有的单元格数据有效性设置信息的获取方法却没发现有介绍。于是自己打开openpyxl模块源文件进行研究，颇为费了一番功夫后终于解决了这个问题。

比如，现在有一个名为test.xlsx的文件，其中A1至A5共5个单元格设置了数据有效性验证，限定输入数据为“足球，篮球，排球”这个序列中的三种球类的名称。

 想要读取哪些单元格设置了什么样的限定序列可以用如下代码：

import openpyxl

wb = openpyxl.open('test.xlsx')
ws = wb.active
print(ws['A1'].value)

# 输出：足球

print(ws.data_validations.dataValidation[0].formula1)

# 输出："足球,篮球,排球"。

print(type(ws.data_validations.dataValidation[0].sqref))

# 输出：<class 'openpyxl.worksheet.cell_range.MultiCellRange'>

print(ws.data_validations.dataValidation[0].sqref)

# 输出：A1:A5

可见，能获取到设置数据有效性验证的单元格为A1:A5，数据限定序列为“足球,篮球,排球”。

细心的朋友可能会发现：ws.data_validations.dataValidation[0]是Python中序列的索引引用呐。
事实上，ws.data_validations.dataValidation就是一个列表一样的对象，保存了该工作表中所有的数据有效性验证的种类及其涉及的单元格。

如果test.xlsx中A1至A5单元格设置了数据有效性验证限定数据为“足球，篮球，排球”这个序列，同时B1至B5单元格设置了数据有效性验证限定数据为“甲，乙，丙”这个序列。

那么，此时：

print(ws.data_validations.dataValidation[0].formula1)

# 输出："足球,篮球,排球"。

print(ws.data_validations.dataValidation[0].sqref)

# 输出：A1:A5

print(ws.data_validations.dataValidation[1].formula1)

# 输出："甲,乙,丙"。

print(ws.data_validations.dataValidation[1].sqref)

# 输出：B1:B5

补充：上述可直接读出序列内容的情况限于在设置数据有效性时，是直接输入序列。如下图

 如果在设置数据有效性时是通过指定表格中某一区域内单元格的内容作为序列的，如下图：

 

 这时再用前面的代码就不能直接得到"足球,篮球,排球"这样的结果了，而是会输出$G$1:$G$3。想要得到序列就需要加工一下。如下代码可以实现自动判断属于哪种情况，并将读出的序列存放到列表变量中：

import openpyxl
from openpyxl.utils.cell import coordinate_from_string, column_index_from_string

wb = openpyxl.open('test.xlsx')
ws = wb.active
a_list = []
for item in ws.data_validations.dataValidation:
    if 'A1' in item.sqref:
        if '$' in item.formula1:
            li = item.formula1.replace('$', '').split(':')
            col1, r1 = coordinate_from_string(li[0])
            col2, r2 = coordinate_from_string(li[1])
            c1 = column_index_from_string(col1)
            c2 = column_index_from_string(col2)
            for i in range(r1, r2+1):
                for j in range(c1, c2+1):
                    a_list.append(ws.cell(i, j).value)
        else:
            a_list = item.formula1.replace('\"', '').split(',')
        break
wb.close()
print('"A1"单元格的数据有效性设置序列列表为：\n', a_list)

输出结果：

"A1"单元格的数据有效性设置序列列表为：
 ['足球', '篮球', '排球']