openpyxl messing up existing DataValidation in excel sheet

Issue #851 duplicate
Robert Harris created an issue

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 $Z$6:$AB$6, but after loading and saving the excel sheet it is erroneously changed to $Z$5:$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='$Z$12:$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

Comments (6)

  1. CharlieC

    The solution for this is going to be to have DataValidation.cells be a set or list of CellRange objects so that expand_cell_ranges doesn't create a union of the various values in sqref.

  2. Robert Harris reporter

    Thanks for the feedback Charlie! Is your recommended DataValidation.cells solution similar to the workaround suggested by Yuan Kong on #827? If not, would you mind providing some example code?

    Thanks, Robert

  3. Robert Harris reporter

    Hi Charlie,

    Sorry for the delayed response - where would I find this CellRange function to try out?

    Thanks, Robert

  4. CharlieC

    It's not a function, it's a class and primarily related to something else. It's in the worksheet package in the 2.5 branch if you you want to look at it.

  5. Log in to comment