In this article we will discuss how to upload an Excel file and then process the content without storing file on server.
One approach could be uploading the file, storing it in upload directory and then reading the file.
Another approach could be uploading file and reading it directly from post data without storing it in memory and displaying the data.
We will work with the later approach here.
You may create a new project or work on existing code.
If you are setting up a new project then create a new virtual environment and install Django 2.0 and openpyxl modules in virtual environment using pip.
pip install Django==2.0.3 openpyxl==2.5.1
Assuming you are working on existing project, follow the below steps to upload and process the excel file in Django.
For this article, I have created a new small project using Django 2.0.3. Source code is available on Github.
Please go through README.md file to setup the project on your system.
We have an excel file user_data.xls with below data in it.
Uploading Excel file:
URLs:
Add a URL inurls.py file of app.
from django.urls import path
from . import views
app_name = "myapp"
urlpatterns = [
path('', views.index, name='index'),
]
In Django 2.0 it is mandatory to define the app_name in urls.py file if we are going to use namespace in project urlconf.
Views:
Create a function in views with the name index . This view will be responsible to read the excel file.from django.shortcuts import render
import openpyxl
def index(request):
if "GET" == request.method:
return render(request, 'myapp/index.html', {})
else:
excel_file = request.FILES["excel_file"]
# you may put validations here to check extension or file size
wb = openpyxl.load_workbook(excel_file)
# getting a particular sheet by name out of many sheets
worksheet = wb["Sheet1"]
print(worksheet)
excel_data = list()
# iterating over the rows and
# getting value from each cell in row
for row in worksheet.iter_rows():
row_data = list()
for cell in row:
row_data.append(str(cell.value))
excel_data.append(row_data)
return render(request, 'myapp/index.html', {"excel_data":excel_data})
Here we are using openpyxl module to read Excel file in Django.
First get the excel file from FILES in request and then get the desired worksheet from the workbook. Now iterate over the rows in worksheet and for each row iterate over the cells and read the value in each cell.
We can get name of all sheets using below code.
# getting all sheets sheets = wb.sheetnames print(sheets) # output. There is only one sheet in our excel file # ['Sheet1']
wb.active.
# getting active sheet active_sheet = wb.active print(active_sheet) # output # <Worksheet "Sheet1">
You can get the value of any cell directly by using below method:
# reading a cell print(worksheet["A1"].value) # output # name
Index.html
Now create HTML form to upload the Excel file and to show its content. Use below code for the same.<html>
<head>
<title>
Excel file upload and processing : Django Example : ThePythonDjango.Com
</title>
</head>
<body style="margin-top: 30px;margin-left: 30px;">
<form action="{% url "myapp:index" %}" method="post" enctype="multipart/form-data">
{% csrf_token %}
<input type="file"
title="Upload excel file"
name="excel_file"
style="border: 1px solid black; padding: 5px;"
required="required">
<p>
<input type="submit"
value="Upload"
style="border: 1px solid green; padding:5px; border-radius: 2px; cursor: pointer;">
</form>
<p></p>
<hr>
{% for row in excel_data %}
{% for cell in row %}
{{ cell }}
{% endfor %}
<br>
{% endfor %}
</body>
</html>
Important: Do not forget to include enctype="multipart/form-data" in form.
Other settings:
- Include the myapp urlconf in project's urlconf file.from django.contrib import admin
from django.urls import path, include
urlpatterns = [
path('admin/', admin.site.urls),
path('', include('myapp.urls', namespace="myapp")),
]
- Add the myapp to the list of installed apps.
INSTALLED_APPS = [
'django.contrib.admin',
'django.contrib.auth',
'django.contrib.contenttypes',
'django.contrib.sessions',
'django.contrib.messages',
'django.contrib.staticfiles',
'myapp',
]
Now restart the python server and go to localhost:8000 . You will see below screen.
Browse the excel file user_data.xls (available in source code on github) and upload it.
After index view read the data from worksheet and render the page again, screen will look like below:
You can add validations to check file extension or file size. Refer the csv upload article for same.
After reading the content you can save it to Database or display it on html page.
In case of any issue comment below.
Host you Django App for free on PythonAnyWhere.
Useful links:
https://www.pyxll.com/blog/
