Django中基于xlsxwriter用表单数据生成Excel模板并下载的实现问题

一、核心问题梳理 #

• 前端表单配置错误：提交方法用了GET、提交路径不匹配、按钮属性重复，无法正常提交表单数据
• 后端POST请求无处理逻辑：接收到表单提交后没有校验参数、生成Excel、返回下载响应的流程
• Excel生成逻辑错误：直接将文件写入服务器本地磁盘，没有返回给浏览器触发下载
• 参数未做格式转换：用户输入的工作表名、列名是字符串，需要拆分后才能使用

二、逐文件修改代码 #

1. views.py 代码调整 #

from django.http import HttpResponse
from django.shortcuts import render
import xlsxwriter
import io
from django.forms import Form, CharField, ChoiceField

def home(request):
    return render(request, 'my_app/home.html')

class TemplateForm(Form):
    doc_name = CharField(label='Document name')
    sheetnames = CharField(label='Sheetnames（多个用英文逗号分隔）')
    choices = []
    for year in range (1900, 2050):
        choices.append( (year, year) )
    year1 = ChoiceField(label='Starting Year', initial=2021, choices=choices)
    year2 = ChoiceField(label='Ending Year', initial=2022, choices=choices)    
    row_names = CharField(label='Column names（多个用英文逗号分隔）')

def create_template(request):
    if request.method == 'GET':
        form = TemplateForm()
        return render(request, 'my_app/create_template.html', {'form':form})
    # 处理POST提交逻辑
    form = TemplateForm(request.POST)
    if not form.is_valid():
        return render(request, 'my_app/create_template.html', {'form': form})
    data = form.cleaned_data
    # 转换参数格式
    doc_name = data['doc_name']
    sheetnames = [i.strip() for i in data['sheetnames'].split(',') if i.strip()]
    row_names = [i.strip() for i in data['row_names'].split(',') if i.strip()]
    start_year = int(data['year1'])
    end_year = int(data['year2'])
    years = list(range(start_year, end_year+1))

    # 将Excel生成到内存中，不写入服务器磁盘
    output = io.BytesIO()
    workbook = xlsxwriter.Workbook(output)
    workbook.add_worksheet("introduction")
    for sheet_name in sheetnames:
        worksheet = workbook.add_worksheet(sheet_name)
        worksheet.write_row(0, 1, years)
        worksheet.write_column(1, 0, row_names)
    workbook.close()

    # 构造下载响应
    output.seek(0)
    response = HttpResponse(
        output,
        content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    )
    response['Content-Disposition'] = f'attachment; filename={doc_name}_template.xlsx'
    return response

2. create_template.html 代码调整 #

{% extends "my_app/base.html" %}
{% block content %}
<form action="{% url 'my-create-template' %}" method="POST">
  {% csrf_token %}
  <h1>Create your template</h1>
  <div class="item">
    <table>
      {{ form.as_table }}
    </table>
  </div>
  <div class="btn-block">
    <input type="submit" value="Create and Download!"/>
  </div>
</form>
{% endblock content %}

3. urls.py 无需调整，原有配置可正常使用 #

三、使用说明 #

提交表单时，多个工作表名、列名请用英文逗号分隔，比如工作表名填业绩表,考勤表、列名填姓名,部门,产出即可生成对应结构的Excel文件。

内容的提问来源于stack exchange，提问作者project.py