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

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

On Wed, 27 Jan 2021 03:12 Gabriel Araya Garcia, <gabrielaraya2011@gmail.com> wrote:

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

El lunes, 25 de enero de 2021 a las 6:26:18 UTC-3, ernes...@gmail.com escribió:

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")

--
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+unsubscribe@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.

--
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+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAPsfuofG1U5pShuB157YAaGksDTgQQpSW_WqzC%2BTBnN3eRMPRQ%40mail.gmail.com.