Remove existing data validation with openpyxl

Question

I'm using openpyxl ws.add_data_validation to add some validation to a cell in Excel. However, I'm finding that if the cell has some previous validation in place then it doesn't seem to take the new validation when I subsequently open it in the Excel application. If I manually clear the previous validation from the target file before I run the program then it takes the new validation fine. My code is:

dv = DataValidation(type='list', formula="{0}!$B$2:$B$18".format(quote_sheetname('values')))
ws.add_data_validation(dv)

row = 3
while row <= ws.max_row:
    dv.add('F{}'.format(row))
    row += 1

I think I may need to remove the existing validation before doing the dv.add command but I can't see anything in the docs about removing pre-existing validation.

Does anyone know a way to do this?

There's certainly no API for this but you should be able to loop over the validations and remove the relevant ones. — Charlie Clark, Mar 23 '20 at 11:29
@yeye Afraid not. In the end I just had to add a manual task to clear the validation each time from the target file before running the script. I haven't had to use it for a while I'll spend some more time on it next time I run it and update this question if I find a resolution. Good luck with your issue - let me know if you crack it before I do. Cheers. — Miff Jacobs, May 15 '20 at 08:31

score 0 · Answer 1 · answered Jan 17 '22 at 06:08

The way I found to remove data validation:

from openpyxl.worksheet.datavalidation import DataValidationList
ws.data_validations = DataValidationList()  # ws is the worksheet object.

Note: the code above would remove all data validation from the worksheet.

score 0 · Answer 2 · answered Aug 21 '23 at 08:04

You actually don't need to wipe out all data validation. The solution, albeit indirect, is given here:

Validations without any cell ranges will be ignored when saving a workbook.

So it basically boils down to:

for dv in workbook_object[worksheet_name].data_validations.dataValidation:
    if some_condition:
        dv.sqref = ''

Remove existing data validation with openpyxl

2 Answers2