range of data validation changed after setting data

Issue #827 resolved
Yuan Kong
created an issue

I created a small xlsx file with Excel 2013

Capture.PNG

yellow cells have data validation which refer to a list [1,2,3] White cells have no validation

here is my code:

from openpyxl import load_workbook

workbook = load_workbook("test.xlsx")
worksheet = workbook.active
worksheet['A1'] = 1
workbook.save(filename="test.xlsx")

I expect A1 is set to 1, A3 is still blank, and A2 and A4 have no validation drop down.

But the actual is:

Capture2.PNG

You can see A2 now have validation drop down, (which refer to a list [1,2,3])

I tried more and found all cells except last cell (A2, A3, A4, ... Ax-1) was set data validation.

Comments (5)

  1. CharlieC

    Thanks for the report. The problem is that the data validation uses discrete cell addresses "A1 A3" but we only support cell ranges "A1:A3". There are a number of cases where this occurs but AFAIK it is an undocumented "feature". There is a similar bug #812 related to conditional formatting.

    It is much better to define separate validations for the different cells but will investigate further, though no promises.

  2. Yuan Kong reporter

    Here is a workaround: split dataValidation in a range to cells:

    from openpyxl import load_workbook
    from copy import deepcopy
    
    workbook = load_workbook("test.xlsx")
    worksheet = workbook.active
    
    _dv_list = []
    for dv in worksheet.data_validations.dataValidation:
        if len(dv.cells) > 1:
            for cell in dv.cells:
                _dv = deepcopy(dv)
                _dv.sqref = cell
                _dv_list.append(_dv)
            dv.sqref = dv.sqref.split(":")[0]
    
    for dv in _dv_list:
        worksheet.add_data_validation(dv)
    
    worksheet['A1'] = 1
    workbook.save(filename="test.xlsx")
    
  3. Log in to comment