需要的execl模板文件

1、前端VUE上传组件

前端url配置类型选择`uploadImg`

export const wuliuLogisticsordernoImportexcel= params => uploadImg({url: `lydvadmin/test/importexcel/`,params})

elementui组件

<el-form-item>
     <el-upload
          ref="upload"
          :multiple="false"
          action=""
          :limit="1"
          :show-file-list="false"
          :auto-upload="true"
          accept=".xls"
          :http-request="ImportExcelUploadRequest"
          :before-upload="ImportExcelbeforeUpload"
          :on-success="ImportExcelhandleSuccess"
          :file-list="fileList">
          <el-button size="small" type="primary" v-show="isShowBtn('xxx','批量导入','Import')">导入</el-button>
    </el-upload>
</el-form-item>

变量：

//上传文件
fileList: [],

methods方法：

//批量导入物流单号
async ImportExcelUploadRequest(param){
    var vm = this
    let obj= await wuliuLogisticsordernoImportexcel(param)
    if(obj.code == 2000) {
        vm.$message.success(obj.msg)
        vm.search()
    } else {
    vm.$message.warning(obj.msg)
    }
},
ImportExcelbeforeUpload(file){
    let isLt50M = true
    isLt50M = file.size / 1024 / 1024 < 50
if(!isLt50M){
    this.$message.error("上传文件不能超过50MB")
}
    return isLt50M
},
    ImportExcelhandleSuccess(response,file,fileList){
    this.$refs.upload.clearFiles()
},

2、后端接口实现：方法一（xlrd解析）xls格式excel

class UsersSerializer(CustomModelSerializer):
    """
    用户导入-序列化器
    """

    class Meta:
        model = Users
        read_only_fields = ["id"]
        fields = '__all__'

class UsersViewSet(CustomModelViewSet):
    """
    后台用户导入 接口
    """
    queryset = Users.objects.all().order_by("-create_datetime")
    serializer_class = UsersSerializer
    search_fields = ('name',)
    filter_fields = ('status',)

    def importexcel(self,request, *args, **kwargs):
        """
        导入execl中字段如果为数字会自动加.0 ，解决方法：execl设置该列为文本型
        """
        f = request.FILES.get('file')
        excel_type = f.name.split('.')[1]
        if excel_type in ['xls']:
            # 开始解析上传的excel表格
            wb = xlrd.open_workbook(filename=None, file_contents=f.read())
            table = wb.sheets()[0]
            rows = table.nrows  # 总行数

            data_import_list = []
            with transaction.atomic():  # 控制数据库事务交易
                for i in range(1, rows):
                    rowVlaues = table.row_values(i)
                    # print(rowVlaues[0])#第一列第一个值
                    obj = Users(name=rowVlaues[0])
                    data_import_list.append(obj)
                Users.objects.bulk_create(data_import_list)
            return SuccessResponse(data=[],msg="导入成功")
        else:
            return ErrorResponse(msg="导入失败")

3、后端接口实现：方法二（openpyxl解析）xlsx格式excel

class UsersSerializer(CustomModelSerializer):
    """
    用户导入-序列化器
    """

    class Meta:
        model = Users
        read_only_fields = ["id"]
        fields = '__all__'

class UsersViewSet(CustomModelViewSet):
    """
    后台用户导入 接口
    """
    queryset = Users.objects.all().order_by("-create_datetime")
    serializer_class = UsersSerializer
    search_fields = ('name',)
    filter_fields = ('status',)

    def importexcel(self,request, *args, **kwargs):
        """
        导入execl中字段如果为数字会自动加.0 ，解决方法：execl设置该列为文本型
        """
        f = request.FILES.get('file')
        excel_type = f.name.split('.')[1]
        filed_data_list = ['name','gender','age','mobile','address']#excel表头部对应映射字段信息
        if excel_type in ['xlsx']:
            # 开始解析上传的excel表格
            wb = openpyxl.load_workbook(filename=f,read_only=True)
            table = wb[wb.sheetnames[0]]  # 选择第一张sheet表
            rows = table.max_row  # 总行数
            columns = table.max_column #总列数
            data_import_list = []
            for row in range(1,rows+1):
                if row == 1:#跳过第一行(表头)
                    continue
                data_dict_list = []
                for column in range(1,columns+1):
                    cellVlaues = table.cell(row=row, column=column).value
                    data_dict_list.append(cellVlaues)
                data_dict = dict(map(lambda x, y: [x, y], filed_data_list, data_dict_list))#合并两个list变成dict key和value
                obj = Users(**data_dict)
                data_import_list.append(obj)
            Users.objects.bulk_create(data_import_list)
            return SuccessResponse(data=[], msg="导入成功")
        return ErrorResponse(msg="仅允许导入xlsx文件")

4、最后在app的urls中添加路由

urlpatterns = [
    .....
    path('users/importexcel/',UsersViewSet.as_view({'post':'importexcel'}), name='导入数据'),
]