Django-Model导出带验证的excel表单

class RetrieveModelMixin:
    """
    允许获得模板及对应key
    """
    def retrieve(self, request, *args, **kwargs):
        pk = request.parser_context["kwargs"]["pk"]
        model = self.serializer_class.Meta.model
        fields = model._meta.fields

        filter_item = lambda _: _.editable
        fields_maps = {item.name: item.verbose_name for item in fields if filter_item(item)}
        if pk == "__csv":  # 模板文件下载
            response = HttpResponse(content_type="text/csv")
            response["Content-Disposition"] = f'attachment; filename="{self.basename}_model.csv"'
            response.write(codecs.BOM_UTF8)
            writer = csv.writer(response)
            writer.writerow([item.verbose_name for item in fields if filter_item(item)])
            return response
        elif pk == "__xlsx": # 模板xlsx下载
            cell_req = NamedStyle(name='req',border=Border(top=Side(style='thick', color="00FF6600")))
            cell_blank = NamedStyle(name='blank',border=Border(top=Side(style='thick', color="0000FFFF")))
            cell_styles = []
            
            def add_valid(worksheet,item,col):
                """[设定认证器]]

                Args:
                    worksheet ([type]): [description]
                    item ([type]): [列]
                    col ([type]): [col字母]

                Raises:
                    ValueError: [description]
                """
                col = col.strip().upper()
                cell_styles.append(lambda : setattr(worksheet[f'{col}1'],'style',cell_blank if item.blank else  cell_req))
                dv = None
                if item.choices:
                    if not re.match('[A-z]*',col):
                        raise ValueError('invalid col')
                    dv = DataValidation(type="list", formula1=f'"{",".join([v.label for k, v in dict(item.choices).items() if v.label])}"', allow_blank=True)
                    dv.prompt = '请选择列表中的选项'
                    dv.promptTitle = 'List Selection'
                elif isinstance(item,models.IntegerField):
                    dv = DataValidation(type="whole")
                    dv.prompt = '请输入一个整数'
                    dv.promptTitle = 'Decimal'
                elif isinstance(item,(models.DateField,models.DateTimeField,)):
                    dv = DataValidation(type="date")
                    dv.prompt = '请输入一个日期或时间'
                    dv.promptTitle = 'DateTime'
                elif isinstance(item,models.TimeField):
                    dv = DataValidation(type="time")
                    dv.prompt = '请输入一个时间'
                    dv.promptTitle = 'Time'
                elif isinstance(item,models.GenericIPAddressField):
                    dv = DataValidation(type="custom",formula1='=AND((LEN(A2)-LEN(SUBSTITUTE(A2,".","")))=3,ISNUMBER(SUBSTITUTE(A2,".","")+0))')
                    dv.prompt = '请输入一个IP'
                    dv.promptTitle = 'IPaddress'
                if not dv:
                    return
                
                dv.promptTitle = item.__class__.__name__
                dv.error ='输入内容有误'
                dv.errorTitle = 'Invalid Entry'
                worksheet.add_data_validation(dv)
                dv.add(f'{col}1:{col}1048576')
                    
            def excel_column_name(n):
                """[数字转列名]

                Args:
                    n ([type]): [description]

                Returns:
                    [type]: [description]
                """
                name = ''
                while n > 0:
                    n, r = divmod (n - 1, 26)
                    name = chr(r + ord('A')) + name
                return name
                
            wb = Workbook()
            ws = wb.active
            header,body = [],[]
            keys = { item:num for num,item in enumerate(model._meta.export_order)}
            for num,item in  enumerate( filter(filter_item, sorted(fields ,key=lambda _: keys.get(_.name,1000)))) :
                header.append(item.verbose_name)
                add_valid(ws,item,excel_column_name(num+1))
            ws.append(header)
            [_() for _ in cell_styles]
            ws.append(body)
            response = HttpResponse(content=save_virtual_workbook(wb), content_type='application/ms-excel')
            response["Content-Disposition"] = f'attachment; filename="{self.basename}_model.xlsx"'
            return response
        elif pk == "__fields":  # 模板对应翻译
            return Response(
                {
                    "data": {item.verbose_name: item.name for item in fields if filter_item(item)},
                    "error": "",
                }
            )
        elif pk == "__export_all":  # 导出所有文档
            datas = [obj.to_dict() for obj in model.objects.all()]
            if datas:
                response = HttpResponse(content_type="text/csv")
                response["Content-Disposition"] = f'attachment; filename="{self.basename}_all.csv"'
                response.write(codecs.BOM_UTF8)
                writer = csv.writer(response)
                writer.writerow([fields_maps.get(key, key) for key in datas[0].keys()])
                for item in datas:
                    writer.writerow(list(item.values()))
                return response
            else:
                return Response(
                    {
                        "detail": "empty datas",
                    },
                    status=400,
                )
        instance = self.get_object()
        serializer = self.get_serializer(instance)
        return Response(serializer.data)

def excel_column_name(n):
    """Number to Excel-style column name, e.g., 1 = A, 26 = Z, 27 = AA, 703 = AAA."""
    name = ''
    while n > 0:
        n, r = divmod (n - 1, 26)
        name = chr(r + ord('A')) + name
    return name

def excel_column_number(name):
    """Excel-style column name to number, e.g., A = 1, Z = 26, AA = 27, AAA = 703."""
    n = 0
    for c in name:
        n = n * 26 + 1 + ord(c) - ord('A')
    return n