CODEWITHSUNDEEP
pythonexcelpython-2.7python-3.xopenpyxl
Robert Harris
Robert Harris

Reputation: 249

openpyxl causing loss of existing DataValidation in excel

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

Upvotes: 5

Views: 6656

Answers (2)

Vijay Bokade
Vijay Bokade

Reputation: 179

try this one 
    wb  = load_workbook(excel_file_path,read_only=False)
    sheet = wb[ws]
    for validation in sheet.data_validations.dataValidation:
    # here exist validation we extract from sheet.data_validation
       if validation.formula1  == exist_validation:
            validation.sqref = [C12:C25]
    wb.save()

Upvotes: 1

Xukrao
Xukrao

Reputation: 8634

It might be that your excel sheet has data validations defined for overlapping cell ranges, causing confusion for openpxyl and/or excel.

When using this code:

wb = load_workbook(filename='DataValidationErrorExample.XLSX')
ws = wb.worksheets[0]
print(ws.data_validations)

I get:

<openpyxl.worksheet.datavalidation.DataValidationList object>
Parameters:
disablePrompts=None, xWindow=None, yWindow=None, count=10, dataValidation=[<openpyxl.worksheet.datavalidation.DataValidation object>
Parameters:
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>
Parameters:
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='I12 J12 K12', formula1='$Z$5:$AC$5', formula2=None, <openpyxl.worksheet.datavalidation.DataValidation object>
Parameters:
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='C13:C26 D13:D26 E13:E26 F13:F26 G13:G26 H13:H26 I13 J13 K13', formula1='$Z$6:$AB$6', formula2=None, <openpyxl.worksheet.datavalidation.DataValidation object>
Parameters:
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='C12:C25 D12:D25 E12:E25 F12:F25 G12:G25 H12:H25', formula1='$Z$5:$AB$5', formula2=None, <openpyxl.worksheet.datavalidation.DataValidation object>
Parameters:
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='G8', formula1='$Z$8:$AB$8', formula2=None, <openpyxl.worksheet.datavalidation.DataValidation object>
Parameters:
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='C16:C29 D16:D29 E16:E29 F16:F29 G16:G29 H16:H29 I16 J16 K16', formula1='$Z$9:$AA$9', formula2=None, <openpyxl.worksheet.datavalidation.DataValidation object>
Parameters:
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='C17:C30 D17:D30 E17:E30 F17:F30 G17:G30 H17:H30 I17 J17 K17', formula1='$Z$10:$AC$10', formula2=None, <openpyxl.worksheet.datavalidation.DataValidation object>
Parameters:
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='C14:C27 D14:D27 E14:E27 F14:F27 G14:G27 H14:H27 I14 J14 K14', formula1='$Z$11:$AA$11', formula2=None, <openpyxl.worksheet.datavalidation.DataValidation object>
Parameters:
showErrorMessage=True, showDropDown=None, showInputMessage=True, allowBlank=False, errorTitle=None, error=None, promptTitle=None, prompt=None, type='date', errorStyle=None, imeMode=None, operator=None, sqref='C8 D8', formula1='41275', formula2='43101', <openpyxl.worksheet.datavalidation.DataValidation object>
Parameters:
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='J8 K8', formula1='$Z$4:$AA$4', formula2=None]

The sqref property shows to which cells a data validation applies, and as you can see there is some overlap between the different data validations.

Upvotes: 2

Related Questions