Django利用xlrd将excel表格数据导入到model数据库中

django-idcops平台之前一直都没开发在线设备导入的功能,近期得空,刚好一些定制的系统又有这种需求,所以就把这块功能开发了。关于Django中如何将数据导出到excel表格请查阅《Django中利用xlwt将Model中的数据写入excel表格》。

Django中数据的导入和导出有很大的区别,导出的时候可以利用框架model已有的数据解析将数据按照现有的规则导出。

而导入则需要根据具体model开发相关的数据解耦方法才能导入。刚好,django-idcops使用了很多必填外键,必填字符串,多对多这种类型的model设计。因此,在导入的时候就需要写更多的耦合函数。

下面是django-idcops 在线设备的model字段及对应类型:

>>> from idcops.models import Online                                       
>>> for f in Online._meta.get_fields(): f, "{}".format(not f.blank)
... 
(<django.db.models.fields.AutoField: id>, 'False')
(<django.db.models.fields.CharField: mark>, 'False')
(<django.db.models.fields.related.ForeignKey: creator>, 'True')
(<django.db.models.fields.related.ForeignKey: operator>, 'False')
(<django.db.models.fields.DateTimeField: created>, 'True')
(<django.db.models.fields.DateTimeField: modified>, 'False')
(<django.db.models.fields.NullBooleanField: actived>, 'False')
(<django.db.models.fields.NullBooleanField: deleted>, 'False')
(<django.db.models.fields.related.ForeignKey: onidc>, 'False')
(<django.db.models.fields.SlugField: name>, 'True')
(<django.db.models.fields.related.ForeignKey: rack>, 'True')
(<django.db.models.fields.related.ForeignKey: client>, 'True')
(<django.db.models.fields.SlugField: sn>, 'True')
(<django.db.models.fields.CharField: ipaddr>, 'False')
(<django.db.models.fields.CharField: model>, 'True')
(<django.db.models.fields.related.ForeignKey: style>, 'True')
(<django.db.models.fields.SlugField: status>, 'True')
(<django.db.models.fields.related.ManyToManyField: units>, 'False')
(<django.db.models.fields.related.ManyToManyField: pdus>, 'False')
(<django.db.models.fields.related.ManyToManyField: tags>, 'False')
(<django.contrib.contenttypes.fields.GenericRelation: comment>, 'False')

进一步提取必须填写的字段如下:

>>> for f in Online._meta.get_fields():                   
...   if not f.blank: f
... 
<django.db.models.fields.related.ForeignKey: creator>
<django.db.models.fields.DateTimeField: created>
<django.db.models.fields.SlugField: name>
<django.db.models.fields.related.ForeignKey: rack>
<django.db.models.fields.related.ForeignKey: client>
<django.db.models.fields.SlugField: sn>
<django.db.models.fields.CharField: model>
<django.db.models.fields.related.ForeignKey: style>
<django.db.models.fields.SlugField: status>
<django.db.models.fields.related.ManyToManyField: units> # 因涉及到机柜中展示在线设备的位置,所以这个也是必填
<django.db.models.fields.related.ManyToManyField: pdus> # 选填
<django.db.models.fields.related.ManyToManyField: tags> # 选填
>>>
>>> need_fields = [f.name for f in Online._meta.get_fields() if not f.blank]
>>> extra = ['units', 'pdus', 'tags']
>>> need_fields + extra
['creator', 'created', 'name', 'rack', 'client', 'sn', 'model', 'style', 'status', 'units', 'pdus', 'tags']
>>>

以下是完整的导入在线设备的代码:

# -*- coding: utf-8 -*-
from __future__ import unicode_literals

import re
# import json
import xlrd
import operator
from functools import reduce
# from io import BytesIO

from django.db.models import Q
# from django.conf import settings
from django.utils.timezone import datetime

from idcops.lib.utils import (
    shared_queryset, get_content_type_for_model
)
from idcops.lib.tasks import log_action
from idcops.models import (
    Option, Rack, Client, Unit, Pdu, User, Online
)


CreatorId = 1


def import_online(path, onidc_id):
    fileds = [
        'name', 'creator', 'rack', 'client', 'created', 'onidc',
        'sn', 'model', 'ipaddr', 'style', 'units', 'pdus', 'tags'
    ]
    workbook = xlrd.open_workbook(path)
    sheets = workbook.sheet_names()
    worksheet = workbook.sheet_by_name(sheets[0])
    # 设置导入错误日志记录到一个字典中
    handler_error = []
    handler_warning = []
    handler_success = []
    for index, row in enumerate(worksheet.get_rows(), 1):
        # header = index
        if index == 1:
            # 跳过表头
            continue
        if index == 2:
            # 获取字段名称
            headers = [h.value for h in row]
            continue
        if index > 503:
            # 每次只处理500条数据
            msg = "一次最多导入500条数据"
            handler_error.append(msg)
            break
        data = dict(zip(headers, [k.value for k in row]))
        raw = {k: data.get(k) for k in fileds}
        created = datetime.strptime(data.get('created'), '%Y-%m-%d')
        raw.update(**dict(created=created, sn=str(data.get('sn'))))
        verify = Online.objects.filter(name=raw.get('name'))
        if verify.exists():
            msg = "第{}行:{}设备已存在".format(index, raw.get('name'))
            handler_error.append(msg)
            continue
        else:
            style = get_or_create_style(raw.get('style'), onidc_id)
            creator = get_creator(raw.get('creator'))
            # 获取机柜信息
            rack, err = get_rack(raw.get('rack'), onidc_id)
            if not rack:
                msg = "第{}行:{}".format(index, err)
                handler_error.append(msg)
                continue
            # 获取客户信息
            client, err = get_or_create_client(raw.get('client'), onidc_id)
            if not client:
                msg = "第{}行:{}".format(index, err)
                handler_error.append(msg)
                continue
            # 实例化在线设备
            instance = Online(
                created=created, style=style, creator=creator,
                rack=rack, client=client, name=raw.get('name'),
                sn=raw.get('sn'), ipaddr=raw.get('ipaddr'),
                model=raw.get('model'), onidc_id=onidc_id
            )
            instance.save()
            # 保存U位
            units, err = clean_units(raw.get('units'), rack.pk)
            if units:
                for u in units:
                    instance.units.add(u)
                units.update(actived=False)
                instance.save()
            else:
                msg = "第{}行:{}".format(index, err)
                handler_error.append(msg)
                # U位不对,删除本实例
                instance.delete()
                continue
            handler_success.append(instance.name)
            log_action(
                user_id=creator.pk,
                content_type_id=get_content_type_for_model(instance, True).pk,
                object_id=instance.pk,
                action_flag="新增",
                created=instance.created
            )
            # 保存PDU
            pdus, err = clean_pdus(raw.get('pdus'), rack.pk)
            if pdus:
                for p in pdus:
                    instance.pdus.add(p)
                pdus.update(actived=False)
                instance.save()
            else:
                msg = "第{}行:{}".format(index, err)
                handler_warning.append(msg)
            # 保存TAGS
            tags = clean_tags(raw.get('tags'), onidc_id, creator.pk)
            if tags:
                for t in tags:
                    instance.tags.add(t)
                instance.save()
    total = (index-2)
    return handler_error, handler_warning, handler_success, total


def get_creator(username):
    fields = ['first_name', 'username', 'mobile']
    query = [Q(**{k: username.strip()}) for k in fields]
    query_str = reduce(operator.or_, query)
    user = User.objects.filter(query_str)
    if user.exists():
        return user.first()
    else:
        return User.objects.filter().order_by('-pk').first()


def get_or_create_style(name, onidc_id):
    f = dict(
        onidc_id=onidc_id, flag='Device-Style', text=name.strip()
    )
    qs = shared_queryset(Option.objects.filter(**f), onidc_id)
    if qs.exists():
        instance = qs.first()
    else:
        extra = dict(
            description=name.strip(),
            creator_id=CreatorId
        )
        f.update(**extra)
        instance = Option.objects.create(**f)
    return instance


def get_or_create_client(name, onidc_id):
    qs = Client.objects.filter(name=name.strip())
    if qs.exists():
        instance = qs.first()
    else:
        types = Option.objects.filter(
            onidc_id=onidc_id, flag='Client-Style'
        )
        if types.exists():
            default = types.filter(master=True)
            if default.exists():
                style = default.first()
            else:
                style = types.first()
        else:
            return None, "客户类型不能为空"
        instance = Client.objects.create(
            onidc_id=onidc_id, creator_id=CreatorId,
            name=name.strip(), style=style
        )
    return instance, None


def get_rack(name, onidc_id):
    """
    Return: (instance, error)
    """
    qs = Rack.objects.filter(name=name.strip(), onidc_id=onidc_id)
    if qs.filter(actived=True).exists():
        return qs.first(), None
    elif qs.filter(actived=False).exists():
        return None, "该机柜未分配使用"
    else:
        return None, "找不到该机柜"


def clean_units(data, rack_id):
    units = sorted([int(i) for i in data.split('|') if len(i) != 0])
    units_list = [
        str(x).zfill(2) for x in range(units[0], units[-1]+1)
    ]
    instances = Unit.objects.filter(rack_id=rack_id, name__in=units_list)
    if instances.exists():
        used = instances.filter(actived=False)
        if used.count() > 0:
            return None, "有U位被占用中"
        return instances, None
    else:
        return None, "找不到U位信息"


def clean_pdus(data, rack_id):
    pdus = re.split('[, |]', data)
    pdus_list = [x.strip() for x in pdus if x]
    instances = Pdu.objects.filter(rack_id=rack_id, name__in=pdus_list)
    if instances.exists():
        used = instances.filter(actived=False)
        if used.count() > 0:
            return instances.filter(actived=True), "部分PDU位被占用中"
        return instances, None
    else:
        return None, "找不到PDU位信息"


def clean_tags(tags, onidc_id, creator_id):
    tags = re.split('[, |]', tags)
    tags_list = [x.strip() for x in tags if x]
    default = dict(onidc_id=onidc_id, flag='Device-Tags')
    instances = []
    for tag in tags_list:
        default.update(**dict(text=tag))
        verify = Option.objects.filter(**default)
        if verify.exists():
            instance = verify.first()
        else:
            default.update(**dict(creator_id=creator_id))
            instance = Option.objects.create(**default)
        instances.append(instance)
    return instances

还有一些前端部分的代码这里就不展示了,感兴趣的同学可以看gitee的提交 , 最终效果

相关阅读RelatedRead

django-idcops 部署线上生成环境

Django个人博客教程-1:开发环境之编译安装python3.7

Django个人博客教程-1:开发环境

Django个人博客教程:开篇

本博启用灰色调 2020年4月4日举行全国性哀悼活动

Django 让用户自动登录

Django博客archives归档页面查询优化

暂无评论添加评论