Django-Excel导入导出

# 批量导入数据(速度快) def importdata(localpath: str, db: str, foreignkey): data = pandas.read_excel(localpath, dtype='str', keep_default_na=False) # 读取本地上传的excel表内容, dtype='str'防止读取001为1这种情况，keep_default_na=False是防止空值为NULL。 verbosename = db._meta.fields # 获取被导入数据库的字段 rows = data.shape[0] # 获取excel表的行数 columns = data.shape[1] # 获取excel表的列数 querysetlist = [] # 用于储存数据 eachdata = {} # 用于储存每一行数据 # 外键需要添加_id ，与数据库中的字段保持一致 for i in range(len(verbosename)): if verbosename[i].name in foreignkey: # 判断是否为外键 verbosename[i].name = verbosename[i].name + '_id' if len(verbosename) == columns: # 必须保证二者长度一致
，顺序一致 。 for i in range(rows): for j in range(len(verbosename)): eachdata[verbosename[j].name] = data.iloc[i, j] # 不会包括表头 querysetlist.append(db(**eachdata)) try: db.objects.bulk_create(querysetlist) # 批量导入数据 msg = '数据导入成功,导入数据共计{}条'.format(rows) except Exception as e: print('报错信息：{}'.format(e)) msg = '数据导入失败' return msg else: msg = '数据导入失败！请检查excel表
。' return msg

# 逐个导入数据(速度慢) def importdata2(localpath: str, db: str, foreignkey): data = pandas.read_excel(localpath, dtype='str', keep_default_na=False) # 读取本地上传的excel表内容, dtype='str'防止读取001为1这种情况。 verbosename = db._meta.fields # 获取被导入数据库的字段 # for i in range(0, len(verbosename)): # print(verbosename[i].name) rows = data.shape[0] # 获取excel表的行数 columns = data.shape[1] # 获取excel表的列数 eachdata = {} # 用于储存每一行数据 # 外键需要添加_id，与数据库中的字段保持一致 for i in range(len(verbosename)): if verbosename[i].name in foreignkey: # 判断是否为外键 verbosename[i].name = verbosename[i].name + '_id' if len(verbosename) == columns: # 必须保证二者长度一致 ，顺序一致 。 for i in range(rows): for j in range(len(verbosename)): eachdata[verbosename[j].name] = data.iloc[i, j] # 不会包括表头 try: db.objects.create(**eachdata) msg = '数据导入成功
，共计{}条'.format(rows) except Exception as e: print('报错信息：{}'.format(e)) msg = '数据导入失败' # return msg else: msg = '数据导入失败！请检查excel表
。' return msg

def import_ceping(request): """导入测评人员""" foreignkey = ['department', 'rank_id', 'post_id'] # 记录外键 # 接收excel文件存储到Media文件夹 rev_file = request.FILES.get('excel') # 判断是否有文件 if not rev_file: return JsonResponse({'code': 0, 'msg': 'Excel文件不存在'}) # 获得一个唯一的名字：uuid+hash new_name = get_random_str() # 准备写入的URL file_path = os.path.join(settings.MEDIA_ROOT, new_name + os.path.splitext(rev_file.name)[1]) # 开始写入磁盘 try: f = open(file_path, 'wb') # 分多次写入 for i in rev_file.chunks(): f.write(i) f.close() except Exception as e: return JsonResponse({'code': 0, 'msg': '写入失败，请联系管理员'}) # 读取存储在Media文件夹的数据 ex_data = importdata(file_path, Ceping, foreignkey) return JsonResponse({'code': 1, 'msg': ex_data})

# 导出数据 def exportdata(localpath, db): verbosename = db._meta.fields columnname = [] # 存储字段名 for i in range(len(verbosename)): columnname.append(verbosename[i].name) info = db.objects.values_list() # 查询数据库数据 data = pandas.DataFrame(info) try: data.to_excel(localpath, na_rep='NULL', header=columnname, index=False) msg = '数据导出成功' except Exception as e: msg = '数据导出失败' return msg