Trouble Implementing List Data Validation with Openpyxl

I am working with the openpyxl library in Python to apply data validation rules to specific cells in an Excel spreadsheet. While I can successfully change the color fill of cells, I am encountering issues with other types of data validation, which aren't being applied as expected. Below is the snippet of my code related to the data validation:

        if ws.max_row < 20:
            for i in range(ws.max_row + 1, 101): 
                ws.cell(row=i, column=1) 



        dropdowns = {
            'B': '"197, 198, 197 S30, 198 S30, B36, B37, B38, B30, BC6, BC6 ANC, BC7, BC7 ANC, BC8, BC8 ANC, BC0, BC0, BH0 D30, BH0 D45, BH0 S30, BH8 S45, BH2 S30, BH6 D30, BH6 D45, BH6 S30, BH6 S45, BH7 D30, BH7 D45, BH7 S30, BH7 S45, BH8 D30, BH8 D45, BH8 S30, BH8 S45, BM6 S30, BM7 S30, BM8 S30, BM6, BM7, BM8, BM0, BP6, BP7, BP8, BP0, BR6, BR7, BR8, BR0, BS0, BS2, BS6, BS7, BS8, CS7, CS8, EDF, FC7 MAX, FC7 MIN, FC8 MAX, FC8 MIN, FC0 MAX, FC0 MIN, FC7 ANC MAX, FC7 ANC MIN, FC8 ANC MAX, FC8 ANC MIN, FC0 ANC MAX, FC0 ANC MIN, FH7, FH8, FL7, FL8, FL0, FR7, FR8, FS7, FS8, M27, M28, M20, M36, M37, M38, M47, M48, MC6 ANC MAX, MC6 ANC MIN, MC6 MAX, MC6 MIN, MC7, MC7 ANC MAX, MC7 ANC MIN, MC7 MAX, MC7 MIN, MC8, MC8 ANC MAX, MC8 ANC MIN, MC8 MAX, MC8 MIN, MC0 ANC MAX, MC0 ANC MIN, MC0 MAX, MC0 MIN, ME7 ANC MAX, ME7 ANC MIN, ME7 MAX, ME7 MIN, ME8 ANC MAX, ME8 ANC MIN, ME8 MAX, ME8 MIN, ME0 ANC MAX, ME0 ANC MIN, ME0 MAX, ME0 MIN, MF7, MF8, MH6 D30, MH6 D45, MH6 S30, MH6 S45, MH7 D30, MH7 D45, MH7 S30, MH7 S45, MH8 D30, MH8 D45, MH8 S30, MH8 S45, MI6, MI7, MI8, ML6, ML7, ML8, MM6, MM7, MM8, MR6, MR7, MR8, MS6, MS7, MS8, MT6, MT7, MT8, MX6, MX7, MX8, ORT, POT, POT MAX, POT MIN, XC6 ANC MAX, XC6 ANC MIN, XC6 MAX, XC6 MIN, XC7 ANC MAX, XC7 ANC MIN, XC7 MAX, XC7 MIN, XC8 ANC MAX, XC8 ANC MIN, XC8 MAX, XC8 MIN"',
            'F': '"Oui, AUT, BRU, CAS, CHO, DEL, EPA, FEN, INS, PER, PIV, POU, ROU, TET"',
            'G':'"Oui, Non"',
            'H':'"Oui, Non"',
            'I':'"Oui, Non"',
            'J':'"Oui, Non"',
            'K':'"Oui, Non"',
            'L':'"Oui, Non"',
            'M':'"Oui, Non"',
            'N':'"Oui, Non"',
            'O': '"TER, BMP, SOC, ROC"',
            'P': '"Non, BGC, BGP, BNC, BNP, HAC, HAP, HBC, HBP"',
            'Q': '"Non, TRM, TDL, TCR, SPC, SPB, PCH, PCG, PCP"',
            'R': '"Non, INV, IN1, IN2, IN3, IN4, IN5, IN6, IN7, IN8, IN9"',
            'S': '"5/9, 5/10, 97-8-6, 97-14-6, 98-4-8, 98-8-4, 98-8-6, 98-14-4, 98-14-6, 98-28-4, 98-28-6, 98-56-4, 98-56-6, 98-112-4, 98-112-6, 98-224-4, 99-14-8, 99-28-8, 99-56-8, 99-8-8, A2, A3, B4, C6, Liaison, L1047-1-A, L1047-1-P, L1047-2-A, L1047-2-P, L1048-A, L1048-P, L1092-1-A, L1092-1-P, L1092-2-A, L1092-2-P, L1092-3-A, L1092-3-P, L1092-11-A, L1092-11-P, L1092-12-A, L1092-12-P, L1092-13-A, L1092-13-P, L1092-14-A, L1092-14-P, L1092-15-A, L1092-15-P, L1083-A, L1083-P, A-H0073A-1F-50, A-H0118B-1FO-50, A-H0151B-2FO-50, A-H0338A-6F-60, A-H0340A-12F-60, A-H0341A-12F-60, A-H0342A-24F-60, A-H0343A-36F-60, A-H0344A-48F-60, A-H0345A-72F-60, A-H0346A-96F-60, A-H0347A-144F-60, A-H0348A-24F-60, A-H0349A-36F-60, A-H0350A-48F-60, A-H0351A-72F-60, A-H0352A-96F-60, A-H0353A-144F-60, A-H0354A-288F-60, A-N7836A-1F-70, A-N7837A-2F-70, A-N7838A-4F-70, A-N7839A-6F-70, A-N7840A-8F-70, A-N7841A-12F-70, A-N8227A-24F-70, A-N8228A-36F-70, A-N8700A-1F-80, A-N8800A-2F-80, A-N8819C-144F-60, A-N8867D-24F-70, A-N8868C-36F-70, A-N9076A-12F-70, A-N9270A-72F-70, A-N9271A-96F-70, A-N9272A-144F-70, A-N9273A-48F-70, A-N9297C-24F-60, A-N9298C-96F-70, A-N9385A-48F-70, A-N9386A-72F-70, A-N9387A-96F-70, A-N9388A-144F-70, A-N9485A-16F-70, A-N9665B-72F-110, A-N9850A-2F-50, A-N9873A-288F-70, A-N9923A-12F-70, A-N9926A-48F-70, A-N9996A-144F-70, A-UNB1625-4F-50, A-Z1295A-288F-80, C-12T12-144F-60, C-1T12-12F-60, C-2T12-24F-60, C-3T12-36F-60, C-4T12-48F-60, C-6T12-72F-60, C-8T12-96F-60, L1047-1-A, L1047-1-P, L1047-1-S, L1047-2-A',
            'V': '"haute, standard, basse"',
            'Z': '"Oui, Non"',
            'AA': '"Oui, Non"',
            'AE': '"197, 198, 197 S30, 198 S30, B36, B37, B38, B30, BC6, BC6 ANC, BC7, BC7 ANC, BC8, BC8 ANC, BC0, BC0, BH0 D30, BH0 D45, BH0 S30, BH8 S45, BH2 S30, BH6 D30, BH6 D45, BH6 S30, BH6 S45, BH7 D30, BH7 D45, BH7 S30, BH7 S45, BH8 D30, BH8 D45, BH8 S30, BH8 S45, BM6 S30, BM7 S30, BM8 S30, BM6, BM7, BM8, BM0, BP6, BP7, BP8, BP0, BR6, BR7, BR8, BR0, BS0, BS2, BS6, BS7, BS8, CS7, CS8, EDF, FC7 MAX, FC7 MIN, FC8 MAX, FC8 MIN, FC0 MAX, FC0 MIN, FC7 ANC MAX, FC7 ANC MIN, FC8 ANC MAX, FC8 ANC MIN, FC0 ANC MAX, FC0 ANC MIN, FH7, FH8, FL7, FL8, FL0, FR7, FR8, FS7, FS8, M27, M28, M20, M36, M37, M38, M47, M48, MC6 ANC MAX, MC6 ANC MIN, MC6 MAX, MC6 MIN, MC7, MC7 ANC MAX, MC7 ANC MIN, MC7 MAX, MC7 MIN, MC8, MC8 ANC MAX, MC8 ANC MIN, MC8 MAX, MC8 MIN, MC0 ANC MAX, MC0 ANC MIN, MC0 MAX, MC0 MIN, ME7 ANC MAX, ME7 ANC MIN, ME7 MAX, ME7 MIN, ME8 ANC MAX, ME8 ANC MIN, ME8 MAX, ME8 MIN, ME0 ANC MAX, ME0 ANC MIN, ME0 MAX, ME0 MIN, MF7, MF8, MH6 D30, MH6 D45, MH6 S30, MH6 S45, MH7 D30, MH7 D45, MH7 S30, MH7 S45, MH8 D30, MH8 D45, MH8 S30, MH8 S45, MI6, MI7, MI8, ML6, ML7, ML8, MM6, MM7, MM8, MR6, MR7, MR8, MS6, MS7, MS8, MT6, MT7, MT8, MX6, MX7, MX8, ORT, POT, POT MAX, POT MIN, XC6 ANC MAX, XC6 ANC MIN, XC6 MAX, XC6 MIN, XC7 ANC MAX, XC7 ANC MIN, XC7 MAX, XC7 MIN, XC8 ANC MAX, XC8 ANC MIN, XC8 MAX, XC8 MIN"',
            'AF': '"Remplacement, Renforcement, Recalage"',
            'AG': '""Ville, commune, Réseau à déployer en calcul de charge, Hypothèses climatique pour le calcul de charge, Appuis concernés par l\'étude, N° appui""',
            'AI': '"Oui, Non"',
            'AJ': '"Non, PB, PEO"',
            'AK': '"0, 1, 2, >2"',
            'AL': '"Non, Existante, Création"',
            'AN': '"Oui, Non"',

        }

        for col, values in dropdowns.items():
            dv = DataValidation(type="list", formula1=values, allow_blank=True)
            ws.add_data_validation(dv)
            form = col+str(row)+":"+col +str(ws.max_row + 1)
            print(form)
            dv.showInputMessage = True
            dv.showErrorMessage = True
            dv.add(form)
            ws.add_data_validation(dv)
            
     
        green_fill = PatternFill(start_color='00FF00', end_color='00FF00', fill_type='solid')
        red_fill = PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid')


       
        for col in 'FGHIJKLMN':
            if ws.max_row >= 9:
                col_range = f'{col}9:{col}{ws.max_row}'
                ws.conditional_formatting.add(
                    col_range,
                    CellIsRule(operator='equal', formula=['"Oui"'], stopIfTrue=True, fill=green_fill)
                )
                ws.conditional_formatting.add(
                    col_range,
                    CellIsRule(operator='equal', formula=['"Non"'], stopIfTrue=True, fill=red_fill)
                )
        
        wb.save(novo_arquivo_path)

In this code, I expected to see data validation such as dropdown lists or input restrictions being applied to the cells, but none of these validations are taking effect—only the color fill works.

Could someone help me identify what might be going wrong here? Any suggestions on how to correctly apply different types of data validations using openpyxl?

Solution

Probably two issues with the DV;

From what I remember there is a limit (~ 256 characters I think) for the list so the 'values' bigger than this will corrupt. That includes the first Column in your dictionary 'B' as well as at least 'S' & 'AE'
I see you are trying to get the values quoted correctly but your method is not working.

You may want to try the following method;
I've extracted a couple of your dictionary elements where the values are within the limit, and changed to be enclosed in single quotes.
The formula1= implementation is changed for the DataValidation.
Since you have no definition for row in your code sample I have just set it to 1.

dropdowns = {
             'B': '197,198,197 S30,198 S30,B36,B37,B38,B30,BC6,BC6 ANC,BC7,BC7 ANC,BC8,BC8 ANC,BC0,BC0,BH0 D30,'
         'BH0 D45,BH0 S30,BH8 S45,BH2 S30,BH6 D30,BH6 D45,BH6 S30,BH6 S45,BH7 D30,BH7 D45,BH7 S30,'
         'BH7 S45,BH8 D30,BH8 D45,BH8 S30,BH8 S45,BM6 S30,BM7 S30,BM8 S30,BM6,BM7,BM8',
             'F': 'Oui, AUT, BRU, CAS, CHO, DEL, EPA, FEN, INS, PER, PIV, POU, ROU, TET',
             'G': 'Oui, Non',
}

row = 1
for col, values in dropdowns.items():
    print(values)
    # dv = DataValidation(type="list", formula1=values, allow_blank=True)
    dv = DataValidation(type="list", formula1=f'"{values}"', allow_blank=True)
    ws.add_data_validation(dv)
    form = col + str(row) + ":" + col + str(ws.max_row + 1)
    print(form)
    dv.showInputMessage = True
    dv.showErrorMessage = True
    dv.add(form)
    ws.add_data_validation(dv)

This will produce a workbook that opens with DV in columns 'F' and 'G' to row max.

UPDATE
If you were to add the list of values (for 'B' column) to the sheet and reference the range instead all 166 elements can be added.
Example; Add all the 'B' column values down column 'A' from 'A1' to 'A166' then set formula1 to the range 'A1:A166'.
In the example code below I have added Column C to the dictionary with a range instead of the values; =Sheet1!$A$1:$A$166.
Note the implementation slightly changes as using a formula doesn't require the double quotes that the values need.
Example referencing a range.

dropdowns = {
    'B': '197,198,197 S30,198 S30,B36,B37,B38,B30,BC6,BC6 ANC,BC7,BC7 ANC,BC8,BC8 ANC,BC0,BC0,BH0 D30,'
         'BH0 D45,BH0 S30,BH8 S45,BH2 S30,BH6 D30,BH6 D45,BH6 S30,BH6 S45,BH7 D30,BH7 D45,BH7 S30,'
         'BH7 S45,BH8 D30,BH8 D45,BH8 S30,BH8 S45,BM6 S30,BM7 S30,BM8 S30,BM6,BM7,BM8',
    'C': '=Sheet1!$A$1:$A$166',
    'F': 'Oui,AUT,BRU,CAS,CHO,DEL,EPA,FEN,INS,PER,PIV,POU,ROU,TET',
    'G': 'Oui,Non',
}

dv = DataValidation(type="list", formula1=f'{value}', allow_blank=True)

This option will add all 166 elements to the list but requires adding the values to a sheet (any sheet that the DV can reference). Is that something you can work with?