基于分类统计的Django Excel导出实现方案问询

核心修改说明 #

1. 修正数据查询逻辑，按AccidentCausation分类分组统计各严重度数量
2. 调整Excel表头匹配需求格式
3. 修复annotate中Q对象的语法错误

修改后的完整视图代码 #

from django.http import HttpResponse
from django.db.models import Count, Q
import xlwt
from .models import IncidentGeneral

def export_accidentcausation_xls(request):
    response = HttpResponse(content_type='application/ms-excel')
    response['Content-Disposition'] = 'attachment; filename="accident_causation.xls"'

    wb = xlwt.Workbook(encoding='utf-8')
    ws = wb.add_sheet('Accident Causation')

    # 样式定义
    alignment = xlwt.Alignment()
    alignment.horz = xlwt.Alignment.HORZ_LEFT
    alignment.vert = xlwt.Alignment.VERT_TOP
    base_style = xlwt.XFStyle()
    base_style.alignment = alignment

    row_num = 0

    # 表头样式
    header_font = xlwt.Font()
    header_font.name = 'Times New Roman'
    header_font.height = 20 * 15
    header_font.bold = True
    borders = xlwt.Borders()
    borders.left = 1
    borders.right = 1
    borders.top = 1
    borders.bottom = 1
    header_style = xlwt.XFStyle()
    header_style.font = header_font
    header_style.borders = borders

    # 内容样式
    body_font = xlwt.Font()
    body_font.name = 'Arial'
    body_font.italic = True
    body_style = xlwt.XFStyle()
    body_style.font = body_font

    # 设置需求的表头
    columns = ['Accident Causation', 'Severity 1', 'Severity 2', 'Severity 3']
    for col_num in range(len(columns)):
        ws.write(row_num, col_num, columns[col_num], header_style)
        ws.col(col_num).width = 7000

    # 数据查询：按事故原因分组，统计各严重度数量
    grouped_data = IncidentGeneral.objects.filter(accident_factor__isnull=False) \
        .values('accident_factor__category') \
        .annotate(
            severity1=Count('id', filter=Q(severity=1)),
            severity2=Count('id', filter=Q(severity=2)),
            severity3=Count('id', filter=Q(severity=3))
        ) \
        .order_by('accident_factor__category')

    # 统计未关联事故原因的记录（可选）
    uncategorized = IncidentGeneral.objects.filter(accident_factor__isnull=True)
    uncategorized_row = {
        'accident_factor__category': '未分类',
        'severity1': uncategorized.filter(severity=1).count(),
        'severity2': uncategorized.filter(severity=2).count(),
        'severity3': uncategorized.filter(severity=3).count()
    }
    grouped_data = list(grouped_data) + [uncategorized_row]

    # 写入Excel内容
    for item in grouped_data:
        row_num += 1
        ws.write(row_num, 0, item['accident_factor__category'], body_style)
        ws.write(row_num, 1, item['severity1'], body_style)
        ws.write(row_num, 2, item['severity2'], body_style)
        ws.write(row_num, 3, item['severity3'], body_style)

    wb.save(response)
    return response

关键改动详解 #

• 查询逻辑修正：

  • 用values('accident_factor__category')实现按事故原因分类分组
  • annotate中使用filter参数配合Q对象，精准统计对应严重度的记录数（替代原错误的only参数）
  • 过滤空事故原因的记录，单独统计归为"未分类"

• Excel结构调整：替换原用户相关表头为需求指定的列，确保输出格式匹配

• 数据写入优化：通过字典键取值写入对应列，避免列顺序混乱

其他说明 #

• 模型代码保持原有结构无需修改
• URL配置保持原设置即可

内容的提问来源于stack exchange，提问作者kimski