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)
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.
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.
From your tags, it looks like you are using panda. But I see DataValidation is available in openpyxl library. Please share from where you import DataValidation.
If it is from openpyxl, it takes in 2 arguments that can enable those checkboxes. showDropdown which you have already enabled. allowBlank to enable the other checkbox.
DataValidation(type=None, formula1=None, formula2=None, showErrorMessage=False, showInputMessage=False, showDropDown=False, allowBlank=False, sqref=(), promptTitle=None, errorStyle=None, error=None, prompt=None, errorTitle=None, imeMode=None, operator=None, allow_blank=False)
So your code will become:
end_use_validation = DataValidation(type="list", formula1='dropdown!$A$1:$A$3', showDropDown=True, allowBlank=True)