Preface ：
One 、 Upload files ：
Two 、 analysis excel Import database
Preface ：Recently, the leaders of the company need to count the percentage of working hours spent by the technology department in each business line , and jira Current Tempo The plug-in can only count individual man hours . So I wrote a report tool , take jira Personal hours exported from excel form Import database , The proportion of working hours for processing various businesses at the back end . Later jira Of API file , Give up the previous idea , Call directly jira API Processing data , Let's not talk about this first . This blog mainly introduces Django Upload files , And then parse excel Import database .
One 、 Upload files ：Upload the file to the specified path of the server , It's very simple , There are three steps ：
1. To configure setting.py
# File upload configuration UPLOAD_ROOT = os.path.join(BASE_DIR,'upload')2. The front-end code is as follows , Use <form> Form submission ,"/upload/" Routing is configured in urls in , I don't want to say more about this .
{% extends 'base.html' %}{% block content %}<body> <form id="form" enctype="multipart/form-data" action="/upload/" method="post"> <p><input type="file" name="file"></p> <input type="submit" name=" Submit "> </form></body>{% endblock %}3. The back-end code is as follows , This code can upload files of any format , File type not verified .
@csrf_exemptdef upload(request): # root name take file Value file = request.FILES.get('file') logger.log().info('uplaod:%s'% file) # establish upload Folder if not os.path.exists(settings.UPLOAD_ROOT): os.makedirs(settings.UPLOAD_ROOT) try: if file is None: return HttpResponse(' Please select the file to upload ') # Loop binary write with open(settings.UPLOAD_ROOT + "/" + file.name, 'wb') as f: for i in file.readlines(): f.write(i) except Exception as e: return HttpResponse(e) return HttpResponse(' Upload successful ') Two 、 analysis excel Import database 1. After file upload , Next, read the data just uploaded to the server excel form , Then write it to the database . So the whole back-end code is like this ：
# take excel Data writing mysqldef wrdb(filename): # Open upload excel form readboot = xlrd.open_workbook(settings.UPLOAD_ROOT + "/" + filename) sheet = readboot.sheet_by_index(0) # obtain excel Rows and columns of nrows = sheet.nrows ncols = sheet.ncols print(ncols,nrows) sql = "insert into working_hours (jobnum,name,workingtime,category,project,date,createtime) VALUES" for i in range(1,nrows): row = sheet.row_values(i) jobnum = row[4] name = row[5] workingtime = row[2] category = row[8] project = row[1] date = xldate_as_datetime(row[3],0).strftime('%Y/%m/%d') values = "('%s','%s','%s','%s','%s','%s','%s')"%(jobnum,name,workingtime,category,project,date,datetime.datetime.now()) sql = sql + values +"," # In order to improve operation efficiency , One time data insert Enter database sql = sql[:-1] # Write to database # DataConnection It is a custom public module , Using a third-party library , To operate the database . of no avail ORM , There will be group by Etc sql It's not easy to operate . DataConnection.MysqlConnection().insert('work',sql)@csrf_exemptdef upload(request): # root name take file Value file = request.FILES.get('file') print('uplaod:%s'% file) # establish upload Folder if not os.path.exists(settings.UPLOAD_ROOT): os.makedirs(settings.UPLOAD_ROOT) try: if file is None: return HttpResponse(' Please select the file to upload ') # Loop binary write with open(settings.UPLOAD_ROOT + "/" + file.name, 'wb') as f: for i in file.readlines(): f.write(i) # write in mysql wrdb(file.name) except Exception as e: return HttpResponse(e) return HttpResponse(' Successful import ')2. After data import , Through some processing, we can get the data we want . The pie chart of one of the reports ：
This is about Django Upload excel This is the end of the article about tables and writing data to the database , More about Django Upload excel Please search the previous articles of SDN or continue to browse the related articles below. I hope you will support SDN more in the future ！
