How to intergrate openpyxl with django and using the excel sheet as the database to query data

[Ernest Thuku]

Hello Everyone,

I have a python file which I run on the terminal and give me the result that I want. I want to to do the same via a web app whereby instead of interacting with the terminal, I can do that via a browser through a form. I have decided to choose django as the server. Below is the file that I need to integrate to django. It works well in the terminal. I also have the excel file.

Now my concern was how can I integrate it with django such that fields like link_name can be filed through a form and when I click a button should give the required results. Thank you fo your time.

def find_fault(request):

wb = xl.load_workbook("Find Fault App Documentation.xlsx")

link_name = input("Link Name: ")

test_location = input("Test Location: ")

distance = float(input("Distance of Fault(km): "))

error = int(input("Error margin(m): "))

nature = input("Nature of Fault: ").lower()

for sheet in wb.worksheets:

if sheet.title == link_name:

for i in range(2, sheet.max_row + 1):

cell0 = sheet.cell(i, 1)

if cell0.value == test_location:

distance1 = distance * 1000 + sheet.cell(i, 2).value

for value in range(2, sheet.max_row + 1):

cell = sheet.cell(value, 2)

cell1 = sheet.cell(value, 1)

cell2 = sheet.cell(value, 3)

if cell.value in range(int(distance1) - error, int(distance1) + error):

if nature == "kink":

print()

print("Kink at {cell1.value}")

print("Pairs at this point:")

print(cell2.value)

elif nature == "break":

print()

print("Break at {cell1.value}")

print("Pairs at this point:")

print(cell2.value)

else:

print()

print("Fault at {cell1.value}")

print("Pairs at this point:")

print(cell2.value)

for value in range(2, sheet.max_row):

cell1 = sheet.cell(value, 2)

cell2 = sheet.cell(value + 1, 2)

cell3 = sheet.cell(value, 4)

cell4 = sheet.cell(value, 1)

if distance1 in range(cell1.value, cell2.value):

if distance1 in range(cell1.value + error, cell2.value - error):

if nature == "kink":

print()

print("Kink at {(distance1 - cell1.value) / 1000} "

"km from {cell4.value}")

print(

"Pair at this point >> {cell3.value}")

elif nature == "break":

print()

print("Break at {(distance1 - cell1.value) / 1000} "

"km from {cell4.value}")

print(

"Pair at this point >> {cell3.value}")

else:

print()

print("Fault at {(distance1 - cell1.value) / 1000} "

"km from {cell4.value}")

print(

"Pair at this point >> {cell3.value}")

if distance * 1000 > (sheet.cell(sheet.max_row, 2).value - (sheet.cell(i, 2).value + error)):

print()

print(

"The Distance from {test_location} to {sheet.cell(sheet.max_row, 1).value} ")

print("is about "

"{(sheet.cell(sheet.max_row, 2).value - sheet.cell(i, 2).value) / 1000} km")

[Kasper Laudrup]

Hi Ernest,

On 25/01/2021 10.25, Ernest Thuku wrote:
> Hello Everyone,
> I have a python file which I run on the terminal and give me the result
> that I want. I want to to do the same via a web app whereby instead of
> interacting with the terminal, I can do that via a browser through a
> form. I have decided to choose django as the server. Below is the file
> that I need to integrate to django. It works well in the terminal. I
> also have the excel file.
> Now my concern was how can I integrate it with django such that fields
> like link_name can be filed through a form and when I click a button
> should give the required results. Thank you fo your time.
>

Sounds like that should be very simple to do. Have you had a look at how
Django uses forms:

https://docs.djangoproject.com/en/3.1/topics/forms/

If you don't have any experience with Django (to be honest, it sounds a
bit like that) it would probably be a very good idea to start with the
tutorial:

https://docs.djangoproject.com/en/3.1/intro/tutorial01/

After you've been through that, you should have a pretty good idea on
what to do. If you have any problems after that, then share the code
here and I'm sure someone will help you with the specific problems you
might face.

Kind regards,

Kasper Laudrup

[Ernest Thuku]

Hello kasper, thank you so much for the information. I know how to use the django forms. The issue was where will I include the python file above. If I decide to uploadthe excel file and put store it in the django admin, how will I access its content. I hope its clear now maybe you can help.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/cccd51fe-e2d7-37b6-4439-afe0f0b4a4c7%40stacktrace.dk.

[Ernest Thuku]

this is the view to the model form that i have created.

def findFault(request):

form = findForm(request.POST or None)

if form.is_valid():

instance = form.save(commit=False)

instance.user = request.user

instance.save()

return redirect("home")

context = {

'form': form,

}

return render(request, 'findFault.html', context)

[Kasper Laudrup]

Hi Ernest,

On 25/01/2021 10.43, Ernest Thuku wrote:
> Hello kasper, thank you so much for the information. I know how to use
> the django forms. The issue was where will I include the python file
> above.

I'm not sure I understand the problem. The file you posted earlier just
contains a Python function. You can copy that function into your views
file or import the function like any other Python function.

> If I decide to uploadthe excel file and put store it in the
> django admin, how will I access its content. I hope its clear now maybe
> you can help.
>

Not sure what you mean by "store it in the django admin"? Do you want to
save it in a model that you can access/modify from Django Admin?

Kind regards,

Kasper Laudrup

[Ernest Thuku]

Hey Kasper, yes I ned to save it as a model so that i can use it to access information.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/eb2f8e35-4a93-c2a6-030c-4584bb3090f4%40stacktrace.dk.

[Kasper Laudrup]

On 26/01/2021 15.19, Ernest Thuku wrote:
> Hey Kasper, yes I ned to save it as a model so that i can use it to
> access information.
>

OK, so what you really want to do is to create a model with the same
fields as the ones in your spreadsheet and then import your data into
that model?

/Kasper

[Ernest Thuku]

Yeah that is what I am talking about. I need  to integrate this screenshot below in django now. The link name, distance etc to be in  a form.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/3d0d33b8-f422-485c-ff59-3b4dcf5c5f74%40stacktrace.dk.

[Kasper Laudrup]

On 26/01/2021 21.37, Ernest Thuku wrote:
> Yeah that is what I am talking about. I need  to integrate this
> screenshot below in django now. The link name, distance etc to be in  a
> form.
>

I'm not really sure I understand what you mean. You say you want to
import some existing data to a database (a Django model) which I assume
is a one time operation. I don't understand why you talk about
screenshots and forms.

If you really "just" need to import some existing data into a database
usable by Django, then start by mapping the columns from the sheet into
something similar in a Django model and define your model.

I'm sure someone can be a lot more helpful than me with that. I'm mostly
trying to get you to describe what it actually is you want to achieve
and what issues you're having since I don't think you've described that
very clearly so far making it hard for anyone to help you.

Best of luck.

Kasper Laudrup

[Gabriel Araya Garcia]

In top of views files (views.py) you must put:

import openpyxl

from openpyxl import Workbook

from openpyxl.styles import Alignment,Border,Font,PatternFill,Side

from openpyxl.styles import colors

from openpyxl.styles import Font, Color,Fill

from openpyxl.styles.borders import BORDER_THIN

from openpyxl.drawing.image import Image as XLIMG

...and the view which prepare the excel out, you should review this example:

# CARTOLA DE RECAUDACION en excel

@login_required(login_url='login_ini')

def acsv(request):

nom_arch = nombrearch() # Se forma string para nombre de archivo excel

string_nombre = 'pac'+nom_arch

query = Pauta_aux.objects.all().order_by('rut') # viene filtrada x rango de fecha

reg_x = 0

if query:

for fech_x in query:

reg_x = reg_x + 1

else:

# estas dos instrucciones van juntas siempre

messages.error(request, "No existen movimientos que mostrar !!")

return redirect("info")

mes_x = fechapautas.strftime('%m')

ano_x = fechapautas.strftime('%Y')

fecha_ini = str(ano_x)+"-"+str(mes_x).zfill(2)+"-01 00:00:00"

#total dias del mes

totdias = calendar.monthrange(int(ano_x),int(mes_x))[1] 

fecha_fin = str(ano_x)+"-"+str(mes_x).zfill(2)+"-"+str(totdias)+" 00:00:00"

wb = Workbook()

ws = wb.create_sheet("hoja1",0)

ws.column_dimensions['A'].width = 5

ws.column_dimensions['B'].width = 12 # rut paciente

ws.column_dimensions['C'].width = 36 # nombre paciente

ws.column_dimensions['D'].width = 17 # fecha

ws.column_dimensions['E'].width = 11 # rut cuid.

ws.column_dimensions['F'].width = 23 # nombre cuidador

ws.column_dimensions['G'].width = 12 # tipo cuidador

ws.column_dimensions['H'].width = 11 # rut cuid.

ws.column_dimensions['I'].width = 23 # nombre cuidador

ws.column_dimensions['J'].width = 12 # tipo cuidador

ws.column_dimensions['K'].width = 11 # rut cuid.

ws.column_dimensions['L'].width = 23 # nombre cuidador

ws.column_dimensions['M'].width = 12 # tipo cuidador

ws.column_dimensions['Q'].width = 12 # rut cuid.

ws.column_dimensions['R'].width = 14# nombre cuidador

ws.column_dimensions['S'].width = 12 # tipo cuidador

ws.column_dimensions['T'].width = 14 # tipo cuidador

r=4 # posicion de la primera fila

ws.cell(row=r-3,column=2).value = "CARTOLA DE RECAUDACION"

ws.cell(row=r-3,column=7).value = "1=Contratado"

ws.cell(row=r-2,column=7).value = "2=Extra"

ws.cell(row=r-3,column=20).value = "1=Normal"

ws.cell(row=r-2,column=20).value = "2=Domingo"

ws.cell(row=r-1,column=20).value = "3=Festivo"

ws.cell(row=r,column=2).value = "Rut paciente"

ws.cell(row=r,column=3).value = "Paciente"

ws.cell(row=r,column=4).value = "Fecha pauta"

ws.cell(row=r,column=5).value = "Rut turno 1"

ws.cell(row=r,column=6).value = "Cuidador t1"

ws.cell(row=r,column=7).value = "Tipo Cuid t1"

ws.cell(row=r,column=8).value = "Rut turno 2"

ws.cell(row=r,column=9).value = "Cuidador t2"

ws.cell(row=r,column=10).value = "Tipo Cuid t2"

ws.cell(row=r,column=11).value = "Rut turno 3"

ws.cell(row=r,column=12).value = "Cuidador t3"

ws.cell(row=r,column=13).value = "Tipo Cuid t3"

ws.cell(row=r,column=14).value = "$ turno 1"

ws.cell(row=r,column=15).value = "$ turno 2"

ws.cell(row=r,column=16).value = "$ turno 3"

ws.cell(row=r,column=17).value = "$ paciente t1"

ws.cell(row=r,column=18).value = "$ paciente t2"

ws.cell(row=r,column=19).value = "$ paciente t3"

ws.cell(row=r,column=20).value = "recargo"

ws.cell(row=r,column=21).value = "Tot.turnos"

cell_range = ws['B1':'T4']

#cell_range.bold = True

tot1=0 # valores de cuidador

tot2=0

tot3=0

tot_pac1 = 0 # valores de paciente (o lo que pagael apoderado)

tot_pac2 = 0 # valores de paciente (o lo que pagael apoderado)

tot_pac3 = 0 # valores de paciente (o lo que pagael apoderado)

va1=0

va2=0

va3=0

tRecauda = 0

subtot = 0

rut_x = ''

r=r+1

for q in query: # pauta_aux - dia a dia

if q.rut != rut_x:

ws.cell(row=r,column=20).value = "Subtotal:"

ws.cell(row=r,column=21).value = subtot

r=r+1

ws.cell(row=r,column=20).value = "Tot.Anticipo:"

totAnticipo = anticipos(rut_x,fecha_ini,fecha_fin)

ws.cell(row=r,column=21).value = totAnticipo

r=r+1

ws.cell(row=r,column=20).value = "Recauda:"

ws.cell(row=r,column=21).value = subtot - totAnticipo

tRecauda = 0

subtot = 0

va1=0

va2=0

va3=0

rut_x = q.rut # paciente

r=r+2

ws.cell(row=r,column=2).value = q.rut 

ws.cell(row=r,column=3).value = q.paciente 

ws.cell(row=r,column=4).value = q.fecha   # fecha de la pauta

ws.cell(row=r,column=5).value = q.rut_t1   # rut cuidador

ws.cell(row=r,column=6).value = q.turno1 # nombre cuidador

ws.cell(row=r,column=7).value = q.tipo_turno1 # Contratado - Extra

ws.cell(row=r,column=8).value = q.rut_t2   # rut cuidador

ws.cell(row=r,column=9).value = q.turno2 # nombre cuidador

ws.cell(row=r,column=10).value = q.tipo_turno2 # Contratado - Extra

ws.cell(row=r,column=11).value = q.rut_t3   # rut cuidador

ws.cell(row=r,column=12).value = q.turno3   # nombre cuidador

ws.cell(row=r,column=13).value = q.tipo_turno3 # Contratado - Extra

ws.cell(row=r,column=14).value = q.valor_t1 # valor cuidador1

ws.cell(row=r,column=15).value = q.valor_t2 # valor cuidador2

ws.cell(row=r,column=16).value = q.valor_t3 # valor cuidador3

ws.cell(row=r,column=17).value = q.valor_p1 # valor paciente

ws.cell(row=r,column=18).value = q.valor_p2 # valor paciente

ws.cell(row=r,column=19).value = q.valor_p3 # valor paciente

#con recargo

if q.reca_cui != '1':

if q.valor_p1 != None:

ws.cell(row=r,column=17).value = q.valor_p1 * 1.5

if q.valor_p2 != None:

ws.cell(row=r,column=18).value = q.valor_p2 * 1.5

if q.valor_p3 != None:

ws.cell(row=r,column=19).value = q.valor_p3 * 1.5

if q.valor_p1 != None:

va1 = q.valor_p1

if q.valor_p2 != None:

va2 = q.valor_p2

if q.valor_p3 != None:

va3 = q.valor_p3

#con recargo

if q.reca_cui != '1': 

if q.valor_p1 != None:

va1 = q.valor_p1 * 1.5

if q.valor_p2 != None:

va2 = q.valor_p2  * 1.5

if q.valor_p3 != None:

va3 = q.valor_p3 * 1.5

ws.cell(row=r,column=21).value = va1 + va2 + va3 

subtot = subtot + va1 + va2 + va3

if q.valor_t1 != None:

tot1 = tot1 + q.valor_t1

if q.valor_p1 != None:

tot_pac1 = tot_pac1 + q.valor_p1

if q.valor_t2 != None:

tot2 = tot2 + q.valor_t2

if q.valor_p2 != None:

tot_pac2 = tot_pac2 + q.valor_p2

if q.valor_t3 != None:

tot3 = tot3 + q.valor_t3

if q.valor_p3 != None:

tot_pac3 = tot_pac3 + q.valor_p3

ws.cell(row=r,column=20).value = q.reca_cui # recargo cuidador

r=r+1  # contador defilas  

ws.cell(row=r,column=20).value = "Subtotal:"

ws.cell(row=r,column=21).value = subtot

r=r+1

ws.cell(row=r,column=20).value = "Tot.Anticipo:"

totAnticipo = anticipos(rut_x,fecha_ini,fecha_fin)

ws.cell(row=r,column=21).value = totAnticipo

r=r+1

ws.cell(row=r,column=20).value = "Recauda:"

ws.cell(row=r,column=21).value = subtot - totAnticipo

tRecauda = 0

#rut_x = q.rut

r=r+1

ws.delete_rows(5, 4) # elimina la fila 5, y mas 3 hacia abajo

response = HttpResponse(content_type='application/vnd.ms-excel')

response['Content-Disposition'] = 'attachment; filename='+string_nombre+'.xlsx'

wb.save(response)

return response

This is running in production well now, but if there is another way to do this, please write me to: gabrielaraya2011<arroba>gmail.com

[Ernest Thuku]

Well thank you so much for this...I really appreciate..let me try it and I will let you know

--

You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/93c1a65c-eaa1-4580-9937-7d85f7e49a63n%40googlegroups.com.