OpenPyXL:是否可以在Excel工作表中创建下拉菜单? [英] OpenPyXL: Is it possible to create a dropdown menu in an excel sheet?

问题描述

我正在尝试使用openpyxl将有效IP地址列表存储在单元格中.目前，数据只是放置在一个单元中，通常会溢出到其他单元中.使用以下代码:

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

但是，我希望将这些IP地址放入一个下拉列表中，因为这样更容易阅读-所以我的问题是双重的，首先，这可以做到吗?因为我找不到关于它的任何信息，其次，是否有更好的方法来显示数据而不会溢出?

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?

感谢您阅读

添加了一些示例地址和子网，以反映列表中可能包含的内容.

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

推荐答案

如果您的ips数量较多(10+)，则更适合先将它们存储到excel中的某个列中，然后将其范围用作数据验证来源"，也称为Formula1.

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

此处的更多信息: https://openpyxl.readthedocs.io/en/2.5/validation.html

这篇关于OpenPyXL:是否可以在Excel工作表中创建下拉菜单?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！