0

i created a form which takes user input and then perform a computation function in excel. i have written all the code but having trouble in using that function. below is the view.py function code

def dpr(request): def report(): userdate_date = request.POST.get('num1') userpath = request.POST.get('num2') # add = f'your num is {num1} and {num2} path = r'\\10.9.32.2\adm\Ash\FY 2019-20\Sale detail sheet' userpath1 = f'SALE DETAIL SHEET {userpath.upper()} 2020' abc = os.path.join(path, userpath1+'.xlsx') customers1 = [20,31,28,27,17,46,18,13,15,14,37,100125] customers2 = [100051,100062,100072,100087,100071,100070] customers3 = [100057,100056,100066,100068,100086,100091,100103,100126,100131,100145,100150,100152,100140,100165,100180] x = datetime.datetime.now() month = x.strftime("%B") df = pd.read_excel(open(abc, "rb"), sheet_name= month.upper() ,index_col=None, header= None) tarik = userdate_date # program for sumifs and countifs for customers1 and appending data to Dpr sum_list1 = [] count_list1 =[] for i in customers1: ab = df[df[2] == i] a= (ab[ab[6]== tarik][8]).sum() b= (ab[ab[6]== tarik][8]).count() sum_list1.append(round(a,2)) count_list1.append(b) app = xlwings.App(visible=False) wb = app.books.open(r'\\10.9.32.2\adm\Ash\FY 2019-20\DAILY REPORT\DAILY REPORT FORMAT.xlsx') # wb = xlwings.Book(r'\\10.9.32.2\adm\Ash\FY 2019-20\DAILY REPORT\DAILY REPORT FORMAT.xlsx') # xlwings.App().visible=False ws = wb.sheets['DPR'] ws.range('E7').options(transpose=True).value = count_list1 ws.range('F7').options(transpose=True).value = sum_list1 # program for sumifs and countifs for customers2 and appending data to Dpr sum_list2 = [] count_list2 =[] for i in customers2: ab = df[df[2] == i] a= (ab[ab[6]== tarik][8]).sum() b= (ab[ab[6]== tarik][8]).count() sum_list2.append(round(a,2)) count_list2.append(b) ws.range('E20').options(transpose=True).value = count_list2 ws.range('F20').options(transpose=True).value = sum_list2 # program for sumifs and countifs for customers3 and appending data to Dpr sum_list3 = [] count_list3 =[] for i in customers3: ab = df[df[2] == i] a= (ab[ab[6]== tarik][8]).sum() b= (ab[ab[6]== tarik][8]).count() sum_list3.append(round(a,2)) count_list3.append(b) ws.range('F27').options(transpose=True).value = sum_list3 ws.range('E27').options(transpose=True).value = count_list3 # print(sum_list3) # print(count_list3) dict1= { 'F7' : 'E49', 'F8' : 'E50', 'F9' : 'E51', 'F10' : 'E52', 'F11' : 'E53', 'F12' : 'E54', 'F13' : 'E55', 'F14' : 'E56', 'F15' : 'E57', 'F16' : 'E58', 'F17' : 'E59', 'F18' : 'E60', } for i,j in dict1.items(): num1 = 0 num1_new = ws.range(i).value num2 = ws.range(j).value ws.range(j).value = (num2+(num1_new - num1)) dict2= { 'F20' : 'E62', 'F21' : 'E63', 'F22' : 'E64', 'F23' : 'E65', 'F24' : 'E66', 'F25' : 'E67', } for i,j in dict2.items(): num1 = 0 num1_new = ws.range(i).value num2 = ws.range(j).value ws.range(j).value = (num2+(num1_new - num1)) dict3= { 'F27' : 'E69', 'F28' : 'E70', 'F29' : 'E71', 'F30': 'E72', 'F31' : 'E73', 'F32' : 'E74', 'F33' : 'E76', ## 'F34' : 'E77', 'F35' : 'E78', 'F36' : 'E79', 'F37' : 'E80', 'F38' : 'E81', 'F39' : 'E82', 'F40' : 'E83', 'F41' : 'E84', } for i,j in dict3.items(): num1 = 0 num1_new = ws.range(i).value num2 = ws.range(j).value ws.range(j).value = (num2+(num1_new - num1)) wb.save() wb.close() return render(request,'dpr.html')

below is the html page code which is dpr.html

<!DOCTYPE html> <html> <head> <meta charset='utf-8'> <meta http-equiv='X-UA-Compatible' content='IE=edge'> <title>Page Title</title> <meta name='viewport' content='width=device-width, initial-scale=1'> <link rel='stylesheet' type='text/css' media='screen' href='main.css'> <script src='main.js'></script> </head> <body> <form action="dpr" method="post"> {% csrf_token %} <b>Enter 1st num : <input type="text" name='num1'></b><br> <br> <b>Enter 2nd num : <input type="text" name='num2'></b><br> <input type="submit"> </body> </html>

my main aim is that when user gives the input in the html and press submit button and then this report function must get called and do the needful. the issue is how to use the dpr function in request rendering.

Improve this question
2
  • you have to check if the request is post in your function stackoverflow.com/questions/19132210/… Commented Jan 17, 2020 at 10:43
  • you can check if the request is post then process your excel logic else render the template. Commented Jan 17, 2020 at 10:43

2 Answers 2

Reset to default
0

You can check if the request is POST or GET. For POST request, process the data and render the page. For GET request, just render the page.

def dpr(request): if request.method == "POST": userdate_date = request.POST.get('num1') userpath = request.POST.get('num2') # add = f'your num is {num1} and {num2} path = r'\\10.9.32.2\adm\Ash\FY 2019-20\Sale detail sheet' userpath1 = f'SALE DETAIL SHEET {userpath.upper()} 2020' abc = os.path.join(path, userpath1+'.xlsx') customers1 = [20,31,28,27,17,46,18,13,15,14,37,100125] customers2 = [100051,100062,100072,100087,100071,100070] customers3 = [100057,100056,100066,100068,100086,100091,100103,100126,100131,100145,100150,100152,100140,100165,100180] x = datetime.datetime.now() month = x.strftime("%B") df = pd.read_excel(open(abc, "rb"), sheet_name= month.upper() ,index_col=None, header= None) tarik = userdate_date # program for sumifs and countifs for customers1 and appending data to Dpr sum_list1 = [] count_list1 =[] for i in customers1: ab = df[df[2] == i] a= (ab[ab[6]== tarik][8]).sum() b= (ab[ab[6]== tarik][8]).count() sum_list1.append(round(a,2)) count_list1.append(b) app = xlwings.App(visible=False) wb = app.books.open(r'\\10.9.32.2\adm\Ash\FY 2019-20\DAILY REPORT\DAILY REPORT FORMAT.xlsx') # wb = xlwings.Book(r'\\10.9.32.2\adm\Ash\FY 2019-20\DAILY REPORT\DAILY REPORT FORMAT.xlsx') # xlwings.App().visible=False ws = wb.sheets['DPR'] ws.range('E7').options(transpose=True).value = count_list1 ws.range('F7').options(transpose=True).value = sum_list1 # program for sumifs and countifs for customers2 and appending data to Dpr sum_list2 = [] count_list2 =[] for i in customers2: ab = df[df[2] == i] a= (ab[ab[6]== tarik][8]).sum() b= (ab[ab[6]== tarik][8]).count() sum_list2.append(round(a,2)) count_list2.append(b) ws.range('E20').options(transpose=True).value = count_list2 ws.range('F20').options(transpose=True).value = sum_list2 # program for sumifs and countifs for customers3 and appending data to Dpr sum_list3 = [] count_list3 =[] for i in customers3: ab = df[df[2] == i] a= (ab[ab[6]== tarik][8]).sum() b= (ab[ab[6]== tarik][8]).count() sum_list3.append(round(a,2)) count_list3.append(b) ws.range('F27').options(transpose=True).value = sum_list3 ws.range('E27').options(transpose=True).value = count_list3 # print(sum_list3) # print(count_list3) dict1= { 'F7' : 'E49', 'F8' : 'E50', 'F9' : 'E51', 'F10' : 'E52', 'F11' : 'E53', 'F12' : 'E54', 'F13' : 'E55', 'F14' : 'E56', 'F15' : 'E57', 'F16' : 'E58', 'F17' : 'E59', 'F18' : 'E60', } for i,j in dict1.items(): num1 = 0 num1_new = ws.range(i).value num2 = ws.range(j).value ws.range(j).value = (num2+(num1_new - num1)) dict2= { 'F20' : 'E62', 'F21' : 'E63', 'F22' : 'E64', 'F23' : 'E65', 'F24' : 'E66', 'F25' : 'E67', } for i,j in dict2.items(): num1 = 0 num1_new = ws.range(i).value num2 = ws.range(j).value ws.range(j).value = (num2+(num1_new - num1)) dict3= { 'F27' : 'E69', 'F28' : 'E70', 'F29' : 'E71', 'F30': 'E72', 'F31' : 'E73', 'F32' : 'E74', 'F33' : 'E76', ## 'F34' : 'E77', 'F35' : 'E78', 'F36' : 'E79', 'F37' : 'E80', 'F38' : 'E81', 'F39' : 'E82', 'F40' : 'E83', 'F41' : 'E84', } for i,j in dict3.items(): num1 = 0 num1_new = ws.range(i).value num2 = ws.range(j).value ws.range(j).value = (num2+(num1_new - num1)) wb.save() wb.close() # after processing the request, the same template will be rendered for POST request. For GET request, only template will be rendered. return render(request,'dpr.html')
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

your suggestion worked and my report got generated. thanx a lot
Happy to help, and welcome to Stack Overflow. If this answer or any other one solved your issue, please mark it as accepted.
0

Create function report(userdate_date, userpath) and call inside main function. try this. (I have not made any change in code).

Create your function

def report(userdate_date, userpath): # userdate_date = request.POST.get('num1') ## No need # userpath = request.POST.get('num2') ## No need # add = f'your num is {num1} and {num2} path = r'\\10.9.32.2\adm\Ash\FY 2019-20\Sale detail sheet' userpath1 = f'SALE DETAIL SHEET {userpath.upper()} 2020' abc = os.path.join(path, userpath1+'.xlsx') customers1 = [20,31,28,27,17,46,18,13,15,14,37,100125] customers2 = [100051,100062,100072,100087,100071,100070] customers3 = [100057,100056,100066,100068,100086,100091,100103,100126,100131,100145,100150,100152,100140,100165,100180] x = datetime.datetime.now() month = x.strftime("%B") df = pd.read_excel(open(abc, "rb"), sheet_name= month.upper() ,index_col=None, header= None) tarik = userdate_date # program for sumifs and countifs for customers1 and appending data to Dpr sum_list1 = [] count_list1 =[] for i in customers1: ab = df[df[2] == i] a= (ab[ab[6]== tarik][8]).sum() b= (ab[ab[6]== tarik][8]).count() sum_list1.append(round(a,2)) count_list1.append(b) app = xlwings.App(visible=False) wb = app.books.open(r'\\10.9.32.2\adm\Ash\FY 2019-20\DAILY REPORT\DAILY REPORT FORMAT.xlsx') # wb = xlwings.Book(r'\\10.9.32.2\adm\Ash\FY 2019-20\DAILY REPORT\DAILY REPORT FORMAT.xlsx') # xlwings.App().visible=False ws = wb.sheets['DPR'] ws.range('E7').options(transpose=True).value = count_list1 ws.range('F7').options(transpose=True).value = sum_list1 # program for sumifs and countifs for customers2 and appending data to Dpr sum_list2 = [] count_list2 =[] for i in customers2: ab = df[df[2] == i] a= (ab[ab[6]== tarik][8]).sum() b= (ab[ab[6]== tarik][8]).count() sum_list2.append(round(a,2)) count_list2.append(b) ws.range('E20').options(transpose=True).value = count_list2 ws.range('F20').options(transpose=True).value = sum_list2 # program for sumifs and countifs for customers3 and appending data to Dpr sum_list3 = [] count_list3 =[] for i in customers3: ab = df[df[2] == i] a= (ab[ab[6]== tarik][8]).sum() b= (ab[ab[6]== tarik][8]).count() sum_list3.append(round(a,2)) count_list3.append(b) ws.range('F27').options(transpose=True).value = sum_list3 ws.range('E27').options(transpose=True).value = count_list3 # print(sum_list3) # print(count_list3) dict1= { 'F7' : 'E49', 'F8' : 'E50', 'F9' : 'E51', 'F10' : 'E52', 'F11' : 'E53', 'F12' : 'E54', 'F13' : 'E55', 'F14' : 'E56', 'F15' : 'E57', 'F16' : 'E58', 'F17' : 'E59', 'F18' : 'E60', } for i,j in dict1.items(): num1 = 0 num1_new = ws.range(i).value num2 = ws.range(j).value ws.range(j).value = (num2+(num1_new - num1)) dict2= { 'F20' : 'E62', 'F21' : 'E63', 'F22' : 'E64', 'F23' : 'E65', 'F24' : 'E66', 'F25' : 'E67', } for i,j in dict2.items(): num1 = 0 num1_new = ws.range(i).value num2 = ws.range(j).value ws.range(j).value = (num2+(num1_new - num1)) dict3= { 'F27' : 'E69', 'F28' : 'E70', 'F29' : 'E71', 'F30': 'E72', 'F31' : 'E73', 'F32' : 'E74', 'F33' : 'E76', ## 'F34' : 'E77', 'F35' : 'E78', 'F36' : 'E79', 'F37' : 'E80', 'F38' : 'E81', 'F39' : 'E82', 'F40' : 'E83', 'F41' : 'E84', } for i,j in dict3.items(): num1 = 0 num1_new = ws.range(i).value num2 = ws.range(j).value ws.range(j).value = (num2+(num1_new - num1)) wb.save() wb.close()

Use the function

def dpr(request): if request.method == 'POST': userdate_date = request.POST.get('num1') userpath = request.POST.get('num2') if userdate_date and userpath: report(userdate_date, userpath) return render(request,'dpr.html')
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.