Python-使用xlsxwriter创建单元格下拉列表（数据校验）

在一个B/S系统中，用户经常需要从以Excel的方式填写后导入，此时网页上需要提供Excel填写的模板，将所需填写的字段以及所处的列的位置固定下来。其中，避免用户随意填写导致系统后台校验出错，对于某些字段，系统需要预设好固定的选项值，用户只能在给定的范围内选择，不允许人工输入填写。

如果字段值的数据范围长期不变，则可直接固化在Excel中；如果字段值偶尔会更新，需要在下载Excel模板时，实时从数据库获取，则需要动态生成该Excel模板。

静态Excel模板

制作Excel模板

新建Excel文件，设置好表头名称，从第二行开始设置数据校验。具体步骤为：

数据》数据验证》设置，“允许”选择“序列”，“来源”字段可手动输入文本（以英文逗号分隔）或选中任意sheet的任意表格内容。

提供下载

from flask import current_app, request, send_from_directory, send_file

def download_excel_template():
    """
        下载Excel模板
    """
    file_dir = os.path.join(current_app.config['LIB_DIR'], 'files', 'templates')  # 模板存放目录
    current_app.logger.info("下载%s下的文件：%s" % (file_dir, 'Template.xlsx'))
    return send_from_directory(file_dir, 'Template.xlsx', as_attachment=True)

动态Excel模板

更特殊的场景下，Excel单元格下拉列表的内容需要从系统中动态获取。例如，用户希望通过填写并上传Excel到某系统，由系统在openstack平台上创建一台虚拟机。虚机的hostname、ip等内容需要由用户自行指定，但虚机所使用的Flavor（即CPU/内存）、镜像（image）、卷的类型（volume type）、网络（network）、子网（subnet）等，名称较为复杂不便记忆，最好是能提供Excel的下拉选择功能。image等信息需要从openstack平台拉取，且管理员随时都有可能会在openstack平台上进行增删改的操作，因此为保持Excel模板的准确性、避免经常性的更新维护，可以动态Excel模板的方式提供下载。

这里，推荐使用xlsxwriter模块生成Excel文件。[Creating Excel files with Python and XlsxWriter — XlsxWriter Documentation]

确定Excel的列名

header_row = ['hostname', 'flavor', 'image']

获取下拉选项数据

flavors = get_openstack_flavor_names()
images = get_openstack_image_names()

列表直接写在文本中

通用函数

# chr(i)  65-90   # A,B,C ... Z

def get_column(idx):
    """
        根据数字序号，获取Excel的列名，从0开始
        例如，0->A
    """
    prefix = int(idx/26)
    suffix = idx%26
    if not prefix:
        return chr(suffix+65)
    else:
        return get_column(prefix)+chr(suffix+65)

def get_excel_cell_name(row_idx, col_idx):
    """
        根据行列的序号，获取单元格的名称，从(0,0)开始
        例如，(0,0)->A1
    """
    if row_idx < 0:
        raise ValueError("Excel的行号必须大于等于0")
    if col_idx < 0:
        raise ValueError("Excel的列号必须大于等于0")
    return "%s%s"%(get_column(col_idx), row_idx+1)

生成Excel

对于单元格的数据校验，可以使用其data_validation功能。Working with Data Validation — XlsxWriter Documentation

import xlsxwriter
from flask import current_app

def generate_excel_template():
    newfile = os.path.join(current_app.config['TMP_DIR'], 'download', 'Template-%s.xlsx' % (time.strftime("%Y%m%d%H%M")))
    workbook = xlsxwriter.Workbook(newfile)
    sheet = workbook.add_worksheet("Sheet1")
    row = 0
    # 表头
    for idx, header in enumerate(header_row):
        sheet.write(row, idx, header)
    row += 1
    # flavor
    flavors = get_openstack_flavor_names()  # todo，从数据库获取数据列表
    cellname = get_excel_cell_name(row, header_row.index("flavor"))
    sheet.data_validation(cellname, {"validate": "list", "source": flavors})
    # image
    images = get_openstack_image_names()    #todo，从数据库获取数据列表
    cellname = get_excel_cell_name(row, header_row.index("image"))
    sheet.data_validation(cellname, {"validate": "list", "source": images})
    workbook.close()
    return newfile

@app.route()    # 略
def download():
    file_path = generate_excel_template()
    return send_file(file_path, as_attachment=True)
    

注意：在该方法中，是将列表的内容直接放在了source里，xlsxwriter对此有所限制，内容长度不超过255，否则无法生效，该单元格最终会变成普通的单元格。

列表维护在单独sheet中

对于列表内容较长的场景，建议单独创建一个sheet，用于作为下拉列表的选项。上述代码可改造为：

def save_data_validate_options_sheet(workbook, object_options):
    """
        保存数据校验的待选项到单独的sheet,object_options格式为{'flavor':[], 'image':[]}
    """
    key2cellrange = {}
    opt_sheet = workbook.add_worksheet("options")
    col = 0
    for k in object_options:
        key2cellrange[k] = ""
        opt_sheet.write(0, col, k)
        row = 1
        for opt in object_options[k]:
            opt_sheet.write(row, col, opt)
            row += 1
        col_char = get_column(col)
        cellrange = "='{sheet}'!${col}${row1}:${col}${row2}".format(sheet=sheetname,col=col_char, row1=2, row2=row)
        key2cellrange[k] = cellrange
        col += 1
    return key2cellrange
    
def generate_excel_template():
    newfile = os.path.join(current_app.config['TMP_DIR'], 'download', 'Template-%s.xlsx' % (time.strftime("%Y%m%d%H%M")))
    workbook = xlsxwriter.Workbook(newfile)
    sheet = workbook.add_worksheet("Sheet1")
    row = 0
    # 表头
    for idx, header in enumerate(header_row):
        sheet.write(row, idx, header)
    row += 1
    # 统一获取待选项
    flavors = get_openstack_flavor_names()  # todo，从数据库获取数据列表
    images = get_openstack_image_names()    #todo，从数据库获取数据列表
    object_options = {"flavor": flavors, "image": images}
    key2cellrange = save_data_validate_options_sheet(workbook, object_options)
    # flavor
    cellname = get_excel_cell_name(row, header_row.index("flavor"))
    sheet.data_validation(cellname, {"validate": "list", "source": key2cellrange['flavor']})
    # image
    cellname = get_excel_cell_name(row, header_row.index("image"))
    sheet.data_validation(cellname, {"validate": "list", "source": key2cellrange['image']})
    workbook.close()
    return newfile