Loading and parse xlsx file

Question

How can I continue working with the downloaded file? parse it into the model

views.py

def model_form_upload(request):
if request.method == 'POST':
    form = DocumentForm(request.POST, request.FILES)
    if form.is_valid():
        form.save()
        return redirect('home')
else:
    form = DocumentForm()
return render(request, 'core/model_form_upload.html', {
    'form': form
})

my model.py

class Document(models.Model):
description = models.CharField(max_length=255, blank=True)
document = models.FileField(upload_to='documents/')
uploaded_at = models.DateTimeField(auto_now_add=True)

class Parameters(models.Model):
par_time = models.DateTimeField('date')
par_recipe = models.CharField(max_length=200)
par_machine = models.CharField(max_length=200)
par_fa = models.CharField(max_length=200)
par_ag_typ = models.CharField(max_length=200)
par_rollennr = models.IntegerField(default=0)
par_definition_id = models.IntegerField(default=0)
par_name = models.CharField(max_length=200)
par_value = models.IntegerField(default=0)

Used for download Excel files (xlsx). Fields in xlsx are represented as described in the model.

As soon as I didn’t try, I’m new to Django and I’ve got nothing

Answer 1

First you need a library that reads XLSX files. I find openpxyl the easiest. Install it with pip install openpyxl.

To handle uploaded file, write a little function. Something like the the code below.

import openpyxl

def handle_parameters_upload(request, file):

    wb = openpyxl.load_workbook(file, read_only=True)
    first_sheet = wb.get_sheet_names()[0]
    ws = wb.get_sheet_by_name(first_sheet)

    data = []

    # Loop over rows and create `Parameters()` objects with column data
    # row_offset=1 is used if you have a header row in your file, you want to skip it 
    # otherwise use 0 or delete the row_offset parameter, since 0 is default
    for row in ws.iter_rows(row_offset=1):
        parameter = Parameters()
        parameter.par_time = row[0].value
        parameter.par_recipe = row[1].value
        parameter.par_machine = row[2].value
        parameter.par_fa = row[3].value
        parameter.par_ag_typ = row[4].value
        parameter.par_rollennr = row[5].value
        parameter.par_definition_id = row[6].value
        parameter.par_name = row[7].value
        parameter.par_value = row[8].value

        data.append(parameter)

    # Bulk create data
    Parameters.objects.bulk_create(data)

    return True

This will open XLSX file, read data from it and save to database. bulk_create() is used, because it inserts objects in the database in an efficient manner.

Adjust your model_form_upload() view.

def model_form_upload(request):
    if request.method == 'POST':
        form = DocumentForm(request.POST, request.FILES)
        if form.is_valid():
            form.save()
            handle_file = handle_parameters_upload(request, request.FILES['document'])
            return redirect('home')
    else:
        form = DocumentForm()
    return render(request, 'core/model_form_upload.html', {'form': form})

There are also other libraries to read/write XLSX you can use. I often use pyexcel as well.