django xadmin后台excel表格导入数据库

新建excel.py 导入插件\xadmin\plugins\目录下

import xadmin
from xadmin.views import BaseAdminPlugin, ListAdminView
from django.template import loader


#excel 导入
class ListImportExcelPlugin(BaseAdminPlugin):
    import_excel = False

    def init_request(self, *args, **kwargs):
        return bool(self.import_excel)

    def block_top_toolbar(self, context, nodes):
        nodes.append(loader.render_to_string('xadmin/excel/model_list.top_toolbar.import.html', context_instance=context))

xadmin.site.register_plugin(ListImportExcelPlugin, ListAdminView)

html代码如下，放入nodes下的指定路径

xadmin/excel/model_list.top_toolbar.import.html

{% load i18n %}
<div class="btn-group export">
  <a class="dropdown-toggle btn btn-default btn-sm" data-toggle="dropdown" href="#">
    <i class="icon-share"></i> 导入 <span class="caret"></span>
  </a>
  <ul class="dropdown-menu" role="menu" aria-labelledby="dLabel">
      <li><a data-toggle="modal" data-target="#export-modal-import-excel"><i class="icon-circle-arrow-down"></i> 导入 Excel</a></li>
  </ul>
    <script>
        function fileChange(target){
//检测上传文件的类型
            var imgName = document.all.submit_upload.value;
            var ext,idx;
            if (imgName == ''){
                document.all.submit_upload_b.disabled=true;
                alert("请选择需要上传的 xls 文件!");
                return;
            } else {
                idx = imgName.lastIndexOf(".");
                if (idx != -1){
                    ext = imgName.substr(idx+1).toUpperCase();
                    ext = ext.toLowerCase( );
{#                    alert("ext="+ext);#}
                    if (ext != 'xls' && ext != 'xlsx'){
                        document.all.submit_upload_b.disabled=true;
                        alert("只能上传 .xls 类型的文件!");

                        return;
                    }
                } else {
                    document.all.submit_upload_b.disabled=true;
                    alert("只能上传 .xls 类型的文件!");
                    return;
                }
            }

        }
    </script>
    <div id="export-modal-import-excel" class="modal fade">
      <div class="modal-dialog">
        <div class="modal-content">
          <form method="post" action="" enctype="multipart/form-data">
              {% csrf_token %}
          <div class="modal-header">
            <button type="button" class="close" data-dismiss="modal" aria-hidden="true">&times;</button>
            <h4 class="modal-title">导入 Excel</h4>
          </div>
          <div class="modal-body">
               <input type="file" onchange="fileChange(this)" name="excel" id="submit_upload">

          </div>
          <div class="modal-footer">
            <button type="button" class="btn btn-default" data-dismiss="modal">{% trans "Close" %}</button>
            <button class="btn btn-success" type="submit" id="submit_upload_b"><i class="icon-share"></i> 导入</button>
          </div>
          </form>
        </div><!-- /.modal-content -->
      </div><!-- /.modal-dalog -->
    </div><!-- /.modal -->

</div>

在adminx.py中重载post方法，并设置导入开关

 　　import_excel = True #控制开关


    def queryset(self):
        qs = super(CourseAdmin,self).queryset()
        qs = qs.filter(is_banner = False)
        return qs

    def save_models(self):
        #在保存课程是后统计课程的课程数
        obj = self.new_obj
        obj.save()
        if obj.course_org is not None:
            course_org = obj.course_org
            course_org.courses = Course.objects.filter(course_org=course_org).count()
            course_org.save()
    #定义重载post方法来获取excel表格中的数据
    def post(self,request,*args,**kwargs):
        if 'excel' in request.FILES:
            execl_file = request.FILES.get('excel')
            files = open_workbook(filename=None, file_contents=request.FILES['excel'].read())
            excel_into_model('course', 'Course', excel_file=files)
            return HttpResponseRedirect('/xadmin/course/course')
        return super(CourseAdmin,self).post(request,*args,**kwargs)

处理excel文件导入model方法

def excel_into_model(appname,model_name,excel_file):
    #tmp[7].verbose_name.__str__()
    try:
        appname_ = apps.get_model(appname,model_name)
        fields = appname_._meta.fields
        #导入model,动态导入
        exec('from %s.models import %s' %(appname,model_name))
    except :
        logger.info('model_name and appname is not exist')
    field_name = []
    #只导入第一个sheet中的数据
    table = excel_file.sheet_by_index(0)
    nrows = table.nrows
    table_header = table.row_values(0)
    for cell in table_header:
        for name in fields:
            if cell in name.verbose_name.__str__():
                field_name.append(name.name)
    if 'add_time' in field_name:
        field_name.remove('add_time')
    for x in range(1, nrows):
        # 行的数据,创建对象,进行报错数据
        exec('obj' + '=%s()' % model_name)
        print(len(field_name))
        for y in range(len(field_name)):
            exec ('obj.%s'%field_name[y]+'="%s"' %(table.cell_value(x,y)))
        exec ('obj.save()')