Python django导出excel详解

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_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

来源：https://blog.csdn.net/u014113686/article/details/121035842