在一个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,否则无法生效,该单元格最终会变成普通的单元格。文章来源:https://www.toymoban.com/news/detail-682505.html
列表维护在单独sheet中
对于列表内容较长的场景,建议单独创建一个sheet,用于作为下拉列表的选项。上述代码可改造为:文章来源地址https://www.toymoban.com/news/detail-682505.html
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
到了这里,关于Python-使用xlsxwriter创建单元格下拉列表(数据校验)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!