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

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

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

静态Excel模板

制作Excel模板

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

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

提供下载

from flask import current_app, request, send_from_directory, send_filedef 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 ... Zdef get_column(idx):"""根据数字序号，获取Excel的列名，从0开始例如，0->A"""prefix = int(idx/26)suffix = idx%26if 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_appdef 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# flavorflavors = get_openstack_flavor_names()  # todo，从数据库获取数据列表cellname = get_excel_cell_name(row, header_row.index("flavor"))sheet.data_validation(cellname, {"validate": "list", "source": flavors})# imageimages = 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 = 0for k in object_options:key2cellrange[k] = ""opt_sheet.write(0, col, k)row = 1for opt in object_options[k]:opt_sheet.write(row, col, opt)row += 1col_char = get_column(col)cellrange = "='{sheet}'!${col}${row1}:${col}${row2}".format(sheet=sheetname,col=col_char, row1=2, row2=row)key2cellrange[k] = cellrangecol += 1return key2cellrangedef 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)# flavorcellname = get_excel_cell_name(row, header_row.index("flavor"))sheet.data_validation(cellname, {"validate": "list", "source": key2cellrange['flavor']})# imagecellname = get_excel_cell_name(row, header_row.index("image"))sheet.data_validation(cellname, {"validate": "list", "source": key2cellrange['image']})workbook.close()return newfile