英文:
Excel Data Validation in Python
问题
我想在我的Excel表格中使用Python创建一个下拉列表。数据源位于同一文件的另一个工作表中。
目前，我正在使用以下方法进行验证：
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表格中检查结果时，没有下拉列表。我注意到我必须选择"忽略空白"和"单元格内下拉"才能显示下拉列表。
我如何在我的Python代码中实现这两个选项？在此提前感谢您。
目前，只有当我进入我的Excel并手动勾选这两个框时，才能解决这个问题。
英文:
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.
答案1
得分: 0
从您的标签中看，您正在使用pandas。但是我看到DataValidation可在openpyxl库中使用。请分享您从哪里导入了DataValidation。
如果是来自openpyxl，它接受两个参数来启用那些复选框。showDropdown您已经启用了。allowBlank用于启用另一个复选框。
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)
所以您的代码将变成：
end_use_validation = DataValidation(type="list", formula1='dropdown!$A$1:$A$3', showDropDown=True, allowBlank=True)
参考链接：https://openpyxl.readthedocs.io/en/latest/api/openpyxl.worksheet.datavalidation.html?highlight=DataValidation#module-openpyxl.worksheet.datavalidation
英文:
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)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论