CODEWITHSUNDEEP
pythonvalidationopenpyxl
Michael Kessler
Michael Kessler

Reputation: 83

Python - openpyxl - Data Validation - DV being removed when opening xlsx

For an existing DataFrame with headers I am trying to add Data Validations:

import openpyxl

wb = Workbook()
ws = wb.active

valid_options = '"Yes, No, Maybe"'
dv = DataValidation(type="list", formula1=valid_options, allow_blank=True)
dv.ranges.add("K2:K150")

ws.add_data_validation(dv)

dv.add(ws["k2"])

wb.save("example.xlsx")

When opening the resulting Excel spreadsheet, I get the error: "We found a problem with some content in 'example.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes."

When I click Yes it opens the workbook but has removed my data validation.

Upvotes: 1

Views: 361

Answers (1)

Michael Kessler
Michael Kessler

Reputation: 83

The issue is the character limit. Openpyxl's data validation has a character limit of 256. I know my example of Yes, No, Maybe is under 256 but the actual data I was working with had a dropdown character length of 450.

Upvotes: -2

Related Questions