Django ManyToMany Queryset导出Excel聚合关联值避免行重复

你当前在values_list中直接指定跨多对多字段presenter__name拉取数据时，Django ORM会自动对Bookings表、Presenter关联中间表做JOIN查询，单条预约记录关联N个Presenter时，SQL返回结果就会生成N条独立行，直接遍历写入Excel自然会出现重复的预约条目，这是跨表关联查询的正常表现，不是功能bug。

不要在初始查询时直接JOIN拉取Presenter字段，改为先查询所有非取消状态的Bookings对象，遍历单条预约记录时再单独获取关联的Presenter名称集合，拼接到行数据末尾即可，从根源避免JOIN产生的重复行。

修改后的导出函数核心逻辑如下，直接替换原有查询和写入部分的代码即可：

def export_excel(request):
    # 下方响应、工作簿、表头、样式部分代码保持原有逻辑即可
    response = HttpResponse(content_type='application/ms-excel')
    response['Content-Disposition'] = 'attachment; filename=Advancement Series.xls'
    wb = xlwt.Workbook(encoding='utf-8')
    advancement = wb.add_sheet('Advancement')
    advancement_row_num = 0

    font_style = xlwt.XFStyle()
    font_style.font.bold = True
    font_style.font.name = 'calibri'

    advancement_columns = ['Booking ID', 'Program Type', 'Delivery Method', 'Booking Date', 'Booking Time', 'Duration', 'School Name', 'Year Level', 'Street', 'Suburb','Post Code', 'Contact Name', 'Email', 'Phone Number', 'Comments', ' Students Attending', 'Status', 'Presenter']
    
    for col_num in range(len(advancement_columns)):
        advancement.write(advancement_row_num, col_num, advancement_columns[col_num], font_style)

    font_style = xlwt.XFStyle()
    font_style.font.name = 'calibri'

    # --- 修改后的查询和写入逻辑 ---
    # 仅查询Bookings自身字段，不做跨表JOIN，避免结果集重复
    bookings = Bookings.objects.all().exclude(status='Cancelled').only(
        'id', 'program_type', 'delivery_method', 'booking_date', 'booking_time', 'duration', 
        'school_name', 'year_level', 'street', 'suburb', 'post_code',
        'contact_name', 'email', 'phone_number', 'comments', 'students_attending', 'status'
    )

    for booking in bookings:
        advancement_row_num += 1
        # 组装单行数据
        row_data = [
            booking.id,
            booking.program_type,
            booking.delivery_method,
            booking.booking_date,
            booking.booking_time,
            booking.duration,
            booking.school_name,
            booking.year_level,
            booking.street,
            booking.suburb,
            booking.post_code,
            booking.contact_name,
            booking.email,
            booking.phone_number,
            booking.comments if booking.comments else '',
            booking.students_attending,
            booking.status,
            # 最后一列取关联的所有Presenter名称，输出格式和预期的['mark', 'jake']完全一致
            list(booking.presenter.values_list('name', flat=True))
        ]
        # 逐列写入Excel
        for col_num in range(len(row_data)):
            advancement.write(advancement_row_num, col_num, str(row_data[col_num]), font_style)

    wb.save(response)
    return response

可选优化 #

如果你觉得Python原生列表格式的方括号、引号可读性差，可以把最后一列的Presenter内容替换为逗号分隔的字符串，对应行代码修改为：

', '.join(booking.presenter.values_list('name', flat=True))

修改后对应单元格会显示为mark, jake，更符合常规Excel导出的阅读习惯。

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