how-to csv upload excel   1   2164
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.


Screenshot from


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.

Screenshot from


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:

Screenshot from 

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.

how-to csv upload excel   1   2164

Related Articles:
Python Script 3: Validate, format and Beautify JSON string Using Python
Validating json using python code, format and beautify json file using python, validate json file using python, how to validate, format and beautify json...
Encryption-Decryption in Python Django
How to encrypt and decrypt the content in DJango. Encrypting the critical information in Django App. Encrypting username, email and password in Django...
Adding Robots.txt file to Django Application
Adding robots.txt file in your Django application. Easiest way to add robots.txt file. Django application robots.txt file. Why should you add robots.txt file in your Django Application....
Scraping 10000 tweets in 60 seconds using celery, RabbitMQ and Docker cluster with rotating proxy
Scrapping large amount of tweets within minutes using celery, RabbitMQ and docker cluster. Scraping huge data quickly using docker cluster with TOR....

1 thought on 'How To Upload And Process The Excel File In Django'
Alex :
How to save data to DB?
Sachin Ranawat :
use django's default ORM
Alex :
Could you please share the code?

Leave a comment:


*All Fields are mandatory. **Email Id will not be published publicly.


SUBSCRIBE
Please subscribe to get the latest articles in your mailbox.



Recent Posts:






© pythoncircle.com 2018-2019