Python django导出excel详解_Python

一、基础环境

web架构：前后端分离，前端使用vue，后端使用django 的rest framework

django版本3.2

django-excel 版本0.0.10

djangorestframework版本3.12.4

二、需求

界面导出excel数据内容

导入model包含外键类型

三、功能实现

1.不使用序列化类：需要手动实现外键对象转换为外键值

2.使用序化类：

四、源码

				?

									#models.py

									from django.db import models

									from django.utils import timezone

									class MyITtype(models.Model):

									    name = models.CharField(verbose_name="名称", max_length=128, unique=True)

									    ittype = models.SmallIntegerField(verbose_name="类型")

									    comment = models.TextField(verbose_name="备注", blank=True, default="")

									    def __str__(self):

									        return self.name

									    class Meta:

									        db_table = "MyITtype"

									        verbose_name = "it资产类型"

									class MyAsset(models.Model):

									    ittype = models.ForeignKey(MyITtype, on_delete=models.SET_NULL, verbose_name="产品类型", null=True)

									    code = models.CharField(verbose_name="资产编码", max_length=128, unique=True)

									    buytime = models.DateField(verbose_name="入仓时间", default=timezone.now)

									    usetime = models.DateField(verbose_name="分配时间", default=timezone.now)

									    comment = models.TextField(verbose_name="规格说明", blank=True, default="")

									    user = models.CharField(verbose_name="使用人", max_length=128, blank=True, default="")

									    status = models.IntegerField(verbose_name="状态")

									    def __str__(self):

									        return self.code

									    class Meta:

									        db_table = "opGTITAsset"

									        verbose_name = "it固产"

一、序化类实现

				?

									#asset-export.py

									from rest_framework.views import APIView

									from rest_framework.response import Response

									from rest_framework.serializers import ModelSerializer

									from .models import MyAsset

									import django_excel as excel

									class ITAssetSer(ModelSerializer):

									    class Meta:

									        model = MyAsset

									        fields = '__all__'

									class ITAssetExport(APIView):

									    """"""

									    use_model = MyAsset

									    queryset = use_model.objects.all()

									    serializer_class = ITAssetSer

									    def post(self, request, *args, **kwargs):

									        try:

									            data = request.data

									            id_list = data.get("data")

									            obj_list = self.queryset.all()

									            # 如果有值，表示导出部分，否则导出全部

									            if id_list:

									                obj_list = obj_list.filter(id__in=id_list)

									            ser = self.serializer_class(instance=obj_list, many=True)

									            return MakeExcel(obj_list=ser.data, class_name=self.use_model, filename="itasset")

									        except:

									            ret = {

									                "code": 599,

									                "message": "导出数据出错"

									            }

									            return Response(ret)

									    def get(self, request, *args, **kwargs):

									        self.http_method_not_allowed(request, *args, **kwargs)

									def MakeExcel(obj_list, class_name, filename):

									    """

									    制作excel表

									    :param ser_list: 内容列表

									    :param class_name: 类名

									    :param filename: 文件名

									    :return:

									    """

									    meta_fields = class_name._meta.fields

									    name_list = [field.name for field in meta_fields]

									    header_list = [field.verbose_name for field in meta_fields]

									    wdata = [header_list]

									    for obj in obj_list:

									        data = [obj[name] for name in name_list]

									        wdata.append(data)

									    sheet = excel.pe.Sheet(wdata)

									    response = excel.make_response(pyexcel_instance=sheet, file_name=filename, file_type="xlsx")

									    return response

二、手动转换外键实现

				?

									#asset-export.py

									from rest_framework.views import APIView

									from rest_framework.response import Response

									from rest_framework.objializers import ModelSerializer

									from .models import MyAsset

									import django_excel as excel

									class ITAssetSer(ModelSerializer):

									    class Meta:

									        model = MyAsset

									        fields = '__all__'

									class ITAssetExport(APIView):

									    """"""

									    use_model = MyAsset

									    queryset = use_model.objects.all()

									    objializer_class = ITAssetSer

									    def post(self, request, *args, **kwargs):

									        try:

									            obj_list = self.use_model.objects.all()

									            return MakeExcel(obj_list=obj_list, class_name=self.use_model, filename="itasset")

									        except:

									            ret = {

									                "code": 599,

									                "message": "导出数据出错"

									            }

									            return Response(ret)

									    def get(self, request, *args, **kwargs):

									        self.http_method_not_allowed(request, *args, **kwargs)

									def MakeExcel(obj_list, class_name, filename):

									    """

									    制作excel表

									    :param obj_list: 内容列表

									    :param class_name: 类名

									    :param filename: 文件名

									    :return:

									    """

									    meta_fields = class_name._meta.fields

									    name_list = [field.name for field in meta_fields]

									    header_list = [field.verbose_name for field in meta_fields]

									    wdata = [header_list]

									 for obj in obj_list:

									        data = []

									        for name in name_list:

									            value = eval("obj." + name)  # 等价于getattr(obj,name)，即获取该对象某个字段内容

									            if isinstance(value, Model):

									                value = eval("".join(["obj." + name + "_id"]))  # 等价于obj.name_id，即获取该对象中外键对象数值

									            data.append(value)

									            # 参考例子

									            # data = [getattr(obj, name) if name != "ittype" else obj.ittype_id for name in name_list]

									        wdata.append(data)

									    sheet = excel.pe.Sheet(wdata)

									    response = excel.make_response(pyexcel_instance=sheet, file_name=filename, file_type="xlsx")

									    return response