Openpyxl:创建的Excel文件没有显示验证错误或提示消息

Question

我正在使用openpyxl生成一个excel文件并添加一个列表作为验证。我成功地创建了下拉列表，但是当我在已验证列中添加无效数据时，它没有显示任何错误或提示，即使我在代码中指定了它，这里是我的代码直接从官方文档中获取:

from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation
# Create the workbook and worksheet we'll be working with
wb = Workbook()
ws = wb.active
#Create a data-validation object with list validation
dv = DataValidation(type="list", formula1='"Dog,Cat,Bat"', allow_blank=True)
# Optionally set a custom error message
dv.error ='Your entry is not in the list'
dv.errorTitle = 'Invalid Entry'
# Optionally set a custom prompt message
dv.prompt = 'Please select from the list'
dv.promptTitle = 'List Selection'
# Add the data-validation object to the worksheet
ws.add_data_validation(dv)
# Create some cells, and add them to the data-validation object
c1 = ws["A1"]
c1.value = "Dog"
dv.add(c1)
c2 = ws["A2"]
c2.value = "An invalid value"
dv.add(c2)
# Or, apply the validation to a range of cells
dv.add('B1:B1048576') # adding validation for the whole column
wb.save("demo.xlsx")

然而，当我在excel文件中看到数据验证选项时，我看到对于给定的列，使用列表进行数据验证是存在的，但是当单元格被选中时显示输入消息。并"显示无效数据的错误信息";复选框未选中，一旦我手动检查这些数据验证开始按预期工作，有任何方法来做这个编程??

Answer 1

正如@Charlie Clark指出的那样，你必须显式地将它们设置为true。您需要将以下内容添加到您的代码

from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation
# Create the workbook and worksheet we'll be working with
wb = Workbook()
ws = wb.active
#Create a data-validation object with list validation
dv = DataValidation(type="list", formula1='"Dog,Cat,Bat"', allow_blank=True)
# Optionally set a custom error message
dv.error ='Your entry is not in the list'
dv.errorTitle = 'Invalid Entry'
# Optionally set a custom prompt message
dv.prompt = 'Please select from the list'
dv.promptTitle = 'List Selection'
# Add the data-validation object to the worksheet
ws.add_data_validation(dv)
# Create some cells, and add them to the data-validation object
c1 = ws["A1"]
c1.value = "Dog"
dv.add(c1)
c2 = ws["A2"]
c2.value = "An invalid value"
dv.add(c2)
dv.showInputMessage = True
dv.showErrorMessage = True
# Or, apply the validation to a range of cells
dv.add('B1:B1048576') # adding validation for the whole column
wb.save("demo.xlsx")