[Python] Excel Data Validation in Python

I wanted to create a dropdown list in my excel sheet using python. The source is inside another sheet but inside the same file. Currently, I'm using this method to do the validation.


def add_data_validation(sheet, dropdown_sheet):
end_use_validation = DataValidation(type="list", formula1='dropdown!$A$1:$A$3', showDropDown=True)
status_validation = DataValidation(type="list", formula1='dropdown!$B$1:$B$3', showDropDown=True)
result_validation = DataValidation(type="list", formula1='dropdown!$C$1:$C$3', showDropDown=True)

for row in range(2, sheet.max_row + 1):
end_use_cell = sheet['V' + str(row)]
status_cell = sheet['W' + str(row)]
result_cell = sheet['X' + str(row)]

end_use_validation.add(end_use_cell)
status_validation.add(status_cell)
result_validation.add(result_cell)

sheet.add_data_validation(end_use_validation)
sheet.add_data_validation(status_validation)
sheet.add_data_validation(result_validation)


Excel error

However, when I run the program and check the result in my excel sheet, there is no dropdown list. I noticed that I have to select Ignore blank and In-cell dropdown in order to show the dropdown list.

Data Validation

How do I do that inside my python code? Thanks in advance.

Currently, the problem can only be solved when I go inside my excel and check those two boxes manually.

Continue reading...