Is there a way to Keep or Add a Dropdown to Excel via openpyxl?

user_13770431 0
Viewed 884

I have a working script to read an excel file and save it as another file. The file being read has multiple cells that have data validation (drop-down menus) that reference cells on another sheet. I'm unable to get the drop-down menus to stay by either not having them overwritten or inserting the drop-down menus within the script.

For example, I have a cell, C42 on ws (Main Form), I'd like a drop-down list that references K2:K4 on ws1 (Drop Down List). Here's what I have so far:

from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.worksheet.datavalidation import DataValidation

wb = load_workbook(pathdb+Filepathdb)
ws=wb['Main Form']
ws1=wb['Drop Down List']

dv = DataValidation(type='list', formula1=ws1['K2:K4'], allow_blank=True, showDropDown=True)
ws.add_data_validation(dv)
dv.add(ws["C42"])

Can anyone help as to where I'm going wrong? When I add this it also messes with the formatting of the entire excel file.

Also tried: 

data_val = DataValidation(type="list",formula1='K2:K4', allow_blank=True, showDropDown=True) 
ws1.add_data_validation(data_val)  
data_val.add(ws["C42"])
openpyxl validation excel
1 Answers

If you have a larger number of ips (10+), it's better suited to first store them into a column somewhere in the excel and then use their range as the data validation "Source" aka formula1

from openpyxl.worksheet.datavalidation import DataValidation
wb = Workbook()
ws = wb.create_sheet('New Sheet')
for number in range(1,100): #Generates 99 "ip" address in the Column A;
    ws['A{}'.format(number)].value= "192.168.1.{}".format(number)

data_val = DataValidation(type="list",formula1='=$A:$A') #You can change =$A:$A with a smaller range like =A1:A9
ws.add_data_validation(data_val)

data_val.add(ws["B1"]) #If you go to the cell B1 you will find a drop down list with all the values from the column A

wb.save('Test.xlsx')
user_108494570
Related