django生成excel文件(两个文件多个sheet页)并下载

import os
import uuid
import json
import re
import zipfile
import openpyxl
from openpyxl import Workbook, load_workbook
from .models import *

def download_zip(zip_file_path):
    """
    下载zip文件
    """
    zip_file_name = os.path.basename(zip_file_path)
    with open(zip_file_path, 'rb') as file:
        response = HttpResponse(file.read(), content_type='application/zip')
        response['Content-Disposition'] = 'attachment; filename=%s' % zip_file_name
        return response


def zip_folder(folder_path, zip_path):
    """生成zip包"""
    with zipfile.ZipFile(zip_path, 'w', zipfile.ZIP_DEFLATED) as zipf:
        for root, _, files in os.walk(folder_path):
            for file in files:
                file_path = os.path.join(root, file)
                zipf.write(file_path, arcname=os.path.relpath(file_path, folder_path))


def new_excel(design_id):
    """
    生成新的excel文件
    """
    # 从这里起生成另一个excel文件
    data = json.loads(list(TaskDesign.objects.filter(id=design_id).values('scenarioPlanning'))[0]['scenarioPlanning'])
    ipprefix = data['ipPrefix']
    template_data = list(NetworkTemplate.objects.filter(id=data['networkSz']).values("switchConfig", "sdnConfig"))[0]
    _data = eval(str(template_data).replace("xxx.xxx", ipprefix).replace("XXX.XXX", ipprefix))
    sdnconfig_data = json.loads(_data['sdnConfig'])
    switchconfig_data = json.loads(_data['switchConfig'])
    for i in switchconfig_data:
        del i['id']
        del i['index']
    # 统一的键列表
    keys = ["name", "node", "mgmtIp", "routerId", "description"]
    switchconfig_data = [dict(sorted(d.items(), key=lambda x: keys.index(x[0]))) for d in switchconfig_data]
    # 创建一个新的工作簿
    workbook = Workbook()
    # 创建第一个 sheet 页
    sheet1 = workbook.active
    sheet1.title = "ROUTER-ID"
    # 设置第一个 sheet 页的列名
    headers1 = ['设备名称', '节点编号', 'MGMT-IP', 'ROUTER-ID', '备注']
    sheet1.append(headers1)
    for item in switchconfig_data:
        item['设备名称'] = item.pop('name')
        item['节点编号'] = item.pop('node')
        item['MGMT-IP'] = item.pop('mgmtIp')
        item['ROUTER-ID'] = item.pop('routerId')
        item['备注'] = item.pop('description')
        row = [item.get(header, '') for header in headers1]
        sheet1.append(row)
    for i in sdnconfig_data:
        del i['id']
        del i['index']
    # 统一的键列表
    keys1 = ["ipType", "businessType", "router", "position", "ip", "description"]
    sdnconfig_data = [dict(sorted(d.items(), key=lambda x: keys1.index(x[0]))) for d in sdnconfig_data]

    sheet2 = workbook.create_sheet(title="Sheet2")
    # 设置第二个 sheet 页的列名
    headers2 = ['IP类别', '业务网段', '发布路由', '配置位置', 'Tunnel_IP', '备注']
    sheet2.append(headers2)
    for item1 in sdnconfig_data:
        print(item1)
        item1['IP类别'] = item1.pop('ipType')
        item1['业务网段'] = item1.pop('businessType')
        item1['发布路由'] = item1.pop('router')
        item1['配置位置'] = item1.pop('position')
        if "ip" in item1.keys():
            item1['Tunnel_IP'] = item1.pop('ip')
        if "description" in item1.keys():
            item1['备注'] = item1.pop('description')
        row = [item1.get(header, '') for header in headers2]
        sheet1.append(row)

    # 保存工作簿
    workbook.save('D:/test/11.xlsx')


def write_excel(design_id, file_path):
    """
    生成另两个表sheet数据（打开已有文件，插入sheet3 4数据）
    """
    # 打开已存在的 Excel 文件
    wb = load_workbook(file_path)
    # 创建第3个工作表
    ws3 = wb.create_sheet(title='步线', index=2)
    # 创建第4个工作表
    ws4 = wb.create_sheet(title='耗材清单', index=3)
    # 获取数据并插入到第3个工作表
    data_sheet3 = TaskDesignCableConnection.objects.filter(design=design_id).all().values('status', 'selfInterface')
    ws3.append(['状态', '本端端口'])  # 列名
    for data in data_sheet3:
        ws3.append([data['status'], data['selfInterface']])
    # 获取数据并插入到第4个工作表
    data_sheet4 = TaskDesignConsumable.objects.filter(design=design_id).all().values('scale', 'unit', 'quantity')
    ws4.append(['耗材规格', '耗材规格单位', '耗材数量'])  # 列名
    for data in data_sheet4:
        ws4.append([data['scale'], data['unit'], data['quantity']])
    # 保存并关闭 Excel 文件
    wb.save(file_path)
    wb.close()
    new_excel(design_id)


def export_file(request):
    '''
     生成excel表格（根据数据类型不同创建多个sheet页）
    '''
    try:
        design_id = request.GET.get("design_id")
        # 查询数据库并获取相关数据集
        data = TaskDesignAssets.objects.filter(design=design_id).values_list('assetsType', 'assetsName')
        # 按照字段值分组
        groups = {}
        for item in data:
            field_value = item[0]
            if field_value not in groups:
                groups[field_value] = []
            groups[field_value].append(item)
        # 创建新的Excel文档
        workbook = openpyxl.Workbook()
        # 针对每个字段值，创建新的Sheet页
        for field_value, group in groups.items():
            if field_value == "SERVER":
                field_value = "服务器"
            else:
                field_value = "交换机"
            worksheet = workbook.create_sheet(title=field_value)
            # 写入标题行
            worksheet.append(['类型', '资源名称'])
            # 写入数据行
            for item in group:
                worksheet.append(item)
        # 删除默认的Sheet页
        default_sheet = workbook['Sheet']
        workbook.remove(default_sheet)
        # 保存文档
        file_path = 'D:/test/output.xlsx'
        workbook.save(file_path)
        write_excel(design_id, file_path)
        zip_file_path = 'D:/zip/test.zip'
        zip_folder("D:/test/", zip_file_path)
        data = download_zip(zip_file_path)
        return data
    except Exception as e:
        return {"failed": e}