OpenPyXL: Is it possible to create a dropdown menu in an excel sheet?

Question

I'm attempting to store a list of valid ip addresses in a cell using openpyxl. At the moment the data is simply placed into a cell, and usually overflows into other cells. Using the code below:

# Regex to return a tidy list of ip addresses in that block
"""
    r = row to be checked
    s = source or destination columns
    iptc = ips to check
"""

def regex_ips(r, s):
    iptc = ['165.11.14.20', '166.22.24.0/24', '174.68.19.11', '165.211.20.0/23']
    if r is not None:
        if s is not None:
            iptc = str(sheet.cell(r, s).value)
            san = re.sub('\n', ', ', iptc)
            sheet_report.cell(r, 8).value = san

However, I would prefer if i could place these ip addresses into a dropdown list since that would be far easier to read - so my question is twofold, first, can this be done? because I couldn't find any info about it, And secondly, is there possibly a better way to display the data without it overflowing?

Thanks for reading over this

EDIT: added some example addresses and subnets to reflect what may be in a list.

Faltboat

"Drop Down" lists can be done fairly easy with openpyxl, so called "data validation" in Excel, can you provide the contents of iptc,or a list of some examples ips, or how are they store. – Vday

Also how many ip address do you have in the list? – Vday

The list can vary in size, usually no more than maybe 40 addresses total, and they are being stored in a list - ill update the example to reflect this with some pseudo examples – Faltboat

Also you have to store your ip addresses into separate strings like this : iptc = ["165.11.14.20", "166.22.24.0/24", "174.68.19.11", "165.211.20.0/23"] – Vday

@Vday Thanks for pointing that out - just updated the example to reflect that – Faltboat

Answer 1

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 import Workbook
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')

More info here: https://openpyxl.readthedocs.io/en/2.5/validation.html

Vday

Answer 2

Here is an alternative line of code to generate a drop down list using Python in an Excel file. Please note, the parameter "showDropDown" is counter intuitive (acts opposite of how I expected it to act).

# Create the list of options for the drop down:
list_each = ['EA', 'KIT']

# Create the data validation rules we want to add:
dv_list_each = DataValidation(type="list", formula1=f'"{",".join(list_each)}"', showDropDown=False, allow_blank=True) # To see the drop down arrow, use "showDropDown=False"

Nada

Answer 3

First you must understand some Excel capabilities. There is one called data validation, which can restrict data input, usually with a drop menu. It can restrict data using a list of values, a range of cells, numeric values, etc.

After you understand data validation, look in library documentation how to use this Excel capability.

Ame

A link to an answer is not an answer – Mel