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)
