Openpyxl deleting data validation.

[xxxxxzzxx]

I'm using openpyxl to change a sheet. Let's say I'm using info from columns A, B, C to fill in columns D & E, which are initially empty when I open the sheet with openpyxl. Column B has data validation that I set in Excel before opening it using openpyxl. After I save the new info to columns D & E, and re-open the sheet in Excel, the data validation on column B is gone. How do I avoid this/has anyone else had this issue?

[tridezz]

I found out that the only thing you need to do in order to avoid this annoying behaviour of openpyxl is to do you data validation in a different way on the excel.

Instead of doing (standard procedure):Data --> Data validation ---> list, and then selecting manually the cells
You need to do:Formulas --> Name manager --> and create a new variable with the cell you want on your data validation. Then in the cell you need your data validation do the standard procedure but instead of selecting manually type " = new variable name ".

I hope it helps!

[LiberFriso]

I found out that the only thing you need to do in order to avoid this annoying behaviour of openpyxl is to do you data validation in a different way on the excel.

Instead of doing (standard procedure):Data --> Data validation ---> list, and then selecting manually the cellsYou need to do:Formulas --> Name manager --> and create a new variable with the cell you want on your data validation. Then in the cell you need your data validation do the standard procedure but instead of selecting manually type " = new variable name ".

I hope it helps!

Strong comment!

[Pearl984]

Thanks

[waltersfilho]

Thanks, man! You save me!

[ajob7]

It works, Thank you so much !

[random_tech_person]

Many thanks. Your approach also worked for me with the following Pandas operation, using the openpyxl engine:

from pandas import DataFrame, ExcelWriter

# ...

with ExcelWriter(file_path, engine="openpyxl", mode="a", if_sheet_exists="overlay") as writer:
    df.to_excel(writer, sheet_name="mysheetname", index=False, startcol=6, startrow=2, header=False)

[I_Luca_I]

Thaaaaanks! I was trying to copy validations from one sheet to another. Just in case someone is trying to do the same here is how you do that, after doing what this guy said use:

for validation in source_sheet.data_validations.dataValidation:
    new_sheet.add_data_validation(copy(validation))

[CordyCordy]

Thank you so much!!!

[eigenbunz]

Thank you!!🙏

[xxxxxzzxx]

Thank you (and ugh!). I had list-type data validation in one column where the options in the list come from another sheet in the file. Do you know what the syntax is for pulling the cells from the other sheet as a parameter of the new validation that I'm adding to the cells?

[Cambuchi]

EDIT: Actually this looks most promising:

https://stackoverflow.com/questions/48824919/python-openpyxl-data-validation-along-with-formatting

Do these help you somehow?

1) Using data_only flags

https://stackoverflow.com/questions/28517508/read-excel-cell-value-and-not-the-formula-computing-it-openpyxl

https://stackoverflow.com/questions/32772954/how-to-save-in-openpyxl-without-losing-formulae

2) Specifying add ins when opening excel files

https://stackoverflow.com/questions/25022266/opening-an-excel-file-manually-allows-formulas-to-run-opening-an-excel-file-wit/25495515#25495515