Django导出CSV遇writerow问题：所有内容在同一单元格

问题代码 #

def export_to_excel(request):
    response = HttpResponse(content_type='text/csv')
    response['Content-Disposition'] = 'attachment; filename=responds.csv'

    writer = csv.writer(response)

    writer.writerow(["First row", "Foo", "Bar", "Baz"])
    writer.writerow(["Second row", "A", "B", "C", '"Testing"', "Here's a quote"])

    return response

代码看似正常，但实际导出后所有内容都集中在同一个单元格中，请问这是什么原因？该如何解决？

原因分析 #

• 分隔符不匹配：Excel默认的CSV分隔符受系统区域设置影响，比如欧洲部分地区默认用分号（;）而非逗号（,），导致逗号分隔的内容被识别为单个单元格。
• 编码识别异常：Windows系统下的Excel对无BOM头的UTF-8 CSV文件解析容易出问题，分隔符无法被正确识别。

解决办法 #

1. 显式指定兼容分隔符 #

根据目标用户的区域设置，直接指定Excel能识别的分隔符，比如分号：

import csv
from django.http import HttpResponse

def export_to_excel(request):
    response = HttpResponse(content_type='text/csv')
    response['Content-Disposition'] = 'attachment; filename=responds.csv'
    # 设置分号为分隔符，适配多数地区的Excel默认配置
    writer = csv.writer(response, delimiter=';')

    writer.writerow(["First row", "Foo", "Bar", "Baz"])
    writer.writerow(["Second row", "A", "B", "C", '"Testing"', "Here's a quote"])

    return response

2. 添加UTF-8 BOM头 #

在响应开头写入UTF-8 BOM，确保Windows Excel正确识别编码：

import csv
from django.http import HttpResponse

def export_to_excel(request):
    response = HttpResponse(content_type='text/csv; charset=utf-8')
    response['Content-Disposition'] = 'attachment; filename=responds.csv'
    # 写入UTF-8 BOM头
    response.write('\ufeff')
    writer = csv.writer(response)

    writer.writerow(["First row", "Foo", "Bar", "Baz"])
    writer.writerow(["Second row", "A", "B", "C", '"Testing"', "Here's a quote"])

    return response

3. 使用Excel标准 dialect #

利用csv模块内置的excel dialect，生成符合Excel规范的CSV文件：

import csv
from django.http import HttpResponse

def export_to_excel(request):
    response = HttpResponse(content_type='text/csv')
    response['Content-Disposition'] = 'attachment; filename=responds.csv'
    # 使用Excel兼容的dialect配置
    writer = csv.writer(response, dialect=csv.excel)

    writer.writerow(["First row", "Foo", "Bar", "Baz"])
    writer.writerow(["Second row", "A", "B", "C", '"Testing"', "Here's a quote"])

    return response

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