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, so it seems to be something intrinsic with openpyxl. The Data Validation in the spreadsheet consists of dropboxes in certain cells which take their possible values from a set of other cells about 20 columns over (this is to generate QC reports for a company). 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. Strangely, this is not consistent across all cells; some cells remain with the correct range, and some cells are five or so rows off instead. It looks like it is only off by rows, not columns. All of the cells with Data Validation are merged, I'm not sure if that matters or not. Does anyone know why this may happen, or how to fix it? There is not much code to show, but here is a simple example of a load/save function that will cause this problem:
wb = load_workbook(filename='myspreadsheet.xlsx')
wb.save('myspreadsheet.xlsx')
Thanks in advance! Robert