Support
Please support this website. Visit the Amazon using this affiliate link. There won't be any difference in your purchage, we will get some commission for every purchase you make.
Advertise with us
csv upload excel   4   95754
How to upload and process the Excel file in Django

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.


how to upload and process the excel file in django


Uploading Excel file:


URLs:
Add a URL in urls.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']


You can either iterate over the sheet names or can get desired sheet by sheet name. To get the active sheet just use the 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 }}&nbsp;&nbsp;
            {% 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.

how to upload and process the excel file in django


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:

how to upload and process the excel file in django 

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/tools-for-working-with-excel-and-python/

csv upload excel   4   95754
4 comments on 'How To Upload And Process The Excel File In Django'
Login to comment

Alex July 26, 2018, 12:10 p.m.
How to save data to DB?
Roshan Pandey Aug. 7, 2019, 6:27 p.m.
Exception Value: File is not a zip file The above code is showing this result
Venu Aug. 9, 2020, 2:46 p.m.
Hi, This is very nice and excellent. Thank you very much. May I know how to do the same using ajax in django template.
Alex Sept. 5, 2020, 3:12 p.m.
Great article. Thanks. But how do I upload a new or existing excel file to a django app for editing ?

Related Articles:
How to download large csv files in Django
How to download large csv file in Django, streaming the response, streaming large csv file in django, downloading large data in django without timeout, using django.http.StreamingHttpResponse to stream response in Django, Generating and transmitting large CSV files in django...
How to validate an uploaded image in Python Django
Validating image before storing it in database or on file storage. Checking the size of uploaded image in python django. Checking the extension of uploaded image in python django. Checking the content size of uploaded image. Checking the mime-type of uploaded Image. Validating a malicious image before saving it. Using python-magic package...
How to upload an Image file in Django
This article explains the simple steps of uploading and storing an image in Django application, After storing the image, how to use it in Django template or emails, Uploading a file in Django, Storing image in Django model, Uploading and storing the image in Django model, HTML for multipart file upload...
Uploading a file to FTP server using Python
Uploading files to FTP server using Python, Python script to connect to ftp server, Python code to login to FTP server and upload file, How to connect to FTP server using python code, ftplib in python, Get server file listing using ftplib in python...
DigitalOcean Referral Badge

© 2022-2023 Python Circle   Contact   Sponsor   Archive   Sitemap