#!/usr/bin/env python # -*- coding: utf-8 -*- import traceback from Ansjer.settings import * from django.views.generic.base import View from django.views.decorators.csrf import csrf_exempt from openpyxl import load_workbook from openpyxl.compat import range import xlrd from Service.ResponseService import * from Service.CommonService import CommonService from Model.models import Device_Info, Device_User class batchAddEquipmentView(View): @csrf_exempt def post(self, request, *args, **kwargs): request.encoding = 'utf-8' fileType = request.POST.get('fileType', None) fileName = request.FILES.get('fileName', None) if fileName != None and fileType != None: return self.batchAddEquipment(fileType, fileName) else: return ResponseJSON(800) @csrf_exempt def get(self, request, *args, **kwargs): request.encoding = 'gb2312' fileType = request.GET.get('fileType', None) fileName = request.FILES.get('fileName', None) if fileName != None and fileType != None: return self.batchAddEquipment(fileType, fileName) else: return ResponseJSON(800) #return HttpResponse(self.batchAddEquipmentfromTXTDict('D://1.txt')) #return HttpResponse(self.batchAddEquipmentfromExecl2('D:/2.xlsx')) def batchAddEquipment(self, fileType, fileName): filename = fileName.name filename = filename.replace(' ', '+') try: filePath = '/'.join((BASE_DIR, "static/SysFile", fileType)).replace('\\', '/') + '/' if not os.path.exists(filePath): os.makedirs(filePath) fullPath = filePath + filename if os.path.exists(fullPath): os.remove(fullPath) destination = open(fullPath, 'wb+') for chunk in fileName.chunks(): destination.write(chunk) destination.close() else: fullPath = filePath + filename if os.path.exists(fullPath): os.remove(fullPath) destination = open(fullPath, 'wb+') for chunk in fileName.chunks(): destination.write(chunk) destination.close() except Exception as e: errorInfo = traceback.format_exc() print('上传文件错误: %s' % errorInfo) return ResponseJSON(700, { 'details': repr(e) }) if fileType == 'TXT': response = HttpResponse(self.batchAddEquipmentfromTXT(fullPath)) return response elif fileType == 'Excel': response = HttpResponse(self.batchAddEquipmentfromExecl(fullPath)) return response def batchAddEquipmentfromExecl(self, filePath): if not os.path.isfile(filePath): return ResponseJSON(701) else: try: xlsData = xlrd.open_workbook(filename = filePath) except Exception as e: errorInfo = traceback.format_exc() print('Open the Excel file error: %s' % errorInfo) return ResponseJSON(702, { 'details': repr(e) }) else: sheetsCount = len(xlsData.sheets()) table = xlsData.sheets()[0] # 打开第一张表 nrows = table.nrows # 获取表的行数 eInfo = [] error_list = [] error_msg = {} errorCount = addCount = 0 for index in range(nrows): # 循环逐行添加 eInfo = table.row_values(index) try: User = Device_User.objects.filter(userID = eInfo[0]) except Exception as e: errorCount += 1 errorInfo = traceback.format_exc() print('error message: %s' % errorInfo) errorDict = {'UID': eInfo[2], 'error_msg': u'error message: {0}'.format(repr(e))} error_list.append(errorDict) continue if not User: errorCount += 1 errorDict = {'UID': eInfo[2], 'error_msg': u'error message: The UserID {0} does not exist!'.format(eInfo[0])} error_list.append(errorDict) continue try: tupleInfo = Device_Info.objects.get_or_create(userID_id=eInfo[0], NickName=eInfo[1], UID=eInfo[2], SID=eInfo[3], View_Account=eInfo[4], View_Password=eInfo[5], AudioFormat=eInfo[6], EventNotification=eInfo[7], ChannelIndex=eInfo[8], Online=eInfo[9], mMonitorIndex=eInfo[10], Type=eInfo[11], DebugMode=eInfo[12], NotificationMode=eInfo[13], qvgaLevel=eInfo[14], defaults={'id': CommonService.getUserID(getUser=False)}) except Exception as e: errorCount += 1 errorInfo = traceback.format_exc() print('error message: %s' % errorInfo) errorDict = {'UID': eInfo[2], 'error_msg': u'error message: {0}'.format(repr(e))} error_list.append(errorDict) continue else: if tupleInfo[1] == True: addCount += 1 else: errorCount += 1 if len(error_list) > 0: error_msg['error'] = error_list xlsData.release_resources() if xlsData: del xlsData resultDict = { "result_code": 0, "reason": u'Success'} if errorCount > 0: if len(error_msg) > 0: if sheetsCount > 1: resultDict.update({ "result": {'appending': addCount, 'errorCount': errorCount, 'error_msg': error_msg, 'sheetsCount': 'The Execl contains {0} tables!'.format(sheetsCount)}, "error_code": 704 }) else: resultDict.update({ "result": {'appending': addCount, 'errorCount': errorCount, 'error_msg': error_msg}, "error_code": 705 }) errorJSON = json.dumps(resultDict, ensure_ascii=False) return errorJSON else: if sheetsCount > 1: resultDict.update({ "result": {'appending': addCount, 'appended': errorCount, 'sheetsCount': 'The Execl contains {0} tables!'.format(sheetsCount)}, "error_code": 704, }) else: resultDict.update({ "result": {'appending': addCount, 'appended': errorCount}, "error_code": 705, }) errorJSON = json.dumps(resultDict, ensure_ascii=False) return errorJSON else: if sheetsCount > 1: resultDict.update({ "result": {'appending': addCount, 'sheetsCount': 'The Execl contains {0} tables!'.format(sheetsCount)}, "error_code": 704, }) else: resultDict.update({ "result": {'appending': addCount}, "error_code": 0, }) JSON = json.dumps(resultDict, ensure_ascii=False) return JSON def batchAddEquipmentfromExecl2(self, filePath): ''' :param filePath: :return: :remarks: 不支持旧版xls ''' #filePath = 'D:/2.xlsx' try: wb = load_workbook(filename = filePath) ws = wb.get_sheet_names() ws = wb.get_sheet_by_name(ws[0]) rows = ws.iter_rows() #cols = ws.iter_cols() #rows = ws.rows # 行数 #columns = ws.columns # 列数 except Exception as e: errorInfo = traceback.format_exc() print('error message: %s' % errorInfo) return ResponseFormal(702,repr(e)) error_list = [] error_msg = {} equipmentlists = [] errorCount = addCount = 0 for row in rows: eInfo = [] for cell in row: #print(cell.coordinate, cell.value) if cell.value == None: cell.value = '' eInfo.append(cell.value) try: User = Device_User.objects.filter(userID = eInfo[0]) except Exception as e: errorCount += 1 errorInfo = traceback.format_exc() print('error message: %s' % errorInfo) errorDict = {'UID': eInfo[2], 'error_msg': u'error message: {0}'.format(repr(e))} error_list.append(errorDict) continue if not User: errorCount += 1 errorDict = {'UID': eInfo[2], 'error_msg': u'error message: The UserID {0} does not exist!'.format(eInfo[0])} error_list.append(errorDict) continue eqValid = Device_Info.objects.filter(userID_id = eInfo[0], UID=eInfo[2]) if eqValid: errorCount += 1 errorDict = {'UID': eInfo[2], 'error_msg': u'The UID has already existed!'} error_list.append(errorDict) continue equipment = Device_Info(id=CommonService.getUserID(getUser=False), userID_id=eInfo[0], NickName=eInfo[1], UID=eInfo[2], SID=eInfo[3], View_Account=eInfo[4], View_Password=eInfo[5], AudioFormat=eInfo[6], EventNotification=eInfo[7], ChannelIndex=eInfo[8], Online=eInfo[9], mMonitorIndex=eInfo[10], Type=eInfo[11], DebugMode=eInfo[12], NotificationMode=eInfo[13], qvgaLevel=eInfo[14], ) equipmentlists.append(equipment) try: elists = Device_Info.objects.bulk_create(equipmentlists) except Exception as e: errorCount += 1 errorInfo = traceback.format_exc() print('error message: %s' % errorInfo) errorDict = {'UID': eInfo[2], 'error_msg': u'error message: {0}'.format(repr(e))} error_list.append(errorDict) else: if len(equipmentlists) != len(elists): addCount += len(elists) errorCount += (len(equipmentlists) - len(elists)) else: addCount += len(elists) if len(error_list) > 0: error_msg['error'] = error_list if errorCount == 0: return ResponseFormal(0, { 'appending': addCount }) else: return ResponseFormal(0, { 'appending': addCount, 'errorCount': errorCount, 'error_msg': error_msg, }) def batchAddEquipmentfromTXT(self, filePath): ''' txt文件批量导入数据库，通过list方式 :param filePath: :return: ''' if os.path.isfile(filePath): file = open(filePath, encoding='utf-8') eInfo = [] error_list = [] error_msg = errorDict = {} errorCount = addCount = 0 for line in file: eInfo = line.split(';') for index in range(len(eInfo)): if index > 5: eInfo[index] = int(eInfo[index]) else: continue dUser = Device_User.objects.filter(userID = eInfo[0]) if not dUser: errorCount += 1 errorDict = {"userID": eInfo[0], "error_msg'": u"The userID_id is no exist!"} error_list.append(errorDict) continue try: tupleInfo = Device_Info.objects.get_or_create(userID_id=eInfo[0], NickName=eInfo[1], UID=eInfo[2], SID=eInfo[3], View_Account=eInfo[4], View_Password=eInfo[5], AudioFormat=eInfo[6], EventNotification=eInfo[7], ChannelIndex=eInfo[8], Online=eInfo[9], mMonitorIndex=eInfo[10], Type=eInfo[11], DebugMode=eInfo[12], NotificationMode=eInfo[13], qvgaLevel=eInfo[14], defaults={'id': CommonService.getUserID(getUser=False)}) except Exception as e: errorCount += 1 errorInfo = traceback.format_exc() print('error message: %s' % errorInfo) errorDict = {'UID': eInfo[2], 'error_msg': u'error message: {0}'.format(repr(e))} error_list.append(errorDict) continue else: if tupleInfo[1] == True: addCount += 1 else: errorDict = {'UID': eInfo[2], 'error_msg': u'The UID is existed!'} error_list.append(errorDict) errorCount += 1 file.close() if len(error_list) > 0: error_msg['error'] = error_list if errorCount > 0: if len(error_msg) > 0: return ResponseFormal(0, { 'appending': addCount, 'errorCount': errorCount, 'error_msg': error_msg, }) else: return ResponseFormal(0, {'appending': addCount,'appended': errorCount,}) else: return ResponseFormal(0,{'appending': addCount}) else: return ResponseFormal(701) def batchAddEquipmentfromTXTDict(self, filePath): if os.path.isfile(filePath): file = open(filePath, encoding='utf-8') error_list = [] error_msg = {} errorCount = addCount = 0 equipmentList = [] equipmentDict = {} try: for line in file: equipmentStr = eval(line) if isinstance(eval(equipmentStr), dict): equipmentDict.update(eval(equipmentStr)) else: return ResponseFormal(706) UID = equipmentDict.get('UID', None) userID_id = equipmentDict.get('userID_id', None) dUser = Device_User.objects.filter(userID = userID_id) if dUser: if UID != None and userID_id != None: dInfo = Device_Info.objects.filter(userID_id = userID_id, UID = UID) if dInfo: errorCount += 1 continue else: equipment = Device_Info(id = CommonService.getUserID(getUser=False), **equipmentDict) equipmentList.append(equipment) else: errorCount += 1 errorDict = {'userID_id': userID_id, 'error_msg': u'The userID_id is None or UID is None!'} error_list.append(errorDict) continue else: errorCount += 1 errorDict = {'userID_id': userID_id, 'error_msg': u'The userID_id is no exist!'} error_list.append(errorDict) continue file.close() sss = Device_Info.objects.bulk_create(equipmentList) print(type(sss), sss) except Exception as e: errorInfo = traceback.format_exc() print('The error Info: %s' % errorInfo) return ResponseFormal(707,{'details':repr(e)}) else: if len(error_list) > 0: error_msg['error'] = error_list if errorCount > 0: if error_msg != None: return ResponseFormal(0, {'errorCount': errorCount, 'error': error_msg, 'appending': len(equipmentList)}) else: return ResponseFormal(0, { 'errorCount': errorCount, 'appending': len(equipmentList)}) else: return ResponseFormal(0,{'appending':len(equipmentList)}) else: return ResponseFormal(701)