如何修改Django站点导出的CSV表格样式以匹配示例效果

问题描述 #

我有一个搭载数据库的Django网站，可在站点上对数据库信息执行增、删、改操作。目前已实现点击按钮导出.csv文件的功能，但导出后在Excel中呈现的样式较为普通。希望点击“下载报告”按钮时，能生成带定制化表格样式的文件，请问是否可行？

当前我的views.py导出代码如下：

def export_racks_view(request, id):
    response = HttpResponse(content_type='text/csv')
    response.write(u'\ufeff'.encode('utf8'))
    writer = csv.writer(response, delimiter=';', dialect='excel')
    writer.writerow([
        'Rack number',
        'Name',
        'Amount',
        'Vendor',
        'Model',
        'Describe',
        'numbering',
        'responsible',
        'financially responsible person',
        'inventory number',
        'row',
        'place',
        'height',
        'width',
        'depth',
        'unit width',
        'unit depth',
        'rack type',
        'place type',
        'max load',
        'power sockets',
        'power sockets UPS',
        'external ups',
        'cooler',
        'updated by',
        'updated at',
        'room name',
        'building name',
        'site name',
        'department name',
        'region name',
    ])
    raw_report = Rack.objects.raw("""select rack.id as id,  rack.rack_name,     rack.rack_amount,   rack.rack_vendor,   rack.rack_model,    rack.rack_description,  rack.numbering_from_bottom_to_top,  rack.responsible,   rack.rack_financially_responsible_person,   rack.rack_inventory_number,     rack.row,   rack.place,     rack.rack_height,   rack.rack_width,    rack.rack_depth,    rack.rack_unit_width,   rack.rack_unit_depth,   rack.rack_type,     rack.rack_place_type,   rack.max_load,  rack.power_sockets,     rack.power_sockets_ups,     rack.external_ups,  rack.cooler,    rack.updated_by,    rack.updated_at,    room.room_name,     building.building_name,     site.site_name,     department.department_name,     region.region_name  from rack   join room on    room.id =   rack.room_id_id     join building on    building.id =   room.building_id_id     join site on    site.id =   building.site_id_id     join department on  department.id =     site.department_id_id   join region on  region.id =     department.region_id_id where region.id ="""+str(id)+""";""")
    for rack in raw_report:
        if rack.numbering_from_bottom_to_top == True:
            numbering_from_bottom_to_top = 'Yes'
        else:
            numbering_from_bottom_to_top = 'No'
        if rack.external_ups == True:
            external_ups = 'Yes'
        else:
            external_ups = 'No'
        if rack.cooler == True:
            cooler = 'Yes'
        else:
            cooler = 'No'
        writer.writerow([
            rack.id,
            rack.rack_name,
            rack.rack_amount,
            rack.rack_vendor,
            rack.rack_model,
            rack.rack_description,
            numbering_from_bottom_to_top,
            rack.responsible,
            rack.rack_financially_responsible_person,
            rack.rack_inventory_number,
            rack.row,
            rack.place,
            rack.rack_height,
            rack.rack_width,
            rack.rack_depth,
            rack.rack_unit_width,
            rack.rack_unit_depth,
            rack.rack_type,
            rack.rack_place_type,
            rack.max_load,
            rack.power_sockets,
            rack.power_sockets_ups,
            external_ups,
            cooler,
            rack.updated_by,
            rack.updated_at,
            rack.room_name,
            rack.building_name,
            rack.site_name,
            rack.department_name,
            rack.region_name,
        ])
    response['Content-Disposition'] = 'attachment; filename="racks.csv"'
    return response

解决方案：导出带样式的XLSX文件 #

完全可行，但CSV格式本身不支持样式（字体、颜色、单元格对齐等），所以需要切换为生成Excel原生格式（.xlsx），借助openpyxl库实现定制化样式。

步骤1：安装依赖 #

先安装openpyxl库：

pip install openpyxl

步骤2：修改views.py的导出代码 #

替换原有CSV导出逻辑，改为生成带样式的XLSX文件：

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.utils import get_column_letter

def export_racks_view(request, id):
    # 创建工作簿和工作表
    wb = Workbook()
    ws = wb.active
    ws.title = "Racks Report"

    # 定义样式：表头样式
    header_font = Font(bold=True, color="FFFFFF")
    header_fill = PatternFill(start_color="2C3E50", end_color="2C3E50", fill_type="solid")
    header_alignment = Alignment(horizontal="center", vertical="center")
    thin_border = Border(left=Side(style='thin'), 
                         right=Side(style='thin'), 
                         top=Side(style='thin'), 
                         bottom=Side(style='thin'))
    
    # 普通单元格样式
    cell_alignment = Alignment(horizontal="left", vertical="center")

    # 写入表头
    headers = [
        'Rack number',
        'Name',
        'Amount',
        'Vendor',
        'Model',
        'Describe',
        'numbering',
        'responsible',
        'financially responsible person',
        'inventory number',
        'row',
        'place',
        'height',
        'width',
        'depth',
        'unit width',
        'unit depth',
        'rack type',
        'place type',
        'max load',
        'power sockets',
        'power sockets UPS',
        'external ups',
        'cooler',
        'updated by',
        'updated at',
        'room name',
        'building name',
        'site name',
        'department name',
        'region name',
    ]
    for col_num, header in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col_num, value=header)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = header_alignment
        cell.border = thin_border

    # 获取数据库数据（保留原有查询逻辑）
    raw_report = Rack.objects.raw("""select rack.id as id,  rack.rack_name,     rack.rack_amount,   rack.rack_vendor,   rack.rack_model,    rack.rack_description,  rack.numbering_from_bottom_to_top,  rack.responsible,   rack.rack_financially_responsible_person,   rack.rack_inventory_number,     rack.row,   rack.place,     rack.rack_height,   rack.rack_width,    rack.rack_depth,    rack.rack_unit_width,   rack.rack_unit_depth,   rack.rack_type,     rack.rack_place_type,   rack.max_load,  rack.power_sockets,     rack.power_sockets_ups,     rack.external_ups,  rack.cooler,    rack.updated_by,    rack.updated_at,    room.room_name,     building.building_name,     site.site_name,     department.department_name,     region.region_name  from rack   join room on    room.id =   rack.room_id_id     join building on    building.id =   room.building_id_id     join site on    site.id =   building.site_id_id     join department on  department.id =     site.department_id_id   join region on  region.id =     department.region_id_id where region.id ="""+str(id)+""";""")

    # 写入数据行
    row_num = 2
    for rack in raw_report:
        numbering_from_bottom_to_top = 'Yes' if rack.numbering_from_bottom_to_top else 'No'
        external_ups = 'Yes' if rack.external_ups else 'No'
        cooler = 'Yes' if rack.cooler else 'No'

        row_data = [
            rack.id,
            rack.rack_name,
            rack.rack_amount,
            rack.rack_vendor,
            rack.rack_model,
            rack.rack_description,
            numbering_from_bottom_to_top,
            rack.responsible,
            rack.rack_financially_responsible_person,
            rack.rack_inventory_number,
            rack.row,
            rack.place,
            rack.rack_height,
            rack.rack_width,
            rack.rack_depth,
            rack.rack_unit_width,
            rack.rack_unit_depth,
            rack.rack_type,
            rack.rack_place_type,
            rack.max_load,
            rack.power_sockets,
            rack.power_sockets_ups,
            external_ups,
            cooler,
            rack.updated_by,
            rack.updated_at,
            rack.room_name,
            rack.building_name,
            rack.site_name,
            rack.department_name,
            rack.region_name,
        ]

        for col_num, value in enumerate(row_data, 1):
            cell = ws.cell(row=row_num, column=col_num, value=value)
            cell.alignment = cell_alignment
            cell.border = thin_border
        row_num += 1

    # 调整列宽适配内容
    for col in ws.columns:
        max_length = 0
        column = col[0].column_letter
        for cell in col:
            try:
                if len(str(cell.value)) > max_length:
                    max_length = len(str(cell.value))
            except:
                pass
        adjusted_width = max_length + 2
        ws.column_dimensions[column].width = adjusted_width

    # 准备响应
    response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    response['Content-Disposition'] = 'attachment; filename="racks_report.xlsx"'
    
    # 保存工作簿到响应
    wb.save(response)
    return response

说明 #

• 样式可自定义：可以修改header_font、header_fill等参数调整表头的字体、背景色，也可以给特定列设置不同样式
• 保留原有数据逻辑：数据库查询和数据转换部分完全复用你原来的代码，只替换了导出格式和样式设置
• 导出格式改为xlsx：用户下载后直接用Excel打开就能看到定制化样式

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