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.

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)

Ref: https://openpyxl.readthedocs.io/en/latest/api/openpyxl.worksheet.datavalidation.html?highlight=DataValidation#module-openpyxl.worksheet.datavalidation