Openpyxl - add data validation to column (or range of columns) in for loop

Question

How can I add my data validation to a range of columns with my for loop?

This is my data validation code:

dv3 = DataValidation(type="list", formula1='"poor, fair, good, no mets"', allow_blank=True)
ws.add_data_validation(dv3)

I know I can add it to a range of cells like:

dv3.add("E6:K6")

I have a for loop that I want to add the validation to a column or range of columns in a row such as:

ws.cell(column=6, row=new_line_num+1, value=dv3.add("E"))
ws.cell(column=6, row=new_line_num+1, value=dv3.add("E:K"))

This is the full function:

def droplist(file): wb2 = openpyxl.load_workbook(file, data_only=True) print(wb2.sheetnames) df1 = wb2['Case List']

def daterange(date1, date2):
    for n in range(int ((date2 - date1).days)+1):
        yield date1 + timedelta(n)
dlist = []
start_dt = date.today() - timedelta(days=1)
end_dt = date.today()+ timedelta(days=21)
for dt in daterange(start_dt, end_dt):
    dlist.append(dt.strftime("%m-%d"))
goodlist1 = ", ".join(dlist)
goodlist2 = "\"" + goodlist1 + "\""
print(goodlist2)

dv1 = DataValidation(type="list", formula1=goodlist2, allow_blank=True)
ws.add_data_validation(dv1)
dv1.add("E4:K4")
dv1.add('Q5')

dv2 = DataValidation(type="list", formula1='"CR, PLF, MP, RM, PAE, CO, JR, TS, CG, Nella, DGC, YA, AY, BKM, JH, FS, KT, JW, EV, EO, CM, SLK, SK"', allow_blank=True)
ws.add_data_validation(dv2)
dv2.add("E5:K5")
dv2.add('O5')

dv3 = DataValidation(type="list", formula1='"poor, fair, good, no mets"', allow_blank=True)
ws.add_data_validation(dv3)
dv3.add("E6:K6")
dv3.add("P5")

for row in df1.iter_rows():
    new_line_num = ws.max_row + 5
    try:
        if 'Chromosome' in row[4].value:
            print(row[0].value, row[1].value, row[4].value, row[7].value)
            ws.cell(column=1, row=new_line_num, value=row[0].value)
            ws.cell(column=2, row=new_line_num, value=row[1].value)
            ws.cell(column=5, row=new_line_num, value=row[7].value)
            ws.cell(column=3, row=new_line_num+1, value=row[6].value)
            ws.cell(column=3, row=new_line_num+2, value=row[11].value)
            ws.cell(column=4, row=new_line_num+1, value="Date:")
            ws.cell(column=4, row=new_line_num+2, value="Tech:")
            ws.cell(column=4, row=new_line_num+3, value="Quality:")
            ws.cell(column=4, row=new_line_num+4, value="Comments:")
            ws.cell(column=15, row=new_line_num+1, value="Tech:")
            ws.cell(column=16, row=new_line_num+1, value="Quality:")
            ws.cell(column=17, row=new_line_num+1, value="Date:")    
            if row[2] == "YES":
                ws.cell(column=3, row=new_line_num, value=row[12].value + ", PED") 
            else:
                ws.cell(column=3, row=new_line_num, value=row[12].value)
            
            cultures = list(row[7].value.split(' '))
            cultures2 = ' '.join(cultures).split()
            print(cultures2)
            try:
                ws.cell(column=5, row=new_line_num, value=cultures2[0])
                ws.cell(column=6, row=new_line_num, value=cultures2[1])
                ws.cell(column=7, row=new_line_num, value=cultures2[2])
                ws.cell(column=8, row=new_line_num, value=cultures2[3])
                ws.cell(column=9, row=new_line_num, value=cultures2[4])
                ws.cell(column=10, row=new_line_num, value=cultures2[5])
                ws.cell(column=11, row=new_line_num, value=cultures2[6])
                ws.cell(column=12, row=new_line_num, value=cultures2[7])
                
            except (TypeError, AttributeError, IndexError):
                continue
        else:
            continue
    except (TypeError, AttributeError):
        continue

I am trying to get the dv1, dv2, and dv3 into the for loop so they will populate with the data extraction from df1.

Openpyxl - add data validation to column (or range of columns) in for loop

0 Answers