Validation doesn't work well across worksheets

Issue #377 invalid
girl spider created an issue

Maybe I'm not supposed to use one validation across two different worksheets.

But I did and didn't get any warning or error. As seen in the code attached, the first sheet should only have 3 (A1 to A3) cells with validation, but A1 through A7 all got validation. I guess it's a bleach over from the 2nd sheet.

Comments (3)

  1. CharlieC

    What do you think should be happening when you extend the scope of the validation? As you are using the same validation object in both worksheets it will behave the same in both. When you add cells you are only adding cell addresses – as data validations are local to each worksheet there's no need to check which cells the worksheets are.

    What you probably should have done is make a copy of the data validation for the second sheet, or just create a new one.

    I cleaned up your code to make it a bit more idiomatic.

    from openpyxl import Workbook
    from openpyxl.worksheet.datavalidation import DataValidation
    
    
    #**************
    #create book in memory
    #******************
    wb = Workbook()
    ws = wb.active
    ws1 = wb.create_sheet()
    
    
    #**************
    #validating
    #******************
    dv = DataValidation(type="list", formula1='"dog, cat, bat"', allow_blank=False)
    
    dv.error = "Your entry is not in the list"
    dv.errorTitle = 'Invalid Entry'
    
    ws.add_data_validation(dv)
    
    
    #first sheet
    for i in range(1, 5):
        cell = ws['A%d' % i]
        cell.value = "here"
        dv.add(cell)
    
    ws1.add_data_validation(dv)
    #second sheet
    for j in range(1, 8):
        cell = ws1['A%d' % j]
        cell.value = "there"
        dv1.add(cell)
    
    #**************
    #write book
    #******************
    wb.save('validate.xlsx')
    
  2. CharlieC

    The issue, if there is one, is really about Python's mutable types especially when they are nested in other objects.

  3. Log in to comment