def post(self, request, *args, **kwargs): """Get form instance from upload. :returns: URL :rtype: HttpResponse """ form_class = self.get_form_class() form = self.get_form(form_class) gw_location_file = request.FILES.get('gw_location_file') gw_level_file = request.FILES.get('gw_level_file') if form.is_valid(): if gw_location_file: gw_location_file.seek(0) if str(gw_location_file).split('.')[-1] == "xls": sheet = xls_get(gw_location_file, column_limit=4) elif str(gw_location_file).split('.')[-1] == "xlsx": sheet = xlsx_get(gw_location_file, column_limit=4) sheetname = next(iter(sheet)) records = sheet[sheetname] for record in records: if record[0].lower() == 'id well': continue point = Point(x=record[3], y=record[2], srid=4326) well = GWWell.objects.create(gwwellname=record[0], gwwelllocation=point, gwwelltotallength=record[1]) if gw_level_file: gw_level_file.seek(0) if str(gw_level_file).split('.')[-1] == "xls": sheet = xls_get(gw_level_file, column_limit=4) elif str(gw_level_file).split('.')[-1] == "xlsx": sheet = xlsx_get(gw_level_file, column_limit=4) sheetname = next(iter(sheet)) records = sheet[sheetname] for record in records: if record[0].lower == 'time': continue try: well = GWWell.objects.get(gwwellname=record[3]) time = dateparse.parse_datetime(record[0]) well_level_log = GWGeologyLog.objects.create( phenomenonTime=time, resultTime=time, gw_level=record[2], reference=record[1]) well.gwwellgeology.add(well_level_log) except GWWell.DoesNotExist: pass pass return self.form_valid(form) else: return self.form_invalid(form)
def index(request): if request.method == 'POST': try: excel_file = request.FILES['excel_file'] if str(excel_file).split('.')[-1] == "xls": data = xls_get(excel_file, column_limit=5) elif str(excel_file).split('.')[-1] == "xlsx": data = xlsx_get(excel_file, column_limit=5) else: return HttpResponse("Invalid File") details = data["Detalle"] if len(details) > 1: for detail in details: if len(detail) > 0 and detail[0] != "Sucursal": Client.objects.create(sucursal=detail[0], cartera=detail[1], clientes=detail[2], fecha_alta=detail[3], saldo=detail[4]) return HttpResponse("Data Readed") except MultiValueDictKeyError: return HttpResponse("Invalid File") else: return render(request, 'excelapp/index.html')
def addExcel(request, id): errors = "" filename = str(request.FILES["file"]) if filename.endswith('.xlsx'): data = xlsx_get(request.FILES["file"]) else: data = xls_get(request.FILES["file"]) for key, sheet in data.items(): c = 0 for row in sheet: c += 1 if sheet.index(row) == 0: continue else: if validationRowXls(row, sheet): dataRecord = Data() dataRecord.name = row[sheet[0].index('name')] dataRecord.age = row[sheet[0].index('age')] dataRecord.gender = row[sheet[0].index('gender')] dataRecord.phone = row[sheet[0].index('phone')] dataRecord.email = row[sheet[0].index('email')] dataRecord.file_id = id dataRecord.save() else: errors += "Error in row" + str(c) + "\n" return errors
def __init__(self, upload_session: UploadSession): self.upload_session = upload_session _file = self.upload_session.upload_file self.total_records = 0 records = {} if _file: _file.seek(0) sheet = None if str(_file).split('.')[-1] == 'xls': sheet = xls_get(_file, column_limit=20) elif str(_file).split('.')[-1] == 'xlsx': sheet = xlsx_get(_file, column_limit=20) if sheet: try: for sheet_name in self.SHEETS: sheet_records = sheet[sheet_name][self.START_ROW:] records[sheet_name] = sheet_records self.total_records += len(sheet_records) except KeyError as e: self.upload_session.update_progress( finished=True, progress=100, status='Sheet {} is needed'.format(e) ) return self.records = records self.process()
def Parse_xl(request, format=None): try: excel_file = request.FILES['myfile'] except MultiValueDictKeyError: messages.error(request, 'Votre Upload a mal tourné') return render(request, 'users/importdb.html') if (str(excel_file).split('.')[-1] == "xls"): data = xls_get(excel_file, column_limit=5) elif (str(excel_file).split('.')[-1] == "xlsx"): data = xlsx_get(excel_file, column_limit=5) else: messages.info(request, 'Veuillez importer un fichier de type Excel') return render(request, 'users/importdb.html') Clients = data["Worksheet"] if (len(Clients) > 1): # We have company data for Worksheet in Clients: if (len(Worksheet) > 0): # The row is not blank if (Worksheet[0] != "id"): # This is not header # Fill ending columns with blank if (len(Worksheet) < 5): i = len(Worksheet) while (i < 5): Worksheet.append("") i += 1 # Check if Client exist # Assume that Client name is unique c = Users.objects.filter(name=Worksheet[1]) if (c.count() == 0): Users.objects.create(name=Worksheet[1], prenom=Worksheet[2], email=Worksheet[3], city=Worksheet[4]) messages.success(request, 'Votre base de donnée a bien été Sauvegardé!') return render(request, 'users/importdb.html')
def get_input_post(request): #print("I am called") try: excel_file = request.FILES['files'] print("hello world") if (str(excel_file).split('.')[-1] == "xls"): data = xls_get(excel_file) print(data) print("xls file called") elif (str(excel_file).split('.')[-1] == "xlsx"): data = xlsx_get(excel_file) print("xlsx file called") else: return render(request, 'index.html') json_data = json.dumps(data) #print(json_data) j = 1 for i in range(5): data['Sheet1'][i + 1][j] = str( int(data['Sheet1'][i + 1][j]) + int(data['Sheet1'][i + 1][j])) print(data['Sheet1'][i + 1][j]) save_data("my_output.xlsx", data) return render(request, 'success.html') except MultiValueDictKeyError: return render(request, 'index.html')
def post(self, request, format=None): try: excel_file = request.FILES['files'] except MultiValueDictKeyError: return redirect('upload/') if (str(excel_file).split('.')[-1] == "xls"): data = xls_get(excel_file, column_limit=4) elif (str(excel_file).split('.')[-1] == "xlsx"): data = xlsx_get(excel_file, column_limit=4) else: return redirect('upload/')
def create(request): form = DataForm() if request.method == 'POST': if request.POST.get("input_excel"): try: excel_file = request.FILES['file-excel'] except MultiValueDictKeyError: return render(request, 'home_data_training_create.html', {'form': form}) if str(excel_file).split('.')[-1] == 'xls': data = xls_get(excel_file, column_limit=5) elif str(excel_file).split('.')[-1] == 'xlsx': data = xlsx_get(excel_file, column_limit=5) else: return render(request, 'home_data_training_create.html', {'form': form}) if data is not None: save_excel_to_db(data) return redirect('home:data-training') else: form = DataForm(request.POST) if form.is_valid(): no = form.cleaned_data['no'] ppm_ch4 = float(form.cleaned_data['ppm_ch4']) ppm_c2h4 = float(form.cleaned_data['ppm_c2h4']) ppm_c2h2 = float(form.cleaned_data['ppm_c2h2']) fault = form.cleaned_data['fault'] sum = ppm_ch4 + ppm_c2h4 + ppm_c2h2 ch4 = (ppm_ch4 / sum) * 100 c2h4 = (ppm_c2h4 / sum) * 100 c2h2 = (ppm_c2h2 / sum) * 100 try: param = Data.objects.get(no=no) except Data.DoesNotExist: param = Data() param.no = no param.ppm_ch4 = ppm_ch4 param.ppm_c2h4 = ppm_c2h4 param.ppm_c2h2 = ppm_c2h2 param.persen_ch4 = ch4 param.persen_c2h4 = c2h4 param.persen_c2h2 = c2h2 param.fault = fault param.save() return redirect('home:data-training') return render(request, 'home_data_training_create.html', {'form': form})
def general(request): if request.method == 'POST': numbers = "" message = request.POST.get("message") parents = request.POST.get("parents") teachers = request.POST.get("teachers") entered_numbers = request.POST.get("entered_numbers") if parents: students = Student.objects.filter(completed=False) for student in students: numbers += f"{student.parent_sms}," if teachers: teachers = Staff.objects.filter(has_left=False) for teacher in teachers: numbers += f"{teacher.sms_number}," if entered_numbers: entered_numbers = entered_numbers.split(",") for number in entered_numbers: if number.strip(): numbers += f"{number.strip()}," excel_file = request.FILES.get("files") if excel_file: COLUMN_LIMIT = 1 file_extention = str(excel_file).split(".")[-1] if (file_extention == "xls") or file_extention == "xlsx": file_extention = str(excel_file).split(".")[-1] if (file_extention == "xls"): data = xls_get(excel_file, column_limit=COLUMN_LIMIT) else: data = xlsx_get(excel_file, column_limit=COLUMN_LIMIT) try: contacts = data["sms_contacts"] for row in contacts: if (len(row) > 0): if (row[0].isdigit() and len(row[0]) == 10): numbers += f"{row[0]}," except KeyError as err: request.session[ "error_message"] = f"The uploaded file has no sheet named '{sheet_name}'" return False else: request.session["error_message"] = "File format not acceptable" return redirect("sms:index") send_bulk_sms.delay(name="bulk", message=message, numbers=numbers) return redirect("sms:index")
def post(self, request, *args, **kwargs): try: excel_file = request.FILES["excelFile"] except MultiValueDictKeyError: return redirect(self.get_success_url()) if str(excel_file).split(".")[-1] == "xls": data = xls_get(excel_file, column_limit=15) elif str(excel_file).split(".")[-1] == "xlsx": data = xlsx_get(excel_file, column_limit=15) users = data["Usuarios"] categories = data["Categorias"] subjects = data["Assuntos"] groups = data["Grupos"] usersList = [] usersErrors = [] categoriesList = [] categoriesErrors = [] subjectsList = [] subjectsErrors = [] groupsList = [] groupsErrors = [] if len(users) > 1: usersList, usersErrors = importUsers(users) if len(categories) > 1: categoriesList, categoriesErrors = importCategories( categories, usersList) if len(subjects) > 1: subjectsList, subjectsErrors = importSubjects( subjects, usersList, categoriesList) if len(groups) > 1: groupsList, groupsErrors = importGroups(groups, subjectsList, usersList) messages.success(self.request, _("Environment imported successfully!")) context = self.get_context_data(**kwargs) context["usersErrors"] = usersErrors context["categoriesErrors"] = categoriesErrors context["subjectsErrors"] = subjectsErrors context["groupsErrors"] = groupsErrors return self.render_to_response(context)
def save_sponsor_data(request): data_len = request.query_params.get('size') # 데이터 길이 수동 입력 # 시트 이름은 어떻게 하지! 일단 하드코딩 : 'Form Responses 1' # 간단한 인증 : 홈페이지 데이터라서 api_key = request.query_params.get('apiKey') # 아주 간단하게 인증 : post 니까! if api_key != settings.API_KEY: return Response({"message": "Request Permission Error."}, status=status.HTTP_403_FORBIDDEN) # 파일 받아오기 file = request.FILES['file'] data = xlsx_get(file, row_limit=int(data_len)) # DATA_LEN[year] data['Form Responses 1'] = data['Form Responses 1'][1:] # header 제외 content_list = [] for row in data['Form Responses 1']: # print('row all', row) # print('name', row[1]) # print('homepage', row[6]) # print('introduction', row[7]) data = { 'name': row[1], 'homepage_link': row[6], 'introduction': row[7], 'sponsorship_rating': row[8], } # 이미 있는지 확인해봐야 하는데! 후원기업 이름 기반으로 확인하기 existing_sponsor = Sponsor.objects.filter(name=row[1]).first() if existing_sponsor is not None: # 이미 있으면 sponsor_serializer = SponsorSerializer(existing_sponsor, data=data) else: sponsor_serializer = SponsorSerializer(data=data) if not sponsor_serializer.is_valid(raise_exception=True): return Response({"message": "Request Body Error."}, status=status.HTTP_409_CONFLICT) # sponsor = Sponsor(introduction=row[7], homepage_link=row[6], sponsorship_rating=row[8], name=row[1]) sponsor_serializer.save() # sponsor_serializer = SponsorSerializer(instance=sponsor) content_list.append(sponsor_serializer.data) return Response({ 'size': len(content_list), 'data': content_list, }, status=status.HTTP_200_OK)
def post(self, request, *args, **kwargs): """Get form instance from upload. :returns: URL :rtype: HttpResponse """ form_class = self.get_form_class() form = self.get_form(form_class) gw_location_file = request.FILES.get('gw_location_file') gw_level_file = request.FILES.get('gw_level_file') location_records = None level_records = None if form.is_valid(): if gw_location_file: gw_location_file.seek(0) if str(gw_location_file).split('.')[-1] == "xls": sheet = xls_get(gw_location_file, column_limit=4) elif str(gw_location_file).split('.')[-1] == "xlsx": sheet = xlsx_get(gw_location_file, column_limit=4) sheetname = next(iter(sheet)) location_records = sheet[sheetname] if gw_level_file: gw_level_file.seek(0) if str(gw_level_file).split('.')[-1] == "xls": sheet = xls_get(gw_level_file, column_limit=4) elif str(gw_level_file).split('.')[-1] == "xlsx": sheet = xlsx_get(gw_level_file, column_limit=4) sheetname = next(iter(sheet)) level_records = sheet[sheetname] job = well_from_excel.delay(location_records, level_records) request.session['task_id'] = job.id return self.form_valid(form) else: return self.form_invalid(form)
class ParseExcel(APIView) def post(self, request),format = None: try: excel_file = request.FILES['parse.xlsx'] except MultiValueDictKeyError: return redirect(<your_upload_file_failed_url>) data = xls_get(excel_file, column_limit=4) elif (str(excel_file).split(‘.’)[-1] == “xlsx”): data = xlsx_get(excel_file, column_limit=4) else:
def get_records(self): """ Get records form upload session """ _file = self.upload_session.upload_file records = [] if _file: _file.seek(0) sheet = None if str(_file).split('.')[-1] == 'xls': sheet = xls_get(_file, column_limit=20) elif str(_file).split('.')[-1] == 'xlsx': sheet = xlsx_get(_file, column_limit=20) if sheet: sheet_name = next(iter(sheet)) records = sheet[sheet_name] return records[self.START_ROW:]
def upload_questions(request, assess_id): current_assessment = Assessment.objects.get(assess_id = assess_id) if request.method == 'POST': files = request.FILES try: excel_file = request.FILES['files'] if (str(excel_file).split('.')[-1] == "xlsx"): data = xlsx_get(excel_file, column_limit=9) assessments = data["Assessment"] if (len(assessments) > 1): for assessment in assessments: if (len(assessment) > 0): # It is not a header row if (assessment[0] != "No"): # Check if data length is less than 8 if (len(assessment) == 9): Question.objects.create(question_text = assessment[2], answer_text = assessment[3], question_type = assessment[1], question_points = assessment[8], option_a = assessment[4], option_b = assessment[5], option_c = assessment[6], option_d = assessment[7], assessment = current_assessment) messages.success(request, f'✔️ Assessment uploaded') questions = Question.objects.filter(assessment=current_assessment.assess_id) is_published = current_assessment.is_published points_added = Question.objects.filter(assessment = current_assessment).aggregate(Sum('question_points')) return render(request, "ELearn/assessment_home.html", {"questions":questions, "module_id":current_assessment.module, "is_published":is_published, "assessment":current_assessment, "points_added":points_added}) except MultiValueDictKeyError: return redirect('home') return render(request, "ELearn/upload_questions.html", {"assessment":current_assessment})
def post(self, request, format=None): try: excel_file = request.FILES['fileToUpload'] except MultiValueDictKeyError: # return reverse_lazy("schools:students:list") return HttpResponse("No File") if str(excel_file).split('.')[-1] == 'xls': data = xls_get(excel_file, column_limit=6) elif str(excel_file).split('.')[-1] == 'xlsx': data = xlsx_get(excel_file, column_limit=6) else: return HttpResponse("Error of Invalid file") students = data["Students"] register_students.delay(request.user, students) return redirect("schools:students:list")
def post(self, request, format=None): """ Import Industries from xlsx/xls files :param request: :type request: :param format: :type format: :return: :rtype: """ try: excel_file = request.data["files"] except MultiValueDictKeyError: raise Exception("Excel files needs to be uploaded!!") file_type = str(excel_file).split('.')[-1] if file_type == "xls": data = xls_get(excel_file, start_row=1) elif file_type == "xlsx": data = xlsx_get(excel_file, start_row=1) else: return Response('Only xls and xlsx file types are allowed') try: for obj in data['Sheet1']: try: Industry.objects.get_or_create(naics_code=obj[1], industry_type=obj[2]) except Exception as error: settings.LOGGER.info( "IndustryImportView >> post >> error: {}, data: {}". format(error, obj)) return Response({ "result": "Industry Successfully exported!!", "status": status.HTTP_201_CREATED }) except Exception as error: settings.LOGGER.info( "IndustryImportView >> post >> error: {}".format(error)) return Response({"error": "{}".format(error)}, status=status.HTTP_400_BAD_REQUEST)
def handle_input_file(self, request): """ Saves the information provided by the excel file into database. Adds error if the file is not an excel file """ try: excel_file = request.FILES['file'] except MultiValueDictKeyError: raise MultiValueDictKeyError("MultiValueDictKeyError") if (str(excel_file).split('.')[-1] == "xls"): data = xls_get(excel_file, column_limit=2)['Post'] elif (str(excel_file).split('.')[-1] == "xlsx"): data = xlsx_get(excel_file, column_limit=2)['Post'] else: messages.error(request, "this is not an excel file") return user = request.user return self.save_data(data, user)
def _get_data_from_sheet(request, excel_file, COLUMN_LIMIT, sheet_name): try: file_extention = str(excel_file).split(".")[-1] if (file_extention == "xls"): data = xls_get(excel_file, column_limit=COLUMN_LIMIT) else: data = xlsx_get(excel_file, column_limit=COLUMN_LIMIT) except Exception as err: request.session["error_message"] = str(err) return False try: data = data[sheet_name] return data except KeyError as err: request.session[ "error_message"] = f"The uploaded file has no sheet named '{sheet_name}'" return False except Exception as err: request.session["error_message"] = str(err) return False
def post(self, request, format=None): try: data_file = request.FILES['file'] data = xlsx_get(data_file, column_limit=4) rows = data.get('Sheet1') print(rows) for row in rows: data = { "name": row[0], "email": row[1], "mobile_no": row[2], "description": row[3], "owner": self.request.user.id } serializer = DataSerializer( data=data) if serializer.is_valid(raise_exception=True): serializer.save() return Response({"status": "confirmed"}) except: return Response({"status": "invalid_file_format"})
def get_context_data(self, **kwargs): context = super(UploadSessionDetailView, self).get_context_data(**kwargs) if self.object.uploader != self.request.user.id: raise PermissionDenied('You do not have permission.') # construct excel excel = {} for row_status in self.object.uploadsessionrowstatus_set.order_by( 'row'): # create header if not excel or row_status.sheet_name not in excel: _file = self.object.upload_file.path sheet = None if str(_file).split('.')[-1] == 'xls': sheet = xls_get(_file, column_limit=20) elif str(_file).split('.')[-1] == 'xlsx': sheet = xlsx_get(_file, column_limit=20) if sheet: sheet_records = sheet[ row_status.sheet_name][:BaseUploader.START_ROW] excel[row_status.sheet_name] = { 'header': sheet_records, 'size': len(sheet_records[0]), 'body': {}, 'id': row_status.sheet_name.replace(' ', '_') } try: excel[row_status.sheet_name]['body'][row_status.row] except KeyError: excel[row_status.sheet_name]['body'][row_status.row] = [ '' ] * excel[row_status.sheet_name]['size'] excel[row_status.sheet_name]['body'][row_status.row][ row_status. column] = row_status.note if row_status.note else 'Skipped' context['excel'] = excel return context
def daftar_buku(request): daftar_buku = data_buku.objects.all() data = "" company = '' if request.method == "POST": try: excel_file = request.FILES["fl"] except MultiValueDictKeyError: return HttpResponse('Salah') if (str(excel_file).split(".")[-1] == "xls"): data = xls_get(excel_file, column_limit=13) elif (str(excel_file).split(".")[-1] == "xlsx"): data = xlsx_get(excel_file, column_limit=13) else: print("salah") key = ''.join(data.keys()) solve = data[key] solved = 0 for i in range(len(solve)): if len(solve[i]) != 0 : solved += 1 else : pass for i in range(1,solved): try : data_buku.objects.create(judul_buku=solve[i][1],jenis_buku=solve[i][2],penulis=solve[i][3],tgl_terbit=solve[i][4],penerbit=solve[i][5],jumlah_buku=solve[i][6]) messages.add_message(request, messages.INFO, 'Buku berhasil ditambahkan') return redirect('home:daftar_buku') except: messages.add_message(request, messages.WARNING, 'Buku gagal ditambahkan') return redirect('home:daftar_buku') return render(request, 'home/daftar_buku.html',{'daftar_buku':daftar_buku})
def handle(self, *args, **options): xls_file = options['xls_file'] row_start = options['row_start'] n = 0 data = xlsx_get(xls_file) for r in data['Import']: n += 1 if len(r) == 3: if '.' in r[0]: #logger.info('row: {}'.format(r[9])) self.stdout.write('row {}'.format(n)) #m = re.search('.*event\/(.+?)\/change.*', r[9]) try: mp = MedProc.objects.get(code=r[0]) except MedProc.DoesNotExist: mp = None if mp: if r[2] > 0: mp.price_old = mp.price mp.price = r[2] if mp.title != r[1]: mp.title = r[1] mp.save() else: mp = MedProc(code=r[0], title=r[1], title_check=r[1], price=r[2]) mp.save() self.stdout.write( self.style.SUCCESS('Successfully import %d rows' % (n)))
def post(self, request): try: excel_file = request.FILES['file'] data = {} if str(excel_file).split('.')[-1] == 'xls': data = xls_get(excel_file, column_limit=50) elif str(excel_file).split('.')[-1] == 'xlsx': data = xlsx_get(excel_file, column_limit=50) data = data['ResearchProductivity'][6:][0:] for d in data: if d[0] != 'end of records': title = d[1] year_accomplished = d[2] classification = 1 psced_classification = d[5] if not d[3]: classification = 2 conference_level = 1 conference_title = d[9] conference_date = d[14] conference_venue = d[15] if d[11]: conference_level = 2 elif d[12]: conference_level = 3 elif d[13]: conference_level = 4 record_len = len( Record.objects.filter( title=title, year_accomplished=year_accomplished, classification=Classification.objects.get( pk=classification), psced_classification=PSCEDClassification.objects. get(pk=psced_classification))) if record_len == 0: record = Record( title=title, year_accomplished=year_accomplished, classification=Classification.objects.get( pk=classification), psced_classification=PSCEDClassification.objects. get(pk=psced_classification)) record.save() UserRecord(record=record, user=request.user).save() else: continue Conference(title=conference_title, conference_level=ConferenceLevel.objects.get( pk=conference_level), date=conference_date, venue=conference_venue, record=record).save() if d[16]: publication_name = d[23] sn_list = "".join(d[24].split()).split(',') isbn = '' issn = '' isi = '' for sn in sn_list: if sn.upper().find('ISBN:') >= 0: isbn = sn.replace('ISBN:', '') elif sn.upper().find('ISSN:') >= 0: issn = sn.replace('ISSN:', '') elif sn.upper().find('ISI:') >= 0: isi = sn.replace('ISI:', '') publication_level = 1 if d[20]: publication_level = 2 elif d[21]: publication_level = 3 elif d[22]: publication_level = 4 year_published = d[18] Publication( name=publication_name, isbn=isbn, issn=issn, isi=isi, publication_level=PublicationLevel.objects.get( pk=publication_level), year_published=year_published, record=record).save() if d[25]: budget_type = 1 budget_allocation = d[30] funding_source = d[31] if d[28]: budget_type = 2 elif d[20]: budget_type = 3 Budget( budget_type=BudgetType.objects.get(pk=budget_type), budget_allocation=budget_allocation, funding_source=funding_source, record=record).save() if d[32]: industry = d[34] institution = d[35] collaboration_type = 1 if len(d) >= 38: if d[37]: collaboration_type = 2 elif len(d) >= 39: if d[38]: collaboration_type = 3 Collaboration( collaboration_type=CollaborationType.objects.get( pk=collaboration_type), industry=industry, institution=institution, record=record).save() else: break messages.success(request, 'Import Success!') except (MultiValueDictKeyError, KeyError, ValueError, OSError): messages.error(request, "Some rows have invalid values") print('Multivaluedictkeyerror/KeyError/ValueError/OSError') except (DataError, ValidationError): messages.error(request, "The form is invalid") print('DataError/ValidationError') return redirect('records-index')
def Extract_dt(request): # Handle file upload if request.user.is_authenticated: if request.method == 'POST': form = DocFileForm(request.POST, request.FILES) if form.is_valid(): try: excel_file = request.FILES['docfile'] except: return JsonResponse({"error": "File not found"}) if (str(excel_file).split('.')[-1] == "xls"): data = xls_get(excel_file, column_limit=10) elif (str(excel_file).split('.')[-1] == "xlsx"): data = xlsx_get(excel_file, column_limit=10) else: return JsonResponse({"error": "Invalid File Format"}) data = data[list(data.keys())[0]] data = to_dict(data) try: cat = Seller.objects.get(id=request.user.id).bs_category except: return redirect('404') if cat == "Literature and Stationary": Product_List.objects.filter( seller=request.user.id).delete() # try: for i in data: Product_List( name=i["title"], brand=i["publisher"], base_price=i["price"], rating=i["rating"], total_ratings=i["total ratings"], stock=i["stock"], description=i["description"] if "description" in i.keys() else "No description provided by Seller", additional=json.dumps({ "author": i["author"], "pages": i["pages"], "language": i["language"], "publication date": i["publication date"] }), seller=request.user.id, ).save() # except: # return JsonResponse({"error":"Bad Request"}) else: return JsonResponse({"error": "Bad Request"}) return JsonResponse({"success": True}) else: form = DocFileForm() # A empty, unbound form return render(request, 'sheet_upload.html', {'form': form}) return redirect('404')
def upload_excel(request): if request.method == 'POST': try: excel_file = request.FILES.get('income_excel_file') if excel_file == None: messages.error(request,'Excel file required') return redirect('import_income') if not (excel_file.name.endswith('.xls') or excel_file.name.endswith('.xlsx')): messages.error(request,'Please Upload a Excel file.') return redirect('import_income') if excel_file.multiple_chunks(): messages.error(request,"Uploaded file is too big (%.2f MB)." % (excel_file.size/(1000*1000),)) return redirect('import_income') if excel_file.name.endswith('.xls'): data = xls_get(excel_file, column_limit=4) elif excel_file.name.endswith('.xlsx'): data = xlsx_get(excel_file, column_limit=4) else: messages.error(request,'Please Upload a Excel file.') return redirect('import_income') keys_excel = list(data.keys()) income_excel_data = data[keys_excel[0]] try: income_excel_data.remove([]) except: pass if len(income_excel_data) > 11: messages.error(request,'Please upload a excel file with less than 10 rows.') return redirect('import_income') if IncomeSource.objects.filter(user = request.user, source='Loaded From Excel'): excel_income_source = IncomeSource.objects.get(user = request.user, source='Loaded From Excel') else: excel_income_source = IncomeSource.objects.create(user = request.user, source='Loaded From Excel') excel_income_source.save() headers = income_excel_data.pop(0) headers = [c.lower() for c in headers] if headers != ['date', 'source', 'description', 'amount']: income_send_error_mail(request,excel_file.name,'Excel') messages.error(request,'Please Check if the format of excel file is correct.') return redirect('import_income') income_count = 0 for row in income_excel_data: if(len(row) != 4): break if not row[0] == '': if isinstance(row[0],datetime.date): date = row[0] else: date = row['date'].split('-') try: date = datetime.date(2000 + int(date[2]) ,int(date[1]),int(date[0])) except: date = datetime.date.today() else: date = datetime.date.today() if not row[1] == '': source = row[1].strip().lower().capitalize() if IncomeSource.objects.filter(user = request.user, source = source).exists(): income_source = IncomeSource.objects.get(user = request.user, source = source) else: income_source = IncomeSource.objects.create(user = request.user, source = source) income_source.save() else: income_source = excel_income_source if not row[2] == '': description = row[2].strip() else: description = 'Loaded From Excel' if not row[3] == '': Income.objects.create( user = request.user, amount = float(row[3]), date = date, description = description, source = income_source ).save() income_count += 1 income_send_success_mail(request,excel_file.name,income_count,'Excel') messages.success(request,'Incomes are saved from excel file.') return redirect('income') except Exception as e: income_send_error_mail(request,excel_file.name,'Excel') print(repr(e)) messages.error(request,'Please Check if the format of excel file is correct.') return redirect('import_income')
def post(self, request): try: file = request.FILES['file'] if (str(file).split('.')[-1] == "xls"): data = xls_get(file, column_limit=12) elif (str(file).split('.')[-1] == "xlsx"): data = xlsx_get(file, column_limit=12) else: return redirect('/pollingstations') # The data is in a sheet labelled PS # ToDo:Refactor We'll refactor this later. For now, I need to get the data into the application # So this function might be a long with multiple ifs, but it'll be okay. admin_user = User.objects.get(pk=1) excel_data = data['PS'] clean_data = excel_data[7:] for row in clean_data: if (len(clean_data) > 0): serial_no, district_code, district_name, county_code, county_name, subcounty_code, subcounty_name, parish_code, parish_name, polling_station_code, polling_station_name, voter_count = row # Save District # ToDo - Exclude excel column titles if District.objects.filter(name=district_name, code=district_code).exists(): district = District.objects.get(name=district_name, code=district_code) else: if district_name: district = District.objects.create( name=district_name, code=district_code, created_by=admin_user, updated_by=admin_user) # Save Counties if County.objects.filter(name=county_name, code=county_code).exists(): county_ = County.objects.get(name=county_name, code=county_code) else: if county_name: county_ = County.objects.create( name=county_name, code=county_code, district=district, created_by=admin_user, updated_by=admin_user) # Save Sub counties if Subcounty.objects.filter(name=subcounty_name, code=subcounty_code).exists(): subcounty = Subcounty.objects.get(name=subcounty_name, code=subcounty_code) else: if subcounty_name: subcounty = Subcounty.objects.create( name=subcounty_name, code=subcounty_code, county=county_, created_by=admin_user, updated_by=admin_user) # Save Parishes if Parish.objects.filter(name=parish_name, code=parish_code).exists(): pass else: if parish_name and parish_name: Parish.objects.create(name=parish_name, code=parish_code, subcounty=subcounty, created_by=admin_user, updated_by=admin_user) if county_: # Save Polling Stations if Pollingstation.objects.filter( name=polling_station_name, code=polling_station_code, county=county_).exists(): pass else: if polling_station_name and polling_station_name: Pollingstation.objects.create( name=polling_station_name, code=polling_station_code, county=county_, total_voters=voter_count, created_by=admin_user, updated_by=admin_user) return redirect('/pollingstation') except MultiValueDictKeyError: print('Exception caught') return redirect('/pollingstation')
def upload(request): if request.method == 'POST': form = UploadFileForm(request.POST, request.FILES) if form.is_valid(): try: excel_file = request.FILES['files'] if (str(excel_file).split('.')[-1] == "xls"): data = xls_get(excel_file, column_limit=7) elif (str(excel_file).split('.')[-1] == "xlsx"): data = xlsx_get(excel_file, column_limit=7) unit_stations = data["unit_station_names"] loan_status = data["loan_status"] loans = data["loans"] #adding unit stations data to the database if (len(unit_stations) > 2): for i in range(1, len(unit_stations)): #to avoid making duplicate records if not Unit_station_names.objects.filter( station_name=unit_stations[i][1]): Unit_station_names.objects.create( station_name=unit_stations[i][1], daily_target=unit_stations[i][2], monthly_target=unit_stations[i][3]) else: #to update only the daily target and monthly target values that have changed station = Unit_station_names.objects.get( station_name=unit_stations[i][1]) station.daily_target = unit_stations[i][2] station.monthly_target = unit_stations[i][3] station.save() #adding loan status data to the database if (len(loan_status) > 2): for j in range(1, len(loan_status)): #to avoid making duplicate records if not Loan_status.objects.filter( loan_status=loan_status[j][1]): Loan_status.objects.create( loan_status=loan_status[j][1]) #adding loans data to the database if (len(loans) > 2): for k in range(1, len(loans)): #to avoid making duplicate records if not Loans.objects.filter(loan_code=loans[k][2]): station = Unit_station_names.objects.get( id=loans[k][4]) status = Loan_status.objects.get(id=loans[k][6]) Loans.objects.create(loan_date=loans[k][0], due_date=loans[k][1], loan_code=loans[k][2], loan_amount=loans[k][3], customer_station=station, customer_id=loans[k][5], loan_status=status) messages.info(request, 'Processed successfully!!!') return HttpResponseRedirect('/upload/') except: #used to raise exceptions if the excel document is not formatted properly messages.info( request, 'This is not a valid document for the above process') return HttpResponseRedirect('/upload/') else: messages.info( request, 'The was an error processing your upload.Check your excel file.' ) return HttpResponseRedirect('/upload/') else: form = UploadFileForm() return render(request, 'home/upload.html', {'form': form})
def register_from_excel(request): if request.method == "POST": file_form = FileUploadForm(request.POST, request.FILES) if file_form.is_valid(): excel_file = file_form.cleaned_data['choose_file'] if (str(excel_file)).split('.')[-1] == "xls": # read the file and loop though to get the sheets available file_data = xls_get(excel_file) elif (str(excel_file)).split('.')[-1] == "xlsx": file_data = xlsx_get(excel_file, column_limit=10) failed_entries = [] saved_entries = 0 if len(file_data) > 1: # check to see the file has one sheet msg = '\nError Bad Excel File....\nFile Contains One Sheet...!!' messages.error(request, msg) return redirect('accounts:view_members') for data_obj in file_data: # loop through each sheet sheet_obj = file_data[data_obj] sheet_len = len(sheet_obj) header_indexes = [] if sheet_len > 1: # we have data for header in sheet_obj[0]: # get the headers if header.lower() == "first name": # change all to lower case and map the indexes header_indexes.append(sheet_obj[0].index(header)) elif header.lower() == 'middle name': header_indexes.append(sheet_obj[0].index(header)) elif header.lower() == 'last name': header_indexes.append(sheet_obj[0].index(header)) elif header.lower() == 'gender': header_indexes.append(sheet_obj[0].index(header)) elif header.lower() == 'email': header_indexes.append(sheet_obj[0].index(header)) elif header.lower() == 'residency': header_indexes.append(sheet_obj[0].index(header)) elif header.lower() == 'phone number': header_indexes.append(sheet_obj[0].index(header)) elif header.lower() == 'occupation': header_indexes.append(sheet_obj[0].index(header)) elif header.lower() == 'citizenship': header_indexes.append(sheet_obj[0].index(header)) elif header.lower() == 'marital status': header_indexes.append(sheet_obj[0].index(header)) else: sheet_obj[0].remove(header) if len(header_indexes) != 10: msg = '\nFile Contains Bad Header Format, Check File And Try Again' messages.error(request, msg) return redirect('accounts:view_members') else: i = 1 # loop trough each row while i < sheet_len: if len(sheet_obj[i]) != 0: available_row = sheet_obj[i] try: gender = available_row[header_indexes[3]] marital_status = available_row[header_indexes[9]] if len(gender) == 1: # if gender is one character implement good variable if gender.lower() == 'm': gender = 'Male' elif gender.lower() == 'f': gender = 'Female' else: gender = 'Other' if len( marital_status) == 1: # if marital_status is one character implement a proper variable if marital_status.lower() == 'm': marital_status = 'Married' elif marital_status.lower() == 'd': marital_status = 'Divorced' elif marital_status.lower() == 'w': marital_status = 'Widowed' else: marital_status = 'Single' ChurchMember.objects.create( first_name=available_row[header_indexes[0]], middle_name=available_row[header_indexes[1]], last_name=available_row[header_indexes[2]], gender=gender, email=available_row[header_indexes[4]], residency=available_row[header_indexes[5]], phone_number=available_row[header_indexes[6]], occupation=available_row[header_indexes[7]], citizenship=available_row[header_indexes[8]], marital_status=marital_status, ) saved_entries += 1 except: failed_entries.append(i) i += 1 else: # no data pass if len(failed_entries) == 0: msg = 'Success All Members Registered, Total: {}'.format(saved_entries) messages.success(request, msg) return redirect('accounts:view_members') else: msg = '\nRegitered Memebrs:{}, Failed:{}, RowsFailed:{}'.format( saved_entries, len(failed_entries), [x for x in failed_entries] ) messages.error(request, msg) return redirect('accounts:view_members') else: messages.error(request, file_form.errors) return redirect('accounts:view_members') else: file_form = FileUploadForm() template = 'accounts/file_upload_form.html' context = { 'form_file': file_form, } html_form = render_to_string( template, context, request=request ) return JsonResponse({"html_response": html_form})
def get(self, request): data = xlsx_get("upc/django-model-try.xlsx") print(list(data)) Product_sheet = list(data)[0] Machine_sheet = list(data)[1] Mixing_sheet = list(data)[2] Type_sheet = list(data)[3] Temp_sheet = list(data)[4] Speed_sheet = list(data)[5] Insulation_sheet = list(data)[6] Condition_sheet = list(data)[7] SubstrateType_sheet = list(data)[8] Layer_sheet = list(data)[9] ABYeildDensity_sheet = list(data)[11] Pressure_sheet = list(data)[12] data1 = data[Product_sheet] for products in data1: Product.objects.create(product_name=products[0]) data2 = data[Machine_sheet] for machines in data2: Machine.objects.create(machine_name=machines[0]) data3 = data[Mixing_sheet] for mixings in data3: MixingChamber.objects.create(mixing_name=mixings[0]) data4 = data[Type_sheet] for types in data4: ApplicationType.objects.create(app_type=types[0]) data5 = data[Temp_sheet] for temps in data5: Temperature.objects.create(temp=temps[0]) data6 = data[Speed_sheet] for speeds in data6: Speed_of_spray.objects.create(speed=speeds[0]) data7 = data[Insulation_sheet] for insulations in data7: Hose_insulation.objects.create(insulation=insulations[0]) data8 = data[Condition_sheet] for conditions in data8: Hose_condition.objects.create(condition=conditions[0]) data9 = data[SubstrateType_sheet] for types in data9: Substrate_type.objects.create(type=types[0]) data10 = data[Layer_sheet] for layers in data10: Layer.objects.create(number=layers[0], reduce=layers[1]) data11 = data[ABYeildDensity_sheet] print(data11) for d in data11: print(d[0]) getABHostHeat.objects.create(sprayer_forms=d[0], Ambient_temp=d[1], a_side_for_00=d[2], a_side_for_01=d[3], a_side_for_02=d[4], a_side_for_03=d[5], b_side_for_00=d[6], b_side_for_01=d[7], b_side_for_02=d[8], b_side_for_03=d[9], hose_heat_for_00=d[10], hose_heat_for_01=d[11], hose_heat_for_02=d[12], hose_heat_for_03=d[13], Y_for_00=d[14], Y_for_01=d[15], Y_for_02=d[16], Y_for_03=d[17], D_for_00=d[18], D_for_01=d[19], D_for_02=d[20], D_for_03=d[21]) data12 = data[Pressure_sheet] for d in data12: getPressureset.objects.create(mixing=d[0], machine_Type=d[1], Pressure_for_wall=d[2], Pressure_for_ceiling=d[3]) Q1 = Questions.objects.create(q="What type of foam are you spraying?") Q2 = Questions.objects.create(q="What kind of machine are you using?") Q3 = Questions.objects.create( q="What mixing chamber size are you using?") Q4 = Questions.objects.create(q="Are you spraying walls or ceilings?") Q5 = Questions.objects.create(q="What is the ambient temperature?") Q6 = Questions.objects.create(q="What is the substrate temperature?") Q7 = Questions.objects.create( q="What is the starting drum temperature?") Q8 = Questions.objects.create(q="What is the humidity level?") Q9 = Questions.objects.create(q="What is speed of spraying?") Q10 = Questions.objects.create(q="Is the hose well insulated?") Q11 = Questions.objects.create( q="Is hose in the sun, on hot asphalt,in the rain, or snow?") Q12 = Questions.objects.create(q="Substrate type?") Q13 = Questions.objects.create(q="How many layers will you spray?") status_header = { 'status': status.HTTP_201_CREATED, 'message': "Objects made successfully", } return Response(status_header)