Django视图类中标准导出Excel文件模版(自用)

一、导出基类、Excel文件处理和保存

import hashlib
import os
import time

import xlsxwriter

from application import settings
from apps.web.op_drf.filters import DataLevelPermissionsFilter
from apps.web.op_drf.response import SuccessResponse
from apps.web.wsystem.models import SaveFile
from apps.web.wsystem.serializers import SaveFileSerializer


class BaseExport:
    """
    导出基类
    """
    file_path = settings.MEDIA_ROOT

    @staticmethod
    def excel_filter(request, obj):
        """
        表模型过滤器
        """
        objs = DataLevelPermissionsFilter.filter_queryset_class(request=request, class_=obj).all()
        if request.data.get("cid"):
            objs = objs.filter(id__in=request.data.get("cid"))
        return objs


class MyExport(BaseExport):
    obj = None  # 表模型
    serializer = None  # 序列化器

    # 必填
    file_name = None  # excel文件名
    file_head = None  # excel表头文字 ["序号", "姓名", "性别", "年龄", "职务", "手机","备注"]
    file_data = None  # excel表数据 ["num", "name", "gender", "age", "job", "phone","remark"]

    def excel_export(self, request):
        """
        excel导出模板
        """
        # 表名、表头、表数据
        file_name = self.file_name  # excel文件名
        file_head = self.file_head  # excel表头文字
        file_data = self.file_data  # excel表数据
        # 基础表记录
        objs = self.excel_filter(request, self.obj)
        # 需要导出的字段
        objs = objs.values(*file_data)

        # 处理对象数据为需要结构：[[],[],[]]
        data = []
        for i in objs:
            data.append([i.get(n) for n in file_data])
        # 保存与导出
        save_model = self.export_save_model(request, data, file_name, file_head)
        return SuccessResponse(save_model)

    def export_save_model(self, request, data, file_name, file_head):
        """
        导出Excel并保存到 SaveFile 文件管理中
        :param request:
        :param data: 数据源
        :param file_name: excel文件名
        :param file_head: excel表头文字
        """
        # 根据生成的字典MD5
        time_stamp = hashlib.md5(str(file_head).encode('utf8')).hexdigest()
        # 存入文件数据库中
        file_name = '.'.join(file_name.split('.')[:-1]) + str(time_stamp) + '.' + file_name.split('.')[-1]
        file_url = self.export_excel(data, file_name, file_head)
        savefile, is_exit = SaveFile.objects.get_or_create(file=file_url)
        if is_exit is True:
            savefile.name = file_name
            savefile.type = 'application/vnd.ms-excel'
            savefile.size = os.path.getsize(os.path.join(self.file_path, file_url))
            savefile.address = '本地存储'
            savefile.source = '导出'
            savefile.creator = request.user
            savefile.dept_belong_id = getattr(request.user, 'dept_id', None)
        savefile.modifier = request.user.username
        savefile.company_id = request.user.company_id
        savefile.save()
        return SaveFileSerializer(savefile).data

    def export_excel(self, data: list, file_name: str, file_head: list):
        """
        Excel 导出数据
        :param data: 数据源
        :param file_name: excel文件名
        :param file_head: excel表头文字
        :return:
        """
        month_time = time.strftime('%Y-%m-%d', time.localtime(time.time()))
        path_root = os.path.join(self.file_path, 'system', month_time)
        if not os.path.exists(path_root):
            os.makedirs(path_root)
        path_name = os.path.join(path_root, file_name)
        workbook = xlsxwriter.Workbook(str(path_name))
        worksheet = workbook.add_worksheet()

        merge_format_head = workbook.add_format({
            'border': 1,
            'align': 'left',  # 行对齐方式
            'valign': 'vcenter',  # 字体对齐方式
            'fg_color': '#f2f2f2',
        })
        merge_format_data = workbook.add_format({
            'text_wrap': 1,  # 自动换行
            'border': 1,
            'align': 'left',  # 行对齐方式
            'valign': 'vcenter',  # 字体对齐方式
        })
        # 设置每行行高
        worksheet.set_row(0, 40)
        # 添加表头
        for index, ele in enumerate(file_head):
            worksheet.write(0, index, ele, merge_format_head)
        count = 1
        for index, ele in enumerate(data):
            for k, v in enumerate(ele):
                worksheet.write(count, k, v, merge_format_data)
            count += 1
        workbook.close()
        return os.path.join('system', month_time, file_name)

二、视图导出

class TradeRefundRecordModelViewSet(CustomModelViewSet, MyExport):
        def excel_export(self, request: Request, *args, **kwargs):
        """
        此为标准导出模版范例
        """
        # 表名、表头、表数据
        file_name = "导出交易退款审批模板.xls"
        file_head = ['订单编号', '退款类型', '车牌号', '所属商户', "退款金额(元)", "时间", "审批状态"]
        file_data = ["order_id", "type", "car_number", "merchant_name", "money", "apply_time", "apply_state"]
        # 基础表记录
        objs = self.excel_filter(request, TradeRefundRecord)
        # 需要导出的字段
        objs = objs.values(*file_data)

        # 处理对象数据为需要结构：[[],[],[]]
        data = []
        apply_states = {1: "待审批", 2: "审批中", 3: "已通过", 4: "已拒绝"}
        for i in objs:
            i["apply_time"] = format_time(i.get("apply_time") / 1000)
            i["apply_state"] = apply_states.get(i.get("apply_state"))
            data.append([i.get(n) for n in file_data])

        # 保存与导出
        save_model = self.export_save_model(request, data, file_name, file_head)
        return SuccessResponse(save_model)