openpyxl messing up existing DataValidation in excel sheet
Created originally on Bitbucket by acemcawesome (Robert Harris)
Hi All,
I am importing an existing excel sheet using openpyxl and attempting to fill in some values and resave, but I noticed that the existing Data Validation dropdown boxes in the spreadsheet get messed up when doing so. This happens even when just loading the spreadsheet, doing no other operations, and then resaving. I have attached an example spreadsheet on Dropbox below. For example cell C13 has an initial Data Validation "source" of $Z6:
AB$6, but after loading and saving the excel sheet it is erroneously changed to $Z5:
AB$5 for that cell. Here is the sheet, and simple code that causes the error:
https://www.dropbox.com/s/sbw07makodkg7oo/DataValidationErrorExample.XLSX?dl=0
wb = load_workbook(filename='DataValidationErrorExample.xlsx') wb.save('DataValidationErrorExample.xlsx')
After some trial and error, I have figured out that this happens when cells in two or more rows from the same column take their data validation from the same cell. The error seems to be happening on the import of the worksheet; for example, when this worksheet is imported, the first line of a print of the read data validations
ws = wb.worksheets[0] print(ws.data_validations)
yields:
showErrorMessage=True, showDropDown=None, showInputMessage=True, allowBlank=False, errorTitle=None, error=None, promptTitle=None, prompt=None, type='list', errorStyle=None, imeMode=None, operator=None, sqref='C15:C28 D15:D28 E15:E28 F15:F28 G15:G28 H15:H28 I15 J15 K15', formula1='$Z12:
AB$12', formula2=None, <openpyxl.worksheet.datavalidation.DataValidation object>
where you can see that rather than both cells C15 and C28 having the same data validation, it is erroneously reading that as all cells BETWEEN C15 and C28. I believe all subsequent issues stem from there. (you can see the I15, J15, K15 rows came out fine because they do not share data validation with another cell in the same column).
Either I am doing something wrong, or there is a bug in the way openpyxl reads in existing data validation. If anyone is able to help with this, it would be very much appreciated!
Thanks, Robert
Attachments: DataValidationErrorExample.XLSX