Upload data via Excel sheet using django-excel

Question

I building a website using Django and I would like to create a functionality in which an user via an excel sheet can upload information and populate the database. So I install the django-excel package and follow the example:

settings.py:

FILE_UPLOAD_HANDLERS = ("django_excel.ExcelMemoryFileUploadHandler",
                    "django_excel.TemporaryExcelFileUploadHandler")

urls.py:

url(r'^import_sheet/', views.import_sheet, name="import_sheet"),

views.py:

class UploadFileForm(forms.Form):
   file = forms.FileField()

def upload(request):
if request.method == "POST":
    form = UploadFileForm(request.POST, request.FILES)
    if form.is_valid():
        filehandle = request.FILES['file']
        return excel._make_response(filehandle.get_sheet(),"xslx",file_name="download")  
    else:
        form = UploadFileForm()
        return render(request,'upload_form.html',{
            'form': form,
            'title': 'Excel file upload',
            'header': 'Please choose a valid excel file'
        })

def import_sheet(request):
    if request.method == "POST":
        form = UploadFileForm(request.POST,
                          request.FILES)
        if form.is_valid():
            request.FILES['file'].save_to_database(
                model= quote_input,
                mapdict= ["value_date", "value", "type", 'name'])
            return HttpResponse("OK")
        else:
            return HttpResponseBadRequest()
    else:
        form = UploadFileForm()
    return render(
        request,
        'upload_form.html',
        {'form': form})

models.py:

class quote_input(models.Model):
    value_date  = models.DateField()
    value       = models.FloatField()
    type        = models.TextField(max_length=254)
    name        = models.TextField(max_length=254)
    # link
    fund            = models.ForeignKey(Fund)
    benchmark       = models.ForeignKey(Benchmark)
    factor          = models.ForeignKey(Factors)
    stress_factor   = models.ForeignKey(Stress_Factors)

When I try this code by uploading a basic excel sheet:

I got an OK (HttpResponse) but when I looked at the database, I have uploaded nothing.

Does someone have the same problem? or can provide another example that I can use? another methodology?

Another question: within the django-excel documentation, there is a reference about a function "choice_func" which is defined as a custom formatting function. Can anyone provide more information about this function?

There are a number of indentation issues with your code. Please fix them so that we can help you. — 2ps
– 2ps, Commented Jan 14, 2017 at 0:04

Gustavo Reyes · Accepted Answer · 2017-01-13 22:54:58Z

3

Here's a version of my code, I use xlrd library.

import re
import xlrd

from agencies.models import Invite
from extras.permissions import send_mail_wrapper
from sms.sms_api import send_sms


def handle_uploaded_file(request, f):
    book = xlrd.open_workbook(file_contents=f.read())
    for sheet in book.sheets():
        number_of_rows = sheet.nrows
        number_of_columns = sheet.ncols

        for row in range(1, number_of_rows):

            first_name = (sheet.cell(row, 0).value)
            last_name = (sheet.cell(row, 1).value)
            email = (sheet.cell(row, 2).value)

            phone_cell = (sheet.cell(row, 3).value)
            if phone_cell:
                phone = re.sub("[^0-9]", "", str(phone_cell))
            else:
                phone = ""
            gender = (sheet.cell(row, 4).value).upper()

            if email != "":
                invite, created = Invite.objects.get_or_create(agency=request.user.agency, email=email)

                if email and created:
                    send_sms(phone)
                    send_mail_wrapper(
                        "You \'ve been invited", 'email/invite_email.html',
                        {}, [email], from_email='Casey <[email protected]>')

And the view:

class StudentBulkAddView(ListView):
    model = Student
    template_name = 'student_add.html'

    @method_decorator(login_required)
    def dispatch(self, *args, **kwargs):
        return super(DashboardStudentBulkAddView, self).dispatch(*args, **kwargs)

    def post(self, request):
        if self.request.user.is_authenticated:
            try:
                handle_uploaded_file(request, request.FILES['student_file'])
                success = True

And the template:

                <form action="{% url 'students:student_dashboard_bulk_invite' %}" method="POST"
                      enctype="multipart/form-data">
                    <div class="form-settings notes-form">
                        {% csrf_token %}
                        {{ upload_form }}
                        <button id="invite-tutor-btn" class="btn btn-primary margin-top"
                                data-loading-text="Loading..." type="submit">Save
                        </button>
                    </div>
                </form>

answered Jan 13, 2017 at 22:54

Gustavo Reyes

1,3442 gold badges17 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Gilles Criton Over a year ago

Hi Gustavo, thank you for sharing your code. There is something that I don't understand, could you explain your code? Many thanks in advance

Gustavo Reyes Over a year ago

Sure, let me know.

Gilles Criton Over a year ago

Hi Gustavo, what is the purpose of @method_decorator? and DashboardStudentBulkAddView? It seems that you have only one table: Student. What part of you code upload into your tables? If you have managed several tables linked by some foreign keys. What would have you added to your code? How do you push data for a specific student? I mean, in your view, you have a condition that check user but I don't understand how you can fill in the database for a specific user ? (foreign key between user and Student?) A big thank you in advance for your help.

Gustavo Reyes Over a year ago

@method_decorator is just a way of verifying the user is logged in. DashboardStudentBulkAddView is the name I game to my view. In this case I'm actually using just one model, the function of this code is to create invites per user , but the same approach can be used for multiple models as well, you just have to create the foreign key model instead.

Gilles Criton Over a year ago

Hi Gustavo, thank you very much for your answer. two last questions: where did you put handle_uploaded_file? in view.py, in forms.py? I don't see where in your code you save information. Have you built a specific function for that?

|

2ps · Accepted Answer · 2017-01-13 23:53:10Z

3

I think all you need to do is this:

def upload(request):
    form = None
    if request.method == "POST":
        form = UploadFileForm(request.POST, request.FILES)
        if form.is_valid():
            filehandle = request.FILES['file']
            # call import_sheet to import the sheet into your database
            return import_sheet(request)  
    form = form or UploadFileForm()
    return render(request,'upload_form.html',{
        'form': form,
        'title': 'Excel file upload',
        'header': 'Please choose a valid excel file'
    })

answered Jan 13, 2017 at 23:53

2ps

16k2 gold badges34 silver badges53 bronze badges

Collectives™ on Stack Overflow

Upload data via Excel sheet using django-excel

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related