Python django导出excel详解

编辑: admin 分类: python 发布时间: 2021-11-14 来源:互联网
目录
  • 一、基础环境
  • 二、需求
  • 三、功能实现
  • 四、源码
    • 一、序化类实现
    • 二、手动转换外键实现
  • 总结

    django restframework 导入excel内容,可以查看另外一篇文章

    一、基础环境

    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_li海外服务器https://www.68idc.cnst]
        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
     

    总结

    本篇文章就到这里了,希望能够给你带来帮助,也希望您能够多多关注hwidc的更多内容!